DB Connection in PHP

Falcon · #41 (**permalink**) 04-17-2008, 04:32 AM

Hi

The example code for the mysql_thread_id() is given below

PHP Code:

  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$thread_id = mysql_thread_id($link);

if ($thread_id){

    printf("current thread id is %d\n", $thread_id);

}

?>

Regards
Falcon

Falcon · #42 (**permalink**) 04-17-2008, 04:33 AM

Hi

mysql_tablename ()
This function deprecated. It is preferable to use mysql_query() to issue a SQL SHOW TABLES [FROM db_name] [LIKE 'pattern'] statement instead.

Regards
Falcon

Falcon · #43 (**permalink**) 04-17-2008, 04:34 AM

Hi

Example for the mysql_tablename() is given below

PHP Code:

  <?php

mysql_connect("localhost", "mysql_user", "mysql_password");

$result = mysql_list_tables("mydb");

$num_rows = mysql_num_rows($result);

for ($i = 0; $i < $num_rows; $i++) {

    echo "Table: ", mysql_tablename($result, $i), "\n";

}

mysql_free_result($result);

?>

Regards
Falcon

Falcon · #44 (**permalink**) 04-17-2008, 05:08 AM

Hi
mysql_stat()
mysql_stat() returns the current server status.
for example

PHP Code:

  <?php

$link   = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$status = explode('  ', mysql_stat($link));

print_r($status);

?>

OutPut will be

Code:

Array
(
    [0] => Uptime: 5380
    [1] => Threads: 2
    [2] => Questions: 1321299
    [3] => Slow queries: 0
    [4] => Opens: 26
    [5] => Flush tables: 1
    [6] => Open tables: 17
    [7] => Queries per second avg: 245.595
)

Regards
Falcon

Falcon · #45 (**permalink**) 04-17-2008, 05:12 AM

Hi

Alternative mysql_stat() example

PHP Code:

  <?php

$link   = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$result = mysql_query('SHOW VARIABLES', $link);

while ($row = mysql_fetch_assoc($result)) {

    echo $row['Variable_name'] . ' = ' . $row['Value'] . "\n";

}

?>

OutPut Will be

Code:

back_log = 50
basedir = /usr/local/
bdb_cache_size = 8388600
bdb_log_buffer_size = 32768
bdb_home = /var/db/mysql/
bdb_max_lock = 10000
bdb_logdir = 
bdb_shared_data = OFF
bdb_tmpdir = /var/tmp/
...

Regards
Falcon

Jeyaseelansarc · #46 (**permalink**) 04-18-2008, 10:41 PM

mysql_list_dbs() lists databases available on a MySQL server

For e.g

PHP Code:

  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$db_list = mysql_list_dbs($link);

 
while ($row = mysql_fetch_object($db_list)) {

     echo $row->Database . "\n";

}

?>

Jeyaseelansarc · #47 (**permalink**) 04-18-2008, 10:42 PM

mysql_db_name() retrieve the database name from a call to mysql_list_dbs().

We can use this function as follows

PHP Code:

  <?php

error_reporting(E_ALL);

 
$link = mysql_connect('dbhost', 'username', 'password');

$db_list = mysql_list_dbs($link);

 
$i = 0;

$cnt = mysql_num_rows($db_list);

while ($i < $cnt) {

    echo mysql_db_name($db_list, $i) . "\n";

    $i++;

}

?>

Jeyaseelansarc · #48 (**permalink**) 04-21-2008, 02:11 AM

mysql_list_fields() Retrieves information about the given table name.

This function is deprecated. It is preferable to use mysql_query() to issue a SQL SHOW COLUMNS FROM table [LIKE 'name'] statement instead.

Jeyaseelansarc · #49 (**permalink**) 04-21-2008, 02:12 AM

Alternate to deprecated mysql_list_fields()

PHP Code:

  <?php

$result = mysql_query("SHOW COLUMNS FROM sometable");

if (!$result) {

    echo 'Could not run query: ' . mysql_error();

    exit;

}

if (mysql_num_rows($result) > 0) {

    while ($row = mysql_fetch_assoc($result)) {

        print_r($row);

    }

}

?>

Jeyaseelansarc · #50 (**permalink**) 04-21-2008, 02:14 AM

In the above way we can use mysql_list_processes() to list various MySQL processes

For example:

PHP Code:

  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

 
$result = mysql_list_processes($link);

while ($row = mysql_fetch_assoc($result)){

    printf("%s %s %s %s %s\n", $row["Id"], $row["Host"], $row["db"],

        $row["Command"], $row["Time"]);

}

mysql_free_result($result);

?>

Jeyaseelansarc · #51 (**permalink**) 04-22-2008, 01:53 AM

here the example for mysql_list_tables() function

PHP Code:

  <?php

$dbname = 'mysql_dbname';

 
if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {

    echo 'Could not connect to mysql';

    exit;

}

 
$sql = "SHOW TABLES FROM $dbname";

$result = mysql_query($sql);

 
if (!$result) {

    echo "DB Error, could not list tables\n";

    echo 'MySQL Error: ' . mysql_error();

    exit;

}

 
while ($row = mysql_fetch_row($result)) {

    echo "Table: {$row[0]}\n";

}

 
mysql_free_result($result);

?>

Jeyaseelansarc · #52 (**permalink**) 04-23-2008, 01:42 AM

Oracle functions allow you to access Oracle 10, Oracle 9, Oracle 8 and Oracle 7 databases using the Oracle Call Interface (OCI). They support binding of PHP variables to Oracle placeholders, have full LOB, FILE and ROWID support, and allow you to use user-supplied define variables.

Jeyaseelansarc · #53 (**permalink**) 04-23-2008, 01:43 AM

You will need the Oracle client libraries to use this extension. Windows users will need at least Oracle version 8.1 to use the php_oci8.dll dll.

The most convenient way to install all the required files is to use Oracle Instant Client, which is available from here: Instant Client. Instant Client does not need ORACLE_SID or ORACLE_HOME environment variables set. You still may need to set LD_LIBRARY_PATH and NLS_LANG, though.

Before using this extension, make sure that you have set up your Oracle environment variables properly for the Oracle user, as well as your web daemon user. These variables should be set up before you start your web-server. The variables you might need to set are as follows:

ORACLE_HOME
ORACLE_SID
LD_PRELOAD
LD_LIBRARY_PATH
NLS_LANG

For less frequently used Oracle environment variables such as TNS_ADMIN, TWO_TASK, ORA_TZFILE, and the various Oracle globalization settings like ORA_NLS33, ORA_NLS10 and the NLS_* variables refer to Oracle documentation.

Jeyaseelansarc · #54 (**permalink**) 04-23-2008, 01:44 AM

After setting up the environment variables for your webserver user, be sure to also add the webserver user (nobody, www) to the oracle group.

If your webserver doesn't start or crashes at startup: Check that Apache is linked with the pthread library:

Code:

# ldd /www/apache/bin/httpd
    libpthread.so.0 => /lib/libpthread.so.0 (0x4001c000)
    libm.so.6 => /lib/libm.so.6 (0x4002f000)
    libcrypt.so.1 => /lib/libcrypt.so.1 (0x4004c000)
    libdl.so.2 => /lib/libdl.so.2 (0x4007a000)
    libc.so.6 => /lib/libc.so.6 (0x4007e000)
    /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)

If the libpthread is not listed you have to reinstall Apache:

Code:

# cd /usr/src/apache_1.3.xx
# make clean
# LIBS=-lpthread ./config.status
# make
# make install

Please note that on some systems, like UnixWare it is libthread instead of libpthread. PHP and Apache have to be configured with EXTRA_LIBS=-lthread.

Jeyaseelansarc · #55 (**permalink**) 04-23-2008, 01:46 AM

The oci8 extension provides you with 3 different functions for connecting to Oracle. It is up to you to use the most appropriate function for your application, and the information in this section is intended to help you make an informed choice.

Connecting to an Oracle server is a reasonably expensive operation, in terms of the time that it takes to complete. The oci_pconnect() function uses a persistent cache of connections that can be re-used across different script requests. This means that you will typically only incur the connection overhead once per php process (or apache child).

Jeyaseelansarc · #56 (**permalink**) 04-23-2008, 01:46 AM

If your application connects to Oracle using a different set of credentials for each web user, the persistent cache employed by oci_pconnect() will become less useful as the number of concurrent users increases, to the point where it may start to adversely affect the overall performance of your Oracle server due to maintaining too many idle connections. If your application is structured in this way, it is recommended that you either tune your application using the oci8.max_persistent and oci8.persistent_timeout configuration settings (these will give you control over the persistent connection cache size and lifetime) or use oci_connect() instead.

Jeyaseelansarc · #57 (**permalink**) 04-23-2008, 01:47 AM

Both oci_connect() and oci_pconnect() employ a connection cache; if you make multiple calls to oci_connect(), using the same parameters, in a given script, the second and subsequent calls return the existing connection handle. The cache used by oci_connect() is cleaned up at the end of the script run, or when you explicitly close the connection handle. oci_pconnect() has similar behaviour, although its cache is maintained separately and survives between requests.

Jeyaseelansarc · #58 (**permalink**) 04-23-2008, 01:47 AM

This caching feature is important to remember, because it gives the appearance that the two handles are not transactionally isolated (they are in fact the same connection handle, so there is no isolation of any kind). If your application needs two separate, transactionally isolated connections, you should use oci_new_connect().

oci_new_connect() always creates a new connection to the Oracle server, regardless of what other connections might already exist. High traffic web applications should try to avoid using oci_new_connect(), especially in the busiest sections of the application.

Jeyaseelansarc · #59 (**permalink**) 04-23-2008, 11:58 PM

In Oracle oci_connect() returns a connection identifier needed for most other OCI calls. The optional third parameter can either contain the name of the local Oracle instance or the name of the entry in tnsnames.ora to which you want to connect. If the optional third parameter is not specified, PHP uses the environment variables ORACLE_SID (Oracle instance) or TWO_TASK (tnsnames.ora) to determine which database to connect to.