php linking two tables using explode and one string containing multiple values

Question

This is my first question, so hope it makes sense.

How can I link two tables using php/mysql when one contains a string with multiple values.

Basically, one table is myMembers table and contains a "friend_array" where multiple ID's are stored in a single string.

The other table contains a list of all my sites users posts (tweets as such).

How do I link both tables to display all a user's friends posts by the newest post?

// myMember table

id, friend_array,
1,  1,2
2,  2,5,7
.
.

// posts table

id, mem_id, post,     post_date
1,  5,      PHP ole,  2011-08-11 11:30
2,  2,      AJAX ole, 2011-08-12 13:10

Hope this makes sense. Thank you all in advance

Answer 1

I would restructure the table if possible so that you get a structure something like the following:

posts

id, mem_id, post,     post_date
1,  5,      PHP ole,  2011-08-11 11:30
2,  2,      AJAX ole, 2011-08-12 13:10

Where id is the primary key

mymembers

id, name
1,  member1
2,  member2

Where id is the primary key

memberconnections

id, mem_id, friendid
1, 1, 1
2, 1, 2
3, 2, 2
4, 2, 5
5, 2, 7

Where id is the primary key and friendid is the foreign key

This way you can easily create a query to get the info you want.

Using a query for this n stead of PHP stuff is fatster and better and the way to do it.

Answer 2

If you want to keep your database structure, you can use FIND_IN_SET() and a subquery. That doesn't split up the CSV values like in a real IN clause, but has the same effect:

SELECT *
  FROM posts
 WHERE FIND_IN_SET(mem_id,
           (SELECT friend_array FROM myMember WHERE id=2)
       )

Answer 3

If I'm unstanding correctly

mysql_query("SELECT * FROM POSTS WHERE mem_id IN( SELECT friend_array FROM myMember WHERE id=" . $id);

Where $id is the id of the current member you wish find friends for.

Answer 4

You can do it easily with PHP. You shouldn't even need to explode. Depends how you do this; basics of it are:

    $q = mysqli_query("SELECT * FROM myMember WHERE id = " . $id);
    while($info = mysqli_fetch_array($q)) {
     $p = mysqli_query("SELECT * FROM posts WHERE mem_id IN(".$info['friendArray'].") ORDER BY post_date DESC");
    while($pinfo = mysqli_fetch_array($p) {
echo $pinfo['post_title']; // etc...
}
    }

Granted you don't want to be doing queries inside a loop if you have hundreds of posts, in which case I find grabbing relevant info and storing it in 2 separate arrays, and then running a foreach loop to be less intensive.

Normalising your database would also be less intensive... someone else posted a suggestion to that below/above.