In-Depth

Access MySQL Database With PHP

Use the PHP extension for MySQL to access data from the MySQL database.

• By Deepak Vohra
• 06/20/2007

The MySQL database is the most commonly used open source relational database. It supports different data types in these categories: numeric, date and time, and string. The numeric data types include BIT, TINYINT, BOOL, BOOLEAN, INT, INTEGER, BIGINT, DOUBLE, FLOAT and DECIMAL. The date and time data types include DATE, DATETIME, TIMESTAMP and YEAR. The string data types include CHAR, VARCHAR, BINARY, ASCII, UNICODE, TEXT and BLOB. In this article, you will learn how you can access these data types with PHP scripting language — taking advantage of PHP 5's extension for the MySQL database.

Install MySQL Database
To install the MySQL database, you must first download the community edition of MySQL 5.0 database for Windows. There are three versions: Windows Essentials (x86), Windows (x86) ZIP/Setup.EXE and Without installer (unzip in C:\). To install the Without installer version, unzip the zip file to a directory. If you've downloaded the zip file, extract it to a directory. And, if you've downloaded the Windows (x86) ZIP/Setup.EXE version, extract the zip file to a directory. (See Resources.)

Next, double-click on the Setup.exe application. You will activate the MySQL Server 5.0 Setup wizard. In the wizard, select the Setup Type (the default setting is Typical), and click on Install to install MySQL 5.0.

In the Sign-Up frame, create a MySQL account, or select Skip Sign-Up. Select "Configure the MySQL Server now" and click on Finish. You will activate the MySQL Server Instance Configuration wizard. Set the configuration type to Detailed Configuration (the default setting).

If you're not familiar with MySQL database, select the default settings in the subsequent frames. By default, server type is set at Developer Machine and database usage is set at Multifunctional Database. Choose the drive and directory for the InnoDB tablespace. In the concurrent connections frame, select the DDSS/OLAP setting. Next, select the Enable TCP/IP Networking and Enable Strict Mode settings and use the 3306 port. Select the Standard Character Set setting and the Install As Windows Service setting with MySQL as the service name.

In the security options frame, you can specify a password for the root user (by default, the root user does not require a password). Next, uncheck Modify Security Settings and click on Execute to configure a MySQL Server instance. Finally, click on Finish.

If you've downloaded the Windows Installer Package application, double-click on the mysql-essential-5.0.x-win32.exe file. You will activate the MySQL Server Startup wizard. Follow the same process as Setup.exe.

After you've finished installing the MySQL database, log into the database with the MySQL command. In a command prompt window, specify this command:

>mysql -u root

The default user root will log in. A password is not required for the default user root:

>mysql -u <username> -p <password>

The MySQL command will display:

mysql>

To list the database instances in the MySQL database, specify this command:

mysql>show databases

By default, the test database will be listed. To use this database, specify this command:

mysql>use test

Install MySQL PHP Extension
The PHP extension for MySQL database is packaged with the PHP 5 download (see Resources). First, you need to activate the MySQL extension in the php.ini configuration file. Remove the ';' before this code line in the file:

extension=php_mysql.dll

Next, restart the Apache2 Web server.

PHP also requires access to the MySQL client library. The libmysql.dll file is included with the PHP 5 distribution. Add libmysql.dll to the Windows System PATH variable. The libmysql.dll file will appear in the C:/PHP directory, which you added to the System PATH when you installed PHP 5.

The MySQL extension provides various configuration directives for connecting with the database. The default connection parameters establish a connection with the MySQL database if a connection is not specified in a function that requires a connection resource and if a connection has not already been opened with the database.

The PHP class library for MySQL has various functions to connect with the database, create database tables and retrieve database data.

Create a MySQL Database Table
Now it's time to create a table in the MySQL database using the PHP class library. Create a PHP script named createMySQLTable.php in the C:/Apache2/Apache2/htdocs directory. In the script, specify variables for username and password, and connect with the database using the mysql_connect() function. The username root does not require a password. Next, specify the server parameter of the mysql_connect() method as localhost:3306:

$username='root';
$password='';
$connection = mysql_connect
('localhost:3306', $username, 
$password);

If a connection is not established, output this error message using the mysql_error() function:

if (!$connection) {
  $e = mysql_error($connection);  
  echo "Error in connecting to 
MySQL Database.".$e;}

You'll need to select the database in which a table will be created. Select the MySQL test database instance using the mysql_select_db() function:

$selectdb=mysql_select_db('test');

Next, specify a SQL statement to create a database table:

$sql="CREATE TABLE Catalog
(CatalogId VARCHAR(25) 
PRIMARY KEY, Journal 
VARCHAR(25), Publisher 
Varchar(25),Edition VARCHAR(25),
 Title Varchar(75), Author 
Varchar(25))";

Run the SQL statement using the mysql_query() function. The connection resource that you created earlier will be used to run the SQL statement:

$createtable=mysql_query 
($sql, $connection );

If a table is not created, output this error message:

if (!$createtable) {
  $e = mysql_error($connection);  
  echo "Error in creating 
Table.".$e;
}

Next, add data to the Catalog table. Create a SQL statement to add data to the database:

$sql = "INSERT INTO Catalog 
VALUES('catalog1', 'Oracle 
Magazine',  'Oracle Publishing',
 'July-August 2005', 'Tuning 
Undo Tablespace', 'Kimberly 
Floss')";

Run the SQL statement using the mysql_query() function:

$addrow=mysql_query ($sql, 
$connection );

Similarly, add another table row. Use the createMySQLTable.php script shown in Listing 1. Run this script in Apache Web server with this URL: http://localhost/createMySQLTable.php. A MySQL table will display (Figure 1).

Retrieve Data From MySQL Database
You can retrieve data from the MySQL database using the PHP class library for MySQL. Create the retrieveMySQLData.php script in the C:/Apache2/Apache2/htdocs directory. In the script, create a connection with the MySQL database using the mysql_connect() function:

$username='root';
$password='';
$connection = mysql_connect
('localhost:3306',
 $username, $password);

Select the database from which data will be retrieved with the mysql_select_db() method:

$selectdb=mysql_select_db('test');

Next, specify the SELECT statement to query the database (The PHP class library for MySQL does not have the provision to bind variables as the PHP class library for Oracle does.):

$sql = "SELECT * from CATALOG";

Run the SQL query using the mysql_query() function:

$result=mysql_query($sql , 
$connection);

If the SQL query does not run, output this error message:

if (!$result) {
  $e = mysql_error($connection);  
  echo "Error in running SQL 
statement.".$e;
}

Use the mysql_num_rows() function to obtain the number of rows in the result resource:

$nrows=mysql_num_rows($result);

If the number of rows is greater than 0, create an HTML table to display the result data. Iterate over the result set using the mysql_fetch_array() method to obtain a row of data. To obtain an associative array for each row, set the result_type parameter to MYSQL_ASSOC:

while ($row = mysql_fetch_array
($result, MYSQL_ASSOC)) {

}

Output the row data to an HTML table using associative dereferencing. For example, the Journal column value is obtained with $row['Journal']. The retrieveMySQLData.php script retrieves data from the MySQL database (Listing 2).

Run the PHP script in Apache2 server with this URL: http://localhost/retrieveMySQLData.php. HTML data will appear with data obtained from the MySQL database (Figure 2).

Now you know how to use the PHP extension for MySQL to access data from the MySQL database. You can also use the PHP Data Objects (PDO) extension and the MySQL PDO driver to access MySQL with PHP .

About the Author
Deepak Vohra is a Web developer, a Sun-certified Java programmer and a Sun-certified Web component developer. You can reach him at [email protected].