I have MANY to MANY relations between workshop and category tables.
I have 3 tables: workshop, category, category_workshop_tie.
In category_workshop_tie i have ids of workshop and category.
category_workshop_tie:
category_id | workshop_id
workshop:
id | title
category:
id | name
And i have code:
$this->db->query('
SELECT workshop.*, tie.category_id
FROM `prefix_workshop` workshop, `prefix_category_workshop_tie` tie
WHERE ((workshop.title LIKE ? OR workshop.descr LIKE ?) AND workshop.city LIKE ? AND workshop.state LIKE ?) AND (workshop.id = tie.workshop_id)
ORDER BY `d_course`', '%'.($d['f'] ? $d['f'] : '' ).'%', '%'.($d['f'] ? $d['f'] : '' ).'%', '%'.($d['city'] != '0' ? str_replace('_', ' ', $d['city']) : '').'%', '%'.($d['state'] != '0' ? str_replace('_', ' ', $d['state']) : '').'%');
$d equals to $_GET;
QUERY STRING: f=&city=Odessa&state=Odesska_Region&category=0&workshop_search_submit=Submit
Example of output:
Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 6 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 9 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 17 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 1 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 4 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 5 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 7 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 8 ) Array ( [id] => 11 [title] => Hello worldicos [presenter] => Bill [hours] => 5.00 [d_course] => 2011-07-06 [d_course_end] => 0000-00-00 [location] => [city] => Odessa [state] => Odesska Region [cost] => 53.00 [descr] => dwqhhd whqdhwq yhhd qywhdy hyqh d [sponsor] => Microshka [contact] => Mirgorod [website] => http://mirgorod.us [dt_update] => 2011-07-07 08:44:15 [user_id] => 1 [addr] => Kominterna street 10 A [image] => [category_id] => 13 )
I print_r'ed every row.
Problem is that i get from this query: (workshop_matched_item) * all categories of this workshop.
For example: If one workshop matched and this workshop has 5 categories then I get 5 rows. But I need 1 row with 5 categories of this workshop.
What I'm doing wrong?
Group by workshop.id and use GROUP_CONCAT() function for tie.category_id :
SELECT workshop.*, GROUP_CONCAT(tie.category_id) AS category_id
FROM `prefix_workshop` workshop, `prefix_category_workshop_tie` tie
WHERE ( (workshop.title LIKE ? OR workshop.descr LIKE ?)
AND workshop.city LIKE ? AND workshop.state LIKE ?)
AND (workshop.id = tie.workshop_id)
GROUP BY workshop.id
ORDER BY ...
See MySQL docs for details of GROUP_CONCAT() function, like if you want a different separator and not comma, or you want to define the order of the category_ids in the list.
Note: The WHERE syntax you use for joining the two tables is very old. Try to start using the explicit JOIN instead:
SELECT workshop.*, GROUP_CONCAT(tie.category_id) AS category_id
FROM `prefix_workshop` workshop
JOIN `prefix_category_workshop_tie` tie
ON workshop.id = tie.workshop_id
WHERE (workshop.title LIKE ? OR workshop.descr LIKE ?)
AND workshop.city LIKE ?
AND workshop.state LIKE ?
GROUP BY workshop.id
ORDER BY ...
Besides the advantage of having the JOIN conditions near the JOINed tables and separated from further WHERE conditions, it's helpful because in many situations (like this one), a row from one table (a workshop) may not be related to any row in the second table (a category) at all. These workshops won't be shown with the code you have (and neither by the above JOIN example).
But you may still want to show them. With the WHERE syntax it gets complicated. WIth the JOIN syntax, you simply change JOIN to LEFT JOIN (so ALL columns from the LEFT table in the workshop LEFT JOIN tie relationship are included and not only the related ones).