foreach array wise multi SQL query not working in PHP

Question

I am trying to execute multiple queries with the multiple selected checkbox value-wise in PHP. I am facing trouble that my code is executing only one checkbox value-wise query and the rest is denied.

I checked on StackOverflow about this issue and I got lots of threads about foreach loop but in my case, it is not working when I am applying that.

Please help me, I am first time trying the foreach loop and so that I have a bit confusing about the same. I have also the problem that I am not able not to validate invalid data through an array. How I fix this? it only works for the first check value but I want all checked checkboxes. I am trying to fetch data from the database of those particular ids which value I selected in the checkbox. and echo it in the array for that all query as I mention below-

Sending Form Data format as seen in dev tool

referenceID[]: PO0203211
referenceID[]: PO203213

PHP

$checkbox = $_POST['referenceID'];
foreach ($checkbox as $chk) {
    $stmt = $con->prepare(
        "SELECT  * FROM `table` WHERE `ref` = :referenceid"
    );
    $stmt->execute([':referenceid' => $chk]);
    $stmt = $stmt->fetchAll();

    $response = [];
    $i = 1;
    foreach ($stmt as $data) {
        $response[] = [
            "slno" => $i,
            "name" => $data['name'],
            "orderid" => $data['address'],
        ];
        $i++;
    }
    echo json_encode($response);
    exit();
}

Move $response to before the first foreach (or it will be overwritten on each iteration). Then move the echo and exit to after the foreach loop. If you have an exit inside the foreach, it will always exit the script after the first iteration. — M. Eriksson
– M. Eriksson, Commented Mar 3, 2021 at 15:25
What have you tried to debug the problem? What's the expected result? — Nico Haase
– Nico Haase, Commented Mar 3, 2021 at 15:30
I'll suggest to use another way. Here, if you have 100 checkbox, you'll perform 100 query. Maybe it's better to "glue" all the checkbox value and then perform only one query using a "FIND_IN_SET" or "IN" action. — Peter
– Peter, Commented Mar 3, 2021 at 15:35
@MagnusEriksson Thank you.. Its work... Please give me chance to vote for you. suppose PO0203211 this not available in the database how I show an error that this is not availe in db with for each loop. — Satyam
– Satyam, Commented Mar 3, 2021 at 15:46
@Peter you are right but could you help me how to apply your idea in my code. I am new and your function sound really tough to understand in my case . you are true - pls help me — Satyam
– Satyam, Commented Mar 3, 2021 at 15:57

Daniel Riera · Accepted Answer · 2021-03-04 00:24:29Z

1

Try this, when using exit() inside the foreach the code does not continue and only performs the first element.

EDIT 1:

Ok, I'm going to explain a little more in depth how to optimize this code.

You get "id" identifiers from checked checkboxes.

You can use SQL IN () to optimize this.

Look at this

$checkboxDivide = implode("','", $_POST['referenceID']);
$response = []; //Final Result

$stmt = [];
$query = mysqli_query($con, 
         "SELECT * FROM `table` WHERE `ref` IN('{$checkboxDivide}')"
);
while($stmt[] = mysqli_fetch_assoc($query));

//Delete empty last array
array_pop($stmt);

$i = 1;
foreach ($stmt as $data) {
    $response[] = [
        "slno" => $i,
        "name" => $data['name'],
        "orderid" => $data['address'],
    ];
    $i++;
}


echo json_encode($response);

edited Mar 4, 2021 at 0:24

answered Mar 3, 2021 at 15:28

Daniel Riera

998 bronze badges

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

4 Comments

Nico Haase Over a year ago

Please add some more explanation to your answer such that others can learn from it

Satyam Over a year ago

How I validate.. check if data in available in DB or not with foreach.

Daniel Riera Over a year ago

@Satyam I edit my answer, now is optimize only one query, You can check if it works correctly for you, in this case please mark my answer as correct :)

Daniel Riera Over a year ago

@Satyam please provide result, edit your question

Tim · Accepted Answer · 2021-03-03 15:32:35Z

0

You're using the exit() function withing your foreach, which will terminate the current script. See php manual on exit.

I am referencing to the

foreach ($checkbox as $chk) {}

not to the

foreach ($stmt as $data) {}

The script will terminate after the first loop of the first foreach.

Try moving it out of the foreach.

**Updated answer because of the comment from @El_vanja

Move your $response = []; array above and out of the foreach loop. This will keep the data in the array and not reset the array every iteration of the foreach loop.

edited Mar 3, 2021 at 15:32

answered Mar 3, 2021 at 15:23

Tim

3302 silver badges11 bronze badges

1 Comment

El_Vanja Over a year ago

Still wouldn't work, since $response = []; is defined inside the loop, which would result in only the last element being output. It would need to be placed outside and before it.

Collectives™ on Stack Overflow

foreach array wise multi SQL query not working in PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related