Querying A mySQL Database Using PHP

Querying a mySQL database requires an established connection to a mySQL database.  You can read our article on connecting to a mySQL database here.

Querying the mySQL database is as simple as creating the SQL query and passing it through the mysql_query function.

<?
$sql = "SELECT * FROM products";
$results = mysql_query( $sql, $resource );
?>

With the $results variable you can use it to do a few different things. The first one we will discuss is to determine the total number of rows that was returned by the Select query. You can do that by doing the following:

<?
$num_rows = mysql_num_rows( $results );
?>

It should be noted that using mysql_num_rows is not the most efficent way to determine the number of rows being returned. If there are a lot of rows (I have seen it happen with as few as 1000 rows) that it is faster to query the database for the number of results like this:

<?
$sql = "SELECT COUNT(*) as num_rows FROM products";
$num_rows = mysql_fetch_array( mysql_query( $sql ) );
?>

This example also introduces the mysql_fetch_array function. This function is used to grab the next row of a mySQL query result. The row is returned as an array with the indexes named as the names of the columns in the database. Once there are no more results the function returns null.

The best way to iterate through a result set of a mySQL query is to use a while loop:

<?
$sql = "SELECT * FROM products";
$results = mysql_query($sql);
while( $row = mysql_fetch_array($results) ) {
    echo $row['products_id'] . ': '. $row['products_name'] . '
'; } ?>

Leave a Reply

Your email address will not be published. Required fields are marked *