Jump to content
Sign in to follow this  
son

Use values from array to select values from diff table

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

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...