MySQL Looping Problem

[woldie]

Hi there,

I've got a wee problem with my looping where I get the data from the DB.

Now the problem I am having is that I am comparing two ID's within the WHILE loop (see the comments in CAPS), but instead of using the WHILE statement which I know won't work I have used the LIST command, but in some cases there might more than one item in the DB.

So at the moment it works if there's only one item so it checks on box only, but it won't do if there's more than one. Now I'm not quite sure whether I can call the query again, within the loop?

Thanks

Woldie.

<table border="1" cellpadding="3" cellspacing="3">
<?
$result=mysql_query("select ncid,news_catname
from tbl_news_categories");
while (list($DBncid,$DBnews_catname)=mysql_fetch_row($result))
{
echo '<tr><td colspan=4>'.'<strong>'.$DBnews_catname.'</strong>'.'</td></tr>';

$result_1=mysql_query("select kwid,keyword,url
from tbl_keywords
where ncid=$DBncid");
while (list($DBkwid,$DBkeyword,$DBurl)=mysql_fetch_row($result_1))
{

// THIS IS THE LOOP WHERE I AM HAVING THE PROBLEMS
// THIS SHOULD DISPLAY CHECK MORE THAN ONE ITEM IN DB

$res=mysql_query("select tbl_keywords.kwid
from tbl_keywords,tbl_keywords_map,tbl_news
where tbl_keywords.kwid=tbl_keywords_map.kwid
and tbl_keywords_map.nwsid=tbl_news.nwsid
and tbl_keywords_map.nwsid=$nwsid");
list($DBkwid_2)=mysql_fetch_row($res);
//{
?>
<tr>
<td><img src="../_images/spacer.gif" width="20" height="1" alt="" /></td>
<td><? echo $DBkeyword?></td>
<td><? echo $DBurl?></td>
<?
if ($DBkwid==$DBkwid_2)
{
?>
<td><input type="checkbox" name="kwids[]" value=<? echo $DBkwid?> checked></td>
<?
}
else
{
?>
<td><input type="checkbox" name="kwids[]" value=<? echo $DBkwid?>></td>
<?
}
?>
</tr>
<?
#}
}
}

?>
</table>

[omoutop]

according to php manual,
list() only works on numerical arrays and assumes the numerical indices start at 0.

So i guess u have to use the while() statement.
Could be wrong though... perhpas there is a "list" way but cant think of one.

[woldie]

Thanks omoutop,

I have tried using the WHILE command, but this displays each row from the DB twice.

I'm nearly there with it, as you can see, I'm using 3 while loops. Maybe I need to look at my DB structure?

Thanks all the same.

Woldie.

[omoutop]

try this code, i have alter some parts (but not quite test it - i think it works anyway)

<table border="1" cellpadding="3" cellspacing="3"> 
<? 
$result=mysql_query("select ncid,news_catname from tbl_news_categories"); 
while($row = mysql_fetch_row($result))
{
$DBncid = $row['ncid'];
DBnews_catname = $row['news_catname'];
echo '<tr><td colspan=4>'.'<strong>'.$DBnews_catname.'</strong>'.'</td></tr>'; 
$result_1=mysql_query("select kwid,keyword,url from tbl_keywords where ncid=$DBncid"); 
while ($row2 = mysql_fetch_row($result_1)) 
{ 
$DBkwid = $row2['kwid'];
$DBkeyword = $row2['keyword'];
$DBurl = $row['url'];
$res=mysql_query("
select 
tbl_keywords.kwid 
from 
tbl_keywords a,
tbl_keywords_map b,
tbl_news c 
where 
a.kwid=b.kwid and 
b.nwsid=a.nwsid and 
b.nwsid='$nwsid'
"); 
while ($row3 = mysql_fetch_row($res)) 
{ 
$DBkwid_2 = $row3['kwid'];
?> 
<tr> 
<td><img src="../_images/spacer.gif" width="20" height="1" alt="" /></td> 
<td><? echo $DBkeyword?></td> 
<td><? echo $DBurl?></td> 
<td><input type="checkbox" name="kwids[]" value=<? echo $DBkwid?> <? if ($DBkwid==$DBkwid_2) { echo " checked"; }?> ></td> 
</tr> 
<? 
} 
} 
} 
?> 
</table>

[woldie]

Hi,

I've given that go, but I am getting an error, its quite trivial, but I'll get back to you at some stage, just snowed under at the moment!

Watch this space!

Thanks

Woldie.

[Ivun]

Just try using mysql_fetch_assoc() instead of mysql_fetch_row(). List() won't be needed then

[woldie]

Thanks Ivun,

I'll give that a go.

I presuming that I should use that here:

$res=mysql_query("select tbl_keywords.kwid from tbl_keywords,tbl_keywords_map,tbl_news
where tbl_keywords.kwid=tbl_keywords_map.kwid
and tbl_keywords_map.nwsid=tbl_news.nwsid
and tbl_keywords_map.nwsid=$nwsid");
while (list($DBkwid_2)=mysql_fetch_row($res))