son Posted March 23, 2015 Share Posted March 23, 2015 (edited) Hi there, I have created one table that holds category names (catTable) and one table (itemTable) that has a field with comma separated list of category ids that are relevant for individual item (setup as VARCHAR). I tried 'SELECT catName FROM catTable WHERE catId IN (SELECT itemCatId from itemTable WHERE itemID = $itemID)' in WHILE loop that displays the individual items which should also show all names of relevant category names. It does not work and shows only first category rather than whole list. I suppose the WHERE IN function is probably incorrect, but am not sure if I need to change the cell type and/or the query. Any pointers appreciated. Son Edited March 23, 2015 by son Link to comment Share on other sites More sharing options...
justsomeguy Posted March 23, 2015 Share Posted March 23, 2015 You're violating relational database design by putting multiple values in one field. You should have a separate table that lists the item ID/category ID pairs. That's how a many-to-many relationship should be represented. Link to comment Share on other sites More sharing options...
son Posted March 24, 2015 Author Share Posted March 24, 2015 Thanks for pointing this out. Have setup a new table now with three fields: combination ID, item ID, category ID. As I have lots of data sitting in the item table wondered if there is a good way to extract the info to the new table? It would need to transfer each itemID/categoryID combination to the new table. For example 'itemID (1), categoryIDS (1,2,3)' would become 'itemID (1), categoryIDS (1)', 'itemID (1), categoryIDS (2)' and 'itemID (1), categoryIDS (3)'. The category ids are in field as a comma-separated list without spaces... Son Link to comment Share on other sites More sharing options...
justsomeguy Posted March 24, 2015 Share Posted March 24, 2015 I don't know of a way to do that with SQL alone, I would write a PHP script to get the data from one table and add it to the other. Link to comment Share on other sites More sharing options...
son Posted March 25, 2015 Author Share Posted March 25, 2015 Thanks for this. Could only think in vague PHP-terms to solve this and it is great to have the confirmation that I am not missing a more direct way of achieving this. Will have a go now:-) Son Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now