CODEWITHSUNDEEP
phparrayssql-servermultidimensional-array
Joe
Joe

Reputation: 1055

PHP 7.1 - Build associative Multidimensional Array from sql query

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

Upvotes: 0

Views: 528

Answers (2)

Arun pandian M
Arun pandian M

Reputation: 882

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

Upvotes: 0

cornelb
cornelb

Reputation: 6066

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']);
}

Upvotes: 1

Related Questions