CODEWITHSUNDEEP
phpmysqlsqldatabase-designtimestamp
Sunil Kumar
Sunil Kumar

Reputation: 1391

PHP - MySql: fetch item from three tables and order by timestamp

I am wondering what is the

best way to fetch different item details from three different tables and ORDER according to their timestamp display on home page

Suppose :
Table 1
+.......+..........+..........+.......+.........+..........+.........+...........+
:postId : sourceId : fee      : pay   : company : elig     : howto   : timestamp :
+.......+..........+..........+.......+.........+..........+.........+...........+

Table 2
+.......+..........+..........+.......+.........+..........+.........+...........+
:postId : sourceId : skill    : title : funct   : location : exper   : timestamp :
+.......+..........+..........+.......+.........+..........+.........+...........+

Table3
+.......+..........+..........+.......+.........+..........+.........+...........+
:postId : sourceId : company  : title : sector  : quali    : state   : timestamp :
+.......+..........+..........+.......+.........+..........+.........+...........+

All tables have no similarity between items, I cann't put them all in one table.

Now, I want to display items from all table on my application HOME PAGE according to their timestamp.

I know how to fetch in sequence. I am using following ways :

 SELECT * FROM Table1;   //Dispaly all items of table1
and then 
 SELECT * FROM Table2;   //Dispaly all items of table2

Similarlly from table3.

Is there any way to select all from all three table and order by their timestamp.

I read an article, which tells to use an separate table with (id, timestamp, table_type) and fetch items according to id order by timestamp but in my case all tables items have different sequence(may be two item from different table have same id).

Upvotes: 0

Views: 287

Answers (1)

Carl
Carl

Reputation: 509

How about this:

select timestamp, postId, sourceId, fee, pay, company, elig, howto, 
       null as skill, null as title, null as funct, null as location, null as exper, 
       null as company, null as title, null as sector, null as quali, null as state
from   table1
union all
select timestamp, postId, sourceId, null as fee, null as pay, null as company, null as elig, null as howto, 
       skill, title, funct,  location, exper, 
       null as company, null as title, null as sector, null as quali, null as state
from   table2
union all
select timestamp, postId, sourceId, null as fee, null as pay, null as company, null as elig, null as howto, 
       null as skill, null as title, null as funct, null as location, null as exper, 
       company, title, sector, quali, state
from   table3
order by timestamp;

Upvotes: 1

Related Questions