SQL query not returning correct values

Question

Problem: I am learning more about SQL queries and I'm trying to understand why this function and query returns only the statuses that have a user_id of 26 and 25 but doesn't return anything from 27 or 31.

Question: Why is this not returning statuses for user 27 or 31? (I have confirmed that user 27 and 31 have statuses to return)

Here is the code below:

public function t_status($friends, $groups, $user_id, $start, $per_page, $db){

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

    $stmt = $db->prepare("SELECT * FROM statuses WHERE (user_id IN (:friend_array) && user_id IS NOT NULL) OR user_id = :auth_id OR (group_id IN (:group_array) && group_id IS NOT NULL  && group_id != 0) ORDER BY updated_at DESC LIMIT :offset, :limit");
    $stmt->bindParam(':auth_id', $user_id);
    $stmt->bindParam(':group_array', $group_array);
    $stmt->bindParam(':friend_array', $friend_array);
    $stmt->bindParam(':offset', $start, PDO::PARAM_INT);
    $stmt->bindParam(':limit', $per_page, PDO::PARAM_INT);

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

When I print_r($group_array) I get 51, and print_r($friend_array) gets 25,27,31. From what I can see that it is only getting statuses from the first user in the friend array 25 and ignoring 27 and 31.

I tested this inside phpmyadmin using:

SELECT * FROM statuses WHERE (user_id IN (25,27,31) && user_id IS NOT NULL) OR user_id = 26 OR (group_id IN ('51,') && group_id IS NOT NULL && group_id != 0) ORDER BY updated_at DESC LIMIT 0, 10

This worked fine. Any help would be greatly appreciated.

nospor · Accepted Answer · 2016-06-09 13:23:01Z

1

You set return $row; in while loop. return exit function immediately and any other iteration is not done. You should write your data in array and then return array:

$arr= array();
while($row = $stmt->fetch()) {
    $arr[]=$row;
}
return $arr;

edit:

there is also error with binding: WHERE (user_id IN (:friend_array)

because it is text id1,id2,id3 it will be treated as text not as array of ids. You shouldn't bind there this ids so it must look like this:

WHERE (user_id IN ($friend_array)

and remove bind method for :friend_array

$stmt->bindParam(':friend_array', $friend_array); - this must be removed

edited Jun 9, 2016 at 13:23

answered Jun 9, 2016 at 13:17

nospor

4,2301 gold badge18 silver badges26 bronze badges

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

2 Comments

Niall Over a year ago

Works now thanks and more importantly I understand why it didn't work. Your explanation of the array and binding problems helped a lot.

nospor Over a year ago

I am glad I could help :)

Tom H · Accepted Answer · 2016-06-09 13:23:47Z

1

I don't believe that PHP allows binding of arrays as parameters like that. This would explain why you're only getting results for the first value in the array. You'll need to convert the array into a comma-separated string and then build your SQL statement from that. Just be aware of the problem of SQL injection.

Also, using column IN (x, y, z) AND column IS NOT NULL is needlessly repetitive. If the column value is in the list of values it cannot be NULL. Even if NULL were in your IN list it would not match.

answered Jun 9, 2016 at 13:23

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Collectives™ on Stack Overflow

SQL query not returning correct values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest