Forum Moderators: coopster
some links appear in multiple categories.
i have successfully managed to do this as below. my question is. ...Is this the most efficient way to do this? i have read that it's not good form to store arrays in a database.
I have two tables
link_category made up of
linkcat_id
linkcategory e.g webdesigners
link_detail made up of
linkdetial_id
linkdtail_link
linkcat_id (istored in an array ie 1,3,6)
i am using the following query to pull out the relevant links in a category
SELECT * FROM `link_detail`
WHERE (FIND_IN_SET('1' , linkcat_id) > 0)
this pulls out all the links with a'1' in the array
any advice, Is there a alternative way to achieve the same result?
cheers
mat
The 'AS cat' above just keeps that field name from clashing with the one in the link_detail table.
would it be a huge leap to offer a user the ability to select link categories with checkboxes
[ ] webdesigner
[ ] webdesigner south london
[ ] webdesigner north london
presumably i would pass the selected items array to the results page, hmm not sure.
I tried adding an extra cat_id to the mysql statement with no success (below)
WHERE ((c.linkcat_id=1 )AND(c.linkcat_id=3 )AND (d.linkdetail_id=c.linkdetail_id))
This statement i realise would have to change dynamically.
Could this be a step too far i wonder.
Roll back from your monitor just a bit to have a look and you'll see why that WHERE phrase doesn't work - the category can't be both 1 and 3 simultaneously. You could do an OR, or you could do it as an array:
WHERE ((c.linkcat_id IN (1,3)) AND (d.linkdetail_id=c.linkdetail_id))
You could build either one dynamically without too much trouble, although I think the array is 'tidier'.
To process the checkboxes, you would foreach through the posted array and add a row to catmembers for each checked category.
