PHP/MYSQL rows as dynamic columns

Beginners - Learning PHP
#1

Hey,
I am trying to turn around the output of a database I have.
The dates need to be dynamic as new dates will pop up in the database.

As I understood it, there is a function called PIVOT in SQL Server 2005+ but I can´t seem to find a solution to it in MYSQL 5.6.

Is there anyone out there who can give me a helping hand?

Many thanks in advance!!

How the db looks like:
[table]
[tr]
[td]DATE[/td]
[td]1_ABR[/td]
[td]2_ASA[/td]
[/tr]
[tr]
[td]2015-01-01[/td]
[td]15[/td]
[td]29[/td]
[/tr]
[tr]
[td]2015-01-02[/td]
[td]11[/td]
[td]20[/td]
[/tr]
[tr]
[td]2015-01-03[/td]
[td]16[/td]
[td]21[/td]
[/tr]
[/table]

Output:
[table]
[tr]
[td] [/td]
[td]2015-01-01[/td]
[td]2015-01-02[/td]
[td]2015-01-03[/td]
[/tr]
[tr]
[td]1_ABR[/td]
[td]15[/td]
[td]11[/td]
[td]16[/td]
[/tr][tr]
[td]2_ASA[/td]
[td]29[/td]
[td]20[/td]
[td]21[/td]
[/tr]
[/table]

#2

It’s not the job of a database system to print nicely formatted tables. If you want to render a pivot table, do that in your application. This is very easy to implement.

#3

HUH? I do not understand why you just do not use the query correctly to do this. Just run the query and
select the data and order it by the date. Simple…

Something loosely like:
$query = “SELECT Date, 1_ABR, 2_ASA from table_name GROUP BY Date”;

That should group all of the rows by the Date field. Not tested, but, you should research the “GROUP” and
“DISTINCT” functions of the MySQL query system. No need to use JOIN’s or PIVOT’s…

EDIT: Actually, PleXy, I found a way to do it. Here is an explanation that covers it all. It explains it and how
to do it. Skip down BELOW the accepted answer and you will see a very detailed explanation how to do it.
I don’t want to rewrite it all as this version explains it very nicely. It uses a nice “max” function to sort the
results exactly like you want it… Think it will solve for you…
http://stackoverflow.com/questions/1241178/mysql-rows-to-columns

#4

Thanks for the help ErnieAlex!

However, I really can’t get this to work when applying it to a PHP page.
Do you mind assisting me? ???

Thanks a lot!

#5

Well, PleXy, sure we can help you out… So, have you done DB Query’s before? What have you now for
code for this? The answer is inside the QUERY, nothing to do with PHP. Normally a general query to pull
out data from a DB would be loosely like this:

$db_connect = Set up your connection to the database
$query = “SELECT * FROM table_name”;
$results = mysqli_query($db_connect, $query) or die ("Error executing query!
Error: " . mysqli_error($db_connect));
while($row = mysqli_fetch_assoc($results)) {
echo “display the results from $row[] array such as $row[“date”], etc”;
}

If you are not familiar with these commands, you need a tutorial on MySQLi database usage. If you are
familiar with these, then use the altered query that I posted to pull the data and group it as you needed.
Once you get that far, show us your code and explain what is not working. (we do not need your database
connection info)

Hope that helps get you going…

#6

Hi ya,

I found a way to do this now - it’s really ugly but it works. :slight_smile:
Here’s the code in case anyone needs… I’ve added in the data to a Google table chart.

[php]<?php
$sql = “SELECT DATE_ADD(date, INTERVAL(1-DAYOFWEEK(date))+1 DAY) as date, SUM(1_abr24_answeredcalls) as TotABR, sum(24_answeredcalls) as AnsweredCalls, SUM(2_asa24_answeredcalls) as TotASA, AVG(3_fteabsent) as 3_fteabsent, AVG(4_fteholiday) as 4_fteholiday, AVG(5_fteactual) as 5_fteactual, SUM(6_acw*24_answeredcalls) as TotACW FROM XXX where Department = ‘XXX’ GROUP BY DATE_ADD(date, INTERVAL(1-DAYOFWEEK(date))+1 DAY) order by date asc”;
?>

[/php]
#7

Silly to do all that mixing of PHP and Javascript to do this when a simple Query will do it for you…
I posted a page that explains how to do it in just a query.
http://stackoverflow.com/questions/1241178/mysql-rows-to-columns

But, a solution that works for you is good… We will mark this one solved…

Sponsor our Newsletter | Privacy Policy | Terms of Service