MySQL Functions

The MySQL functions provide a set of tools for working with the popular MySQL database.

OverviewMySQL is a popular, speedy, and robust Open Source database. To be more specific, it's an SQL Relational Database Management System (RDBMS) optimized for light-to-midweight database applications. For more information on MySQL, visit http://www.mysql.com.

MySQL is the database most commonly used with PHP. Its speed, reliability, and ease of use make it an excellent choice for building Web-based applications. Additional features such as a platform-independent data format, ODBC support, and a rich set of built-in functions round out the usefulness of the database.

Because MySQL is the most commonly used database with PHP and is often the first database encountered by PHP users new to databases, we have given it a fairly rigorous writeup.

How the MySQL Functions WorkUsing MySQL from within PHP is a fairly simple business. The general flow of usage is as follows:

The following script illustrates this flow:

<pre>
<?php
// Attempt to connect to the default database server
// An ID that refers to the connection opened is stored in $mysql_link
$mysql_link = mysql_connect ()
   or die ("Could not connect to the default MySQL database.");

$db = 'some_db';

// Set the active database that will be used when making queries
mysql_select_db ($db, $mysql_link)
   or die ("Could not set database '$db' as the active database.");

// Write a SQL query and store it in a variable to aid debugging
$query = "SELECT * FROM user";

// Run the query
// In the case of SELECT queries, mysql_query() returns a 
// result handle that points to the query result
// If the query fails, the error message can be retrieved by calling mysql_error()
$mysql_result = mysql_query ($query, $mysql_link)
   or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');

// Traverse the $mysql_result result handle using mysql_fetch_assoc()
// mysql_fetch_assoc() grabs a row from the result handle and returns
// an associative array that uses field names as keys for the array
while ($row = mysql_fetch_assoc($mysql_result)) {

    // Use print_r() to quickly show what is contained in $row
    print_r ($row);
}
?>
</pre>

Notes on the ExamplesThe following examples are based on this simple table:

CREATE TABLE user
(
  id            MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  login         CHAR (16) NOT NULL,
  password      CHAR (16) NOT NULL,
  PRIMARY KEY (id));
)

Many of the examples use this include file:

<?php
// filename 'mysql_connect.inc.php'
// Connect to a MySQL server and select a database

$host = 'localhost';
$user = '';
$pass = '';

$db = 'some_db';

$mysql_link = mysql_pconnect ($host, $user, $pass)
   or die ("Could not connect to the MySQL server located at '$host' as user '$user'.");

mysql_select_db ($db, $mysql_link)
   or die ("Could not set database '$db' as the active database.");
?>

Configuring MySQLThe following configuration directives can be used to control the behavior of the MySQL functions.

Installing MySQL SupportTo use PHP's built-in client libraries for MySQL support, use the --with-mysql configure option.

To use the MySQL libraries (instead of the libraries included with PHP), set the --with-mysql= /path/to/mysql/libraries configure option.

Windows users: The Win32 binaries of PHP have MySQL support built in. No external extensions are needed.

Additional InformationFor more information on MySQL, see

For more information on SQL, see