What is PhpDig?
PhpDig is a PHP MySQL based
Web Spider & Search Engine.

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

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:

  • Connect to a database server.

  • Select a database to work with.

  • Query tables within the selected database.


    Successful queries don't return the results of the query directly - instead, they return a result handle. Other functions, such as mysql_result() and mysql_fetch_row() , use the result handle to retrieve the data.

  • Retrieve the query results.

  • Disconnect from the database server. (This step is optional; PHP manages MySQL connections automatically, closing them as needed.)

The following script illustrates this flow:

// 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);


Unless otherwise noted, all mysql_*() functions take an optional connection argument. If no connection argument is given, the last connection opened is used by default. If no connection is open, the function attempts to connect to a MySQL database by calling mysql_connect() without arguments.


Effective use of the MySQL functions depends very heavily on having a good knowledge of SQL. An excellent resource on SQL is "SQL-99 Complete, Really" by Peter Gulutzan and Trudy Pelzer (CMP Books, 1999).

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

  login         CHAR (16) NOT NULL,
  password      CHAR (16) NOT NULL,
  PRIMARY KEY (id));

Many of the examples use this include file:

// filename ''
// 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.

Directive Name Value Type Description
mysql.allow_persistent boolean (on/off)

Enable or disable persistent MySQL connections (see mysql_pconnect() ).


Persistent connections don't work for command-line and CGI API scripts.

mysql.default_host string The default host for calls to mysql_connect() and mysql_pconnect() .
mysql.default_password string The default password for calls to mysql_connect() and mysql_pconnect() .
mysql.default_port integer The default port for calls to mysql_connect() and mysql_pconnect() .
mysql.default_user string The default user for calls to mysql_connect() and mysql_pconnect() .
mysql.default_socket string The default socket for calls to mysql_connect() and mysql_pconnect() . (Added in version PHP 3.0.10.)
mysql.max_links integer The maximum number of MySQL connections (including persistent connections) allowed per process.
mysql.max_persistent integer The maximum number of persistent MySQL connections allowed per process.
sql.safe_mode boolean (on/off)

If sql.safe_mode is enabled, mysql_connect() and mysql_pconnect() ignore any arguments passed to them. Instead, PHP attempts to connect using the following details:

  • host: local host

  • user: the user PHP runs as

  • password: an empty string ("")

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.


Users building PHP as an Apache module should avoid using PHP's built-in MySQL client libraries.

If any other Apache modules use the MySQL client libraries (such as auth-mysql or mod-perl), there will be a conflict between the MySQL libraries provided with PHP and the MySQL libraries used by the other modules. To avoid the conflict, configure PHP using the --with-mysql= /path/to/mysql/libraries option.

Additional InformationFor more information on MySQL, see

  • The MySQL Web site (

  • "MySQL" by Paul DuBois (New Riders, 1999)

For more information on SQL, see

  • "SQL-99 Complete, Really" by Peter Gulutzan and Trudy Pelzer (CMP Books, 1999)

PHP Functions Essential Reference. Copyright © 2002 by New Riders Publishing (Authors: Zak Greant, Graeme Merrall, Torben Wilson, Brett Michlitsch). This material may be distributed only subject to the terms and conditions set forth in the Open Publication License, v1.0 or later (the latest version is presently available at The authors of this book have elected not to choose any options under the OPL. This online book was obtained from and is designed to provide information about the PHP programming language, focusing on PHP version 4.0.4 for the most part. The information is provided on an as-is basis, and no warranty or fitness is implied. All persons and entities shall have neither liability nor responsibility to any person or entity with respect to any loss or damage arising from the information contained in this book.

Powered by: vBulletin Version 3.0.7
Copyright ©2000 - 2005, Jelsoft Enterprises Ltd.
Copyright © 2001 - 2005, ThinkDing LLC. All Rights Reserved.