Comparing values in first While loop with values in second While loop

[Andrew777]

I'm new here and would really appreciate some help with this. Been racking my brain but just don't know how to do it.

 

I have 2 While Loops on a page, basically the inbox page on a message system for a website......

The First While Loop:

//First Query

$sqlm="SELECT * FROM msgs WHERE to_id='$id' AND newflag='1' ORDER BY newflag DESC, auto_id DESC ";

$rsm=mysql_query($sqlm);

while($rowm=mysql_fetch_array($rsm))

//Shows the rows of messages sent to the user that are new (newflag set to 1).

//So if there are no new messages, then this loop won't show anything.

 

The Second While Loop:

//Second Query

$sql="SELECT * FROM msgs WHERE to_id='$id' group by msg_id ORDER BY auto_id DESC ";

$rs=mysql_query($sql);

while($row=mysql_fetch_array($rs))

//Shows the rows of messages that have been read by the user and don't have any new messages.

 

 

The First Loop works fine.

BUT in the Second Loop, I don't want to show any messages with the same msg_id that are used in the first loop.

 

Basically what I'm getting is...

First Loop shows new messages (grouped by msg_id):

111222 (msg_id)    Hi, How Are you? (3)      <---New Message exists in this Conversation Thread.

 

Second Loop shows read messages (grouped by msg_id):

111222 (msg_id)    Hi, How Are you? (3)      <----Don't want to show this Conversation but since it has the same msg_id it shows up in this loop

567567 (msg_id)    Welcome to the site (5)  <----Older message Conversation

 

So how do I tell mysql/php to filter out any messages with the same msg_id that was used in the first loop, in order to hide those conversations in the second loop?? As the second loop runs though the rows in the db table, to output them in the loop, how do you compare the msg_id with ALL msg_ids used in the first loop?

Is this possible?

 

Any help would be greatly appreciated!

[mikesta707]

There are a couple ways to accomplish this. The easiest way I can think of (but perhaps not the most efficient) would be to keep an array of all the message ids from the first loop, and then during the second loop before you output, check to make sure the msg_id isnt in the array you compiled in the first loop. So for example, using your queries

$msg_ids = array();//empty array to keep track of msg_ids
$sqlm="SELECT * FROM msgs WHERE to_id='$id' AND newflag='1' ORDER BY newflag DESC, auto_id DESC ";
$rsm=mysql_query($sqlm);
while($rowm=mysql_fetch_array($rsm)){
//Shows the rows of messages sent to the user that are new (newflag set to 1).
//also keep track of the ids
$msg_ids[] = $rowm['msg_id'];//replace msg_id with whatever the column name is
}


//second loop:
$sql="SELECT * FROM msgs WHERE to_id='$id' group by msg_id ORDER BY auto_id DESC ";
$rs=mysql_query($sql);
while($row=mysql_fetch_array($rs)){
//Shows the rows of messages that have been read by the user and don't have any new messages.
//however, we need to make sure that the msg_id hasnt been shown above from first loop. 
// a simple if statement will suffice
$id = $row['msg_id'];//replace msg_id with whatever the column name is
if (!in_array($id, $msg_ids)){
//now we know that it msg_id isn't one shown above
//however you handle output can safely be run here
}
}//end while

 

 

 

A more efficient way would also include keeping track of previously gotten msg_ids with the array like we did with the first loop, but in the second loop, instead of grabbing all the rows and filtering via in_array(), you could use the array to build a WHERE clause in the query to filter. This would result in less rows being grabbed by your query and less iterations of the second while loop

 

[Andrew777]

Hi Mikesta707,

 

I tried your suggestion as per the code you added and it worked beautifully!!!

Thank you very, very much!

 

I don't know the syntax of the second more efficient way to do it, by building the msg_id check into the WHERE clause...

so I guess I will forgo that option right now, but the first one works fine. I don't think this application will be super intensive, so it

shouldn't really matter if the first method (if (!in_array($id, $msg_ids)))  is not the most efficient way to do it.

 

Thank you again!

A777