combine 3 select statements and sort by timestamp

Question

I have 3 mysql statements each giving out different result set and I would like to represent all the results gathered from the queries sorted by timestamp desc in total .

For eg: if Purchased orders has timestamp before products then he items in purchased orders should be before products .
How do I do that and sent the combined sorted result of the 3 result set.I really appreciate any help.Thanks in Advance.

$query=mysql_query("select * from products order by timestamp desc ;");
$numrows = mysql_num_rows($query);
if ($numrows!=0)
{
    $rows = array();
    while($r = mysql_fetch_assoc($query)) {
        $rows[] = $r;
    }
}
echo json_encode($rows);

$query2=mysql_query("select * from user_purchased order by timestamp desc ;");
$numrows2 = mysql_num_rows($query2);
if ($numrows2!=0)
{
    $rows2 = array();
    while($r2 = mysql_fetch_assoc($query2)) {
        $rows2[] = $2r;
    }
}
echo json_encode($rows2);

$query3=mysql_query("select * from delivery order by timestamp desc ;");
$numrows3 = mysql_num_rows($query3);
if ($numrows3!=0)
{
    $rows3 = array();
    while($r3 = mysql_fetch_assoc($query3)) {
        $rows3[] = $r3;
    }
}
echo json_encode($rows3);

Answer 1

To combine all the data and sort them by date descending, you would use a sub-query with UNION ALL and combine un-ordered results from all 3 tables...and then order the records by timestamp descending. Below is an example. However, if they all have different field names and the number of field names are different, you could create dummy field names to create consistent information. It's described below in an edit.

For the sake of simplicity, let's assume that all tables had the following structure

+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| id    | int(11)   | YES  |     | NULL              |       |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+

Let's look at the dummy data in each table.

Products

mysql> select * from products;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2014-03-26 04:39:06 |
|    4 | 2014-03-26 04:40:05 |
+------+---------------------+

User_Purchased

mysql> select * from user_purchased;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    2 | 2014-03-26 04:39:19 |
|    5 | 2014-03-26 04:40:00 |
+------+---------------------+

Delivery

mysql> select * from delivery;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    3 | 2014-03-26 04:39:28 |
|    6 | 2014-03-26 04:39:38 |
+------+---------------------+

PHP

<?php
$db = mysqli_connect('host','usr','pwd');
mysqli_select_db($db,'yourdb');
$data = mysqli_query($db, "
    select * from
    (
        select 'products' as tbl, id, ts from products
        union all
        select 'purchased' as tbl, id, ts from user_purchased
        union all
        select 'delivered' as tbl, id, ts from delivery
    ) a
    order by ts desc
    "
);

echo "tablename\tid\ttime\n";
foreach ($data as $row) {
    echo "{$row['tbl']}\t{$row['id']}\t{$row['ts']}\n";
}
?>

Output

tablename       id      time
products        4       2014-03-26 04:40:05
purchased       5       2014-03-26 04:40:00
delivered       6       2014-03-26 04:39:38
delivered       3       2014-03-26 04:39:28
purchased       2       2014-03-26 04:39:19
products        1       2014-03-26 04:39:06

Edit

Let's say products table had fields id, productname, productnumber. Purchased had id, productid, purchasedate. Delivery had id, purchaseid, deliverydate.

You could combine all the fields in each query by doing something like and then sort on mytimestamp:

select 
    'products' as tbl, pdt_id, productname as pdt_productname, productnumber as pdt_productnumber,
    '-' as pur_id, '-' as pur_productid, '-' as pur_purchasedate,
    '-' as del_id, '-' as del_purchaseid, '-' as del_deliverydate,
    ts as mytimestamp
from products
union all
select 
    'purchased' as tbl, '-' as pdt_id, '-' as pdt_productname, '-' as pdt_productnumber, 
    id as pur_id, productid as pur_productid, purchasedate as pur_purchasedate,
    '-' as del_id, '-' as del_purchaseid, '-' as del_deliverydate,
    ts as mytimestamp
from purchased
union all
select 
    'delivered' as tbl, '-' as pdt_id, '-' as pdt_productname, '-' as pdt_productnumber, 
    '-' as pur_id, '-' as pur_productid, '-' as pur_purchasedate,
    id as del_id, purchaseid as del_purchaseid, deliverydate as del_deliverydate,
    ts as mytimestamp
from delivered