Lookup and Find a table and show data based on another table

[ArthurHick]

I have a database with the table name "Controllers" and the primary ID and other fields. Each time their is an event (alarm) the database creates a new table the name being based on the primary ID from one of the rows from the table "Controllers" to which the event relates to.

 

Thus for example a Table "Alarm212" is created (the 212 is one of the primary ID's in the Table "Controllers")

 

Is it possible and what would such a php statement be so that when viewing the data in the Table "Controllers" on a Web Page the php function is run for a particular row when a button is clicked, which then runs the php based on the "Controllers" primary ID in that particular row so it finds and the appropriate Table and then displays the info from this Table "Alarms212" , etc...

 

New tables, Alarms 213, Alarms 214, are continually being added to the database.

 

Any guidence will be appreciated.

[ginerjm]

A new table for each new event?  Or a new row in the events table?  The former seems more likely.  YOu create a new table and put how many rows in it after that?

[boompa]

This sounds like a poor database design. Based on what little you've told us, you should have a single Alarms table with a foreign key which links the alarm to the controller.

controllers
========
id
<other fields>
 
alarms
=====
id
controller_id
<other fields>

where controller_id is a foreign key referencing the id of the controllers table. Assuming MySQL this requires InnoDB table types.

[ArthurHick]

A new table is made for each initial event for each controller and in the new table say Alarms212 their are 8 columns and 1 row is thus initially created, each time that particular Controller Alarm goes off then an additional row is added to the table Alarms212, thus the Table created can have many rows.

 

I agree a single table Alarms linking back to controllers_id would be a better database design, unfortunately I did not design how the database works. Is it still possible however to write php to link the table Controllers to each Alarm table created based on the controller_ID and the table name which is Alarms.controller_ID and then display the alarms for that particular controller?

[kicken]

Is it still possible however to write php to link the table Controllers to each Alarm table created based on the controller_ID and the table name which is Alarms.controller_ID and then display the alarms for that particular controller?

Yes, first you SELECT the controller id from your controllers table and read the results into PHP. Then you take that ID and generate a new SELECT statement for the appropriate table name and run that query.