CODEWITHSUNDEEP
phpmysqldatetime
Dan
Dan

Reputation: 1110

Organize MySQL results by DateTime

Using PHP I am trying to find a way to combine the results of multiple MySQL queries and put them into one array ordered by their DateTime timestamps which in the database is saved under 'timestamp'.

My Code:

        $reactions = DB::query('SELECT * FROM reactions WHERE poster_id=:userid', array(':userid' => $userid));
        $comments = DB::query('SELECT * FROM comments WHERE poster_id=:userid', array(':userid' => $userid));
        $mentions = DB::query('SELECT * FROM comments WHERE FIND_IN_SET(:users , users)', array(':users' => $userid));
        $postMentions = DB::query('SELECT * FROM posts WHERE FIND_IN_SET(:users , users)', array(':users' => $userid));

Edit: Updated Code

$reactions = "'SELECT timestamp as date FROM reactions WHERE poster_id=:userid', array(':userid' => $userid)";
        $comments = "'SELECT timestamp as date FROM comments WHERE poster_id=:userid', array(':userid' => $userid)";
        $mentions = "'SELECT timestamp as date FROM comments WHERE FIND_IN_SET(:users , users)', array(':users' => $userid)";
        $postMentions = "'SELECT timestamp as date FROM posts WHERE FIND_IN_SET(:users , users)', array(':users' => $userid)";

        $sql = 'SELECT timestamp FROM ('.$reactions.' UNION ALL '.$comments.' UNION ALL '.$mentions.' UNION ALL '.$postMentions.') as combined order by timestamp desc';

        $result = DB::query($sql, array());

        print_r($result);

Upvotes: 1

Views: 60

Answers (1)

Sodium
Sodium

Reputation: 1066

You can achieve same through UNION. Try below code snippet...

$reactions = 'SELECT reactions.date as date FROM reactions WHERE poster_id=:userid', array(':userid' => $userid);
$comments = 'SELECT comments.date as date FROM comments WHERE poster_id=:userid', array(':userid' => $userid);
$mentions = 'SELECT comments.date as date FROM comments WHERE FIND_IN_SET(:users , users)', array(':users' => $userid);
$postMentions = 'SELECT posts.date as date FROM posts WHERE FIND_IN_SET(:users , users)', array(':users' => $userid);

$sql = SELECT combined.date from (".$reactions." UNION ALL ".$comments." UNION ALL ".$mentions." UNION ALL ".$postMentions.") as combined order by combined.date desc;

Upvotes: 1

Related Questions