Max Max - 1 month ago 6
MySQL Question

Return all the posts of people followed by user Mysql

I have three tables

table_users: id_user | name
table_posts: id_post | id_user | post
table_follows: id_follow | id_follower | id_followed


The first table shows the users, the second one the posts posted by them & and the third one, the relations amongst the users (follower/followed)

So for example if I have the user Mattew
id=3
, I want to retrieve the posts "posted" by all the people that he follows. I this case, Adam
id=1
, Tom
id=7
& Zoe
id=9
are followed by Mattew.

I have developed a working
php/mysql
code but I would like to improve it using only one
SQL
statement.

//retrieve all the people followed by Mattew
$followeds=mysql_query("SELECT * FROM table_follows WHERE id_follower='3' /*Mattew*/");

//create an array with the ids
$array_followeds=array();

while($fwd=mysql_fetch_array($followeds)){
$id_followed=$fwd['id_followed'];
$array_followeds[]="id_user='".$id_followed."'";
}

//if exists people followed by Mattew
if(count($array_followeds)>0){
$array_followeds=implode(' OR ', $array_followeds);
}else{/*$array_followeds="id_user=0";*/}

//main query: shows all the posts of the people Mattew follows
$main_query=mysql_query("SELECT * FROM $table_posts WHERE ($array_followeds) AND id_user!='3'/*Mattew*/");

while($posts=mysql_fetch_array($main_query)){
/*results */
}


I would like suggestions of how to improve the
Sql
query using a single statement and avoiding usin the array.

Answer

Choose what fields you need

SELECT f.*, p.*
FROM table_follows f
JOIN table_posts p
  ON f.id_followed = p.id_user 
WHERE f.id_follower = '3'
Comments