Problem with mysql join

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).

It's still not completely clear what format you'd like your results in.

You could use the MySQL GROUP_CONCAT() method to combine all the category ids into one string, perhaps comma-separated.

I would think, however, that if you're going to use the category ids for anything other than display, then performing a second query, just for the ids, would be simpler and more useful - something like this:

SELECT category_id
FROM prefix_category_workshop_tie
WHERE workshop_id = $workshopId

If you're going to use the category ids to query the category table, then you don't need to store the ids in-between, you can do something like:

SELECT category_id category.somevalue
FROM category_workshop_tie
  WHERE workshop_id = $workshopId
LEFT JOIN category
  ON category.id = category_id

Both of those assume $workshopId has been set from the first query.