使用switch语句PHP循环SQL查询结果

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.

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;
          }
    }
}

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";
        }
    }
}