mysql function, return name instead of id

Question

Hi I have Table tbl_itemlist with

id | itemName | suppliers
1  | uSDReader| 1,2,3,4,5

the suppliers column contains comma delimited value ex: (1,2,3,4,5)

Which correspond to suppliers name from other table tbl_supplier

id | supplierName
1  | Supplier 1
2  | Supplier 2
3  | Supplier 3
4  | Supplier 4
5  | Supplier 5

Is there any builtin function of MySQL that when I query using SELECT the itemlist table, something like JOINs, supplier column will return the name instead of supplier's id only. ex:

 id | itemName | suppliers
 1  | uSDReader| Supplier 1,Supplier 2,Supplier 3,Supplier 4,Supplier 5

See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Jan 27 '15 at 00:13
Incredibly bad design: you really should normalize your database — Mark Baker, Jan 27 '15 at 00:13
Any best way to do that? I can redisign the schema, 'comma delimited is just my ex.' — user3774278, Jan 27 '15 at 00:40

score 0 · Accepted Answer · answered Jan 27 '15 at 00:40

You indeed have a really bad MySQL schema. You have to create 3 tables named as tbl_itemlist, tbl_suppliers, tbl_itemsupplies.

tbl_itemslist and tbl_suppliers are the same as yours with tbl_itemslist with suppliers column deleted.

Your new table, tbl_itemsupplies, must contain these exact columns: id, item_id, supplier_id

You can check which suppliers does have the item with a simple query like:

SELECT `supplier_id` FROM tbl_itemsupplies WHERE `item_id`= {$itemid}

mysql function, return name instead of id

1 Answers1