mysql count into PHP variable

Question

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 ?

Have you tried to do a print_r ( $sql_result ) to see what it returns? It returns an array, so you can pick up stuff through $num = $result[0][0], for example (do an if (isset(.)) first) — AlexanderJohannesen
– AlexanderJohannesen, Commented Apr 28, 2009 at 11:09

Greg · Accepted Answer · 2009-04-28 11:08:16Z

25

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'];

answered Apr 28, 2009 at 11:08

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

millimoose · Accepted Answer · 2013-09-24 16:33:53Z

4

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

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

edited Sep 24, 2013 at 16:33

answered Apr 28, 2009 at 11:26

millimoose

40.1k11 gold badges90 silver badges138 bronze badges

Collectives™ on Stack Overflow

mysql count into PHP variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related