Dynamic table/matrix using PHP and MySQL

Question

I have a problem whilst trying to display MySQL data in a table format. Basically, I have a table which stores payments with the fields amount, item, currency and location. I want to display these in a table that shows the locations across the top, the currencies on the second row, the items in the first columns row and then the sum of the payments within the rest of the table corresponding to the location/currency. Here is an example of my MySQL table data:

item        | amount | currency | location
----------------------------------------------
Item 1      | 250    | USD      | UK    
Item 2      | 450    | GBP      | UK
Item 3      | 780    | EUR      | Germany   
Item 2      | 50     | GBP      | Spain
Item 2      | 150    | GBP      | Spain
Item 4      | 375    | USD      | UK
Item 4      | 650    | GBP      | Germany
Item 1      | 350    | USD      | UK

The end result I'm looking to achieve would be something like:

        |  Germany  | Spain |     UK    |
        | EUR | GBP |  GBP  | GBP | USD |
        ---------------------------------
Item 1  |     |     |       |     | 600 |
Item 2  |     |     |  200  | 450 |     |
Item 3  | 780 |     |       |     |     |
Item 4  |     | 650 |       |     | 375 |

At the moment, I am achieving the result using multiple queries which uses lots of nested queries for each of the items as follows:

<table width="1046" border="0">
  <tr>
    <td></td>
    <td colspan="2">Germany</td>
    <td>Spain</td>
    <td colspan="2">UK</td>
  </tr>
  <tr>
    <td></td>
    <td>EUR</td>
    <td>GBP</td>
    <td>GBP</td>
    <td>GBP</td>
    <td>USD</td>
  </tr>
  <? $q1 = mysqli_query("SELECT item FROM table1 group by item order by item asc");
  while($row1 = mysqli_fetch_assoc($q1)){
    $item = $row1['item']; ?>
    <tr>
      <td><? echo $item; ?></td>

      <? $q2 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='EUR' and item='$item' and location='Germany'");
      while($row2 = mysqli_fetch_assoc($q2)){ ?>
        <td><? echo number_format($row2['amt'],0); ?></td>
      <? } ?>

      <? $q3 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='GBP' and item='$item' and location='Germany'");
      while($row3 = mysqli_fetch_assoc($q3)){ ?>
        <td><? echo number_format($row3['amt'],0); ?></td>
      <? } ?>

      <? $q4 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='GBP' and item='$item' and location='Spain'");
      while($row4 = mysqli_fetch_assoc($q4)){ ?>
        <td><? echo number_format($row4['amt'],0); ?></td>
      <? } ?>

      <? $q5 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='GBP' and item='$item' and location='UK'");
      while($row5 = mysqli_fetch_assoc($q5)){ ?>
        <td><? echo number_format($row5['amt'],0); ?></td>
      <? } ?>

      <? $q6 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='USD' and item='$item' and location='UK'");
      while($row6 = mysqli_fetch_assoc($q6)){ ?>
        <td><? echo number_format($row6['amt'],0); ?></td>
      <? } ?>
    </tr>
    <? } ?>
</table>

I am wondering if there is a better way of achieving the same result?

peterm · Accepted Answer · 2013-04-14 11:33:02Z

1

If locations and currencies are fixed lists then you can use following query

SELECT item,
       SUM(CASE WHEN location = 'Germany' AND currency = 'EUR' THEN amount ELSE NULL END) Germany_EUR,
       SUM(CASE WHEN location = 'Germany' AND currency = 'GBP' THEN amount ELSE NULL END) Germany_GBP,
       SUM(CASE WHEN location = 'Spain' AND currency = 'GBP' THEN amount ELSE NULL END) Spain_GBP,
       SUM(CASE WHEN location = 'UK' AND currency = 'GBP' THEN amount ELSE NULL END) UK_GBP,
       SUM(CASE WHEN location = 'UK' AND currency = 'USD' THEN amount ELSE NULL END) UK_USD
  FROM yourtable
 GROUP BY item

Here is SQLFiddle

edited Apr 14, 2013 at 11:33

answered Apr 14, 2013 at 10:56

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taryn · Accepted Answer · 2013-04-14 10:55:39Z

MySQL does not have a PIVOT function but if you want to retrive this data in one query, then you could use an aggregate function with a CASE expression to get the data from rows into columns:

select item,
  sum(case when location = 'Germany' and currency = 'EUR' then amount else 0 end) Ger_EUR,
  sum(case when location = 'Germany' and currency = 'GBP' then amount else 0 end) Ger_GBP,
  sum(case when location = 'Spain' and currency = 'GBP' then amount else 0 end) Spa_GBP,
  sum(case when location = 'UK' and currency = 'GBP' then amount else 0 end) UK_GBP,
  sum(case when location = 'UK' and currency = 'USD' then amount else 0 end) UK_USD
from table1
group by item;

See SQL Fiddle with Demo. This gives the result:

|   ITEM | GER_EUR | GER_GBP | SPA_GBP | UK_GBP | UK_USD |
----------------------------------------------------------
| Item 1 |       0 |       0 |       0 |      0 |    600 |
| Item 2 |       0 |       0 |     200 |    450 |      0 |
| Item 3 |     780 |       0 |       0 |      0 |      0 |
| Item 4 |       0 |     650 |       0 |      0 |    375 |

Collectives™ on Stack Overflow

Dynamic table/matrix using PHP and MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related