COUNT(*) php variable

Bastern

Hello,

Thank you in advance for your time and efforts, it is greatly received.

I am trying to get the number of records in a database into a variable in php, is this the best way to go about it?


$dbCount = "";

$resData = mysql_db_query ("clothesimages", "SELECT COUNT (*) FROM sportswear GROUP BY image");

while ($entry = mysql_fetch_array ($resData))
{	
	$dbCount = $dbCount . $entry["image"];
}

What would i put where 'image' is? When i have done this before, i have just put the names of the attributes in there, but because i am just doing a COUNT(*) i don't have any?

Do i put my GROUP BY clause in there instead?

Should i even be using mysql_fetch_arrray?

Thank you once again for your advice.

Regards, Bastern 🙂

Roger_Ramjet

$dbCount = "";

$resData = mysql_db_query ("clothesimages", "SELECT COUNT (*)  as  count FROM sportswear GROUP BY image");

while ($entry = mysql_fetch_array ($resData))
{    

    $dbCount = $dbCount . $entry['count'];
}

edit::

Just reread your post, simplify things

$dbCount = ""; // what for ??

$resData = mysql_db_query ("clothesimages", "SELECT COUNT (*) FROM sportswear");

if (!= $resData) { 
   echo 'query failed';
   exit;
}

if (mysql_num_rows($resData)>0) {
   $entry = mysql_fetch_array ($resData);
   $dbCount = $entry['count'];
}
else {
   echo 'nothing in the database';
   exit;
}

Bastern

Thank you very much Roger Ramjet. Much appreciated.

I didn't know you could use the COUNT(*) as the variable. I suppose i could just select all and use mysql_num_rows too.

Thank you for your help.

Regards, Bastern 🙂

Roger_Ramjet

from the manual

"COUNT() is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT() is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

This optimization applies only to MyISAM and ISAM tables only, because an exact record count is stored for these table types and can be accessed very quickly. For transactional storage engines (InnoDB, BD😎, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count."

Sxooter

Note that while most databases do it this way, it's probably one of those implementation dependent things. Better to force the name with an alias:

select name, count(*) as namecount from table group by name;

The same thing can be done for other fields, which is handy for computed fields:

select name, salary/12 as monthly from table...