Is this the best way of listing a user's "Favourites"?

[MortimerJazz]

Personally I think I've gone around the houses a bit here, but I'm not sure how else to do it.

I've added a "favourites" field to my "users" table and I was planning to insert the id of each favourite into this filed followed by a "|". That way, when listing them I could pull all the numbers out of that field and explode them around the "|" symbol.

Here's the code I've got so far:

[code]
echo("Favourites");

/* Pull the list of numbers out of the users table */

$recent_sql = mysql_query("SELECT favourites
  FROM users");




/* Explode the numbers around the "|" symbol and push them into an array*/

while ($recent = mysql_fetch_array($recent_sql))
    {

        $id=$recent['id'];
$favourites=$recent['favourites'];
}
$fav_array = explode("|", $favourites);
$total=count($fav_array);

}

/*Use a loop to cycle through the array pulling out the relevant details from the "Toy" table*/


for($i=0; $i<=$total; $i++){

$recent_sql = mysql_query("SELECT *
  FROM toys
WHERE id='$fav_array[i]'
ORDER BY price DESC");


while ($recent = mysql_fetch_array($recent_sql))
    {

$type=$recent['type'];
                $price=$recent['price'];
$pic1=$recent['pic1'];
$i++;

[/code]

Have I made a proper dog's ear of this? Because I can't seem to get it working at the moment ...

[Jenk]

Create a new table called favorites, with an id field, a user id field and a field for the favorite what ever it is you are calling favorites.

[code]SELECT `favorite` FROM `favorites` WHERE `user_id` = '$userid';[/code]

[MortimerJazz]

[quote]Create a new table called favorites, with an id field, a user id field and a field for the favorite what ever it is you are calling favorites.[/quote]

Thanks for that Jenk - does that mean that users will only be allowed a limited number of favourites then depending on how many filelds I create for the table?

[Jenk]

No, you won't be creating new fields, you'll be adding rows. A new row for each and every favorite out there. The user id field is to identify which user owns the favorite, hence my SQL snippet above.

If you want the users to only have, say 5, fav's then you can just simply count the number of existing favorites. [code]SELECT COUNT(`id`) FROM `favorites` WHERE `user_id` = '$userid';[/code]

[MortimerJazz]

Ok - I think I get it now.

So how would I access information from two tables? eg. I take the 'id' out of the favourites table and then display the information from another table that matches that id.

Would I need to use:
[code]mysql_data_seek($recent_sql, 0); [/code]

[Jenk]

JOIN's.

[MortimerJazz]

Sorry Jenk, I dont quite understand what you mean.

The way I've been doing it in the past, I've been using two mysql queries to search the relevant tables, but that's when I've only been searching for one piece of data.

If, for example, somebody had 5 favourites what would be the most server efficient way of getting the relevant information? Would I have to insert each id number into an array and then loop through that?

I'm still learning the most efficient way to access databases - but I really appreciate your help with this.