1

I updated this question as it was pointed out to change the query and the how implode was used. I have added the query again after reading through the suggested SO posts. I am still learning PDO so I apologize if I made a mistake.

Here is the query:

$group_array = implode(',', $groups);
    $friend_array = implode(',', $friends);

    $stmt = $db->prepare("SELECT *
                          FROM statuses
                          WHERE parent_id = 0
                           AND (   user_id = :auth_id
                                OR FIND_IN_SET(group_id, :group_array)
                                OR FIND_IN_SET(user_id, :friend_array)
                               )
                          ORDER BY updated_at DESC LIMIT :start,
                                                         :per_page");

    $stmt->bindParam(':auth_id', $user_id);
    $stmt->bindParam(':group_array', $group_array);
    $stmt->bindParam(':friend_array', $friend_array);
    $stmt->bindParam(':start', $start);
    $stmt->bindParam(':per_page', $per_page);

    $stmt->execute();
    if ($stmt->rowCount() > 0) {
        while($row = $stmt->fetchAll()) {
            return $row;
        }
    }

When I var_dump the friends and group I get the following results:

friend array: array(2) { [0]=> string(2) "25" [1]=> string(2) "27" }

group array: array(2) { [0]=> NULL [1]=> NULL }

And when I test it using the following query it only returns statuses with the user id of 26(auth_id) and nothing from the friend array id's 25 or 27.

SELECT * FROM statuses WHERE parent_id = 0 AND (user_id = 26 OR FIND_IN_SET(group_id, '') OR FIND_IN_SET(user_id, 27)) ORDER BY updated_at DESC LIMIT 0, 10

Can anyone tell me shed some light on this?

Ryan Vincent
  • 4,438
  • 7
  • 21
  • 31
Niall
  • 784
  • 9
  • 26
  • What's in `$friends` and `$groups` ? – dimlucas May 25 '16 at 12:38
  • 1
    If you're going to use prepare, please use prepared statements properly. You can find examples in [the documentation](http://php.net/manual/en/pdo.prepare.php). – Jonnix May 25 '16 at 12:38
  • There is one user_id called 6 in $friends and there is nothing in $groups. – Niall May 25 '16 at 12:38
  • 7
    Using `prepare` and `IN (".implode(',', $groups).")` just makes me facepalm – Mark Baker May 25 '16 at 12:38
  • There's nothing in groups? I would expect an SQL syntax error. – Jonnix May 25 '16 at 12:39
  • I think the syntax `group_id in ()` might be invalid if there is nothing between those parentheses (same for friends). As a side note: You have a redundant set of parentheses around the whole expression, starting before `user_id IN ` you need only one, and at the end you need only two. – GolezTrol May 25 '16 at 12:40
  • @JonStirling Where does that page contain a sample of using a prepared statement with `IN` and any number of values? – GolezTrol May 25 '16 at 12:42
  • @GolezTrol It doesn't that's a whole other kettle of fish. `LIMIT $start, $per_page` on the otherhand... – Jonnix May 25 '16 at 12:43
  • 2
    For prepared statements with IN, reading http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition?rq=1 might help. – Jonnix May 25 '16 at 12:45
  • I updated my question using an example I got from the above SO posts. – Niall May 26 '16 at 07:02

0 Answers0