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.

Dig Deeper on Linux servers

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.

Please create a username to comment.