MySQL/PHP processing a query help

Question

Ok, I have two tables that I am joining and doing a select on to get three fields back (timestamp, message, and articleNum). Now, this data is basically a log of when an article was created and modified, I have to write the logic to process those three fields I got back. In order to figure out when an article was created and/or modified last, I need to look at the message and look for the keywords ("added" for created and "updated" for modified). I have the results of the query back in an assoc array and I would eventually like to have the end result be in an assoc array with the articleNum being the key and a two value array (created, modified) being the key. Sometimes though, there won't always be a modified value, but there will always be a created. Any idea on how I would even start a problem like this?

EDIT From what I can tell, it looks like the date is stored as a bigint in Unix seconds. Clarification: the created and modified values are not fields from the table, I need to figure it out from the message field. There will always be one added time but sometimes there could be 0 or more updated messages and I would need to figure out the latest.

EDIT 2 Ok, sorry about the wording of the question. After looking at the problem a little longer I realized I could do this all in two SQL statements. For finding the added date I used:

"SELECT MIN(action_logs.time_added), article.number
 FROM action_logs
 JOIN proposal ON action_logs.article_num = article.number
 WHERE action_logs.message LIKE '%added%'
 GROUP BY article.number"

Could probably do the same thing for last modified, except with a MAX. Thanks for the suggestions though.

Answer 1

Not sure I understand the question..

You want to look in message and extract Created Time and Modified Time?

If that's the case, what is the date/time format? It should be very easy to find that information using a regular expression.

Ian

Edit -

If you want to extract them from the message that you get out of the query, then regular expressions are a likely candidate for completing this task.

Post a sample message.

Answer 2

Try something like this:

$articles = array();
while ($row = mysql_fetch_assoc($result)) {
    $articles[$row['articleId']] = array(
        'created'  => $row['created'],
        'modified' => $row['modified']
    );
}

Answer 3

I'm making some assumptions here:

If you're table stores a row for each of your messages (added/updated), then this query would return one row per article, with the columns you need (articleNum, Created, Updated):

SELECT 
  A.articleNum, 
  MCreated.Timestamp AS Created, 
  MUpdated.Timestamp Updated
FROM Articles A
JOIN Messages MCreated 
  ON MCreated.articleNum = A.articleNum
  AND MCreated.Message = 'added'
LEFT JOIN Messages MUpdated
  ON MUpdated.articleNum = A.articleNum
  AND MUpdated.Message = 'updated'

Answer 4

This is a poorly worded question. It smells a bit of this

What are the two tables? What is the message field? Presumably events?

Why don't you just have 'created' and 'updated' in the articles tables?

Are you sure your design is sound?