Select from multiple tables using union all and single order by ,php

Question

I ran into an error I cant seem to come out of. am not too good with unions I want to loop through 4 different tables(using union all) and manipulate their values to fit my needs. I also need to use single 'ORDER by Date DESC' (Date are integer values) for the whole union all, so that I can arrange the output in a pattern,

when I add the 'order by date desc ' to it, code doesn't work . and when I remove it , the values of the second query are attached to the names of the first query, am sooo confused.
I tried "Select * from table_name where..... it idnt work in this case , that's why I had to bring out all table_names I need to the query,

Basically , I want to echo each value from the query uniquely when I need to, any help is appreciated, thanks

<?php

$q114="(SELECT  id AS id1,text_post AS text_post1,likes AS likes1 
        FROM timeline_posts WHERE email='$owner_email') 
        UNION ALL (SELECT  pic_comment AS pic_comment2, comments AS comments2, date AS date2 
            FROM pictures WHERE email='$owner_email') 
        UNION ALL (SELECT image AS image3,likes AS likes3, comments AS comments3 
            FROM profile_pics WHERE email='$owner_email') 
        UNION ALL (SELECT  likes AS likes4, comments AS comments4, date AS date4 
            FROM friends_timeline_post WHERE timeline_email='$owner_email') 
        ORDER BY 'date' DESC";

$pages_query=mysqli_query($connect,$q114);

while($fetch9=mysqli_fetch_assoc($pages_query))        
{ 
    print_r($fetch9['likes3'] );

    //a lot of work to be done here
}

?>

maybe this will help you https://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql — Mahmood A.Shakir, May 25 '17 at 19:10

score 0 · Answer 1 · answered May 25 '17 at 20:33

For "unioning" the results of multiple selects and ordering those results by a column name across all the results of the multiple selects, column names must be the same in all selects.

You could add a column to all selects that would contain your "digit" in order to still be able to distinguish between let say "likes1" and "likes2" even if their column name is "likes" for all the selects that you "unioned".

Select from multiple tables using union all and single order by ,php

1 Answers1