php/html invoice table using mysql data combining all sales of a person into one row

I have a mysql table that contains invoice info for my members.

invoice table

memberid | item | amount
------------------------
mem1     | book | 100.00
mem1     | lamp | 50.00
mem2     | desk | 200.00
mem1     | pen  | 1.00

I would like to combine all of the rows of a specific memberid in开发者_JAVA百科to a single row (either in a temporary mysql table or even just dump it into an html table for viewing)

example - new invoice table

memberid | item1 | amount1 | item2 | amount2 | item3 | amount3 
---------------------------------------------------------------
mem1     | book  | 100.00  | lamp  | 50.00   | pen   | 1.00
mem2     | desk  | 200.00  | NULL  | NULL    | NULL  | NULL</pre>

I would need this new table to add the new item and amount column dynamically as the number of times a member buys an item is unknown. so lets say mem1 buys another item, this new table would have to append 2 more columns (item4, amount4).

It's hard to pivot a table in MySQL, but you do have a build-in pivot function: GROUP_CONCAT
Here's my suggestion.

The idea is to build a table in html, using the tags for start table etc.

Here's what a table looks like in html source.

<table border="1">
<tr>
<td>A1</td> <td>B1</td>
</tr>
<tr>
<td>A2</td> <td>B2</td>
</tr>
</table> 

and here's the ascii art of the output

+--------+--------+
| A1     | B1     |
+--------+--------+
| A2     | B2     |
+--------+--------+

You can build this using group concat

SELECT 
  concat('<tr><td>',s.member_id,'</td>'
         ,group_concat(s.itempair SEPARATOR '')
         ,'</tr>') as row
  FROM (
    SELECT member_id, concat('<td>',item,'</td><td>',amount,'</td>') as itempair
    FROM invoice ORDER BY member_id, item ) s
  GROUP BY s.member_id;

Now you can just start the table by echoing a <table border = "1"> tag,
Echoing all the rows from the select
and finally echoing a </table> tag.

For more info on group_concat
see: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Good luck with the php code.

Create a table with a foreign key to a user, a foreign key to a product, and a foreign key to an invoice. Create separate tables for products, users, and invoices. Then, use joining to combine all that information together.