Loop SQL query results with a switch statement PHP

Question

I have a query which selects everything from a database. I then Loop through the results with a foreach statement. I have a column in the database called CODE.

This column contains numbers such as 21, 55, 51, 11 . Sometimes each row has more than one code within it.

I want to loop through the results and put the charge codes for that row into its own array. The array must contain a set number of indexes.

For example. If the row contains CODE 21 and 11. The array for that result would be

Array(
      [0]=>21
      [1]=>0
      [2]=>0
      [3]=>11
)

So the row contained code 21 which was put into an array at the zero index. It then looks for code 55 which is not there so it puts a 0 at the first index. looks for code 51 which is also not there so it puts a 0 in the 2nd index. It then looks for code 11 which is there so it puts 11 in the third index.

How can I do this? I tried using a switch statement but it it inserted alot of zeros if more than one code was in the row.

SQL Query

$sql_query2 = $DFS->prepare( "

select
       *
from
       TABLE

where

       REFERENCE= '".$ref."'

" );

$sql_query2->execute();

$result2 = $sql_query2->fetchall();

What I am Currently doing

foreach( $result2 as $row2 ) {


     switch( $row2[ 'CODE' ] ) {

         case "21":
             $some_new_array[] = $row2[ 'AMOUNT' ];
             $some_new_array[] = "0";
             $some_new_array[] = "0";
             $some_new_array[] = "0";
            break;

         case "55":

             $some_new_array[] = $row2[ 'AMOUNT' ];
             $some_new_array[] = "0";
             $some_new_array[] = "0";
             $some_new_array[] = "0";
            break;

         case "51":
             $some_new_array[] = $row2[ 'AMOUNT' ];
             $some_new_array[] = "0";
             $some_new_array[] = "0";
             $some_new_array[] = "0";
            break;

         case "11":
             $some_new_array[] = $row2[ 'AMOUNT' ];
             $some_new_array[] = "0";
             $some_new_array[] = "0";
             $some_new_array[] = "0";
            break;
      }

}

The above code will work if there is one code in the row. eg. if there is code 21, it will input the amount along with 3 zeros. but if there is code 21 and code 55, it will input the amount for both along with 6 zeros.

If a reference has TWO codes eg 21 and 4. there will be two rows with each code.

Id appreciate any help and pointers. Thanks.

Answer 1

if you know what CODEs exist you can set an array and compare, else you can make a query to get them

$codes = array(21,55,51,11);
foreach( $result2 as $row2 ) {
    $res = explode(" ",$row2['CODE']);
    foreach($codes as $cc){
        if(in_array($cc, $res))
        {
              $some_new_array[] = $row2[ 'AMOUNT' ];
        }else{
              $some_new_array[] = "0";
        }
    }
}

Answer 2

You can 'explode' your data (i suposed that your data it's separated by " "):

foreach( $result2 as $row2 ) {

     $temp=explode(" ",$row2[ 'CODE' ])

     foreach( $temp as $temp2 ) {

         switch( $temp2 ) {

             case "21":
                 $some_new_array[] = $row2[ 'AMOUNT' ];
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                break;

             case "55":

                 $some_new_array[] = $row2[ 'AMOUNT' ];
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                 break;

             case "51":
                 $some_new_array[] = $row2[ 'AMOUNT' ];
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                break;

             case "11":
                 $some_new_array[] = $row2[ 'AMOUNT' ];
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                 $some_new_array[] = "0";
                break;
          }
    }
}