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');
}
}
}