Category Count using mysql

[prem]

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

[fenway]

Explain that logic of that query, please... does it do what you expect/

[prem]

Thanks for the feedback:

before going any further i am listing the required details based on your guidelines:

 

MySQL Version : 5.0.20

 

 

Table Schema: I simplified the tables and added a few data for experimental sake.

-- Database: `cars`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_cat`
-- 

CREATE TABLE `tbl_cat` (
  `c_id` int(9) NOT NULL auto_increment,
  `c_title` varchar(100) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`c_id`),
  KEY `fk_tbl_cat_tbl_map` (`c_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='This table will consist the basic details of a car posted un' AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `tbl_cat`
-- 

INSERT INTO `tbl_cat` VALUES (1, 'Petrol');
INSERT INTO `tbl_cat` VALUES (2, 'Diesel');
INSERT INTO `tbl_cat` VALUES (3, 'Auto');
INSERT INTO `tbl_cat` VALUES (4, 'Manual');
INSERT INTO `tbl_cat` VALUES (5, 'picture');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_item`
-- 

CREATE TABLE `tbl_item` (
  `i_id` int(2) NOT NULL auto_increment,
  `i_name` varchar(45) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`i_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='This table will hold the main and subcatergory for refine se' AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `tbl_item`
-- 

INSERT INTO `tbl_item` VALUES (1, 'Tata Indica');
INSERT INTO `tbl_item` VALUES (2, 'Mitsubishi Pajero');
INSERT INTO `tbl_item` VALUES (3, 'Suzuki Alto');
INSERT INTO `tbl_item` VALUES (4, 'Tata Dimo Ace');
INSERT INTO `tbl_item` VALUES (5, 'Mahendra Scorpion');
INSERT INTO `tbl_item` VALUES (6, 'BMW Z3');
INSERT INTO `tbl_item` VALUES (7, 'Benz SLK');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_map`
-- 

CREATE TABLE `tbl_map` (
  `m_id` int(10) NOT NULL auto_increment,
  `i_id` int(4) NOT NULL,
  `c_id` int(2) NOT NULL,
  PRIMARY KEY  (`m_id`),
  KEY `fk_tbl_map_tbl_item` (`i_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=15 ;

-- 
-- Dumping data for table `tbl_map`
-- 

INSERT INTO `tbl_map` VALUES (1, 1, 1);
INSERT INTO `tbl_map` VALUES (2, 1, 4);
INSERT INTO `tbl_map` VALUES (3, 2, 2);
INSERT INTO `tbl_map` VALUES (4, 2, 4);
INSERT INTO `tbl_map` VALUES (5, 3, 1);
INSERT INTO `tbl_map` VALUES (6, 3, 4);
INSERT INTO `tbl_map` VALUES (7, 4, 2);
INSERT INTO `tbl_map` VALUES (8, 4, 4);
INSERT INTO `tbl_map` VALUES (9, 5, 2);
INSERT INTO `tbl_map` VALUES (10, 5, 4);
INSERT INTO `tbl_map` VALUES (11, 6, 1);
INSERT INTO `tbl_map` VALUES (12, 6, 3);
INSERT INTO `tbl_map` VALUES (13, 7, 1);
INSERT INTO `tbl_map` VALUES (14, 7, 4);

 

The SQL Query i am using (based on the above schema)

 

SELECT c.c_id, c.c_title AS Name, COUNT(m.c_id) AS qty
FROM tbl_cat c
LEFT OUTER JOIN tbl_map m LEFT JOIN tbl_item i ON m.i_id = i.i_id ON c.c_id = m.c_id
WHERE i.i_name LIKE '%BMW%'
GROUP BY c.c_id
UNION
SELECT c.c_id, c.c_title AS Name, 0 AS qty
FROM tbl_cat c
WHERE c.c_id NOT IN(SELECT c.c_id
FROM tbl_cat c
LEFT OUTER JOIN tbl_map m LEFT JOIN tbl_item i ON m.i_id = i.i_id ON c.c_id = m.c_id
WHERE i.i_name LIKE '%BMW%'
GROUP BY c.c_id)
ORDER BY c_id

 

Now to your question: logic this is where i have the problem actually... my objective it to list out all the predefined categories found on the tbl_cat based on the query performed (for ex: BMW).

 

Example:

lets say i am searching for a BMW car - and lets say i got only one result based on my query, i want my categories to show like (based on the tbl_cat given above):

 

Petrol (1)

Diesel (0)

Auto  (1)

Manual (0)

Picture (0)

 

The purpose i want the list to show up like this is because i want to use this list to help refine the search and narrow down the result..

 

now my complicating query generates the expected result but when i click on a category item for refining - the results get refined but the categories don't..

 

can you advice me what i am doing wrong? or if results could be achieved by a different approach?

please advice.

thanks

[fenway]

Why the union? And the syntax is a bit funny... cut-and-paste error?

[prem]

There are no errors by far up to now - just the the expected result does not appear when i perform a refined search!

[fenway]

What does that 2nd query accomplish? And you have two ON clauses back-to-back.

[prem]

Hi Fenway

I figured out an alternate approach to achieve my requirement..

thanks anyways

[fenway]

Hi Fenway

I figured out an alternate approach to achieve my requirement..

thanks anyways

Then please post is here, otherwise this entire thread is just very confusing.

[prem]

well the feedback i received in the forum was waaay to slow and i've received feedback only from one person (you - TY for that) and none that hinted a solution - in other words this forum is as good as dead.. no offense. i thought you would have figured out, when i did not bump the thread for feedback. 

 

[fenway]

I was actually looking for the representative code/query...