PHP search needs to sort cars and cities

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 Mercedes
My 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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top