downloads | documentation | faq | getting help | mailing lists | reporting bugs | sites | links | my 
search for in the  
view the version of this page
Last updated: Thu, 21 Aug 2003

LXII. MySQL Functions


These functions allow you to access MySQL database servers. More information about MySQL can be found at

Documentation for MySQL can be found at


In order to have these functions available, you must compile PHP with MySQL support.


By using the --with-mysql[=DIR] configuration option you enable PHP to access MySQL databases.

In PHP 4, the option --with-mysql is enabled by default. To disable this default behavior, you may use the --without-mysql configure option. Also in PHP 4, if you enable MySQL without specifying the path to the MySQL install DIR, PHP will use the bundled MySQL client libraries. In Windows, there is no DLL, it's simply built into PHP 4. Users who run other applications that use MySQL (for example, auth-mysql) should not use the bundled library, but rather specify the path to MySQL's install directory, like so: --with-mysql=/path/to/mysql. This will force PHP to use the client libraries installed by MySQL, thus avoiding any conflicts.

In PHP 5, MySQL is no longer enabled by default, nor is the MySQL library bundled with PHP. Read this FAQ for details on why.

This MySQL extension will not work with MySQL versions greater than 4.1.0. For that, use MySQLi.


Crashes and startup problems of PHP may be encountered when loading this extension in conjunction with the recode extension. See the recode extension for more information.

Note: If you need charsets other than latin (default), you have to install external (not bundled) libmysql with compiled charset support.

Runtime Configuration

The behaviour of these functions is affected by settings in php.ini.

Table 1. MySQL Configuration Options

For further details and definition of the PHP_INI_* constants see ini_set().

Here's a short explanation of the configuration directives.

mysql.allow_persistent boolean

Whether to allow persistent connections to MySQL.

mysql.max_persistent integer

The maximum number of persistent MySQL connections per process.

mysql.max_links integer

The maximum number of MySQL connections per process, including persistent connections.

mysql.default_port string

The default TCP port number to use when connecting to the database server if no other port is specified. If no default is specified, the port will be obtained from the MYSQL_TCP_PORT environment variable, the mysql-tcp entry in /etc/services or the compile-time MYSQL_PORT constant, in that order. Win32 will only use the MYSQL_PORT constant.

mysql.default_socket string

The default socket name to use when connecting to a local database server if no other socket name is specified.

mysql.default_host string

The default server host to use when connecting to the database server if no other host is specified. Doesn't apply in safe mode.

mysql.default_user string

The default user name to use when connecting to the database server if no other name is specified. Doesn't apply in safe mode.

mysql.default_password string

The default password to use when connecting to the database server if no other password is specified. Doesn't apply in safe mode.

mysql.connect_timeout integer

Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.

Resource Types

There are two resource types used in the MySQL module. The first one is the link identifier for a database connection, the second a resource which holds the result of a query.

Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

Since PHP 4.3.0 it is possible to specify additional client flags for the mysql_connect() and mysql_pconnect() functions. The following constants are defined:

Table 2. MySQL client constants

MYSQL_CLIENT_COMPRESSuse compression protocol
MYSQL_CLIENT_IGNORE_SPACEAllow space after function names
MYSQL_CLIENT_INTERACTIVEAllow interactive_timeout seconds (instead of wait_timeout) of inactivity before closing the connection.

The function mysql_fetch_array() uses a constant for the different types of result arrays. The following constants are defined:

Table 3. MySQL fetch constants

MYSQL_ASSOC Columns are returned into the array having the fieldname as the array index.
MYSQL_BOTH Columns are returned into the array having both a numerical index and the fieldname as the array index.
MYSQL_NUM Columns are returned into the array having a numerical index to the fields. This index starts with 0, the first field in the result.


This simple example shows how to connect, execute a query, print resulting rows and disconnect from a MySQL database.

Example 1. MySQL extension overview example

    /* Connecting, selecting database */
    $link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
        or die("Could not connect : " . mysql_error());
    print "Connected successfully";
    mysql_select_db("my_database") or die("Could not select database");

    /* Performing SQL query */
    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed : " . mysql_error());

    /* Printing results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        print "\t</tr>\n";
    print "</table>\n";

    /* Free resultset */

    /* Closing connection */

Table of Contents
mysql_affected_rows -- Get number of affected rows in previous MySQL operation
mysql_change_user --  Change logged in user of the active connection
mysql_client_encoding -- Returns the name of the character set
mysql_close -- Close MySQL connection
mysql_connect -- Open a connection to a MySQL Server
mysql_create_db -- Create a MySQL database
mysql_data_seek -- Move internal result pointer
mysql_db_name -- Get result data
mysql_db_query -- Send a MySQL query
mysql_drop_db -- Drop (delete) a MySQL database
mysql_errno --  Returns the numerical value of the error message from previous MySQL operation
mysql_error --  Returns the text of the error message from previous MySQL operation
mysql_escape_string --  Escapes a string for use in a mysql_query.
mysql_fetch_array --  Fetch a result row as an associative array, a numeric array, or both.
mysql_fetch_assoc --  Fetch a result row as an associative array
mysql_fetch_field --  Get column information from a result and return as an object
mysql_fetch_lengths --  Get the length of each output in a result
mysql_fetch_object -- Fetch a result row as an object
mysql_fetch_row -- Get a result row as an enumerated array
mysql_field_flags --  Get the flags associated with the specified field in a result
mysql_field_len --  Returns the length of the specified field
mysql_field_name --  Get the name of the specified field in a result
mysql_field_seek --  Set result pointer to a specified field offset
mysql_field_table --  Get name of the table the specified field is in
mysql_field_type --  Get the type of the specified field in a result
mysql_free_result -- Free result memory
mysql_get_client_info -- Get MySQL client info
mysql_get_host_info -- Get MySQL host info
mysql_get_proto_info -- Get MySQL protocol info
mysql_get_server_info -- Get MySQL server info
mysql_info --  Get information about the most recent query
mysql_insert_id --  Get the ID generated from the previous INSERT operation
mysql_list_dbs --  List databases available on a MySQL server
mysql_list_fields -- List MySQL table fields
mysql_list_processes -- List MySQL processes
mysql_list_tables -- List tables in a MySQL database
mysql_num_fields -- Get number of fields in result
mysql_num_rows -- Get number of rows in result
mysql_pconnect --  Open a persistent connection to a MySQL server
mysql_ping -- Ping a server connection or reconnect if there is no connection
mysql_query -- Send a MySQL query
mysql_real_escape_string --  Escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection.
mysql_result -- Get result data
mysql_select_db -- Select a MySQL database
mysql_stat -- Get current system status
mysql_tablename -- Get table name of field
mysql_thread_id -- Return the current thread ID
mysql_unbuffered_query --  Send an SQL query to MySQL, without fetching and buffering the result rows

add a note add a note User Contributed Notes
MySQL Functions
gyohng at netscape dot net
21-Jun-2003 12:16
The following page contains a complete easy to read tutorial of MySQL programming with PHP.
imho at auspantheon dot com
07-Jun-2003 02:26
I wrote this function to replace mysql's <em>mysql --html</em> function.

It takes the result of a query, and creates the table.

Usage is as follows:

$result = mysql_query("SELECT col1,col2 FROM yourtable LIMIT 100")

function sqlDoTable ($result) {
    print "<table border=1 cellpadding=3 cellspacing=0>";
    // Print Table Title
    print "<tr>";
    for ($i=0;$i < mysql_num_fields($result);$i++) {
        print "<th>" . mysql_field_name($result, $i) . "</th>";
    print "</tr>\n";
    // Print table Content
    for ($i=0;$i < mysql_num_rows($result);$i++) {
        print "<tr>";

        $row = mysql_fetch_row($result);
        foreach ($row as $value) {
            // Make sure 0's are displayed
            if (is_numeric($value)) { $value = (int)$value; }

            // Make sure table is displayed correctly
            if (empty($value) && ($value !== 0)) { $value = "&nbsp;"; } else { $value = htmlspecialchars($value); }
            print "<td>$value</td>";

        print "</tr>\n";
    print "</table>";
leader at k2wrpg dot org
04-May-2003 06:27
While doing lots of mysql work remotely I got tired of having to upload my files over and over again to find problems in my sql queries. I was also having a hard time checking the data in the DB itself (especially when behind a firewall, ugh.) anyway I present to you a single file query analyzer. So I made an easier way. It not persistent so some complex statements using temp tables and such probably won't work, but for simple Selects, Updates, Inserts and Deletes, it works just fine. I would say it handles about 95% of the queries I want to run. Anyway I found it useful so I'll share. Just save the following code into a file named sqlquery.php, and put it on your server If you name it something else you'll have to change the target of the form. Also its totally insecure so if youe worried about eavesdroppers don't use it.


rather than post the code, here is a download:
Sparc at brturbo dot com
03-May-2003 05:35
At 25-Apr-2002 10:23 jeyoung at priscimon dot com said:
"If I establish two connections within the same PHP page, start a transaction in the first connection and execute an INSERT query in the second one, and rollback the transaction in the first connection, the INSERT query in the second connection is also rolled-back. "

As a matter of fact, you must call mysql_connect with the new_link parameter set to true to make a new connection, otherwise, you'll get the same resource id as returned by the first mysql_connect in the later connections.

andre dot hoogeland at zonnet dot nl
01-May-2003 06:13
To find the holes in a table like this:
id| myField
3 | 6
4 | 0
5 | 1
6 | 5
7 | 4

I came up with this function:

function findhole( $table, $field ) {
  $retval = 0;
  $qrytxt = "SELECT $field FROM $table ORDER BY $field ASC";
  $query  = mysql_query( $qrytxt );
  $current = mysql_fetch_array( $query, MYSQL_ASSOC );
  if( $current[$field] != 0 || mysql_num_rows( $query ) < 1 ) {
    // no initial values: return 0
    return $retval;
  } else {
    do {
      $next = mysql_fetch_array( $query, MYSQL_ASSOC );
      // if successor doesn't appear in the table
      if( $next[$field] != $current[$field] + 1) {
        $retval = $current[$field] + 1;
      } else {
        $current = $next;
    } while( true );
  return $retval;

And... to _fill_ the holes:

$fh = findhole( "myTable", "myField" ); // will return 2
mysql_query( "insert into myTable values( null, '$fh' )" );

I hope this is useful!
Regards, Andr?Hoogeland
soren at byu dot edu
15-Mar-2003 05:23
Let's say that you want to generate a MySQL password hash from a plain text password.  Normally, you would just submit the MySQL query "SELECT PASSWORD('password')", but if for some reason you can't access to MySQL database directly, then you can use the following function (translated right out of the MySQL source code):

function mysql_password($passStr) {
        $charArr = preg_split("//", $passStr);

        foreach ($charArr as $char) {
                if (($char == '') || ($char == ' ') || ($char == '\t')) continue;
                $charVal = ord($char);
                  $nr ^= ((($nr & 63) + $add) * $charVal) + ($nr << 8);
                $nr2 += ($nr2 << 8) ^ $nr;
                  $add += $charVal;

        return sprintf("%08x%08x", ($nr & 0x7fffffff), ($nr2 & 0x7fffffff));


<? print mysql_password("hello"); ?>



Which is the same result you get if you do "SELECT PASSWORD('hello')" directly in MySQL.  Hopefully you'll never be in a situation where you have to use this, but if you need it (like I did), it's here.
past at sbox dot tugraz dot at
22-Feb-2003 06:17
As MySQL docs say, RAND() is not very usefull for generation of randomized result orders.

But this worked for me on Linux, however:
Somewhere before:
"SELECT *, " RAND(".mt_rand(0,86622340).")*10000%100 AS randomvalue ORDER BY randomvalue"

The upper value for mt_rand() has to be Quite Big to see any effect on MySQL's RAND(). The exact number shouldn't be significant. Note the multiplication and modulo; MySQL seems to count steadily upwards when generating random numbers, so we take some numbers from between.
mijnpc at xs4all dot nl
21-Nov-2002 06:33
If you have a Windows machine running a webserver with PHP you don't need to install MySQL server to locally test scripts, if you are granted to establish a Secure Telnet connection (port 22) to the remote webserver.

To do this you need a Secure Telnet client, which supports port-forwarding.
Before you establish a connection, define the port-forward.
Forward local port 3306 to [name or ip of remote server]:3306
Make sure that local ports accept connections from other hosts
Save this session

Connect to remote server with username and password
Minimize the shell and that's it...

You can use the same username (and password) as if you were working on the remote server !
E.g. : $link = mysql_connect("localhost", "root", "") or die("no way jose");

You may get a shell-timeout after xx minutes depending on your remote server, just reconnect or press enter in the shell once in a while...

An example of a superb freeware Secure Telnet client is Putty : Putty :

This 'discovery' really has saved me a lot of time because I don't have to upload the scripts to the remote server time and time again, pressing [save] is enough, heh (-:
opaf at yahoo dot com
13-Nov-2002 11:56
Two notes about PHP and MySQL:

1. Get used to the syntax

$myquery = "<your query here>";
$result = mysql_query($myquery);

instead of

$result = mysql_query("<your query here>");

Some of the benefits of the proposed syntax are:

- abilty to perform a global search to your scripts nice and easy
- ability to easily output/debug your queries

2. The second note has more to do with MySQL:

Timestamp fields update automaticaly everytime you issue an Update query. Read the documentation!!

I lost 3 days of my life because of this
16-Jun-2002 02:38
Regarding transactions, you must use a recent MySQL version which supports InnoDB tables. you should read the mysql manual (the part about Innodb tables, section 7.5) and configure your server to use them.
Some reading about how it works:$1446?mode=topic
(Click where it says Part2, I can't put the direct URL here because it is too long)

Then in PHP you use commands like:


You must make sure that you convert your existing tables to innodb or create new ones: CREATE TABLE (...) type=innodb;
vinod at jobsure dot com
09-Jun-2002 04:22
To protect your mysql server from long running query which hangs the database

This PHP code basically kills the long running sql process .

I kept this PHP file in cron to run every 15 minues and believe me
it had dramatic performance on my website and my mysql database NEVER HANGED

//################ //

//################ //


$result=mysql_query("show processlist");

while ($row=mysql_fetch_array($result))
if (($row["Time"] > 100 ) || ($row["Command"]=="Sleep") )
print $row["Id"];
$sql="kill $process_id";

ajenks at discoverymining dot com
05-Jun-2002 11:04
just a little FYI, when installing PHP from an RPM.  In order to get MySql support you need to download the php-mysql*.rpm.  If you don't install the php-mysql*.rpm you'll just get the common error: Call to undefined function: mysql_connect().
pvenable at cs dot cmu dot edu
31-May-2002 07:43
I had some difficulties installing PHP with MySQL support on RedHat-7.1, but it works at last. :)  Here are a few of the unexpected things I had to do to get it working:
After installing apache, mysql, and php4, I found a php-mysql rpm, since the php4 rpm lacks MySQL support.  (I also had to find a few more rpms before this one would install.) Then, I had to make sure and export the path of the library (export LD_LIBRARY_PATH=/usr/local/lib) before running the Apache httpd.  Otherwise PHP fails to load the MySQL library, which apparently depends on, but doesn't give much useful feedback except that the MySQL-specific commands you're using are unrecognized.  I hope this saves someone else from going through the hours of hassle it took me to figure it out!
jeyoung at priscimon dot com
25-Apr-2002 11:23
MySQL transactions

MySQL supports transactions on tables that are of type InnoDB. I have noticed a behaviour which is puzzling me when using transactions.

If I establish two connections within the same PHP page, start a transaction in the first connection and execute an INSERT query in the second one, and rollback the transaction in the first connection, the INSERT query in the second connection is also rolled-back.

I am assuming that a MySQL transaction is not bound by the connection within which it is set up, but rather by the PHP process that sets it up.

This is a very useful "mis-feature" (bug?) because it allows you to create something like this:

class Transaction {
  var $dbh;

  function Transaction($host, $username, $password) {
    $this->dbh = mysql_connect($host, $username, $password);

  function _Transaction() {

  function begin() {
    mysql_query("BEGIN", $this->dbh);

  function rollback() {
     mysql_query("ROLLBACK", $this->dbh);

  function commit() {
    mysql_query("COMMIT", $this->dbh);

which you could use to wrap around transactional statements like this:

$tx =& new Transaction("localhost", "username", "password");
$dbh = mysql_connect("localhost", "username", "password");
$result = mysql_query("INSERT ...");
if (!$result) {
} else {

The benefit of such a Transaction class is that it is generic and can wrap around any of your MySQL statements.
nospam at nospam dot nos
20-Nov-2001 01:17
ever wanted to know the date a table was last updated? use this:

$info = mysql_fetch_array(mysql_query("show table status from databasename like 'tablename'"));
echo $info["Update_time"];
skelley at diff dot nl
26-Sep-2001 04:11
Hi, here's a nice little trick to select records in random order from a table in a MySQL database prior to version 3.23

SELECT *, (ItemID/ItemID)*RAND() AS MyRandom FROM Items ORDER BY MyRandom

[[[Editors note:
And just SELECT * FROM foo WHERE bar = RAND() after 3.23
mbabcock-php at fibrespeed dot net
29-Jul-2001 09:41
Since there aren't functions to start and end/rollback transactions, you'll have to use mysql_query("BEGIN"), mysql_query("COMMIT") and mysql_query("ROLLBACK").  These will only work properly on tables that support transactions.  You may also wish to roll your own mysql_begin (etc) functions that run the above queries for you.
philip at cornado dot com
24-Jul-2001 02:24
If you're new to this, you really should learn basic SQL before moving on.  PHP != SQL. Here's are a few good basic SQL tutorials:

rupert at ditzed dot org
14-Jul-2001 03:23

The problem with
is that it actually fetches two arrays, a number index array ($arr[0], $arr[1], $arr[2], ...) and an associative (hash) array, ($arr["field1"], $arr["field2"], $arr["field3"], ...), thus $arr actually contains two sets of data (as you have observed).

If you then try to push out all the data from $arr without explicitly naming those field ids, then you WILL get your data appearing twice.

The full definition for mysql_fetch_array is as follows:
mysql_fetch_array ($result_id, [MYSQL_NUM|MYSQL_ASSOC])
Since MYSQL_NUM is equivalent to mysql_fetch_row, you have found that switching to mysql_fetch_row works fine.
In fact, the only real reason you may want to use mysql_fetch_array is with the MYSQL_ASSOC option, as this allows you to reference the fields in the associative array returned.
keith at keithtyler dot com
30-Jun-2001 01:41
It looks like the reason for the double vision is so that when you want to use the sql results, you can call the array elements via either indices (e.g. $line[0], $line[1], etc.) or by their database field names (e.g. $line["uid"], $line["pw"], $line["gid"], etc.).

You can see this by changing the example to print $col_name as well as $col_value. Each repeated val shows up with a different key (of course) each time.
davey at shakedownwizards dot com
30-Jun-2001 01:24
The mysql_fetch_row function returns an array with numeric indices.

For example, a row from the query:

SELECT bookid,title,author FROM books
would look like:

$record[1]="A Farewell To Arms"
$record[2]="Ernest Hemingway"

The mysql_fetch_assoc function returns an array with indices that are the field names, like such:

$record["title"]="A Farewell To Arms"
$record["author"]="Ernest Hemingway"

The mysql_fetch_array function returns an array with both kinds of indices, so this array looks like:

$record[1]="A Farewell To Arms"
$record["title"]="A Farewell To Arms"
$record[2]="Ernest Hemingway"
$record["author"]="Ernest Hemingway"

This is the reason for the "doubled" field output from the example.

Note that the mysql_fetch_array function has an optional parameter where you can specify the indices.

So replacing the call:




would be an identical fix to:


Hope this helps!
mw-php at ender dot com
22-Jun-2001 11:11
The mysql_fetch_[row|object|array] functions return data as type string. Owing to the very flexible nature of php variables, this is normally not relevent, but if you happen to retrieve two integers from a database, then try to compare with bitwise operators, you'll run into trouble, because (19 & 2) == 2, but ("19" & "2") == 0. To remedy this, if you use variables from a database with bitwise operators, use the settype() function to explicitly cast your variables as integers before comparing.
jcn at iki dot fi dot no_spam
01-May-2001 08:37
For one solution to problems with MySQL and special Nordic characters, please see
mwalkup at morningstarstudios dot net
05-Apr-2001 04:04
Just a note, when using MySQL to output files (ie: a database backup script) if the directory permissions are not set right, it will not create the files AND WILL NOT return an error code.  You must have the permissions set right.
16-Feb-2001 04:51
A good place to get beginner's info and some good pointers on using MySQL is:
craig at chiid dot ie
22-Dec-2000 02:17
Something to Note:

MySQL uses Unix time functions and has no problems with dates until 2069; all 2-digit years are regarded to be in the range 1970 to 2069, which means that if you store 01 in a year column, MySQL treats it as 2001.
laura at freschinfo dot com
22-Mar-2000 06:38
The easy way to surpress MySQL error messages is to put an "@" in front of the mysql command.  For example, @mysql_db_query(whatever).

<P>This also works for any other PHP functions. (ed.)
steer at projex dot hu
07-Mar-2000 03:53
A quick way to extract a mysql query result into variables, without using an array:

$result = mysql_query("select model,color,price from cars where id=$id");


Now you have the variables $model, $color, $price. You can ask extract() to prefix it for you, with like extract(stuff,EXTR_PREFIX_ALL,"r"), and right there you have $r_model, $r_color...

If you have to pull multiple rows from the query, e.g. using "while", you have to pull 'em to an array, but still can use extract() to extract them :)

while ($r = mysql_fetch_array($result)) {

 Last updated: Thu, 21 Aug 2003
show source | credits | sitemap | mirror sites 
Copyright © 2001-2003 The PHP Group
All rights reserved.
This mirror generously provided by:
Last updated: Sat 01 Nov 2003 04:13:36 EST EST