Using MySQL with PHP

This chapter describes:

• How to configure PHP with a MySQL server.
• What are the basic functions to use a MySQL server.
• A test script with a MySQL server.

Configuring PHP with MySQL

To use PHP with MySQL, of course you need to install MySQL server on you system first. On my local system, I have MySQL server installed in \mysql directory. If you need help on installing MySQL on your system, please read "Herong's Notes on SQL".

To make sure MySQL is running on my local system, I did this in a command window:

>\mysql\bin\mysqld 

>\mysql\bin\mysqladmin ping
mysqld is alive

Now edit \php\php.ini with:

extension=php_mysql.dll

Then add \php\ext to the PATH system environment variable.

I think we are ready to test the configuration. Run this script:

<?php # MySqlTest.php
# Copyright (c) 2002 by Dr. Herong Yang, http://www.herongyang.com/
# 
   $con = mysql_connect('localhost');
   print "MySQL server info = ".mysql_get_server_info()."\n";
   print "MySQL status = ".mysql_stat()."\n";
   mysql_close($con);
?>

You should get something like:

C:\herong\php_20050403\src>php MySqlTest.php
MySQL server info = 5.0.2-alpha
MySQL status = Uptime: 1167  Threads: 1  Questions: 5  Slow querie...
  Flush tables: 1  Open tables: 0  Queries per second avg: 0.004

Cool, I can now access my MySQL server from PHP scripts.

PHP's MySQL Support

PHP's MySQL support comes from an extension library, php_mysql.dll, which offeres a number of functions:

• mysql_connect() - Connects to a MySQL server, and returns a connection resource.
• mysql_close() - Closes a MySQL connection resource.
• mysql_get_server_info() - Returns a string of server information.
• mysql_status() - Returns a string of server status.
• mysql_query() - Sends a query to the server, and returns a result set resource.
• mysql_affected_rows() - Returns the number of effected rows of the given result set, if the executed query is an INSERT or UPDATE statement.
• mysql_num_rows() - Returns the number of rows of the given result set, if the executed query is a SELECT statement.
• mysql_fetch_array() - Fetches a row from a given result set, and returns the row as an array with both numeric index, and column name map. It will return boolean false, if there is no row left in the result set.
• mysql_free_result() - Frees the given result set.

MySQL Test - MySqlLoop.php

To show you some those functions should be used, I wrote this simple script, MySqlLoop.php:

<?php # MySqlLoop.php
# Copyright (c) 2002 by Dr. Herong Yang
# 
   $con = mysql_connect('localhost');
   $rs = mysql_query('DROP DATABASE MyBase');
   $rs = mysql_query('CREATE DATABASE MyBase');
   $rs = mysql_query('USE MyBase');
   print "Creating a table...\n";
   $rs = mysql_query('CREATE TABLE MyTable (ID INTEGER,'
      .' Value INTEGER)');
   $n = 100;
   $i = 0;
   print "Inserting some rows to the table...\n";
   while ($i < $n) {
      $rs = mysql_query('INSERT INTO MyTable VALUES ('.$i.', '
         .rand(0,$n-1).')');
      $i++;
   }
   print "Query some rows from the table...\n";
   $rs = mysql_query('SELECT * FROM MyTable WHERE ID < 10');
   print "   ".mysql_field_name($rs,0)."   "
      .mysql_field_name($rs,1)."\n";
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."\n";
   }
   mysql_free_result($rs);   
   mysql_close($con);
?>

Note that if the connection resource is not specified in a query call, the last connection resource will be used. If you run this script, you will get something like:

Creating a table...
Inserting some rows to the table...
Query some rows from the table...
   ID   Value
    0   14
    1   91
    2   84
    3   16
    4   88
    5   51
    6   12
    7   19
    8   39
    9   5

Conclusion

• PHP supports MySQL through an extension library.
• Configuring PHP to with a MySQL server is simple.
• Database functions are server dependent.