score:2
Accepted answer
SELECT
company,
GROUP_CONCAT(value SEPARATOR ',') AS value,
GROUP_CONCAT(monthyear SEPARATOR ',') AS monthyear
FROM
yourTable
GROUP BY
company
Some Reference for GROUP_CONCAT
.
PHP solution:
Select the to be grouped attribute sorted (company). Loop over them and open a new group every time you encounter a different value for company. As long as the current row has the same row as the previous, add value
and monthyear
to the current company.
You could do this even without sorting:
while($row = mysql_fetch_assoc($resource))
{
$values[$row["country"]][] = $row["value"];
$monthyear[$row["country"]][] = $row["monthyear"];
}
Some output example
foreach ($values as $country => $valuesOneCountry)
{
// each country
var_dump($country);
foreach ($valuesOneCountry as $i => $value)
{
// value, monthyear for each original row
var_dump($value, $monthyear[$country][$i]);
}
}
Elegant way with OOP:
class Tuple
{
public $country, $values, $monthyears;
public function __construct($country, $values = array(), $monthyears = array())
{
$this->country = $country;
$this->values = $value;
$this->monthyears = $monthyears;
}
}
$tuples = array();
while($row = mysql_fetch_assoc($resource))
{
if (!isset($tuples[$row["country"]]))
$tuples[$row["country"]] = new Tuple($row["country"]);
// save reference for easy access
$tuple = $tuples[$row["country"]];
// or some method like $tuple->addValue($row["value"]);
$tuple->values[] = $row["value"];
$tuple->monthyears[] = $row["monthyear"];
}
var_dump($tuples);
Source: stackoverflow.com
Related Articles
- A Challenge? converting mysql rows to very specific format with php
- Problems with retrieving mysql data into javascript in a specific format
- Searching large (6 million) rows MySQL with stored queries?
- highchart with a specific json format in django
- Highcharts: Format all numbers with comma?
- HighCharts - number format with $ in its short form
- Add dynamic data to line chart from mysql database with highcharts
- Highcharts label format with tickPositioner in a datetime x Axis
- last label in x axis with datetime format not coming in highchart
- How can I configure the legend with a specific height in highstock?
- Highcharts - Global configuration with common code and unique data & Headings
- Highcharts / JavaScript month is incompatible with MySQL
- Highcharts - How to populate date with Mysql and PHP?
- How can i get the json data with the specific between Year?
- Php and MySQL with Highchart
- c# WPF Webbrowser with Highchart, Javascript from external source not working "An error has occurred in the script on this page"
- How to highlight specific Point with Highcharts Js
- Posted value is not getting in the where clause with json highcharts-php and mysql
- Converting Poloniex API Callback JSON into format suitable for Highcharts.Stockchart
- Spiderweb chart with circular gridline interpolation: Label is seen very far away from Plotline
- Using Highcharts with mysql for Page Visits
- How to loop the data[] series with php and mysql
- How make a growth baby chart with data in mysql +PDO
- Highcharts zooming on x-axis with specific labelling
- passing formatting JavaScript code to HighCharts with JSON
- ColdFusion JSON return in a specific format
- csv with dates in decimal format
- Generating Highcharts from MySQL data with PHP does not work
- Have an issue with JavaScript, AJAX code displaying data
- Highchart polar chart with scatter in specific directions
- FusionCharts vs GoogleCharts vs HighCharts suggestions required for commercial use
- How to force the navigator to show a full year timeline even if the data collectively within it doesn't span an entire year
- How to get/set highcharts xAxis step?
- Highlight slice of a pie chart in highcarts
- Highcharts - Drag ColumRange
- js coding newbie - having trouble with variables and for loops
- HighCharts areaspline multicolor gradient opacity fade
- HighCharts not updating when the state changes in React
- When calling an AJAX post request to rails db, the return data is a list of generic objects
- How to call EventEmitter on click event with HighCharts
- how to render angular directive in Highcharts tool tip in Angular8
- How do I display category names instead of point.y in Highcharts scatterplot?
- How to remove Title from highchart flags
- Add image in highcharts labels
- Unhandled promise rejection mongoose aggregate
- How to display positive and negative values in positive side of graph in Highchart?
- How to add fill color in highcharts data series
- Prevent source to load twice
- How to create 2 axis and 2 yaxis in highchart
- Highchart show string on x-axis