Using a variable in a select query

Question

The Problem
I am trying to create a website, where users can post posts and then their friends can see these posts. Similar to facebook, twitter etc.
I got to the point where users can be friends and they can post things. However, I am stuck restricting the shown posts to just the user's friends' posts. At the moment every user can see every post.
I am using PDO in a MVC architecture.

The database structure

TABLE `friends` (
    `friendship_id` int(11) NOT NULL AUTO_INCREMENT,
    `user_one` int(11) NOT NULL,
    `user_two` int(11) NOT NULL,
    PRIMARY KEY (`friendship_id`),
    FOREIGN KEY (user_one) REFERENCES users(user_id),
    FOREIGN KEY (user_two) REFERENCES users(user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In the table above, depending on who sent the friend request to who, 'user_one' can be either myself or my friend or 'user_two' can be myself or my friend.

TABLE `posts` (
 `post_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `post_text` text NOT NULL,
 `user_id` int(11) unsigned NOT NULL,
 PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The Model

    /**
     * Get all posts from the suer's friends
     * @return array an array with several objects (the posts)
     */
    public static function getAllPosts()
    {   
        $my_id = Session::get('user_id');

        $database = DatabaseFactory::getFactory()->getConnection();

        // get all of the user's friends
        $friendsQuery = $database->prepare("
                                    SELECT user_one FROM friends WHERE user_two = '$my_id';
                                    SELECT user_two FROM friends WHERE user_one = '$my_id';
                                    ");

        $friendsQuery->execute();
        $friends = $friendsQuery->fetchAll();
        $friendsQuery->closeCursor();

        $query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id = '$friends'");
        $query->execute(array());

        return $query->fetchAll();
    }

In the above code my problem lies in the $friends variable. If I replace it manually with the user_id of my friend it works as intended.
E.g.:

"SELECT * FROM posts WHERE user_id = '$my_id' OR user_id = '1'";

The Controller

    /**
     * Gets all posts (of the user and his friends).
     */
    public function index()
    {
        $this->View->render('post/index', array(
            'posts' => PostModel::getAllPosts()
        ));
    }

The View

       <?php if ($this->posts) { ?>
            <?php foreach($this->posts as $key => $value) { ?>
                <p><?= htmlentities($value->post_text); ?></p>
            <?php } ?>
        <?php } ?>

Summary
I am not able to create a sql query, which just selects posts, which were posted by myself or my friends and does not select any other posts.

I would be very thankful, if somebody could help me out with this!

UPDATE
My model now looks like this:

    /**
     * Get all posts from the suer's friends
     * @return array an array with several objects (the posts)
     */
    public static function getAllPosts()
    {   
        $my_id = Session::get('user_id');

        $database = DatabaseFactory::getFactory()->getConnection();

        // get all of the user's friends
        $friendsQuery = $database->prepare("
                                    SELECT user_one FROM friends WHERE user_two = '$my_id';
                                    SELECT user_two FROM friends WHERE user_one = '$my_id';
                                    ");

        $friendsQuery->execute();
        $friends = $friendsQuery->fetchAll();
        print_r($friends);
        $friendsQuery->closeCursor();

        foreach($friends as $friend)
        {
            $friend_ids[$friend->key] = $friend->val;
        }
        print_r($friend_ids);
        $friendsSQL = implode(',',$friend_ids);

        $query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id IN('$friendsSQL')");
        $query->execute();

        return $query->fetchAll();
    }

This is my output:

Array ( [0] => stdClass Object ( [user_one] => 1 ) )

Notice: Undefined property: stdClass::$key

Notice: Undefined property: stdClass::$val

Array ( [] => )

faltay · Accepted Answer · 2015-02-26 12:53:23Z

2

You can add all friends_id into an array and then use this code.

//$friends is an array.
$friendsSQL = implode(',',$friends);
$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id IN($friendsSQL)");

answered Feb 26, 2015 at 12:53

faltay

836 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Schwesi Over a year ago

Thank you for your answer! If I do this, I can just see my own posts.

faltay Over a year ago

Did you add IN operator into your SQL and what is $friendsSQL value?

faltay Over a year ago

firstly, you add all friend_id values into an array and use this array in implode function.

faltay Over a year ago

your $friends array contains result objects. So you define a new array and put all user_id values into this new array. It must contains id values instead of objects

faltay Over a year ago

$friend_ids = array(); foreach($friends as $friend) { $friend_ids[] = $friend->user_id; }

|

Community · Accepted Answer · 2017-05-23 10:10:17Z

1

You should note that $friendsQuery->fetchAll() will return an Array. Also, if PDO::FETCH_ASSOC is supplied as an additional argument [fetchAll(PDO::FETCH_ASSOC)], it would be an associative array.

The structure of the array could look like this :

Array ( [int] datasetNumber =>
    Array (
       [string] "friendship_id" => [int] 1,
       [string] "user_one"      => [int] 1,
       [string] "user_two"      => [int] 1       
    )
)

I gave some more Information about returning data from PDO Statements in this post.

edited May 23, 2017 at 10:10

CommunityBot

11 silver badge

answered Feb 26, 2015 at 12:53

Tacticus

57111 silver badges25 bronze badges

2 Comments

Schwesi Over a year ago

Thank you for your answer! I am not sure how this answers my question, though. I can display the posts just fine. The problem is that all posts are displayed and not just mine and the ones of my friends.

Tacticus Over a year ago

You put an array in a string; that can't work. The solution would be to "convert" the array into a string using a comma as separators, like faltay pointed out in their answer.

Horaland · Accepted Answer · 2015-02-26 13:06:57Z

-1

your $friends variable is being returned by a fetchAll() function so will probably be a list of results. Your then using that list as a string in the query which won't work. You need to extract the ID of the friend from the $friends list.

if you're using PDO then use the variable handling built into PDO ie:

$query = $database->prepare("SELECT * FROM posts WHERE user_id = :variable_ID OR user_id = '1'");
$query->bindValue( ':variable_ID', $my_id, PDO::PARAM_STR ] );
$query->execute();

answered Feb 26, 2015 at 13:06

Horaland

9078 silver badges14 bronze badges

1 Comment

Schwesi Over a year ago

Did you mean to write OR user_id = '1' at the end of your query?

Collectives™ on Stack Overflow

Using a variable in a select query

3 Answers 3

9 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related