CODEWITHSUNDEEP
phpsqlsorting
AAA
AAA

Reputation: 3168

Query is not being sorted properly

I have this query I am using. I need to sort it by $variis, but it seems the sort is not working. I double checked field names and tables. All is correct but it won't sort it by the $variis:

Code: 

//Unseen
$variis = "Need Help";
$myid = This is the user's id;

$sql = "select car_help.car_id, agent_names.agent_name, help_box.status, 
car_help.why_car, car_help.date_time_added, car_help.just_date, 
car_help.type, agent_names.agent_id
from car_help LEFT JOIN agent_names on car_help.agent_whois = agent_names.agent_id 
where agent_names.system_id = '$myid' and car_help.system_id='$myid' 
and added_by <> '$myid' and help_box.status = '$variis'
UNION
select magazine_help.note_id, agent_names.agent_name, help_box.status, 
magazine_help.note_name, magazine_help.date_time_added, 
magazine_help.just_date, magazine_help.type, agent_names.agent_id
from magazine_help LEFT JOIN agent_names on 
magazine_help.agent_id = agent_names.agent_id 
where agent_names.system_id='$myid' and 
magazine_help.system_id = '$myid' and added_by <> '$myid' 
and help_box.status = '$variis'
UNION
select motorcycle_help.rand_id, agent_names.agent_name, 
help_box.status, motorcycle_help.rand_name, motorcycle_help.date_time_added,     
motorcycle_help.just_date, motorcycle_help.type, agent_names.agent_id
from motorcycle_help LEFT JOIN agent_names ON 
motorcycle_help.by_who = agent_names.agent_id
where agent_names.system_id = '$myid' and 
motorcycle_help.system_id='$myid' and added_by <> '$myid' 
and help_box.status = '$variis'
UNION
select mobile_questions.bal_test_id, agent_names.agent_name, 
help_box.status, mobile_questions.bal_why, mobile_questions.date_time_added,   
mobile_questions.just_date, mobile_questions.type, agent_names.agent_id
from mobile_questions LEFT JOIN agent_names ON 
mobile_questions.agent_who_ordered = agent_names.agent_id
where agent_names.system_id = '$myid' and 
mobile_questions.system_id='$myid' and added_by <> '$myid' 
and help_box.status = '$variis'
ORDER BY date_time_added DESC LIMIT $startrow, 20";

$result = mysql_query($sql);

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


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


$rows = mysql_num_rows($result);

if($rows == 0)
{
print("");

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

$row1 = $row['row_name'];


print("$row1");
}

}

Ok an update: I selected the status field and displayed it. For all of these rows that are being returned teh the status is no help needed when the $variis is in fact need help

Upvotes: 1

Views: 183

Answers (3)

Mat&#237;as C&#225;nepa
Mat&#237;as C&#225;nepa

Reputation: 5974

try this:

from magazine_help LEFT JOIN agent_names on 
magazine_help.agent_id = agent_names.agent_id **and help_box.status = '$variis'**
where agent_names.system_id='$myid' and 
magazine_help.system_id = '$myid' and added_by <> '$myid'

instead of using the *and help_box.status = '$variis'* in the where clause, use it in the join

Upvotes: 1

a.b
a.b

Reputation: 9574

I believe by "sort", you mean "filter". You should include help_box table in your FROM clause every time you need help_box.status in your WHERE clause. Only then it can run.

Upvotes: 0

dev-null-dweller
dev-null-dweller

Reputation: 29462

Please read the manual: http://dev.mysql.com/doc/refman/5.1/en/union.html

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses: 

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

Upvotes: 4

Related Questions