Ask the Expert

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

Looks like you need a GROUP BY clause. Try something like:

SELECT Type, Count(*) AS CarCount
FROM CarTable
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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: