How can I store the result of an SQL COUNT statement in a PHP variable

Question

I want to get the number of rows in my MySQL table and store that number in a php variable. This is the code I'm using:

$size = @mysql_query("SELECT COUNT(*) FROM News");

$size ends up being "Resource ID #7." How do I put the number of rows directly into $size?

Out of curiousity, why is this a wiki?

gpojd
– gpojd

2009-02-28 00:47:29 +00:00
Commented Feb 28, 2009 at 0:47 — gpojd
– gpojd, Commented Feb 28, 2009 at 0:47
I asked this back before I knew what a community wiki was.

cstack
– cstack

2012-08-21 02:10:23 +00:00
Commented Aug 21, 2012 at 2:10 — cstack
– cstack, Commented Aug 21, 2012 at 2:10

3 revs, 2 users 88% · Accepted Answer · 2009-02-28 09:34:16Z

14

mysql_query returns a query resource id. In order to get values from it you need to use mysql_fetch_assoc on the resource id to fetch a row into an array.

$result = mysql_query("SELECT COUNT(*) FROM News");
$row = mysql_fetch_assoc($result);
$size = $row['COUNT(*)'];

edited Feb 28, 2009 at 9:34

community wiki

3 revs, 2 users 88%
Daniel Von Fange

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

4 Comments

gpojd Over a year ago

I haven't used php in a while, but won't it not release the $result if you don't loop through until mysql_fetch_assoc returns a null? Shouldn't it usually be used with a while loop, or is there another way to tell mysql that you don't want any more rows from $result?

gpojd Over a year ago

Nevermind, found this on the doc page: "// Note: If you're expecting just one row, no need to use a loop".

Rob Over a year ago

You could do mysql_free_result( $result ), but you could argue that was unnecessary - the result buffer is only going to contain one row, in any case, and all the resources will be freed when the script exits.

Ólafur Waage Over a year ago

mysql_query("SELECT COUNT(*) AS count FROM News"); would be a lot cleaner also.

2 revs · Accepted Answer · 2009-02-28 00:44:09Z

1

You need to call mysql_fetch_row or one of its sister functions.

<?php
// untested
$result = @mysql_query("SELECT COUNT(*) FROM News");
// error handling
$row = mysql_fetch_row($result);
$count = $row[0];
?>

edited Feb 28, 2009 at 0:44

community wiki

2 revs
gpojd

Comments

AliMohsin · Accepted Answer · 2012-11-20 17:56:19Z

0

try the following:

$size = @mysql_query("SELECT COUNT(*) AS `total` FROM News");
$query = mysql_fetch_array($size);
echo $query['total'];

answered Nov 20, 2012 at 17:56

community wiki

AliMohsin

Comments

2 revs · Accepted Answer · 2023-11-17 19:24:28Z

-1

On a related note you can use the mysql_num_rows() function to obtain the number of rows from a given query. This is handy if you need to grab the data but also know the number of rows.

<?php
  $result = @mysql_query("SELECT * FROM news");
  $count = @mysql_num_rows($result);
?>

edited Nov 17, 2023 at 19:24

community wiki

2 revs
Paulo

Collectives™ on Stack Overflow

How can I store the result of an SQL COUNT statement in a PHP variable

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related