PHP call to MySQL db: Select Count(*)

Trying to pull a list from a database, and at the top of the list I wanted to display the count of the rows found. My original code involved 2 queries to the db, one to select the rows and the next using select count(*) to count them. My code looked something like this:

<?php

$query = "SELECT * from songs ORDER BY title ASC;";

$result = mysql_query($query)
or die("<p align='center' class='error'>Sorry, query could not be executed<br>".mysql_error()."</p>");

$row = mysql_fetch_row($result);

$html = "$count_rows Songs Found.<br><br>";
while ($row=mysql_fetch_array($result))
{
extract($row);

$html .= "$title - $artist<br>";
}

$query = "SELECT COUNT(*) from songs;";

$result = mysql_query($query)
or die("<p align='center' class='error'>Sorry, query could not be executed<br>".mysql_error()."</p>");
echo $html;

echo $result;
?>

Instead of getting a count I was getting “Resource id #4.” The correct code to use is as follows:

<?php
$query = "SELECT * from songs WHERE type = 'D' ORDER BY title ASC;";

$result = mysql_query($query)
or die("<p align='center' class='error'>Sorry, query could not be executed<br>".mysql_error()."</p>");

$count = mysql_num_rows($result);

$html = "$count Songs Found.<br><br>";

while ($row = mysql_fetch_array($result)) {
extract($row);
$html .= "$title - $artist<br>";
}

echo $html;
?>

Look carefully and you can see that instead of using mysql_fetch_rows, this code uses mysql_num_rows and does get the expected result. 🙂