array in query returns only result of first value

Question

I am learning about PHP, so I try to do some exercises. Now I wanted to upload an array in JSON format to my script and insert this array into the query. To test it I used three fixed values in the array. The problem I have is that only the result of the first element of the array is returned and not all three. I thought about fetching multiple values wrong, but with a fix condition providing more then one result it does work fine. I also thought maybe the array is passed wrong, but when I printed it from the script I got it returned completely like this:

(
    [0] => 141
    [1] => 145
    [2] => 54
)

Does anyone know where it is thought wrong?

My script:

<?php 
    include 'DBConfig.php';

    $conn = new mysqli($servername, $username, $password, $dbname);

    if(isset($_POST['arrayList'])){

        $JSON_Received = $_POST['arrayList'];
        $obj = json_decode($JSON_Received, true);

        $matches = implode(',', $obj);

        $query = "SELECT * FROM TestTable WHERE id IN ('$matches')";

        $result = mysqli_query($conn, $query);

        while($row = mysqli_fetch_assoc($result)) {
            $array[] = $row;
        }
    }

    header('Content-Type:Application/json');
    echo json_encode($array);
    mysqli_close($conn);
?>

I read it back into my app and my logcat only returns D/TAG: Response: [{"id":"145","name":"x"}]. It looks like that's pretty much it, but I don't have an explanation why — user9155899
– user9155899, Commented Sep 21, 2018 at 16:00
What does your HTML look like, and what values are you entering into the form? — miken32
– miken32, Commented Sep 21, 2018 at 16:01
You can do $query = "SELECT * FROM TestTable WHERE id IN (".implode(',', $obj).")"; to avoid one line and if $obj looks like an array like this $obj = array(0=> your_id, 1=> your_id, ...); — Mickaël Leger
– Mickaël Leger, Commented Sep 21, 2018 at 16:05
@MickaelLeger For some reason it did the trick for me to follow the suggestion and include the implode() directly in the query! Do you have any idea why that might cause such a problem? — user9155899
– user9155899, Commented Sep 21, 2018 at 16:08
Your ids are INT var, so when you do ...id IN ('$matches') you will have something that looks like this '141, 145, 54' (a STRING) instead of 141, 145, 54 (a list of INT). You can try to write ...id IN ($matches) to check if it works too :) — Mickaël Leger
– Mickaël Leger, Commented Sep 21, 2018 at 16:13

Mickaël Leger · Accepted Answer · 2018-09-21 16:16:58Z

2

When you write WHERE id IN ('$matches') the result will be WHERE id IN ('141, 145, 54'), so you have a STRING and id are INTEGER.

Try to replace :

$matches = implode(',', $obj);
$query = "SELECT * FROM TestTable WHERE id IN ('$matches')";

By

$matches = implode(',', $obj);
$query = "SELECT * FROM TestTable WHERE id IN ($matches)";

Or

$query = "SELECT * FROM TestTable WHERE id IN (".implode(',', $obj).")";`

This way you will have WHERE id IN (141, 145, 54), a list of INT.

answered Sep 21, 2018 at 16:16

Mickaël Leger

3,4482 gold badges21 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

array in query returns only result of first value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related