Using Count with Where

[PhilipK]

Hello, I'm using the following code to count the amount of SQL results and divide it. Works great as is but I want to alter it so it works with categories.

 

$amount = mysql_query('SELECT COUNT(`car_id`) AS num FROM `tbl_cars`') or die(mysql_error());

$amount_row = mysql_fetch_assoc($amount);

 

I tried adding...

 

WHERE car_cat = '".$cat."'

 

which causes an error.

 

Why doesn't this work, and how can I get the amount of results as a variable?

[kenrbnsn]

Please show us your attempt and the error message you got.

 

When posting code, please put it between

tags.

 

Ken

[PhilipK]

Ok here is the code after my attempt.

 

$amount = mysql_query('SELECT COUNT(`car_id`) AS num FROM `tbl_cars` WHERE car_cat = '".$cat."'') or die(mysql_error());

$amount_row = mysql_fetch_assoc($amount);

 

It causes a syntax error in Dreamweaver and server error.

[noXstyle]

Mmh, nothing wrong with the query.

 

Try the following:

$amount = mysql_query("select count(car_id) as num from tbl_cars where car_cat='$cat'") or die(mysql_error());

[kenrbnsn]

If this is what is really in your code:

<?php
$amount = mysql_query('SELECT COUNT(`car_id`) AS num FROM `tbl_cars` WHERE car_cat = '".$cat."'') or die(mysql_error());
?>

you have a quote problem. It should be

<?php
$amount = mysql_query("SELECT COUNT(`car_id`) AS num FROM `tbl_cars` WHERE car_cat = '$cat'") or die(mysql_error());
?>

 

Ken

[PhilipK]

Thanks Ken that fixed the problem.

 

I now tried using your structure for an alteration for a search string.

 

<?php
$amount = mysql_query("SELECT COUNT(`car_id`) AS num FROM `tbl_cars` WHERE car_name LIKE '%$srch%' OR car_year LIKE '%$srch%' OR car_cat LIKE '%$srch%'") or die(mysql_error());
?>

 

Is it a similar problem?