How to list all items in a category with count using mysql? : phpMyAnswers

How to list all items in a category with count using mysql?

demo 23 Dec 2008 09:36am
I am trying to have a refined search option for a car classified site, such that when a search is performed a set of predefined categories will list out (by default) with the 'count' of availability based on the search made. the concept is more like tag cloud but the difference is the weighted keywords appear based on the search term performed and it should list out all the predefined keywords regardless of the availability as per search performed.

I have 3 tables, they are:

- Car_table (which holds information of the item)
- Category Table (which has the predefined categories)
- Bind Table (which uses the primary keys of the car and category tables)

Example:
Lets say there are 4 items in the category such as

petrol
diesel
auto transmission
manual transmission

and lets say i perform a search for BMW when the result pops (assume there are 4 results) up i want the category to list like

petrol (2)
diesel (2)
Auto tran.. (4)
manual tran.. (0)

Now i have managed to achieve this result using one mysql query below

SELECT c.ca_id, c.ca_parentid, c.ca_name AS Name, COUNT(b.sc_id) AS qty
FROM tbl_category c
LEFT OUTER JOIN tbl_bind b LEFT JOIN tbl_item i ON b.itm_id = i.itm_id ON c.ca_id = b.sc_id
WHERE i.itm_name LIKE '%BMW%'
GROUP BY c.ca_id
UNION
SELECT c.ca_id, c.ca_parentid, c.ca_name AS Name, 0 AS qty
FROM tbl_category c
WHERE c.ca_id NOT IN(SELECT c.ca_id
FROM tbl_category c
LEFT OUTER JOIN tbl_bind b LEFT JOIN tbl_item i ON b.itm_id = i.itm_id ON c.ca_id = b.sc_id
WHERE i.itm_name LIKE '%BMW%'
GROUP BY c.ca_id)
ORDER BY ca_id

but when i select / click a category i do not get the result nor the category gets refined. can some one advice me what i am doing wrong. this is my first post in this forum hope someone could shed some light on me
:)
Thanks

Answers

demo 23 Dec 2008 10:29am
I would think you could limit your SQL to something like this:

SELECT c.ca_id, c.ca_parentid, c.ca_name AS Name, COUNT(b.sc_id) AS qty
FROM tbl_category c
LEFT OUTER JOIN tbl_bind b LEFT JOIN tbl_item i ON b.itm_id = i.itm_id ON c.ca_id = b.sc_id
WHERE i.itm_name LIKE '%BMW%'
GROUP BY c.ca_id
ORDER BY qty DESC

And your SQL doesn't include the category in there anywhere, aside from making sure that the category ID = bind table sc_id.
Did you want to switch off of category OR itm_name?

Submit Answer

You must be logged in to post an answer. Please Login or Register.