LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 01-17-2008, 07:53 PM   #1
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,052

Rep: Reputation: 484Reputation: 484Reputation: 484Reputation: 484Reputation: 484
MySQL/PHP get data from dissimilar tables


Hi all,

In my application I have some dissimilar tables which share only a datetime field and possibly a field to indentify what kind of record it concerns. Records look like this:

Code:
table1:
2008-01-14 10:10:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 14:50:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 17:15:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 18:10:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-15 00:45:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-15 03:20:00 device1 parm1 parm2 parm3 parm4 parm5

table2:
2008-01-14 16:25:00 device2 parm11 parm12
2008-01-14 17:05:00 device2 parm11 parm12
2008-01-14 18:30:00 device2 parm11 parm12
2008-01-15 01:35:00 device2 parm11 parm12
2008-01-15 04:45:00 device2 parm11 parm12
2008-01-15 06:50:00 device2 parm11 parm12
Now I want to retrieve those records into my PHP application and show them in one HTML table, sorted by time. So:
Code:
2008-01-14 10:10:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 14:50:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 16:25:00 device2 parm11 parm12
2008-01-14 17:05:00 device2 parm11 parm12
2008-01-14 17:15:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 18:10:00 device1 parm1 parm2 parm3 parm4 parm5
2008-01-14 18:30:00 device2 parm11 parm12
2008-01-15 00:45:00 device1 parm11 parm12 parm3 parm4 parm5
2008-01-15 01:35:00 device2 parm1 parm2
etc
The time stamp for a record is more or less random, no assumptions can be made that that many records are displayed from one table, then so many from the other table etc.

The tables are event queues which specify on which moment a certain device (device1 or device2) must be sent a control message.

The event handlers are all different applications, each event handlers has its own table (hence the different tables).

What I need is to find an algorithm to gather the data from the various tables and display them in sorted order. There are 4 tables, each containing 30-40 records. The sorted display has to be updated about once a second. The parameters, parm1, parm2 etc. are different for the different devices. They can be integers or strings. If parm1 is a string, parm11 can be an integer. The time stamp is common though

Unioning the tables would not work, because the number of fields is different.

I see two possible approaches:

1. Read all records from all tables into a PHP array. Merge the arrays in such an order that the datetime fields become in sequence. Only 4 queries but a lot of calculation

2. Perform a query which selects only the datetimefield, like:
Code:
select start_dt from table1 union select start_dt from table2 sort by start_dt
What this will return me is a sorted list with all unique datetime values. I can then perform a query for each datetime value in each table, which will return one or more records satisfying that time stamp. It is guaranteed that they will be in the correct order.

Are there any better methods for retrieving the data sorted? Which method would be preferable? Doing the calculation in PHP, or doing more queries? Maybe I am overlooking somethin terrible simple...

jlinkels
 
Old 01-18-2008, 12:26 AM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,225

Rep: Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021
If you want an update every second, I take it this is basically for visual display, so you can add dummy select cols (actually constants) to the shorter tables ie those with less cols.

eg
Code:
mysql> create table t1 (dt datetime, dev varchar(1));
Query OK, 0 rows affected (0.12 sec)

mysql> create table t2 (dt datetime, dev varchar(1), parm1 varchar(1));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 (dt, dev) values (now(), 'v');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t2 (dt, dev, parm1) values (now(), 'x', 'p');
Query OK, 1 row affected (0.03 sec)

mysql> select dt, dev, 'DUMMY' from t1 union select dt, dev, parm1 from t2 order by 1;
+---------------------+------+-------+
| dt                  | dev  | DUMMY |
+---------------------+------+-------+
| 2008-01-18 16:19:21 | v    | DUMMY |
| 2008-01-18 16:19:40 | x    | p     |
+---------------------+------+-------+
2 rows in set (0.00 sec)
 
Old 01-18-2008, 12:31 PM   #3
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,052

Original Poster
Rep: Reputation: 484Reputation: 484Reputation: 484Reputation: 484Reputation: 484
Indeed it is for a visual display.

You pointed me into the right direction. I also found that it is possible to select the same column twice from the table containing less columns. Basically the same as what you said.

I needed that idea, now I can solve it elegantly.
Thanks

jlinkels
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting data from multiple mysql tables derzok Programming 3 10-14-2007 07:00 AM
php mysql no data appears on browser. sunlinux Programming 6 06-15-2006 06:37 AM
Dynamically select data from MySQL tables tangle Programming 6 02-28-2006 05:48 PM
Mass editing mysql tables via php. apepost Programming 1 12-17-2005 06:25 PM
I need help with inserting data into mysql w/ PHP lostboy Programming 3 08-25-2003 12:08 PM


All times are GMT -5. The time now is 06:10 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration