"New" and "Updated" indicators by timestamps

[stre1026]

Hi Everyone,
I am in the process of making my company's intranet more user friendly. When I started this project, I asked the employees what they would like to see in the intranet. One of those things happened to be display indicators on the homepage of the 6 most recent new or updated articles in the intranet so they don't have to look through the whole intranet to find new or updated information. I thought that was a great idea so I decided to attempt it but I've run into a problem.

In my database, I have two fields, timestamp and mod_timestamp. They are currently in the format of "04/9/06 - 4:43 pm" (which can change as long as the date and time are in there). Here is what I want to do, I want to be able to search the table called "content" for 6 articles that were either added or updated within lets say a week. Here is the code I have so far that works when the content is recently added but will not check for updated articles. I wasn't sure how to do that.

Thanks in advance for any help you can give me. Also, I am open to new ideas on how to approach this.

$resultID = mysql_query("SELECT * FROM content WHERE status = 'Live' ORDER BY contentid DESC LIMIT 0,6", $linkID);

echo '<table width="700" cellspacing="0" cellpadding="0" align="center" style="border:1px solid black">';
echo '<tr>';
print "<td align=\"left\" colspan=\"2\" style=\"border-bottom:1px solid black; padding:7.5px; background-color:#999999\"><font color=\"#000000\" size=\"2\"><strong>What's New @WORK?</strong></font></td>";
echo '</tr>';
while ($row = mysql_fetch_array($resultID)) {
echo '<tr>';
echo '<td style="background-color:#ffffff; padding:2px" width="59" align="center">';
if($row['mod_timestamp']!= "") {
echo '<img src="images/updated.gif"></a>';
}else{
echo '<img src="images/new.gif"></a>';
}
echo '</td>';
echo '<td style="background-color:#ffffff; padding:5px">';
echo '<a href="index.php?contentid='.$row['contentid'].'">';
echo $row['contentheader'];
echo '</a>';
echo '</td>';
echo '</tr>';
}
echo '</table>';

 

[vacunita]

This is more of a DB question than it is PHP, but i'll take a stab at it:

If your timestamp fields are really timestamp fields and not string fields, then checking the last time the article was updated is simply a case of

SELECT *FROM content where mod_timestamp < todaystimestamp and modtimestamp > specifieddate.

Most DB's have specific timestamp data types nd fucntions for such comparisons, bt since you haven't told us what DB you are using there is not much more we can say.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[stre1026]

Thanks for your reply, vacunita...

I wasn't sure if this would be a DB question or PHP question. I am using MySQL 4 and PHP 5.

The code you supplied didn't work, but I think is because I am not using timestamps in MySQL they are just strings. Right now, I am just storing the date made by PHP (date("m/j/y - g:i a")) as a string in the database. I will trying setting the DB to timestamps.

 

[vacunita]

I strongly recommend using either the mysql timestmp or a unix timestamp so that the comparisons work as expected. Also Mysql and PHP have many date comparing functions.

MYSQL:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

That should give you some help.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ArkM]

Use a proper specific field data type (timestamp) or a char string with date/time in ISO format: YYYY-MM-DDTHH:MM:SS. This date/time text format is suitable for string comparison.

 

[stre1026]

vacunita and ArkM, thank you..

I actually ended up using the strtotime function in PHP and storing that as an integer in the database. In order to display that time in readable format, I just did something like this:

$date = strtotime("now");
$timestamp = date("F j, Y, g:i a", $date);

So that converted the unix timestamp into a human readable timestamp but the unix timestamp is what gets stored in the database. I then do a MySQL select statement like vacunita suggested and voila! I get what I needed....

Thanks for all your help!
Steve