[SOLVED] while loop is getting the wrong row

[thefollower]

I am having a weird problem with my while loop the 3rd row it echo's is completely different row that should not be there and the echo's are not correct...

 

Not sure how it's happening but this is what i got:

 

My query:

<?php
$GetMessages = mysql_query("SELECT * FROM messages
UNION
SELECT * FROM textmessages
UNION
SELECT * FROM phonecalls WHERE (Sender='$ReportedPlayer' AND Receiver='$OtherPlayer') OR (Sender='$OtherPlayer' AND Receiver='$ReportedPlayer')
ORDER BY SentTime ASC")
or die(mysql_error());
?>

 

Then the table:

 

<?php
while($rowmessage = mysql_fetch_assoc($GetMessages)){
	$SenderID = $rowmessage['Sender'];
	$RecieverID = $rowmessage['Receiver'];

	If($SenderID == $ReportedPlayer){
		$Font = '#32CD32';
		}Else{
		$Font = '#FFFFFF';
		}
	If($RecieverID == $ReportedPlayer){
		$Font2 = '#32CD32';
		}Else{
		$Font2 = '#FFFFFF';
		}
	$Message = $rowmessage['MessageText'];
	$SentTime = $rowmessage['SentTime'];
	$GetUserName = mysql_query("SELECT Username FROM userregistration WHERE UserID ='$SenderID'")
	or die(mysql_error());
	$GetUserNameRow = mysql_fetch_assoc($GetUserName);
	$Sender = $GetUserNameRow['Username'];

	$GetUserName2 = mysql_query("SELECT Username FROM userregistration WHERE UserID ='$RecieverID'")
	or die(mysql_error());
	$GetUserNameRow2 = mysql_fetch_assoc($GetUserName2);
	$Reciever = $GetUserNameRow2['Username'];

	If($Reciever == ''){
	$Reciever = '----Deleted User----';
	}
	If($Sender == ''){
	$Sender = '----Deleted User----';
	}
	?>
		<tr>
	<td width="100"><center><font style="font-size:14px" color="<?=$Font?>" face="Arial"><?=$Sender?>[<?=$SenderID?>]</center></font></td>
	<td width="100"><center><font style="font-size:14px" color="<?=$Font2?>" face="Arial"><?=$Reciever?>[<?=$RecieverID?>]</center></font></td>
	<td width="100"><center><font style="font-size:14px" color="#FFFFFF" face="Arial"><?=$Message?></center></font></td>
	<td width="100"><center><font style="font-size:14px" color="#FFFFFF" face="Arial"><?=$SentTime?></center></font></td>

		</tr>
</td>

<?php
}
?>

 

The results come out wrong as it shows an ID that does not match the query's WHERE clause ... and the row that should not be there is also echo'in the "subject" field for the "$SentTime" variable... it should be a time stamp but its echo'in the message subject which is most strange.

The first two rows it echo's match fine the third one does not.. and the third row is coming from "textmessages".

 

I think its picking it up because the receiver of the message is the reported player.. how ever.. the sender is not the same so it should not be loading up.

 

Can any one see a mistake or something in the php and mysql? =/

[Barand]

The WHERE clause is only applied to the phonecalls table. You selected all rows from the others

[thefollower]

How do i get the WHERE clause to apply to all the tables then ?

[Barand]

Specify it for each SELECT

[thefollower]

Ok..

 

<?php
$GetMessages = mysql_query("SELECT * FROM messages WHERE (Sender='$ReportedPlayer' AND Receiver='$OtherPlayer') OR (Sender='$OtherPlayer' AND Receiver='$ReportedPlayer')
ORDER BY SentTime ASC")
UNION
SELECT * FROM textmessages WHERE (Sender='$ReportedPlayer' AND Receiver='$OtherPlayer') OR (Sender='$OtherPlayer' AND Receiver='$ReportedPlayer')
ORDER BY SentTime ASC")
UNION
SELECT * FROM phonecalls WHERE (Sender='$ReportedPlayer' AND Receiver='$OtherPlayer') OR (Sender='$OtherPlayer' AND Receiver='$ReportedPlayer')
ORDER BY SentTime ASC")
or die(mysql_error());
?>

 

Like that? or does the ORDER BY only need to be at the end?

[Barand]

Just one ORDER BY at the end, yes

 

As you are using "SELECT *" all the tables need to be structured the same for UNION to work

[thefollower]

Yeah they are. Thanks for the help and merry Christmas