CODEWITHSUNDEEP
mysqlcodeignitermany-to-many
MarkC
MarkC

Reputation: 179

Best way to handle many-to-many relations with Codeigniter and MySQL?

I really couldn't find anything useful. I'm making my own Admin panel with Codeigniter and MySQL, and everything is done, except for the many-to-many relations.

The main table is Product table. I have 5 many to many relations (Categories, Materials, Colors, Tags, Cities). There are 5 tables for each of them with these columns (ID, and name of category/tag/city...) and of course there are tables between that has both IDs of the Product and the other table.

As first, I want to list all the products in one table, and in the columns I want to have column for categories, materials, tags, etc. and in the corresponding to cell, all the items to be listed with comma...

Here is how one row should look like.

| ID | Name | Description | Category 1, Category 2, ... | Material 1, ... | Tag1, Tag2, Tag3, Tag4, ... |

I hope you understood me. And next, hopefully, the Add function seems easy, but the Update function should be really tricky I guess, so any help for there is also appreciated, hot to solve it to be as fast as it can. I guess that first I should check for all the selected items to check if there is any new entry that is not in the linking table, and then to check if any of the unselected is in the linking table to delete it. How to do this to be most optimized?

Upvotes: 1

Views: 1079

Answers (1)

dakdad
dakdad

Reputation: 2955

This is not directly a codeigniter thing, more of a thing with SQL.

Managing the display is the simple part. You can join all the related tables and GROUP BY your product_id so your result set only displays a single product once. Then you can use the GROUP_CONCAT function of MySQL to group the related table fileds per row.

The performance of this really depends on the application and its usage. For example, with an app we have, it is quicker and more responsive to run multiple queries per row returned by the product_id to get the other information, rather than running one single query to get all information in one hit. So, you have to test it out.

With the update, yes, it can be messy. One way is to drop all existing by product_id and then add all the submitted ones. Another is to check the existing ones against the submitted ones and only add the new ones and remove the ones that don't match. I hope that makes sense to you :)

Upvotes: 2

Related Questions