Niall Niall - 5 months ago 15
SQL Question

SQL query not returning correct values

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.

Answer

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