mysql函数，返回名称而不是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

Answer 1

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}