php variables from mysql group by and sum

[mdannatt]

can some please tell me how to assign variables this mysql query using sum and group by? my problem is that the number of groups may vary. the query 'as is' returns this:

 

Apples - $10,000

Bananas - $10,000

Carrots - $10,000

 

I need to assign new variables so I can use each value in a new calculation.

I thought of using this but it only returns Apples - 10,000

$data = array();

while($row = mysql_fetch_assoc($tres)){

  foreach($row as $key=>$value){

      $data[$key] = $value;

  }

}

print_r($data);

 

here is my code at the moment:

//Group procurement Spend by Supplier Type

$startdate = $sdate["sdate"];

$enddate = $edate["edate"];

$tquery =

"SELECT suppliertype.suppliertype , sum(transaction.tvalue)

FROM suppliertype

LEFT JOIN supplierinfo ON suppliertype.Supplier_Control_ID = supplierinfo.Supplier_Control_ID

LEFT JOIN transaction ON supplierinfo.Supplier_ID = transaction.supplier_ID

WHERE certficateexpiry >= '$startdate'

AND tdate >= '$startdate'

AND certficateexpiry <= '$enddate'

AND tdate <= '$enddate'

GROUP BY suppliertype

LIMIT 0 , 30";

$tres = mysql_query($tquery);

//Lets print out procurement by supplier type

while($tresult = mysql_fetch_assoc($tres))

{

echo $tresult['suppliertype']. " - ". number_format($tresult['sum(transaction.tvalue)'],2);

echo "<br />";

}

 

 

 

[bluejay002]

Am sorry but I don't get your question. Can you rephrase on what you are aiming to do?

[redarrow]

example only.

 

<?php

$data=array("Apples" => "$10,000", "Bananas"=> "$10,000", "Carrots" => "$10,000");



foreach($data as $key=>$value){

$result[$key]=$value;

$apples=$result['Apples'];
$bannas=$result['Bananas'];
$carrots=$result['Carrots'];


}

echo " i love carrots they cost\n $carrots <br />";

echo " i love bannas they cost\n $bannas <br />";

echo " i love apples they cost\n $apples <br />";

?>

[mdannatt]

sorry, maybe i didnt explain myself very well. my code is a mysql query. basically it takes this data

type    value

apples    5000

apples    5000

bananas  5000

bananas  5000

carrots  5000

carrots  5000

 

and using  and 'group by' type i get

apples 10000

bananas 10000

carrots 10000

 

i now need to assign the values to new variables:

$a = 10000

$B= 10000

$C= 10000

 

the problem is at some point i may also have grapes and peaches and other things to group by as well.

[redarrow]

i done that just add the while loop mate.

[bluejay002]

Try this:

 

$content = array();

while($tresult = mysql_fetch_assoc($tres))
{
    $content[$tresult['suppliertype']] = number_format($tresult['sum(transaction.tvalue)'],2);
}

 

it should have the following values:

 

$content = array("Apples" => 10000, "Bananas"=> 10000, "Carrots" => 10000);

 

Did it work?

[mdannatt]

hi bluejay

no, unfortunately that didnt work. As it is, this is the output using print_r($result); so the question is how is assign 50000 to a variable, 176083 to a variable, 50000 as a variable? the other problem what happens id a new supplier type is added? thanks for all the help.

Array ( [suppliertype] => Exempt Micro Enterprise [sum(transaction.tvalue)] => 50000.00 )

Array ( [suppliertype] => Generic Enterprise [sum(transaction.tvalue)] => 176083.00 )

Array ( [suppliertype] => Qualifying Small Enterprise [sum(transaction.tvalue)] => 50000.00 )

[bluejay002]

Mmmm... how about you do this instead:

 

$startdate = $sdate["sdate"];
$enddate = $edate["edate"];

$tquery =
"SELECT suppliertype.suppliertype, 
SUM(transaction.tvalue) as totalPrice
FROM suppliertype
LEFT JOIN supplierinfo ON suppliertype.Supplier_Control_ID = supplierinfo.Supplier_Control_ID
LEFT JOIN transaction ON supplierinfo.Supplier_ID = transaction.supplier_ID
WHERE certficateexpiry >= '$startdate'
AND tdate >= '$startdate'
AND certficateexpiry <= '$enddate'
AND tdate <= '$enddate'
GROUP BY suppliertype
LIMIT 0 , 30";

$tres = mysql_query($tquery);

$output = array();

while($tresult = mysql_fetch_assoc($tres)) {
    $tempArray = array();

    $tempArray['type'] = $tresult['suppliertype'];
    $tempArray['total'] = $tresult['totalPrice'];

    $output[] = $tempArray;
}

// to check the values in the array $output;
print_r($output);

 

I guess this should do the trick and would look like this... well hopefully.

 

$output = array(
        0 => array(
            'type' => 'apple',
            'total' => 10000
        ),
        1 => array(
            'type' => 'banana',
            'total' => 10000
        ),
        2 => array(
            'type' => 'mango',
            'total' => 10000
        )
    );