CODEWITHSUNDEEP
phpmysqlsql-order-by
Your Yummy
Your Yummy

Reputation: 153

show data from multiple table order by datetime

I have a php page and want to display data from multiple tables (currently 3 tables)
all tables have date-time column
and other columns are totally different (in numbers & data-type etc) I want to display
most recent row from either of three tables
2nd most recent row from either of three tables
3rd most recent row from either of three tables
and so on

my tables structure is like below 

ATTANDANCE [a_id,date-time,employee,attandance_more....]   

TASKS_ASSIGN[ta_id,date-time,employee,title,detail,duration,task_assign_more.....]

TASK_COMPLETED [tc_id,date-time,employee,stars,task_completed_more....]

i want to display as facebook as 

 Alam has joined office at 23-mar-2013 **08:00am**   
 Haider has joined office at 23-mar-2013 **08:01am**  
 Alam has completed xyz-task at 22-mar-2013 **03:45pm**
 Alam has joined office at 22-mar-2013 **10:12am**
 ......

Upvotes: 0

Views: 1561

Answers (2)

Frédéric Clausset
Frédéric Clausset

Reputation: 1096

First you add all the results (from the 3 tables) in one array.($theArray)
Then you sort the array using usort. Something like this :

function cmp($a, $b)
{
    if ($a->date-time == $b->date-time) {
        return 0;
    }
    return ($a->date-time < $b->date-time) ? -1 : 1;
}

usort($theArray, "cmp");

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

It seems like you only want a subset of the columns. I'm not sure what the exact relationship is between the tables and your results, but something like:

select  'attandance' as which, employee, `datetime`
from attendance
union all
select 'assign' as which, employee, `datetime`
from tasks_assign
union all
select `completed` as which, employee, `datetime`
from tasks_completed
order by `datetime` desc

That is, you can use subqueries to get just a subset of the columns in each table. Then use union all to bring them together and order by to put them in the order you want.

Upvotes: 3

Related Questions