Using stored info, multiple numbers one column?..

I have a mysql table full of items, and I have a field called "related". Each item has a unique id called id. I'm wondering what's the best way to store the id's of related items?

id ¦ name ¦ related
1 ¦ name1 ¦ 2,4,9
2 ¦ name2 ¦ 12,1,4
3 ¦ name3 ¦ 9,29,7
4 ¦ name4 ¦ 34,2,2

etc, etc, etc. There are more fields but unimportant for what I am trying to accomplish.

Is this a good way of storing them? If not what is? And how would I be able to use that info usefully? Can you write a PHP script to separate them? I'm thinking of using a script with while() to go through and list all the items that are related with their name and description. Thanks for your help.

 

While you could store the related IDs in the same table in a field, that would be pretty hard to maintain. A better approach (in the vein of relational databases) would be to store related item records in a seperate table, e.g:

table: related
id - related primary key
itemid - original item
relatedid - related item id

This makes it incredibly easy to add/remove related items, and you can use simple queries to gather the related items for a given id.

If you still want to go ahead with storing the related ids in a field, explode(",", $var) will turn the text into an array of IDs for you, implode(",", $var) will turn it back into a string.

 

You can easily use split, or explode to seperate each number in that field.

Something like:

//do database stuff up here

for ($i; $i <= $num_rows; $i++){
$my_array = mysql_fetch_array($my_query);
$id = $my_array['id'];
$name = $my_array['name'];
$related = $my_array['related'];

$related_array = explode(",",$related);

//each number will be a seperate value in the array
$number1 = $related_array[0];
$number2 = $related_array[1];

//etc...


}

PHP:

 

or you can use http://www.php.net/serialize