After building many static Web sites, I am finally getting into database-driven sites. I am using PHP/MySQL. My first site is coming along, but now I have reached a brick wall. I have one table, two columns, one search.php (single seachbox), two results.php.
Col1 CITY - Col2 TYPE Dallas | red BMW Dallas | blue BMW Dallas | green BMW Dallas | white BMW Dallas | red Ford Dallas | blue Ford Dallas | green Jaguar Dallas | white MercedesMy searches will ONLY be city-based, i.e. you can only type city names in the search box to receive a result, not car types/colors etc. At the moment, when I type in 'Dallas', it returns the following result:
Thank you, we have found the following cars in Dallas BMW (8)BMW, because it is first field in col2 and (8), which is the total sum.
What I need it to return is:
Thank you, we have found the following cars in Dallas BMW (4) Ford (2) Jaguar (1) Mercedes (1)These will be then linked to the description pages. I have been playing around with the COUNT and DISTINCT rules but am getting nowhere. I would be grateful for any assistance.
Looks like you need a GROUP BY clause. Try something like:
SELECT Type, Count(*) AS CarCount FROM CarTable GROUP BY Type;That should give you a summary of all car types and the number of cars in each type.
This was first published in August 2004