CODEWITHSUNDEEP
phpmysqlpdo
Adam Cole
Adam Cole

Reputation: 173

Why does my SQL statement only return one record?

I am making a query using PHP PDO to a database in which i am looking for all users where a number of foriegn keys in the table match that of an array outputted by a previous PDO object.

$in = "";
foreach ($parent_ids as $i => $item)
{
    $key = ":".$i;
    $in .= "$key,";
    $in_params[$key] = $item; // collecting values into key-value array
}
$in = rtrim($in,",");

$sqlRequestParentInfo = "SELECT * FROM academy_user WHERE user_id IN ($in)";
$stmt = $dbConn->prepare($sqlRequestParentInfo);
var_dump($stmt);
$stmt->execute($in_params);
$parent_info = $stmt->fetchObject();

/*echo print_r($ids);
echo json_encode($parent_ids);*/
echo "<pre>";
echo "<br> PARENT INFO <br>";
var_dump($parent_info);
$parent_info = json_encode($parent_info);
echo $parent_info;
echo "</pre>";

The array parent_ids is an array of a key formatted in the following way: parent3_id_fk, parent4_id_fk, etc.

And the value of each of these keys is a 3 digit number representing the foreign key of a 'parent' assigned to a 'student' in a table.

Using these foreign keys i am querying academy_user to find information on these parents. So i use the WHERE user_id IN (values) SQL statement. It seems to run fine except i only get one record returned for user '426', even though there is also a user '427' foreign key in this table.

This is the var_dump of parent_info:

object(stdClass)#2 (11) {
  ["user_id"]=>
  string(3) "426"
  ["user_email"]=>
  string(11) "[email protected]"
  ["password"]=>
  string(60) "$2y$10$V4sPIs3fdJ7F49cNtl8eyufuLGu9leTUH4Fl5VUUAcgq4zB/NzUn6"
  ["f_name"]=>
  string(1) "a"
  ["l_name"]=>
  string(1) "a"
  ["address_id_fk"]=>
  NULL
  ["access_lvl"]=>
  string(1) "3"
  ["date_created"]=>
  string(19) "2019-03-28 13:42:15"
  ["date_gdpr_remove"]=>
  NULL
  ["approved"]=>
  string(1) "1"
  ["requested_access_fk"]=>
  string(1) "3"
}

This is just sample data. Any ideas?

EDIT: This is what the SQL statement looks like before the parameters are injected in then executed:

SELECT * FROM academy_user WHERE user_id IN (:parent1_id_fk,:parent2_id_fk,:parent3_id_fk,:parent4_id_fk,:parent5_id_fk,:parent6_id_fk,:parent7_id_fk,:parent8_id_fk,:parent9_id_fk)

Upvotes: 0

Views: 103

Answers (1)

Nick
Nick

Reputation: 147166

You're only getting one piece of data returned because you're only calling $stmt->fetchObject() once. You need to call it in a loop and get an array of parent_info:

$stmt->execute($in_params);
$parent_info = array();
while ($parent = $stmt->fetchObject()) {
    $parent_info[] = $parent;
}
echo json_encode($parent_info);

Upvotes: 4

Related Questions