Foreach loop only showing last item in array in api

Question

I am making a CRUD API for my android app, all this API needs to do is check the database for information regarding an indeterminate incoming user ID's and return the query results to the app(the R in CRUD). I have gotten the API to query and output both single item arrays and the last Item in the array.

my code:

$jsonobj = '{"code":"ascaasease","code":"ascaesease","code":"onetoelevn"}';
//mock json
$obj = json_decode($jsonobj);
foreach($obj as $key => $value) {
    $sql = "SELECT code, header, body FROM `messages` WHERE code= '$value'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
        $response['code'] = $row["code"];
        $response['header'] =  $row["header"];
        $response['body'] = $row["body"];
        $array_push[] = $response;
        }
    }
    mysqli_close($conn);
}
$return = array("resoponse"=>array("check"=>"check"),"array"=>$array_push);
echo json_encode($return);

in this configuration, it will just output the final item in the array ("code","onetoelevn"). If the formating is dropped, and you tell the script to just echo the response it still only displays the last item in the array.

if you could tell me how to run an SQL query on each item in the array I would be very thankful.

Maybe use `in` instead of `=` and use `implode`. Should look into parameterized queries and prepared statements as well — user3783243, Nov 20 '20 at 00:12
Also no need for `$response`, `$array_push[] =$row;` or probably better yet, https://www.php.net/manual/en/mysqli-result.fetch-all.php — user3783243, Nov 20 '20 at 00:12
the main issue though is the closing connection `mysqli_close` after first loop (edit.. and the SQL Injection problem) — Lawrence Cherone, Nov 20 '20 at 00:13
The cause of your error is that you are redeclaring `$array_push` in each iteration, thus ending up only with the values from the last iteration. This is just FYI, to help you understand the problem, you should follow other commentators' advice about eliminating the loops altogether. — El_Vanja, Nov 20 '20 at 00:38
@El_Vanja That is wrong. `[]` generates a new indice everytime. — user3783243, Nov 20 '20 at 02:42
I also keep coming back to that JSON. It sure looks to me like there’s no array there; just an object that assigns `code` twice. — Tim Morton, Nov 20 '20 at 04:49
@user3783243 True, that was silly of me. And I was focused on the flow so much I didn't give a second look to the payload. @TimMorton is right on the money - if you dump `$obj`, you should see that the `code` property has simply been overwritten by the last occurrence of that key in your json. — El_Vanja, Nov 20 '20 at 08:23
@El_Vanja Not overwritten, the `mysqli_close` makes all the other queries fail because there's no connection. So `if (mysqli_num_rows($result) > 0) {` never is met and only is 1 returned value — user3783243, Nov 20 '20 at 09:34
@user3783243 `$obj = json_decode($jsonobj);` will give a `stdClass` object with a `code` property of value `onetoelevn` because it's the last occurence of a duplicate key in the json. That's what my previous comment was referring to. There won't even be three keys to iterate over. It was a good catch by Tim. — El_Vanja, Nov 20 '20 at 09:48

Dharman · Accepted Answer · 2020-11-21T17:18:35.170

First of all, your mock JSON is broken. It contains repeated properties.

If you must use a loop (which you don't have to) you can prepare the statement before the loop and execute within. Then use get_result() and fetch_all() to get the data.

mysqli

//mysqli connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli('localhost', 'user', 'password', 'db_name');
$conn->set_charset('utf8mb4'); // always set the charset

$jsonobj = '[ "ascaasease", "onetoelevn"]';
//mock json
$obj = json_decode($jsonobj);

$stmt = $conn->prepare('SELECT code, header, body FROM messages WHERE code=?');
$stmt->bind_param('s', $value);

$array_push = [];
foreach ($obj as $key => $value) {
    $stmt->execute();
    $data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $array_push = array_merge($array_push, $data);
}
$return = array("resoponse" => array("check" => "check"), "array" => $array_push);
echo json_encode($return);

If you don't have to use a loop, then you can use WHERE IN()

$jsonobj = '[ "ascaasease", "onetoelevn"]';
//mock json
$obj = json_decode($jsonobj);

$wherein = str_repeat(',?', count($obj) - 1);
$stmt = $conn->prepare("SELECT code, header, body FROM messages WHERE code IN(? $wherein )");
$stmt->bind_param(str_repeat('s', count($obj)), ...$obj);
$stmt->execute();
$array_push = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

$return = array("resoponse" => array("check" => "check"), "array" => $array_push);
echo json_encode($return);

PDO

Of course, all of this would be simpler if you were using PDO. If it is not too late, can you consider switching over to PDO instead of using mysqli?

Option 1:

// PDO connection
$pdo = new PDO("mysql:host=localhost;dbname=db_name;charset=utf8mb4", 'user', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

$jsonobj = '[ "ascaasease", "onetoelevn"]';
//mock json
$obj = json_decode($jsonobj);

$stmt = $pdo->prepare('SELECT code, header, body FROM messages WHERE code=?');

$array_push = [];
foreach ($obj as $key => $value) {
    $stmt->execute([$value]);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $array_push = array_merge($array_push, $data);
}
$return = array("resoponse" => array("check" => "check"), "array" => $array_push);
echo json_encode($return);

Option 2:

$jsonobj = '[ "ascaasease", "onetoelevn"]';
//mock json
$obj = json_decode($jsonobj);

$wherein = str_repeat(',?', count($obj) - 1);
$stmt = $pdo->prepare("SELECT code, header, body FROM messages WHERE code IN(? $wherein )");
$stmt->execute($obj);
$array_push = $stmt->fetchAll(PDO::FETCH_ASSOC);

$return = array("resoponse" => array("check" => "check"), "array" => $array_push);
echo json_encode($return);

thank you for your answer, But it does not work. it says:Fatal error: Uncaught Error: Call to a member function bind_param() on bool in C:\xampp\htdocs\appscript.php:9 Stack trace: #0 {main} thrown in C:\xampp\htdocs\appscript.php on line 9 for both ways of doing it. I would be very interested to see how you can do this in PDO if you could show it to me or send me a link on how its done I would be very appreciative. — seigmüller, Nov 21 '20 at 17:12
@seigmüller This means you haven't enabled mysqli error reporting. See [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Nov 21 '20 at 17:14
you beautiful man, all 4 of these worked (with some fiddling with localisation so watch out if you came here with the same problem that i had). thank you — seigmüller, Nov 21 '20 at 19:17

Foreach loop only showing last item in array in api

1 Answers1

mysqli

PDO