PHP array mysql retrieve each record (part 2)

Question

• Database (book table)
  serialID   price
   0001     10.00
   0001      30.00
   0002      15.00
   0004(A)  9.00
   0004(B)  5.00
   0005       3.00
  (Noted: 0003 no record)

• Code
  

  $serialID = array("0001","0002","0003","0004","0005");
  
  //DB Connection
  for($i = 0; $i < count($serialID); $i++)
  {
      $q = "select * from book where serial like \"$serialID[$i]%\" limit 1";
      $r = mysqli_query($dbc,$q);
  
      while($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
      {
            $serial[$i] = $row['serial'];
            $price[$i] = $row['price'];
            echo $serial[$i].' '.$price[$i];
      }
  }
  
  //pass db value into array
  for($j = 0; $j < count($serialID); $j++)
  {
       $data[$j] = array($serialID[$j],$price[$j]); 
  }
  

• This time my questions is how to skip the serialID 0003 value?

• My expected output: (echo $serial[$i].' '.$price[$i])
  0001      10.00
  0002      15.00
  0003
  0004(A)   9.00
  0005        3.00

Answer 1

Modify last loop to

for($j = 0; $j < count($serialID); $j++)
{
if(null != $price[$j]){
     $data[$j] = array($serialID[$j],$price[$j]); 
    }
}

But my question is, it's not sucide to query something in loop? Maybe you should use "IN" statement? Or "OR"? For e.g:

foreach($serialId as $id){
  $string.= ' %'.$serialId. ' % OR '; //of course you should check if this is first or last cell in array. So you don’t add unnecessary OR or space
}
select * from book where serial like \"$string\" limit 1

Answer 2

Just check it in the for loop. Use the code below

   $serialID = array("0001","0002","0003","0004","0005");

//DB Connection
for($i = 0; $i < count($serialID); $i++)
{
    $q = "select * from book where serial like \"$serialID[$i]%\" limit 1";
    $r = mysqli_query($dbc,$q);

    while($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
    {
          $serial[$i] = $row['serial'];
if($serialID=="0003"){
          $price[$i] = $row['price'];
    }
          echo $serial[$i].' '.$price[$i];
    }
}

//pass db value into array
for($j = 0; $j < count($serialID); $j++)
{
     $data[$j] = array($serialID[$j],$price[$j]); 
}

Hope this helps you

Answer 3

I think this code should work better for your needs:

$serialID = array("0001", "0002", "0003", "0004", "0005");

//DB Connection
foreach ($serialID as $serialItem) {
    $q = "select * from book where serial like \"$serialItem%\" limit 1";
    $r = mysqli_query($dbc, $q);

    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        if (count($row) > 0) {
            echo $row['serial'] . ' ' . $row['price'];

            //pass db value into array
            $data[] = array(
                'serial' => $row['serial'],
                'price'  => $row['price']
            );
        }
    }
}

// debug output
print_r($data);

Answer 4

$serialID = array("0001","0002","0003", "0004","0005");

$preparing = [];
foreach($serialID as $value) {
 $preparing[] = "'" . $value . "'";
}

$sql = "select * from book where serial IN (" . implode(',', $preparing) . ")";
$query = mysqli_query($sql);

$data = mysqli_fetch_all($query, MYSQLI_ASSOC);