How do you count how many rows?

Inkybro

I need to display how many members I have on the front page of my site. Does anyone know the best code to do this?

stolzyboy

<?
$query = "select field from members;";
$result = mysql_query($result);

$i = 0;

while ($row = mysql_fetch_array)
{
$i = $i + 1;
}

echo "you have " . $i " members in your database";
?>

that should do it, probably better ways with count in mysql or something that would do it faster, but this should work

eoghain

Let the database do the work for you:

SELECT count(*)
  FROM members;

No need to have the while loop, your database will do this faster.

Inkybro

this sounds dumb, could you explain how to replace the while loop with your dealy?

eoghain

After stolzyboy's code you would have a variable $i that would hold the count of records that you had returned from your query. The count(*) function does all of this inside the database and just returns the final count of records. This saves you time in having to walk through all of your results, and also saves you time in getting all of those records from the database.

So to change stolzyboy's code to work with the count() query you'd have this:

<?
$query = "select count(*) as cnt from members;";
$result = mysql_query($result);

$row = mysql_fetch_array

echo "you have " . $row['cnt'] . " members in your database";
?>

smith15

Shouldn't it be:

  

<?
$query = "select count(*) as cnt from members;";
$result = mysql_query($query);

$row = mysql_fetch_array

echo "you have " . $row['cnt'] . " members in your database";
?>

mysql_query($query); not mysql_query($result);

keith73

other than using count in the SQL query, you can do your query and get the # of rows returned.

$result = mysql_query("SELECT * FROM members");
$total = mysql_num_rows($result);
echo "Members: " . $total;

you don't need a while loop to count the members for you when mysql_num_rows will do that for you. if you want to narrow your results to specific criteria.

$result = mysql_query("SELECT * FROM members WHERE country = 'USA'");
$total = mysql_num_rows($result);
echo "Members in United States: " . $total;

keith