Running mysql query in loop for each entry in array

Question

I am no programmer and could really use some help in configuring a query below such that it will run for each element in an array and finally add the queried data to the 'mArray'.

Below is two examples of the queries I am running. I have many more of these so it would be much more practical to include all of the ID's in an array and run a foreach loop based on the number of array elements.

  $sth = mysqli_query($conn, "SELECT * FROM {$tableName} WHERE ID = 'List1'");
  $list1Array = array();
  while($r = mysqli_fetch_array($sth)) {
    For ($n = 1; $n <= $CI_NOYEARS; $n++){
        $list1Array['data'][] = $r[$n];
    }
  }

  $sth = mysqli_query($conn, "SELECT * FROM {$tableName} WHERE ID = 'List2'");
  $list2Array = array();
  while($r = mysqli_fetch_assoc($sth)) {
      For ($n = 1; $n <= $CI_NOYEARS; $n++){
        $list2Array['data'][] = $r[$n];
    }
  }

  $mArray = array();
  $mArray['list1'] = $list1Array;
  $mArray['list2'] = $list2Array;

I have been trying to create this using a simple array and foreach statement as below. However, I just can't seem to make this work. Any help is much appreciated.

  $idArray  = array(
    "List1",
    "List2",
  );

  foreach($idArray as $val) {
    $sth = mysqli_query($conn, "SELECT * FROM {$tableName} WHERE ID = $val");
    $yearsArray = array(); // Not sure what to do here
    while($r = mysqli_fetch_array($sth)) {
      For ($n = 1; $n <= $CI_NOYEARS; $n++){
        $yearsArray['data'][] = $r[$n]; // Again not sure what to do here
      }
    }
  }

Your `$val` contains a string but your query is not quoted. But don't just add quotes because that will introduce a SQL injection issue. Instead use prepared statements with bound parameters. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Nov 14 '18 at 20:56
Alternative to a loop, you could do it in a single query with `SELECT * FROM WHERE ID IN (x, y, z, etc)` — Alex Howansky, Nov 14 '18 at 20:57

Running mysql query in loop for each entry in array

0 Answers0