"New" and "Updated" indicators by timestamps

"New" and "Updated" indicators by date added timestamp and modified timesta

[macman1]

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>';

[coopster]

Do you really need two separate TIMESTAMP columns? Also, I would encourage anybody that is storing date/time data to create the column in a format that allows you to use SQL date and time functions against it. In this case the DATETIME type.

Then all you would have to do is query your database by DESC date to get a LIMIT of 6.

[macman1]

Hi Coopster...

The reason I have the two TIMESTAMP colums is because I have to be able to tell when the article was originally added and when it was modified. That was the only way I could think of to accomplish that. I am open to ideas on a new solution for that. Also, I am now using php's STRTOTIME function which I understand can be used in the way you are describing. So in the TIMESTAMP and the MOD_TIMESTAMP columns, the date is something like 1109123123 and then I use date('n/j/Y' g:i:sa', $date) (where date is the value stored in the database) to turn it into something readable.

Thanks!
Steve

[coopster]

because I have to be able to tell when the article was originally added and when it was modified

That's what I figured.

That was the only way I could think of to accomplish that. I am open to ideas on a new solution for that.

That's how I would do it.

As far as how you choose to store the data, that is up to you. Personally I don't store timestamps in unix_timestamp form. I prefer an ISO format yyyy-mm-dd hh:mm:ss. Most of the work you want to do with date/time values you can do long before it even gets to your application, meaning you can calculate, manipulate and format date/time values much more readily than you can unix_timestamp values with the database server as opposed to having to perform the same tasks within PHP. Have a look through the MySQL Date and Time Functions [dev.mysql.com] and you will see what I am talking about.

[hakre]

i'd like to add that with mysql (and other dbs) you have got a date-time built in which is automatically updated to the servers date and time when the record is created and updated. the type is called TIMESTAMP (so it's not really of type DATETIME but quite the same) ;) .

so the only thing you need to take care about is to fill the creation column once the article is created.

secondly, date and time values are stored as such in the database, not as the format you choose for accessing them. unless you store them as something else like date/time, for example mediumint or varchar, you can format, calculate and compare them in different datetime formats like ISO or others.

in my own opinion i prefer UNIXTIME to have a more logical approach to such a thing like date and time.

[coopster]

>> in my own opinion i prefer UNIXTIME to have a more logical approach to such a thing like date and time.

Sounds good until you try to use dates prior to epoch. Then you get zero (0) every time.

mysql> SELECT UNIX_TIMESTAMP('1969-01-01') AS t; 
+---+ 
¦ t ¦ 
+---+ 
¦ 0 ¦ 
+---+

Same with TIMESTAMP in MySQL (try to INSERT the same '1969-01-01' date into a column defined as type TIMESTAMP in MySQL and you'll see what I mean, zero value). If you need to store an actual DATE or DATE and TIME I think you'll find DATE and DATETIME much more accommodating without any surprises.