MySQL LEFT JOIN array, how do I return multiple rows?

Question

I have the following code which has worked perfectly up until now:

$db->table = "horse_products as p";
$db->colums = "p.*, l.address as address, l.storename as supplier_name, c.title as cat_title, c.id_parent as id_parent";
$db->join =    "LEFT JOIN locations as l ON l.id = p.id_location"
                ." LEFT JOIN categories as c ON c.id = p.id_category WHERE c.id_parent = 8";
$db->orderBy = "p.id_location ASC, p.id_product ASC";
$items = $db->Select();

Previously p.id_category was an integer because only one category within the 'categories' table was allowed to be associated with a product. However today I decided to make p.id_category a comma separated array. The downside to this is that when I try to echo $item['cat_title'], I only get the first c.title within the p.id_category array. I would like to get the title for all the categories within p.id_category. Can anyone help me here? I hope that I've made myself understood, as a MySQL novice even getting my LEFT JOIN's to work was a challenge :-)

EDIT:

Sat myself down this morning to find a solution and found it in the answer to the following question: MySQL: How to fetch data with left-join if column contains multiple ids?. Thanks to all that helped.

Filip Kováč · Accepted Answer · 2016-07-15 23:47:34Z

1

Bad idea beacause id_category should be unique integer value. If you want to have m:n relationship you need add another table with at least two primary foreign keys from tables that you want to have relationship. So in your case you will create table title_to_categories with primary key id_product and id_category. Id_product will be equals to p.id and id_category to c.id.

answered Jul 15, 2016 at 23:47

Filip Kováč

5878 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

luke_mclachlan Over a year ago

Thank you Filip. I am going to have to store these relationships in separate tables in the near future because my database is growing with ever more interrelationships. For now though I used GROUP_CONCAT() and FIND_IN_SET() which did the trick short term.

Filip Kováč Over a year ago

Ok for next time when your creating database in php thinking about model before you start using it. That will save you a lot of your time.

uncklegwebdev · Accepted Answer · 2016-07-15 23:43:56Z

0

Try with:

$items= $db->fetchAll();

And then go with foreach.

answered Jul 15, 2016 at 23:43

uncklegwebdev

622 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL LEFT JOIN array, how do I return multiple rows?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related