I am trying to execute what I considered to be a simple command using MYSQL. I am working on an e-commerce site and I want to count the number of products in a category. I am trying to do a "SELECT COUNT" statement, but I can not get it to execute. Here are my statements:

$sql = "SELECT COUNT (*) FROM products WHERE Category =\"$cat\"";
$sql_result = mysql_query($sql,$connection) or die ("Could not execute count");

I get "Could not execute..." returned from the Query.

I know my table and variables are OK, because I am able to execute this query a few lines earlier.

$sql = "SELECT * FROM products WHERE Category = \"$cat\" LIMIT $j,$num_display";
$sql_result = mysql_query($sql,$connection) or die ("Could not execute query");

What am I doing wrong with my "SELECT COUNT" statement? I am guessing that I have a syntax problem by trying to perform the query from PHP (it works fine in MYSQL front).

I don't think you can do a COUNT(*)... replace * with one field, usually the ID of the product, so it'll be SELECT COUNT(id), something like that.

Also, to get a more detail error, change:

or die ("Could not execute count");
to:

or print mysql_error();
that will give you details of which part of the SQL statement is incorrect.

Tell me if that helps.

Alan

Thanks for the quick response. That corrected my syntax problem. My next question is how do I get the result of the "select count" into a variable that I can use?

There is a quicker way to get it into a variable than the way I'm going to tell you, but this is the way I use:

Change the SQL to:

SELECT COUNT (id) as productCount FROM...

then after the $sql_result line, put this:

while ( $row = mysql_fetch_array ($sql_result) ) {
$numberOfProducts = $row["productCount"];
}

Alan