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 Query
- A Challenge? converting mysql rows to very specific format with php
- Problems with retrieving mysql data into javascript in a specific format
- Php and MySQL with Highchart
- How to loop the data[] series with php and mysql
- Generating Highcharts from MySQL data with PHP does not work
- Searching large (6 million) rows MySQL with stored queries?
- HighCharts - making a Pie with MySQL and PHP
- Select format mysql to highchart in php
- highchart with a specific json format in django
- Highstock with php and mysql
- mySQL with PHP to JSON for clientside chart
- Only my title shows when using highcharts with php and MySql
- Implementing High charts with PHP and Mysql
- Highcharts code with same set of code/data works in PHP but does not work in JSfiddle
- Highcharts: Format all numbers with comma?
- Highcharts data series issue with ajax/json and PHP
- 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
- How to populate a Highcharts axis with string formatted data from a PHP array
- Highcharts export charts as image on serverside with php
- Highcharts / JavaScript month is incompatible with MySQL
- Highcharts Mysql Json arrays PHP
- Highcharts Mysql Json arrays PHP
- Highcharts Column-chart with php and Ajax
- Highcharts - How to populate date with Mysql and PHP?
- How can i get the json data with the specific between Year?
More Query from same tag
- Indicator above bar in graph
- Highcharts stacked bar data
- Child component not loading correctly in ngFor (but updates after resizing windows)
- Get x and y value of a series in highcharts when curve is being plotted
- Highcharts showing nearest point causes flickering crosshair and strange order
- How to draw HighChart XAxis dynamically
- Highchart series mouseover
- How to provide additional arguments to custom symbol renderer in Highcharts?
- Highcharts not rendering data points
- Highcharts: How to shift yAxis to center (e.g.Population by sex and age group)
- Remove whitespace from highcharts gauge chart?
- Node JS, Highcharts Memory usage keeps climbing
- Plotting the below json in a HighChart
- Multiple series in Highcharts with multiple drilldowns
- Export option not visible in Highcharts
- Categories are skipped in highcharts on xAxis
- Which one is better to represent an API fetched data in react js and please explain how to do that?
- Highchart Barchart xAxis
- Highcharts change the line color if its out of the area range
- One Average Spline for each Column in Highcharts Combination Chart
- HighCharts multiple x series data for line graph
- highcharts 5.0.14 issue with textOverflow
- Is there a way to limit the range of x, when using dates in highcharts?
- Convert CSV webservice into multiple year Highcharts line graph
- Set axis extremes dynamically on drilldown
- How to add comma to data labels value in bar chart race of highcarts?
- Highcharts tool-tip show extra data
- Highcharts in Laravel 4 project are not displayed
- Highcharts won't display data
- HighCharts: Display total value below the legend title