One of the most compelling things PHP has going for it is it support for a wide variety of databases. And this week, PHP 101 is going to take advantage of that database support to create dynamic data-driven Web sites. This primer covers different techniques to select, insert and delete records, together with some tips to track and squash bugs when building SQL-driven sites.
All working? Good. Now, let's use PHP to do exactly the same thing - fire a SELECT query at the database, and display the results in an HTML page.
<html>
<head>
</head>
<body>
<?php
// set up some variables
//
server name
$server = "localhost";
// username
$user = "test";
// password
$pass
= "test";
// database to query
$db = "php101";
// open a connection to the database
$connection
= mysql_connect($server, $user, $pass);
// formulate the SQL query - same as
above
$query = "select count(*) from url_list";
// run the query on the database
//
assume the database is named "php101"
$result = mysql_db_query($db,$query ,$connection);
//
assign the result to a variable
$counter = mysql_result($result,0);
// and display
it
echo "There are $counter records in the database";
// free up used memory
mysql_free_result($result);
?>
</body>
</html>
And you'll see something like this:
There are 2 records in the database
As you can see, using PHP to get data from a database involves several steps,
each of which 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.
2. In order 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.
In order to initialize this connection, PHP offers the mysql_connect() function.
The function requires three parameters: the name of the server, and the mySQL
username and password. If the database server and the Web server are both running on the same physical machine, the server name is usually "localhost"
This function then returns a "link identifier", which is stored in the variable $connection; this identifier is used throughout the script when communicating with the database.
3. Now that you have a connection to the database, it's time to send it a query via the mysql_db_query() function. This function also needs three parameters: the database name, the query string and the link identifier for the connection.
The result set returned by the function above is stored in the variable $result.
This result set may contain, depending on your query, one or more rows or columns of data. You then need to retrieve specific sections or subsets of the result set with different PHP functions - the one we've used is the mysql_result() function, which uses the result variable and the row number to return the value you need. You can optionally add the column name as well to get to a specific value.
$counter = mysql_result($result,0);
There are several other efficient alternatives to this function, which will be
explained a little further down.
4. Finally, each result set returned after a query occupies some amount of memory - and if your system is likely to experience heavy load, it's a good idea to use the mysql_free_result() function to free up the used memory.
