Jump to content

Use values from array to select values from diff table


son

Recommended Posts

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 by son
Link to comment
Share on other sites

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...