Web Hosting Talk : Other Forums : Web Hosting Lounge : PHP/MYSQL problem

[rrsnider]

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).

[ho247]

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

[rrsnider]

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?

[ho247]

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