PDO SELECT only selecting first in foreach loop

Question

I am trying to select multiple products from my database and inserting the id into another table.

I have tried doing as below and if I just echo $name, $_POST["txtMaterial"][$key] and $_POST["txtSize"][$key] before the query, I get all selected names, materials and sizes, but when I bind the values in the query, I only get the first one.

    foreach($_POST["txtName"] as $key => $name){

      $sQuery = $db->prepare('SELECT id FROM products WHERE name = :sName AND material = :sMaterial AND size = :sSize');
      $sQuery->bindValue(':sName', $name);
      $sQuery->bindValue(':sMaterial', $_POST["txtMaterial"][$key]);
      $sQuery->bindValue(':sSize', $_POST["txtSize"][$key]);
      $sQuery->execute();
      $aOrders = $sQuery->fetchAll();

      foreach($aOrders as $aOrder){
        echo print_r($aOrders);
      }
}

The print_r of $aOrders is this:

"Array
(
    [id] => 174
)
1"

Which is the first id that I need. Can anyone help me out how to get all the id's?

var_dump of $_POST["txtName"] before the foreach:

"array(50) {
  [0]=>
  string(0) ""
  [1]=>
  string(6) "BACURI"
  [2]=>
  string(0) ""
  [3]=>
  string(0) ""
  [4]=>
  string(0) ""
  [5]=>
  string(0) ""
  [6]=>
  string(0) ""
  [7]=>
  string(0) ""
  [8]=>
  string(0) ""
  [9]=>
  string(0) ""
  [10]=>
  string(0) ""
  [11]=>
  string(0) ""
  [12]=>
  string(0) ""
  [13]=>
  string(0) ""
  [14]=>
  string(0) ""
  [15]=>
  string(0) ""
  [16]=>
  string(0) ""
  [17]=>
  string(0) ""
  [18]=>
  string(0) ""
  [19]=>
  string(0) ""
  [20]=>
  string(0) ""
  [21]=>
  string(0) ""
  [22]=>
  string(0) ""
  [23]=>
  string(0) ""
  [24]=>
  string(0) ""
  [25]=>
  string(0) ""
  [26]=>
  string(0) ""
  [27]=>
  string(0) ""
  [28]=>
  string(0) ""
  [29]=>
  string(6) "CAJARI"
  [30]=>
  string(0) ""
  [31]=>
  string(0) ""
  [32]=>
  string(0) ""
  [33]=>
  string(0) ""
  [34]=>
  string(0) ""
  [35]=>
  string(0) ""
  [36]=>
  string(0) ""
  [37]=>
  string(0) ""
  [38]=>
  string(0) ""
  [39]=>
  string(0) ""
  [40]=>
  string(0) ""
  [41]=>
  string(0) ""
  [42]=>
  string(0) ""
  [43]=>
  string(0) ""
  [44]=>
  string(0) ""
  [45]=>
  string(0) ""
  [46]=>
  string(0) ""
  [47]=>
  string(0) ""
  [48]=>
  string(0) ""
  [49]=>
  string(0) ""
}
"

var_dump of $_POST["txtMaterial"] before foreach:

"array(2) {
  [0]=>
  string(1) "3"
  [1]=>
  string(1) "2"
}
"

var_dump of $_POST["txtSize"] before foreach:

"array(2) {
  [0]=>
  string(2) "20"
  [1]=>
  string(1) "4"
}
"

Use print_r on $aOrders.. $aOrder will only ever have 1 row. — user3783243
– user3783243, Commented Jun 21, 2020 at 22:05
On the second iteration $aOrders has 174 again? Can you output values and verify they are correct? — user3783243
– user3783243, Commented Jun 21, 2020 at 22:16
There is no second iteration, that is the issue. All it shows is the first value and then nothing else, although it does exist before doing the query. — caroline4321
– caroline4321, Commented Jun 21, 2020 at 22:17
What exists before the query? If you comment out all the PDO code and do echo $name . PHP_EOL; you get all the names back? If the remove comments then run again you only get one $name return?? — user3783243
– user3783243, Commented Jun 21, 2020 at 22:19

Serghei Leonenco · Accepted Answer · 2020-06-22 13:01:45Z

According provided information you getting one record ID because the only time when you prepare your statement and it is not empty is the second iteration of the loop. You have only 2 keys in associated arrays txtMaterial and txtSize, the any next ones will return null. Take a look here:

First loop Your prepared statement will looks like this:

SELECT id FROM products WHERE name = "" AND material = 3 AND size = 20

this statement will return an empty array

Second loop

SELECT id FROM products WHERE name = "BACURI" AND material = 2 AND size = 4

This will end up with result what you get. Array with one record ID = 174.

Any next iteration...

SELECT id FROM products WHERE name = "" AND material = NULL AND size = NULL

This will return an empty array

Here is the test:

$name = ["", "BACURI", ""];
$material = ["3", "2"];
$size = ["20", "4"];

foreach($name as $key => $val){
    echo "\n";
    var_dump($val);
    var_dump($material[$key]);
    var_dump($size[$key]);
}

Output

string(0) ""
string(1) "3"
string(2) "20"

string(6) "BACURI"
string(1) "2"
string(1) "4"

string(0) ""
NULL
NULL

Hope it helps.

Collectives™ on Stack Overflow

PDO SELECT only selecting first in foreach loop

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