CODEWITHSUNDEEP
phpmysqlsql-order-byunion
ariel
ariel

Reputation: 3072

How to use a Union properly with Order BY?

The code I have below joins 5 tables and then is suppose to sort by date_timed_added. The query worked perfectly if i only join 4 tables. For some reason after the 4th table, its giving me issues. The issue is that it sorts and displays the 5th table first and then the rest follow. How can i fix it so that it sorts date_time_added properly by querying all the other tables? 

//$sid is a variable that is drawn from DB

$sql = "select `client_visit`.`visit_id`, `client_visit`.
`why_visit`, `client_visit`.`date_time_added`, `client_visit`.
`just_date`, `client_visit`.`type` from `client_visit` where 
`client_visit`.`system_id` = '$sid' and `client_visit`.
`added_by` = '$sid' 
UNION
select `client_notes`.`note_id`, `client_notes`.`note_name`, 
`client_notes`.`date_time_added`, `client_notes`.`just_date`
, `client_notes`.`type` from `client_notes` where `client_notes`.
`added_by` = '$sid'
UNION
select `client_conditions`.`med_id`, `client_conditions`.`med_name`,  
`client_conditions`.`date_time_added`, `client_conditions`.`just_date`,    
`client_conditions`.`type` from `client_conditions` where 
`client_conditions`.`system_id` = '$sid' and `client_conditions`.
`added_by` = '$sid'
UNION
select `client_stats`.`stat_test_id`, `client_stats`.`stat_why`,  
`client_stats`.`date_time_added`, `client_stats`.`just_date`, 
`client_stats`.`type`
from `client_stats` where `client_stats`.`system_id` = '$sid' 
and `client_stats`.`added_by` = '$sid'
UNION
select `client_documents`.`doc_id`, `client_documents`.`doc_name`,  
`client_documents`.`date_time_added`, `client_documents`.`just_date`, 
`client_documents`.`type` from `client_documents` where `client_documents`.
`system_id` = '$sid' and `client_documents`.`added_by` = '$sid'
 ORDER BY `date_time_added` DESC LIMIT $startrow, 20";

 $query = mysql_query($sql) or die ("Error: ".mysql_error());

 $result = mysql_query($sql);

 if ($result == "")
 {
 echo "";
 }
 echo "";


 $rows = mysql_num_rows($result);

 if($rows == 0)
 {

 }
 elseif($rows > 0)
 {
 while($row = mysql_fetch_array($query))
 {

 //Just using these two variables i can display the same row info 
 //for all the other tables

 $stuffid = htmlspecialchars($row['visit_id']);
 $title = htmlspecialchars($row['why_visit');

 }

 }

 }

Upvotes: 0

Views: 165

Answers (2)

Marc B
Marc B

Reputation: 360592

As per the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/union.html

If you want to order the ENTIRE result set, the ORDER BY clause must be placed on the LAST query in the UNION, with each query being bracketed.

(SELECT ...)
UNION
(SELECT ...)
ORDER BY ...

Upvotes: 2

Bob
Bob

Reputation: 49

sth like this should do.

SELECT Tbl1.field1
    FROM ( SELECT field1 FROM table1
           UNION
           SELECT field1 FROM table2
         ) Tbl1
    ORDER BY Tbl1.field1

Upvotes: 0

Related Questions