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
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}