Article
The PHP Anthology Volume I, Chapter 3 - PHP and MySQL
Counting Rows with MySQL
The alternative approach is to use MySQL's COUNT function within the query. This requires that you perform two queries—one to count the results and one to actually get the results—which will cost you a little in terms of performance.
Here's how you could use the MySQL COUNT function:
Example 3.18. 13.php (excerpt)
// A query to select all articles
$sql = "SELECT COUNT(*) AS numrows FROM articles";
// Query to count the rows returned
$queryResource = mysql_query($sql, $dbConn);
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);
echo $row['numrows'] . " rows selected<br />";
// A query to select all articles
$sql = "SELECT * FROM articles ORDER BY title";
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
// Fetch rows from MySQL one at a time
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {
echo 'Title: ' . $row['title'] . '<br />';
echo 'Author: ' . $row['author'] . '<br />';
echo 'Body: ' . $row['body'] . '<br />';
}
Notice we used an alias to place the result of the COUNT function?
SELECT COUNT(*) AS numrows FROM articles
We do this so that the number of rows can be identified later using $row['numrows']. The alternative would have been to omit the alias:
SELECT COUNT(*) FROM articles
This would require that we access the information as $row['COUNT(*)']/#epc#/, which can make the code confusing to read.
When we use the COUNT function, it becomes important to construct queries on the fly as we saw in the section called "How do I create flexible SQL statements?". You need to make sure your COUNT query contains the same WHERE or LIMIT clauses you used in the "real" query. For example, if the query we're actually using to fetch data is:
SELECT * FROM articles WHERE author='HarryF'
In PHP, we'll probably want something like this:
Example 3.19. 14.php (excerpt)
// Define reusable "chunks" of SQL
$table = " FROM articles";
$where = " WHERE author='HarryF'";
$order = " ORDER BY title";
// Query to count the rows returned
$sql = "SELECT COUNT(*) as numrows" . $table . $where;
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);
echo $row['numrows'] . " rows selected<br />";
// A query to fetch the rows
$sql = "SELECT * " . $table . $where . $order;
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
// Fetch rows from MySQL one at a time
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {
echo 'Title: ' . $row['title'] . '<br />';
echo 'Author: ' . $row['author'] . '<br />';
echo 'Body: ' . $row['body'] . '<br />';
}
Row Counting with Classes
Let's look again at the classes we've been developing throughout this section. We can add the ability to find out the number of rows selected by introducing the following method to the MySQLResult class:
Example 3.20. Database/MySQL.php (in SPLIB) (excerpt)
/**
* Returns the number of rows selected
* @return int
* @access public
*/
function size()
{
return mysql_num_rows($this->query);
}
Here's how to use it:
Example 3.21. 15.php (excerpt)
// Connect to MySQL
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);
// Select all results for a particular author
$sql = "SELECT * FROM articles WHERE author='HarryF'";
$result = $db->query($sql);
echo "Found " . $result->size() . " rows";