5 replies to this topic

[Clinton]

First, the idea: I want to post announcements for all users and then allow them to "clear" them off of their announcement list.

I have two tables.

Table 1 (announce) holds announcements. It has the fields of id, date, and info.

Table 2 (ackannounce) holds the fact that a user has "cleared/acknowledged" an announcement and contains the fields aid (announcement ID) and uid (user ID).

Essentially I want the code to check the table 'ackannounce' and then display whatever announcements have NOT been "cleared/acknowledged" by the user.

The code below, however, is not accomplishing that (all the variables are fine and populate appropriately).

Any help in demistifying this for me would be greatly appreciated.

<?
$sql="SELECT * FROM ackannounce WHERE uid='$uid'";
$result=mysql_query($sql); ?>
<p>
<center>
<font color="green">
<table width="85%" border="3" cellspacing="0" cellpadding="0">
<?
while($row = mysql_fetch_array($result))
{
$aid = $row['aid'];
$sql2="SELECT * FROM announce WHERE NOT id = '$aid'";
$result2=mysql_query($sql2);
while($row = mysql_fetch_array($result2))
{
$id = $row['id'];
$info = $row['info'];
?>
<tr><td width="20%">Date: <? echo date("m-d-Y",strtotime($row['date']));?> </td><td><br><? echo $info; ?><br>&nbsp;<br><a href="ackannounce.php?aid=<?echo $id;?>&uid=<?echo $uid; ?>">Acknowledge/Clear</a><br>&nbsp;</td></tr>
<? }} ?>
</table>
</font>
</center>
</p>

[Muddy_Funster]

Don't run queries within loops a single query is all you need

$sql = "SELECT id, date, info FROM announce WHERE id not in (SELECT aid from ackannounce WHERE uid = '$uid')"

[Jessica]

Muddy, that's not a single query.

[Muddy_Funster]

Muddy, that's not a single query.

now your just being pedantic I only ment that a single query string should be run against the database, yeas it's two queries, or rather one query and a sub query, but I know you know that I know you know what I ment

[Clinton]

Man, that's a lot of 'knows'! :-)

Awesome, it worked. Thanks, Muddy.

[Muddy_Funster]

No problem, glad to help.