I have concluded that my first quick fix of storing an array of ids in a singular database field (1,5,48) is probably not best but if I break them out into a joining table of 'parent item id' (singular) and then sub related item id (multiple) which links to a separate table it would be better.
But now I am unsure what mysql query to use to get matches.
So a search form is submitted where "related_item" array is "1,5,8" and one of my "parent_items" has related item matches of "1" and "8" in the joining table....
So what mysql query would return these matches?
Something like this... its unclear to me form you description if you are "searching" children or parents. I think you mean to search children so:
-- SEARCH CHILDREN -- SELECT p.*, c.* FROM child_table c LEFT JOIN linking_table l ON (p.id = l.child_table_id) LEFT JOIN parent_table p ON (l.parent_item_id = p.id) WHERE c.id IN (1,5,4,8) -- OPTIONAL AND CLAUSE for p.id = ? --
If you mean to search parents then you can just rework the order, although you could use the exact same query with the exception that your WHERE IN clause would be on
p.id instead of
Be aware though that this will get you 1 row per match for the children so you will have the same parent multiple times. If you dont need the actual child data then you could use
DISTINCT to only return one instance.