Solved

Posted on 2011-10-17

I have included a sample database for this query with full create and insert commands. 2 tables, 2 rows on one table, 8 on another.

One table is periods, the other is period data, Using the following (kindly supplied on this site) MYSQL command I can successfully draw the latest 2 periods of data. 4 weeks in each. These are displayed as rows of data.. HOWEVER I want to generate these rows (using php or mysql.. or whatever it takes) so that the php page displays the data vertically to fit a table such as demonstrated in the image included.

The MYSQL statement I use to pull of the 2 period data is

All advise / code appreciated.

The MYSQL is in a JOOMLA MODEL, and the display will be in a VIEW

sampledatabase.sql

One table is periods, the other is period data, Using the following (kindly supplied on this site) MYSQL command I can successfully draw the latest 2 periods of data. 4 weeks in each. These are displayed as rows of data.. HOWEVER I want to generate these rows (using php or mysql.. or whatever it takes) so that the php page displays the data vertically to fit a table such as demonstrated in the image included.

The MYSQL statement I use to pull of the 2 period data is

```
SELECT *
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = ( SELECT MAX(id) FROM mfugi_kpi_period )
OR period1.periodnumber = ( SELECT MAX(id) FROM mfugi_kpi_period WHERE id < ( SELECT MAX(id) FROM mfugi_kpi_period ) )
ORDER BY period1.periodnumber DESC, weeknumber ASC
```

All advise / code appreciated.

The MYSQL is in a JOOMLA MODEL, and the display will be in a VIEW

sampledatabase.sql

4 Comments

To make it cleaner, I suggest you do it in steps:

Get Period 2:

SELECT MAX(id) FROM mfugi_kpi_period; -- store this in a PHP variable

Get Period 1:

SELECT MAX(id) FROM mfugi_kpi_period WHERE id < $period2; -- store this in a PHP variable

For each KPI, execute:

SELECT if(period_number=$period1 and weeknumber=1, kpi01value,0) as p1w1,

if(period_number=$period1 and weeknumber=2, kpi01value,0) as p1w2,

if(period_number=$period1 and weeknumber=3, kpi01value,0) as p1w3,

if(period_number=$period1 and weeknumber=4, kpi01value,0) as p1w4,

if(period_number=$period2 and weeknumber=1, kpi01value,0) as p1w1,

if(period_number=$period2 and weeknumber=2, kpi01value,0) as p1w2,

if(period_number=$period2 and weeknumber=3, kpi01value,0) as p1w3,

if(period_number=$period2 and weeknumber=4, kpi01value,0) as p1w4

FROM mfugi_kpi_perioddata period1

WHERE period1.periodnumber = $period1

OR period1.periodnumber = $period2;

Repeat the above for each KPI, replacing kpi01value with appropriate column.

I put the period values to variables so you won't have to do multiple subselects.

It would have been easier if instead of one column for each KPI, you had kpi_number and kpi_value. This way you can do it in one statement instead of one call for each KPI:

SELECT t.kpi_number, t.p1w1, t.p1w2, t.p1w3, t.p1w4, t.p2w1, t.p2w2, t.p2w3, t.p2w4 FROM

(SELECT kpi_number, if(period_number=$period1 and weeknumber=1, kpi_value,0) as p1w1,

if(period_number=$period1 and weeknumber=2, kpi_value,0) as p1w2,

if(period_number=$period1 and weeknumber=3, kpi_value,0) as p1w3,

if(period_number=$period1 and weeknumber=4, kpi_value,0) as p1w4,

if(period_number=$period2 and weeknumber=1, kpi_value,0) as p1w1,

if(period_number=$period2 and weeknumber=2, kpi_value,0) as p1w2,

if(period_number=$period2 and weeknumber=3, kpi_value,0) as p1w3,

if(period_number=$period2 and weeknumber=4, kpi_value,0) as p1w4

FROM mfugi_kpi_perioddata period1

WHERE period1.periodnumber = $period1

OR period1.periodnumber = $period2

) t

group by t.kpi_number order by t.kpi_number;

```
SELECT sum(t.p1w1), sum(t.p1w2), sum(t.p1w3), sum(t.p1w4), sum(t.p2w1), sum(t.p2w2), sum(t.p2w3), sum(t.p2w4) FROM
(SELECT if(period_number=$period1 and weeknumber=1, kpi01value,0) as p1w1,
if(period_number=$period1 and weeknumber=2, kpi01value,0) as p1w2,
if(period_number=$period1 and weeknumber=3, kpi01value,0) as p1w3,
if(period_number=$period1 and weeknumber=4, kpi01value,0) as p1w4,
if(period_number=$period2 and weeknumber=1, kpi01value,0) as p1w1,
if(period_number=$period2 and weeknumber=2, kpi01value,0) as p1w2,
if(period_number=$period2 and weeknumber=3, kpi01value,0) as p1w3,
if(period_number=$period2 and weeknumber=4, kpi01value,0) as p1w4
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = $period1
OR period1.periodnumber = $period2) t;
```

while the alternative I'm proposing should be:

```
SELECT t.kpi_number, sum(t.p1w1), sum(t.p1w2), sum(t.p1w3), sum(t.p1w4), sum(t.p2w1), sum(t.p2w2), sum(t.p2w3), sum(t.p2w4) FROM
(SELECT kpi_number, if(period_number=$period1 and weeknumber=1, kpi_value,0) as p1w1,
if(period_number=$period1 and weeknumber=2, kpi_value,0) as p1w2,
if(period_number=$period1 and weeknumber=3, kpi_value,0) as p1w3,
if(period_number=$period1 and weeknumber=4, kpi_value,0) as p1w4,
if(period_number=$period2 and weeknumber=1, kpi_value,0) as p1w1,
if(period_number=$period2 and weeknumber=2, kpi_value,0) as p1w2,
if(period_number=$period2 and weeknumber=3, kpi_value,0) as p1w3,
if(period_number=$period2 and weeknumber=4, kpi_value,0) as p1w4
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = $period1
OR period1.periodnumber = $period2
) t
group by t.kpi_number order by t.kpi_number;
```

http://www.experts-exchang

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

PHP Radio Array | 4 | 27 | |

Does PHPMyAdmin pose a security risk? | 2 | 30 | |

Strip HTML Entities and convert <br> Entity to \n | 4 | 20 | |

Could you point how to disable all the combobox of a modal view at the same time? | 7 | 8 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!