CODEWITHSUNDEEP
phpmysqlsql
user1760150
user1760150

Reputation: 654

algorithm to arrange multiple sql queries by timestamp fields in PHP

I'm looking for a way to sort the results of 3 seperate queries of different tables by a timestamp field that's on each table.

$sql1 = "SELECT * FROM chores WHERE Status = 'unchecked' ORDER BY Time DESC";
$sql2 = "SELECT * FROM contacts WHERE Status = 'unchecked' ORDER BY DateTime DESC";
$sql3 = "SELECT * FROM gyms WHERE Status = 'unchecked' WHERE Endtime IS NOT
NULL ORDER BY EndTime DESC";

So say I have those 3 queries, and I want the data from the row whose timestamp field is the 3rd oldest, compared to all of the other rows in all of the other tables. Is there an easy way to do this either in a SQL Query or in PHP? I'm struggling with figuring out the logic for it.

Upvotes: 1

Views: 52

Answers (1)

Jake Psimos
Jake Psimos

Reputation: 640

Lets assume that you have ran your queries and have three separate associative arrays containing all the rows...

$array1, $array2, and $array3

$test = array_merge($array1, $array2, $array3);
usort($test, 'sortit');
print_r($test);

function sortit($a, $b){
    /* 
        Note, if the column name for the timestamp is not the same for each table,
        this function must know what element to compare for a proper sort.

        Also, this is assuming that the [timestamp] field is a linux epoch, if it is not, then
        simply do this:

        $a_time = strtotime($a['DateString']);
        $b_time = strtotime($b['TimeString']);
        return $a_time >= $b_time ? 1 : -1;
    */
    return $a['timestamp'] >= $b['timestamp'] ? 1 : -1;
}

Upvotes: 1

Related Questions