mysql count into PHP variable

Hi there,

Let say that we have the following query:

SELECT DISTINCT COUNT(`users_id`) FROM `users_table`;

this query will return the number of the users from a table. I need to pass this value to a PHP variable. I'm using this:

$sql_result = mysql_query($the_query_from_above) or die(mysql_error());

if($sql_result)
{
    $nr_of_users = mysql_fetch_array($sql_result);
}
else
{
    $nr_of_users = 0;
}

please correct my code where you think is necessary.

Which is the best approach. How do you recommend to do this ?

From stackoverflow

• Like this:

  // Changed the query - there's no need for DISTINCT
  // and aliased the count as "num"
  $data = mysql_query('SELECT COUNT(`users_id`) AS num FROM `users_table`') or die(mysql_error());
  
  // A COUNT query will always return 1 row
  // (unless it fails, in which case we die above)
  // Use fetch_assoc for a nice associative array - much easier to use
  $row = mysql_fetch_assoc($data);
  
  // Get the number of uses from the array
  // 'num' is what we aliased the column as above
  $numUsers = $row['num'];
  

• Also, an alternative using mysqli, which you should be using anyway for parameter interpolation:

  $stamement = $connection->prepare($the_query_from_above);
  $statement->execute();
  $statement->bind_result($nr_of_users);
  $statement->fetch();