I want to store my data in the best possible way, so that when I come to query it, I don't have to make a loop of infinite size to get all the data I need.
I want to create a tree of related content as follows:
Items table:
Item ID | Item name
--------------------
1 | Pyjamas
2 | Car
3 | Toy Cat
4 | Phone
Connections Table:
Item ID | Connected Item ID
----------------------------
1 | 2
2 | 3
3 | 4
When I look at item 1, I want to show all the related content, which will include items 2,3 and 4, because Item 1 is related to item 2, item 2 is related to item 3 and item 3 is related to item 4 but I don't want to have to loop through mysql queries every time a new connection ID is found as this could lead to an inordinate number of mysql queries running on the website.
I could achieve the desired result with something like this pseudo code:
while(connected_item_id !== '') {
SELECT * FROM connections WHERE id = connected_item_id;
}
This would run as many queries as there are connected items, but that seems ludicrously inefficient and I would soon run out of memory or something bad. I did think about storing an array (comma separated values) of the connected IDs all together so that I can just run one query to get all the connected items but I gather that it is bad practice to store CSV data?
Item ID | Connected Item IDs
----------------------------
1 | 2,3,4
I could add and remove IDs from this CSV list in PHP every time a new connection is made or removed but don't want to do anything hacky.
What is the best solution? I assume there is a crafty way of running one query to loop through all the connections or something but I am not aware what it might be.
