CODEWITHSUNDEEP
phpmysqltabular
Lukas
Lukas

Reputation: 23

Displaying Data From Multiple MySQL Tables

I have 14 tables (one for every year) with product code, firm name and invoice numbers. Main structure of table is identical (product code, ID), but there can be some variables in names of firms.

Table2011
| ID | productcode | firm1 | firm2 | firm3 | etc |
| 1  | G-00001     | 2;5;40| 32;67 |       | 150 |
| 2  | G-00005     |       | 50    |       |     |
|etc |             |       |       |       |     |

Table2010
| ID | productcode | firm1 | firm2 | firm3 |etc |
| 1  | G-00001     | 1;10  |       |  55   |    |
| 2  | G-00003     |       | 2     |       |    |
| 3  | G-00005     |       | 50    |  40   |    |
| etc|             |       |       |       |    |

Table2009
...

Column Firm1 do not usually equals to same firm as firm 1 in other table

I am using table editor to work with tables (adding columns to table, editing values…).

I would like to know if it is possible to achieve result like below. It is above my PHP skills.

Product G-00001 page

…
<UL>
<LI>Year 2011: 150etc; 67firm2; 40firm1; 32firm2; 5firm1; 2firm1</LI>
<LI>Year 2010: 55firm3; 10firm1; 1firm1</LI>
<LI>Year 2009: ...</LI>
...
</UL>
…

Upvotes: 1

Views: 523

Answers (2)

JRSofty
JRSofty

Reputation: 1256

If you just want to display the data and not worry about the restructuring just yet you can use a JOIN to display the information from all the tables.

Although I would agree with teresko you really need to redesign that database. It is not maintainable the way it is.

Upvotes: 0

tereško
tereško

Reputation: 58444

Lemme begin with book recommendation : SQL Antipatterns. You will need it, doesn't matter if you caused this mess or ar just assigned to fix it.

If i was in your place, first thing would do would be to fix the database structure. This is madness. You do not need a new table for each year and new column for each company. Database is not a form of Excel spreadsheet.

Invoices             Years          Companies
 -----------------      -------------     ---------------
| product_code PK |    | year_id  PK |   | company_id PK |
| company_id   FK |    | number      |   | title         |
| amount          |     -------------     ---------------
| year_id      FK |
 -----------------

Where PK - primary key and FK - foreign key.

This structure would make the gathering of information much much much MUCH easier.

Upvotes: 4

Related Questions