help with select/count query

jacksonpt

I've got a database of tire reviews. I want to query the db and return which manufacturers have tires reviewed in the db, and how man reviews they have, generating a list that looks something like this:

The following tire manufacturers have reviews in the DB:
- Firestone (2)
- BF Goodrich (6)
- Super Swamper (1)
- Bridgetsone (3)

The list of manufacturers will be generated by the select statement. For this, I'm currently using the following, which works fine:

SELECT DISTINCT TireMake FROM trucktires ORDER BY TireMake

How do I add the ability to count how many records there are for each manufacturer? I tried this:

$db = mysql_connect("localhost", "root");
mysql_select_db("tires",$db);
$result = mysql_query("SELECT DISTINCT TireMake, COUNT(*) FROM trucktires ORDER BY TireMake",$db);
if ($record = mysql_fetch_array($result)) {

but got the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Apache\Apache2\htdocs\TireReport.php on line 88

Thanks.

bastien

$result = mysql_query("SELECT TireMake, COUNT(TireMake) FROM trucktires
GROUP BY TireMake
ORDER BY TireMake",$db);

jacksonpt

How would I refer to the Counts in php? I assume that I'd still use $result["TireMake"] to refer to the tire manufactuers.

bastien

better alias is:

$result = mysql_query("SELECT TireMake, COUNT(TireMake) as counts FROM trucktires
GROUP BY TireMake
ORDER BY TireMake",$db);

then

$result['counts'] to get the value out

jacksonpt

Thanks - that seems to work well.

Now, would you mind explaining it to me so I understand WHY it works? Specifically the "as counts" part of the query - is that what defines how I refer to the value when I use $result['counts']?

bastien

Its known as an 'alias' for the column. Alias' can be applied to table as well. What you do in assigning the alias is to give another name to the table/column to be able to avoid conflicts or ambiguities in the sql statement.

An example is having two fields in different tables that you require at the same time. Call them tablea.ID and tableb.ID...if you query this, you'll get an ambiguous field name error...so you create an alias for these fields and then you can use the alias to get the data out and avoid the error.

hth