mdannatt Posted March 21, 2009 Share Posted March 21, 2009 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 />"; } Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/ Share on other sites More sharing options...
bluejay002 Posted March 21, 2009 Share Posted March 21, 2009 Am sorry but I don't get your question. Can you rephrase on what you are aiming to do? Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-789988 Share on other sites More sharing options...
redarrow Posted March 21, 2009 Share Posted March 21, 2009 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 />"; ?> Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-789995 Share on other sites More sharing options...
mdannatt Posted March 21, 2009 Author Share Posted March 21, 2009 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. Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-789998 Share on other sites More sharing options...
redarrow Posted March 21, 2009 Share Posted March 21, 2009 i done that just add the while loop mate. Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-790000 Share on other sites More sharing options...
bluejay002 Posted March 21, 2009 Share Posted March 21, 2009 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? Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-790027 Share on other sites More sharing options...
mdannatt Posted March 21, 2009 Author Share Posted March 21, 2009 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 ) Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-790153 Share on other sites More sharing options...
bluejay002 Posted March 21, 2009 Share Posted March 21, 2009 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 ) ); Link to comment https://forums.phpfreaks.com/topic/150418-php-variables-from-mysql-group-by-and-sum/#findComment-790279 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.