Problem solve Get help with specific problems with your technologies, process and projects.

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.

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 last published in August 2004

Dig Deeper on Linux servers



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.