CODEWITHSUNDEEP
phpmysql
btaylor507
btaylor507

Reputation: 211

Returning GROUP BY results as separated variables

I'm new to mysql/php and I'm trying to build a custom query using 

$query = "SELECT expo_location,  SUM(total_sale) FROM _sales_db WHERE day IN('Friday') GROUP BY expo_location"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
  echo "<span style='font-weight:normal;color:#cfcfcf;'>"."Total ". $row['expo_location']. " = $". number_format((float)$row['SUM(total_sale)'], 2, '.', '')."</span>";
  echo "<br />";
}

Which Returns :

//
Total Canton = $295.94
Total Malone = $980.24
Total Massena = $1187.10
//

I'm looking to just return the "expo_location" names as variables from using GROUP BY.

Example
$Canton = 
$Malone = 
$Massena =

So to sum it up, taking all entries in one column and grouping them together and returning them as Variables?

Upvotes: 2

Views: 152

Answers (2)

user188654
user188654

Reputation:

You could store the extracted location data in an array while you do the records loop and then simply implode all the collected data in a string.

$query = "SELECT expo_location,  SUM(total_sale) FROM _sales_db WHERE day IN('Friday') GROUP BY expo_location"; 

$result = mysql_query($query) or die(mysql_error());

$locations = array();

// Print out result
while($row = mysql_fetch_array($result)){
    $locations[] = $row['expo_location'];
}

$locations_string = implode(', ', $locations); // String version
var_dump($locations); // Displays array content.

Upvotes: 1

Bryan
Bryan

Reputation: 6752

The code below will create an associative array for you, so that you can do something like $expo_locations['Canton'] which will contain 295.94

    $query = "SELECT expo_location,  SUM(total_sale) FROM _sales_db WHERE day IN('Friday') GROUP BY expo_location"; 

    $result = mysql_query($query) or die(mysql_error());

    $expo_locations = array();

    // Print out result
    while($row = mysql_fetch_array($result)){
        $expo_locations[$row['expo_location']] = number_format((float)$row['SUM(total_sale)'], 2, '.', '');
    }

Upvotes: 2

Related Questions