PHP 7.1 - Build associative Multidimensional Array from sql query

Question

I have a sql server table, VATTable like this:

VatCode     |   VATRate     |   Description         |  Active
00              0               VAT Rate 0.00%        1
04              4               VAT Rate 4.00%        1
06              6               VAT Rate 6.00%        1
...
21              21              VAT Rate 21.00%       1
....

with this query

$query = "SELECT VatCode, VatRate, Description, 0 as Goods, 0 as eFees, 0 as pFees, 0 as sFees, 0 as VAT, 0 as Total from VATTable where active=1";
$result = sqlsrv_query($conn,$query);

I need to build a multidimensional array with such data, that looks like this:

$VATTable = array
(
    '04'=> array(
        'VATRate'=>'4',
        'Desc'=>'VAT 4.00%',
        'Goods'=>0,
        'eFees'=>0,
        'pFees'=>0,
        'sFees'=>0,
        'Taxable'=>0,
        'VAT'=>0,
        'Total'=>0          
    ),
    '06'=> array(
        'VATRate'=>'06',
        'Desc'=>'VAT 6.00%',
        'Goods'=>0,
        'eFees'=>0,
        'pFees'=>0,
        'sFees'=>0,
        'Taxable'=>0,
        'VAT'=>0,
        'Total'=>0          
    ),
    '10'=> array(
        'VATRate'=>'10',
        'Desc'=>'VAT 10.00%',
        'Goods'=>0,
        'eFees'=>0,
        'pFees'=>0,
        'sFees'=>0,
        'Taxable'=>0,
        'VAT'=>0,
        'Total'=>0          
    )
);

so to be able to manage it in the following way:

$vatCode='10';
$VATTable[$vatCode]['Goods']=15;
echo $VATTable[$vatCode]['Desc'].': '.$VATTable[$vatCode]['Goods'];

although my php knowledge is poor I think I have first to build the inner array and then use array_push to add to VATTable Array, but while searching for such solution, I found some example where I understood (??) that perhaps this can be done while fetching the recordset, and I thought I could do in this way: $VATTable = array();

while($row = sqlsrv_fetch_array($result))
{
   $VATTable[$row['VATCode']]['VATRate']=$row['VATRate'];
   $VATTable[$row['VATCode']]['Desc']=$row['Description'];
   $VATTable[$row['VATCode']]['Goods']=$row['Goods'];
};

but it does not work.

can suggest solution?

Thanks

cornelb · Accepted Answer · 2017-07-23 08:34:34Z

1

This should work:

while($row = sqlsrv_fetch_array($result)) {
   $id = $row['VATCode'];
   $VATTable[$id] = [
      'VATRate' => $row['VATRate'],
      'Desc' => $row['Description'],
      'Goods' => $row['Goods'],
}

print_r($VATTable);

Also, you don't need the word "array" in php7. You can use

$VATTable[$id] = [ ... ]

instead of

$VATTable[$id] = array( ... );

If you don't want to hardcode indexes:

while($row = sqlsrv_fetch_array($result)) {
   $id = $row['VATCode'];
   $VATTable[$id] = $row;
   unset($VATTable[$id]['VATCode']);
}

edited Jul 23, 2017 at 8:34

answered Jul 23, 2017 at 8:05

cornelb

6,0663 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Over a year ago

yes, seen array is not required, but since it was not working.. tested also with old notation.. your solution is more compact although can avoid a further row, but that's easy also for me. since fields are few this solution is OK for this example, but what if have large number of fields and want to avoid hard coding indexes?

Joe Over a year ago

Previous answer was clear.. for this have to learn a lot, but it works.

Joe · Accepted Answer · 2017-07-23 13:39:45Z

0

while($row = sqlsrv_fetch_array($result))
{
      $tempVariable = array (
        'VATRate'=>$row['VATRate'],
        'Desc'   =>$row['Description'],
        'Goods'  =>$row['Goods'],
        'eFees'  =>$row['eFees'],
        'pFees'  =>$row['pFees'],
        'sFees'  =>$row['sFees'],
        'Taxable'=>$row['Taxable'],
        'VAT'    =>$row['VAT'],
        'Total'  =>$row['Total']  
     ); 
 $VATTable[$row['VatCode']] = $tempVariable;
};

Now $VATTable contains your multidimensional array

edited Jul 23, 2017 at 13:39

Joe

1,0651 gold badge18 silver badges45 bronze badges

answered Jul 23, 2017 at 8:05

Arun pandian M

89210 silver badges17 bronze badges

1 Comment

Joe Over a year ago

yes, both works well, but what about avoid hard coding indexes and having arbitrary number of fields?

Collectives™ on Stack Overflow

PHP 7.1 - Build associative Multidimensional Array from sql query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related