CODEWITHSUNDEEP
phpmysql
Mitch8910
Mitch8910

Reputation: 183

Special tables in MySQL

I want to make a table that will store an ID and a list of items that ID has. I want to be able to store multiple IDs in the table, each with a list of items they own, but the amount of items will be different for each ID. Is this possible? Or will I have to make a table for every ID to store their items. The amount of items for each ID will be different still, so I'm not sure how I'd do it either way. The list of items are stored in an array. I'm going to have the user click an 'update' button, which I want to find thier ID in the database, then overwrite their old list of items with the new one.

I have been stuck on this for a long time, the more specific answer of how I could do this or helping me, the better. Thanks.

Upvotes: 0

Views: 202

Answers (1)

MueR
MueR

Reputation: 977

Why do you want to store everything for an ID in a single record? While technically possible with mysql's FIND_IN_SET function, it is bad for performance. A better idea would be to use a link table, with a record for each item per ID. As long as your ID column is an index, it's going to outperform FIND_IN_SET any time. MySQL itself does not know arrays, so you will need to serialize the data to either php serialized strings, json, comma separated values or any other structure.

Upvotes: 1

Related Questions