CODEWITHSUNDEEP
phpmysql
wordsongoku
wordsongoku

Reputation: 13

Only one query instead of two

I have 2 tables, one is called post and one is called followers. Both tables have one row that is called userID. I want to show only posts from people that the person follows. I tried to use one MySQL query for that but it was not working at all.

Right now, I'm using a workaround like this:

$getFollowing = mysqli_query($db, "SELECT * FROM followers WHERE userID = '$myuserID'");
while($row = mysqli_fetch_object($getFollowing))
{
    $FollowingArray[] = $row->followsID;
}
    if (is_null($FollowingArray)) { 
    // not following someone
    } 
    else {
    $following = implode(',', $FollowingArray); 
    }


$getPosts = mysqli_query($db, "SELECT * FROM posts WHERE userID IN($following) ORDER BY postDate DESC");

As you might imagine im trying to make only one call to the database. So instead of making a call to receive $following as an array, I want to put it all in one query. Is that possible?

Upvotes: 0

Views: 62

Answers (1)

Drathier
Drathier

Reputation: 14519

Use an SQL JOIN query to accomplish this.

Assuming $myuserID is an supposed to be an integer, we can escape it simply by casting it to an integer to avoid SQL-injection.

Try reading this wikipedia article and make sure you understand it. SQL-injections can be used to delete databases, for example, and a lot of other nasty stuff.

Something like this:

PHP code:

$escapedmyuserID = (int)$myuserID; // make sure we don't get any nasty SQL-injections

and then, the sql query:

SELECT *
FROM followers
  LEFT JOIN posts ON followers.someColumn = posts.someColumn
WHERE followers.userID = '$escapedmyuserID'
ORDER BY posts.postDate DESC

Upvotes: 3

Related Questions