David.P David.P - 1 month ago 4x
MySQL Question

How to display search results from different categories with PHP and MySql

I am working on a Classifieds website, and I have a question about retrieving search results.

My goal is to display the number of classifieds in each category next to the category-names.

For instance:

  • Red Cars ( 821 )

  • Android Phones (291 )

  • Notebook Computers ( 929 )

What is the ideal way of getting these counts from the MySql database tables?

Is it to write one query to every single table in the database? This would mean a lot of queries for one visitor, and perhaps cause performance issues?



I haven't created the MySql table yet, so feel free to recommend ideal structure if it plays a big role in the counting.


I would prefer to have one query for this need.

Assuming you have a Categories table and a no of classifieds say products you should be able to do something like this:

    Select C.Category, Count(P.Id) as ProductsCount 
From Categories C left join Products P on C.Id = P.Category_Id
Group by C.Category    
Order by C.Category