Reporting new activity

[elmas156]

Hello everyone,

 

I have a messaging site where I have an email generated every night to report new activity on my site.  The way it is currently set up, I have each message reply attached to the previous message, such as quoting the original text in an email message.  Each string of messages in my database has one common field ("string"), but each new reply has a different message ID field in the database ("messid").  When each new message and reply is created the "reported" field in my database has a default value of n, meaning that it has not been reported yet.  At the end of the day, when the email is generated, the reported field is then updated to y, meaning that it HAS been reported.  The problem that I'm having now, is that I'm getting several of the same messages in every report.

 

I am using the following code to select each unreported message from the database to generate my report:

 

<?php
$result = mysql_query("SELECT `to`,`studentname`,`staffname`,`subject`,`message`,`date` FROM caresa6_$acct.allmsgs WHERE reported = 'n' ORDER BY `messid` ASC") or die (mysql_error());
?>

 

Basically, I need to know how to select only the most recent unreported message within each string of messages.

 

For example, now if there is a new message:

 

"Hello there."

 

Then a reply from the recipient:

 

"Hi! How are you?"

 

Then another reply from the original sender:

 

"I am fine."

 

My report will look like this:

 

___________

New Message:

 

"Hello there."

------------------

Reply: "Hi! How are you?"

------------------

Reply: "I am fine."

------------------

 

___________

New Message:

 

"Hello there."

------------------

Reply: "Hi! How are you?"

------------------

 

___________

New Message:

 

"Hello there."

------------------

 

So I am getting carbon copies of every new message every time someone replies.

 

I want to only select the most recent message so that my report only reads:

 

___________

New Message:

 

"Hello there."

------------------

Reply: "Hi! How are you?"

------------------

Reply: "I am fine."

------------------

 

Thanks in advance.

 

[RussellReal]

Hello, I'd love to help you, can you show us a screenshot or dump of your database  when filtering by a specific message string..

 

like do this query

 

SELECT * FROM caresa6_$acct.allmsgs WHERE reported = 'n' AND string = 'abcUNIQUE123'

 

also I think your issue doesn't lie in MySQL, its more-so in the rest of your code, so it could be good if you showed us that aswell..

 

Thanks!

[grissom]

Just a suggestion but you could add an extra field in your mysql database called something like "thread_id"

 

When an initial message is sent, the PHP code generates a brand new thread_id. If a message is replied to, then the PHP is set up to capture the thread_id and to re-post the reply with the same thread_id

 

Then when you do the retrieval you can sort the replies out by thread_id

 

Just a suggestion off the top of my head, there may be other better ways  !!

[elmas156]

Thanks for your replies.  Grissom, I do have a field in the database called "stringid" that does exactly what you suggest.  Every message in a string of messages has the same "stringid."  Basically, I just need to know how to select the most recent entry for each "stringid" in the database.