Nested loops and select queries error

[brownfox]

OK Two tables, one with username and status ("new" or "old") another table (for PM's) with username,sendername and `read` ("yes" or "no"). I want to echo all usernames that are "old" from table1 and next to their names echo the sendername of all "old" messages from table2. Here's my code:

$query="SELECT username FROM `table1` WHERE status='old' order by username;
$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);
echo"<table border=\"1\" width=\"%50%\">";
$i=0;
$i1=0;
while ($i<$num){
$username=mysql_result($result,$i,"handle");
echo"<tr><td>$username,</td><td>";
$query1="SELECT sendername FROM `table2` WHERE username='$username' and `read`='0'";
$result1=mysql_query($query1) or die (mysql_error());
$num1=mysql_numrows($result1);
if($num1>0){
while ($i1<$num1){
$sendername=mysql_result($result1,$i1,"sendername");
echo"$sendername,";
++$i1;
}
}else{
echo"&nbsp;";
}
echo"</td></tr>";
++$i;
}
echo"</table>";

The above code is not listing all the people who's status is "old" neither is it listing all the unread PM's. Any ideas?
Thanks in advance

 

[RobBroekhuis]

You're much better off using mysql_fetch_row:

$query="SELECT username FROM `table1` WHERE status='old' order by username;
$result=mysql_query($query) or die (mysql_error());
echo"<table border=\"1\" width=\"%50%\">";
$i1=0;
while ($name=mysql_fetch_row($result){
$username=$name[0]mysql_result($result,$i,"handle");
echo"<tr><td>$username,</td><td>";
$query1="SELECT sendername FROM `table2` WHERE username='$username' and `read`='0'";
$result1=mysql_query($query1) or die (mysql_error());
do while ($sname=mysql_fetch_row($result1) {
echo "{$sname[0]},";
}
}
echo"&nbsp;";
}
echo"</td></tr>";
}
echo"</table>";


Also, I'm not sure what the back-quotes around `read` mean, but that may be my MySQL ignorance...


Rob

 

[brownfox]

Read" is a reserved word in mySQL and, therefore, has to be escaped thus: `read`
Regarding your code, I'm getting a few errors due to missing parenthesis. I have edited it:
$query="SELECT username FROM `table1` WHERE status='old' order by username;
$result=mysql_query($query) or die (mysql_error());
echo"<table border=\"1\" width=\"%50%\">";
$i=0;
while ($name=mysql_fetch_row($result)){
$username=$name[0]mysql_result($result,$i,"handle");
echo"<tr><td>$username,</td><td>";
$query1="SELECT sendername FROM `table2` WHERE username='$username' and `read`='0'";
$result1=mysql_query($query1) or die (mysql_error());
do while ($sname=mysql_fetch_row($result1)) {
echo "{$sname[0]},";
}
echo"</td></tr>";
}
echo"</table>";

But I'm still getting a "parse error, unexpected T_ECHO, expecting T_WHILE " at this line: echo"</td></tr>";

 

[brownfox]

Got it! Thanx you RobBroekhuis:

$query="SELECT username FROM `table1` WHERE status='old' order by username;
$result=mysql_query($query) or die (mysql_error());
echo"<table border=\"1\" width=\"%50%\">";
$i=0;
while ($name=mysql_fetch_row($result)){
$username=$mysql_result($result,$i,"handle");
echo"<tr><td>$username,</td><td>";
$query1="SELECT sendername FROM `table2` WHERE username='$username' and `read`='0'";
if(mysql_fetch_row($result1)>0){
do {
echo "{,$sname[0]},";
}while ($sname=mysql_fetch_row($result1));
}echo"</td></tr>";
++$i;
}
echo"</table>";

 

[brownfox]

I mean: echo ",{$sname[0]}";