fetch data form mysql table

Question

hello all below is my query and its giving fine results

SELECT `collection_series`.`chart_name`, `datadatexnumericy`.`x` as date, `datadatexnumericy`.`y`
FROM (`datadatexnumericy`)
JOIN `collection_series` ON `collection_series`.`series_id` = `datadatexnumericy`.`series_id`
WHERE `collection_series`.`collection_id` =  '265'

chart_name            date              y

Sydney             1973-09-30         2.50000
Melbourne          1973-09-30         5.70000
Brisbane           1973-09-30         6.60000
Perth              1973-09-30         7.10000

but what if i want results like below is there any solution any help would be appriciated thanks in advance ...

date             Sydney         Melbourne      Brisbane     Perth       

1973-09-30       2.50000        5.70000        6.60000      7.10000

below is my table structure

datadatexnumericy(first table)

series_id     x            y
43532        1991-12-31   -2.10000

don't confuse about series_id because city name is coming from collection series table where series_id matches and fetches city name

collection_series(second table)

in this table there is coloumn which name is collection_id and series_id
collection id is '265' and i am matching `collection_series`.`series_id` = `datadatexnumericy`.`series_id`

can you share you table structure with complete test data. Moreover, please post your complete code. — Keep Coding
– Keep Coding, Commented Apr 3, 2015 at 12:41

Abhik Chakraborty · Accepted Answer · 2015-04-03 13:28:00Z

If its for a known set of chart_names, then you can use the following technique for generating the pivot table

select
dd.x as date,
max( case when cs.chart_name = 'Sydney' then dd.y end ) as `Sydney`,
max( case when cs.chart_name = 'Melbourne' then dd.y end ) as `Melbourne`,
max( case when cs.chart_name = 'Brisbane' then dd.y end ) as `Brisbane`,
max( case when cs.chart_name = 'Perth' then dd.y end ) as `Perth`
from datadatexnumericy dd
join collection_series cs on cs.series_id = dd.series_id
group by dd.x

You can also add the where condition before the group by as

WHERE cs.collection_id =  '265'

Here is how you can make it dynamic

set @sql = NULL;
select
  group_concat(distinct
    concat(
      'max(case when cs.chart_name = ''',
      cs.chart_name,
      ''' then dd.y end) AS ',
      replace(cs.chart_name, ' ', '')
    )
  ) INTO @sql
from collection_series cs
join datadatexnumericy dd on cs.series_id = dd.series_id
;

set @sql = concat('select dd.x as date, ', @sql, ' from datadatexnumericy dd
join collection_series cs on cs.series_id = dd.series_id
group by dd.x');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

Check the demo here

thanks but what when the city name is coming from db and city name can change if user wants .... than how can i make it dynamic
So you may have n number of cities , and if so then the same query needs to be written using dynamic sql with prepared statement which is more complex in nature.

Kevin · Accepted Answer · 2015-04-03 12:56:15Z

I can't think of a way right now to query it in such fashion but you could restructure the normal fetched results first. Build it with that specialized format first, then present it.

First is to get the headers (the dates and places etc.), then you need to group the body data according to dates, push them inside another container.

Rough example:

<?php
// temporary container
$temp = array();
while($row = whatever_fetch_function_assoc($result)) {
    $temp[] = $row; // push the rows
}

// restructure
$places = array_column($temp, 'chart_name'); // if this is not available (only PHP 5.5)
// foreach($temp as $v) {
//  $places[] = $v['chart_name']; // if its not available just use foreach
// }
// header creation
$headers = array_merge(array('Date'), $places); // for headers
foreach($temp as $v) { // then extract the dates
    $data[$v['date']][] = $v['y']; // group according to date
}

?>

Then, once the structure is made, then you present it, (as you normally would) in a loop:

<!-- presentation -->
<table cellpadding="10"> 
    <thead>
        <tr><?php foreach($headers as $h): // headers ?>
        <th><?php echo $h; ?></th>
        <?php endforeach; ?></tr>
    </thead>
    <tbody>
    <?php foreach($data as $date => $values): ?>
        <tr>
            <td><?php echo $date; // the date ?></td>
            <?php foreach($values as $d): ?>
            <td><?php echo $d; ?></td>
            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

Somewhat of a sample output

Wait what? Am I misunderstanding the question? Doesn't he just want to have it group by date and that's it?
@Ghost is there any modification in query that i have to make or its fine?
@vivek i'm just basing my answer on the query result itself, as i have said, i think my brain is dead to figure out how to group them based on yours, if someone answers them, remodifying them by only query, i'd definitely upvote that :)
@Ghost My brain is dead as well right now but wouldn't it just be an easy group by date at the end of his query and then in php just put it in the table like he wants?
@Loko i can't think of a query right, but its definitely has a group by :) lol with the date

Collectives™ on Stack Overflow

fetch data form mysql table

2 Answers 2

2 Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Related