Using MySQL and PHP Together
To illustrate these functions, let’s create a simple MySQL database table, and
then use PHP to connect to the server, retrieve a set of results, and format them for
display on a web page. The sample table used here consists of a single table named
items, which holds a list of products and their prices. Here are the SQL queries
needed to create and initialize this table:
CREATE TABLE items (
itemID int(11) NOT NULL auto_increment,
itemName varchar(255) NOT NULL default ‘’,
itemPrice float NOT NULL default ‘0′,
PRIMARY KEY (itemID) ) TYPE=MyISAM;
INSERT INTO items VALUES (1, ‘Paperweight’, ‘3.99′);
INSERT INTO items VALUES (2, ‘Key ring’, ‘2.99′);
INSERT INTO items VALUES (3, ‘Commemorative plate’, ‘14.99′);
INSERT INTO items VALUES (4, ‘Pencils (set of 4)’, ‘1.99′);
INSERT INTO items VALUES (5, ‘Coasters (set of 3)’, ‘4.99′);
You can enter these commands either interactively or noninteractively through
the MySQL client program. Once entered, run a SELECT query to ensure that the data has been successfully imported.
mysql> SELECT * FROM items;
+——–+———————+———–+
| itemID | itemName | itemPrice |
+——–+———————+———–+
| 1 | Paperweight | 3.99 |
| 2 | Key ring | 2.99 |
| 3 | Commemorative plate | 14.99 |
| 4 | Pencils (set of 4) | 1.99 |
| 5 | Coasters (set of 3) | 4.99 |
+——–+———————+———–+
5 rows in set (0.00 sec)
Now, to do the same thing using PHP, create the following PHP script:
<html>
<head></head>
<body>
<?php
// open connection to MySQL server
$connection = mysql_connect(’localhost’, ‘guest’, ‘pass’) ↵
or die (’Unable to connect!’);
// select database for use
mysql_select_db(’db2′) or die (’Unable to select database!’);
// create and execute query
$query = ‘SELECT * FROM items’;
$result = mysql_query($query) ↵
or die (’Error in query: $query. ‘ . mysql_error());
// check if records were returned
if (mysql_num_rows($result) > 0)
{
// print HTML table
echo ‘<table width=100% cellpadding=10 cellspacing=0 border=1>’;
echo
‘<tr><td><b>ID</b></td><td><b>Name</b></td><td><b>Price</b></td></tr>’;
// iterate over record set
// print each field
while($row = mysql_fetch_row($result))
{
echo ‘<tr>’;
echo ‘<td>’ . $row[0] . ‘</td>’;
echo ‘<td>’ . $row[1] . ‘</td>’;
echo ‘<td>’ . $row[2] . ‘</td>’;
echo ‘</tr>’;
}
echo ‘</table>’;
}
else
{
// print error message
echo ‘No rows found!’;
}
// once processing is complete
// free result set
mysql_free_result($result);
// close connection to MySQL server
mysql_close($connection);
?>
</body>
</html>
Output:
1. To begin communication with the MySQL database server, you first need to
open a connection to the server. All communication between PHP and the
database server takes place through this connection, which is initialized by
the mysql_connect() function.
The mysql_connect() function requires three parameters: the host
name of the MySQL server, and the MySQL username and password
required to gain access to it. If the function is able to successfully initialize
a connection, it returns a link identifier, which is stored in the variable
$connection. This identifier is used throughout the script when
communicating with the database.
2. Once a connection has been initialized, the next step is to select a database
for use (this is equivalent to the SQL USE command) with the mysql_
select_db() command, and then send the server a query through the
mysql_query() function. Both functions use the last opened connection
as their default for all operations.
3. The result set returned by the query is assigned to the variable $result.
This result set may contain, depending on your query, zero or more rows
or columns of data. The number of rows in the result set is obtained from
the mysql_num_rows() function. Assuming one or more rows exist,
the mysql_fetch_row() function is used to iterate over the result set
and retrieve rows as arrays. Individual field values can then be accessed as
array elements.
4. Each result set returned by a query occupies some amount of memory.
Once you’re done processing it, therefore, it’s a good idea to use the
mysql_free_result() function to free up the used memory for
other purposes. And, once you’re done querying the database, close the
connection with a call to mysql_close().
