getting double results

and i have no clue why

[mylungsarempty]

this code might be a little bit, erm, 'mangled'... but i've been toying with it for an hour trying to figure out why after the results are displayed, they are simply displayed over again. the entire set of results just gets displayed twice. What am i missing here?

<?

mysql_select_db($db, $con);

$all_mail="SELECT * FROM messages WHERE recipient='$username'";
$the_mail = mysql_query($all_mail);
if (!$the_mail) {
die('<p>error retrieving zipcodes<br>' . 'Error: ' . mysql_error() . '</p>');
}

$N=mysql_num_rows($the_mail);

for($i=0; $i<$N; $i++) {
$row=mysql_fetch_assoc($the_mail);
$your_mail[]=$row['username'];

}

foreach ($your_mail as $col_name => $col_data) {

if(1==1){$find_my_mail="SELECT * FROM messages WHERE recipient='$username'";}

$all_my_mail=mysql_query($find_my_mail);

if (!$all_my_mail) {
die('<p>error retrieving mail<br>' . 'Error: ' . mysql_error() . '</p>');
}

while ($result = mysql_fetch_array($all_my_mail)) {
$recipient = $result['recipient'];
$sender = $result['sender'];
$subject = $result['subject'];
$body = $result['body'];
$maildate = $result['date'];
$ifread = $result['ifread'];

echo $col_data[0] . '<A HREF="viewmsg.php" target="right" onFocus="if(this.blur)this.blur()"><p><font color="maroon"><B><U>' . $subject . '</U></B></font><font color="ivory"> : ' . $body . '<br /></font><font color="maroon"><I>date: </font><font color="BBBBBB">' . $maildate . '</A></I></font><hr>';

unset($recipient);
unset($sender);
unset($subject);
unset($body);
unset($maildate);
unset($ifread);

}

}

?>

[jatar_k]

is it just this line

echo $col_data[0] . '<A.....

spitting out twice?

why are you doing the same query twice?

at top
$all_mail="SELECT * FROM messages WHERE recipient='$username'";

and then
$find_my_mail="SELECT * FROM messages WHERE recipient='$username'";

same query should have the same return

[BitBanger]

Let me guess, there are two records for the 'username' you are searching for.

What I think is happening is that you are doing the first query and getting a list of all the mail for a particular username. Then you are doing the same query again for every returned entry on the first query.

What I think you should do is to eliminate the first query entirely. What you have for the second query should be sufficient.

<? 
mysql_select_db($db, $con);  
$find_my_mail="SELECT * FROM messages WHERE recipient='$username'"; 
$all_my_mail=mysql_query($find_my_mail); 
if (!$all_my_mail) { 
die('<p>error retrieving mail<br>' . 'Error: ' . mysql_error() . '</p>'); 
} 
while ($result = mysql_fetch_array($all_my_mail)) { 
$recipient = $result['recipient']; 
$sender = $result['sender']; 
$subject = $result['subject']; 
$body = $result['body']; 
$maildate = $result['date']; 
$ifread = $result['ifread']; 
echo $username . '<A HREF="viewmsg.php" target="right"  
onFocus="if(this.blur)this.blur()"><p><font color="maroon"><B><U>' . $subject .  
'</U></B></font><font color="ivory"> : ' . $body . '<br /></font><font  
color="maroon"><I>date: </font><font color="BBBBBB">' . $maildate . '</A></I> 
</font><hr>'; 
unset($recipient); 
unset($sender); 
unset($subject); 
unset($body); 
unset($maildate); 
unset($ifread); 
}  
?> 

I also changed your echo statement to use $username rather than the (now) unused $col_data.

edited to fix overly long line

[edited by: BitBanger at 2:56 am (utc) on Jan. 22, 2004]

[mylungsarempty]

i'm doing the same query twice because this is heavily modified from the "find users" search script that i finally got to work... i thought it would be an easy modification, but i just got confused.

echo $col_data[0] . '<A HREF="...

yes, this is the part that is repeated. I only have two messages in the database right now, and what this script is doing is displaying them both, and then displaying both of them right after the first pair.

[mylungsarempty]

Hey, thanks a lot BitBanger . .

i will study this and try to understand exactly what i had wrong.

a tangent: how would i go about letting records in a database be searched by some text in a text field, but not as an exact match... only, if the word appears anywhere in the record? i recall something about 'like' and %% or something...

[BitBanger]

echo $col_data[0] . '<A HREF="...

yes, this is the part that is repeated. I only have two messages in the database right now, and what this script is doing is displaying them both, and then displaying both of them right after the first pair.

If you had 10 messages, then the data would be repeated 10 times.

What you are doing with the original code is getting a list of messages for a user, then for each message, listing all the messages. So for N messages you will get N² messages displayed.

[BitBanger]

Having not done pattern matching myself, I'm going by MySql's manual. Here is a sample of what should work:

$search_term = <word or string you are searching for>; 
$query = "SELECT * from messages WHERE `body` REGEXP {$search_term}"; 

[coopster]

The LIKE clause is one of the MySQL String Comparison Functions [mysql.com]

SELECT * FROM messages WHERE body LIKE '%mylungsarempty%'; 

[BitBanger]

Yeah, LIKE will probably work faster too.