PHP 101 (part 8): Databases And Other Animals
Now, let's use PHP to do exactly the same thing: fire a SELECT query at the database 'testdb', and display the results in an HTML page:// set database server access variables:
$host = "localhost";
$user = "test";
$pass = "test ";
$db = "testdb";
// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!") ;
// select database
mysql_select_db($db) or die ("Unable to select database!");
// create query
$q uery = "SELECT * FROM symbols";
// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error ());
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
// yes
// print them one after another
echo "
| ".$row[0]." | ";" . $row[1]." | ";".$row[2]." | ";
}
else {
// no
// print status message
r echo "No rows found!";
}
// free result set memory
mysql_free_result($result);
// close conn ection
mysql_close($connection);
?>
Here's what the res ult looks like:
Output image
As you can see, using PHP to get data from a database involves several steps, each of whic h is actually a pre-defined PHP function. Let's dissect each step:
1. The first thing to do is specify some important information needed to establish a connection to the database server. This information includes the server name, the username and password required to gain access to it, and the name of the database to query. These values are all set up in regular PHP variables.
$host = "localho st";
$user = "test";
$pass = "test";
$db = "testdb";
?>
2. To begin communi cation with a MySQL database server, you need to open a connection to that server. All communication between PHP and the database server takes place through this connection.
In order to initialize this connection, PHP offers the mysql_connect() function:
$connection = mysql_connect($server, $user, $pass);
?>
All the parameters in mysql_connect() are optional , but there are three you will generally need to use anywhere beyond your own machine: the database server name, username and password. If the database serve r and the Web server are running on the same physical machine, you can use localhost as the database server this is in fact the default value suppliedname by PHP.
mysql_connect() returns a "link identifier", which is stored in the variable $connection. This identifier is used when communic ating with the database.
3. Once you have a connection to the database, you must select a database for use with the mysql_select_db() func tion:
mysql_select_db($db) or die ("Unable to select database!");
?>
This function must be passed the name of the database to be used for all subsequent queries. An optional second argument here is the link ide ntifier; if no identifier is specified, the last opened link is assumed. If you have two or more database connections open simultaneously, it's a good idea to specify the link identifier as the second argument to mysql_select_db() - and indeed to all other mysql_* functions in the script, so that PHP doesn't ge t confused about which connection to use where.
4. The next step is to create the query and execute it. This is accomplished with the mysq l_query() function.
$query = "SELECT * FROM symbols";
$result = mysql_query($query) or die ( "Error in query: $query. ".mysql_error());
?>
This function also needs two parameters: the query string and the link identifier for the connection. Again, if no link identifier is specified, the last opened link is used. Depending on whether or not the query was s uccessful, the function returns true or false; a failure can be caught via the ...or die() clause of the statement, and the mysql_error() function can be use d to display the corresponding error message.
5. If mysql_query() is successful, the result set returned by the query is stored in the var iable $result. This result set may contain one or more rows or columns of data, depending on your query. You can retrieve specific subsets of the result set with different PHP functions, including the one used here - the mysql_fetch_row() function - which fetches a single row of data as an array called $row. Fiel ds in that row can then be accessed using standard PHP array notation.
Each time you call mysql_fetch_row(), the next record in the result set is returned. This makes mysql_fetch_row() very suitable for use in a while() or for() loop.
if (my sql_num_rows($result) > 0) {
while($row = mysql_fetch_row($result)) {
echo "
echo "
echo "
}
}
?>
Noti ce that the call to mysql_fetch_row() is wrapped in a conditional test, which first checks to see if any rows were returned at all. This information is provi ded by the mysql_num_rows() function, which contains the number of rows returned by the query. Obviously, you can only use this function with queries that re turn data, like SELECT or SHOW; it is not appropriate for use with INSERT, UPDATE, DELETE or similar queries.
There are several other alte rnatives to mysql_fetch_row(), which will be explained a little later.
6. Finally, since each result set returned after a query occupies m emory, it's a good idea to use the mysql_free_result() function to free up the used memory. After the result set is freed, if no further queries are to be r un, you can close the connection to the MySQL server with mysql_close().
mysql_free_result($result);
mysql_close($connection);
?>