time since recorded timestamp
I humbly ask for your assistance.
I've got a field in my mySQL database for when the record was last updated. It is of the type "timestamp(8)". Now what I'd really like to do is display a rating according to how old the record is (and hence how reliable it may be) by somehow figuring out the difference in time between that "last updated" timestamp( and today's date. Something to the effect of "if the difference between today's date and the last time this record was updated is between 30 and 120 days then echo '4_star_rating.gif'" (by now you can figure out that I don't really know too much about this stuff.) Can somebody help me out of at least tell me what it is I have to learn. Thanks a bunch!
P.S. My site is in PHP.
Mark Hensler posted this at 05:59 — 13th June 2003.
He has: 4,048 posts
Joined: Aug 2000
query:
SELECT (TO_DAYS(NOW())-TO_DAYS(FOM_UNIXTIME(last_updated))) AS days_stale FROM table_name
php:
if ($days_stale>=30 && $days_stale<=120) {
echo '4_star_rating.gif';
}
Mark Hensler
If there is no answer on Google, then there is no question.
fifeclub posted this at 14:15 — 13th June 2003.
He has: 688 posts
Joined: Feb 2001
You da man! Thanks! That's awesome, and it looks pretty simple in how it functions (although I'm just guessing because half those terms are new to me). I'll try to work the code into use this weekend. Thanks again!
:jump:
fifeclub posted this at 03:38 — 14th June 2003.
He has: 688 posts
Joined: Feb 2001
Well I proved something tonight: I know even less than I thought I did.
Here's my original query
<?php
$per_page = 25;
$limit = $page * $per_page;
$query = \"SELECT * FROM alumni WHERE lastname RLIKE '^[$lastname]' ORDER BY lastname LIMIT $limit,$per_page\";
$result = mysql_query ($query);
if (@mysql_num_rows($result))
{
print \"<table .....
?>
Since I couldn't figure out how to add the query you wrote into my existing query, I added a second query underneath it, and then added the "if" parts.
<?php
$per_page = 25;
$limit = $page * $per_page;
$query = \"SELECT * FROM alumni WHERE lastname RLIKE '^[$lastname]' ORDER BY lastname LIMIT $limit,$per_page\";
$result = mysql_query ($query);
$stalequery = \"SELECT (TO_DAYS(NOW())-TO_DAYS(FOM_UNIXTIME(lastupdated))) AS days_stale FROM alumni\";
$staleresult = mysql_query ($stalequery);
if ($days_stale>=0 && $days_stale<=29) {
$stalerating = \"10NEW\";}
if ($days_stale>=30 && $days_stale<=179) {
$stalerating = \"10\";}
if ($days_stale>=180 && $days_stale<=359) {
$stalerating = \"9\";}
if ($days_stale>=360 && $days_stale<=539) {
$stalerating = \"8\";}
if ($days_stale>=540 && $days_stale<=719) {
$stalerating = \"7\";}
if ($days_stale>=720 && $days_stale<=899) {
$stalerating = \"6\";}
if ($days_stale>=900 && $days_stale<=1079) {
$stalerating = \"5\";}
if ($days_stale>=1080 && $days_stale<=1259) {
$stalerating = \"4\";}
if ($days_stale>=1260 && $days_stale<=1439) {
$stalerating = \"3\";}
if ($days_stale>=1440 && $days_stale<=1619) {
$stalerating = \"2\";}
if ($days_stale>=1619) {
$stalerating = \"1\";}
else $stalerating = \"zero or not working\";
if (@mysql_num_rows($result))
{
print \"<table .....
?>
Here's a link to the page I'm testing and as you can see, I'm not necessarily getting any error messages but it's only picking up the "else" part. I'm sure "lastupdated" is the name of my timestamp(
field and "alumni" is the name of the table. Otherwise I'm lost on where I went wrong. Do you see where I screwed things up?
Thanks.
necrotic posted this at 14:13 — 14th June 2003.
He has: 296 posts
Joined: May 2002
Nevermind...
Mark Hensler posted this at 19:30 — 14th June 2003.
He has: 4,048 posts
Joined: Aug 2000
<?php
$stalequery = \"SELECT (TO_DAYS(NOW())-TO_DAYS(FOM_UNIXTIME(lastupdated))) AS days_stale FROM alumni\";
list($days_stale) = mysql_fetch_row(mysql_query($stalequery));
if ($days_stale < 0) $stalerating = \"error: <0\";
elseif ($days_stale < 30) $stalerating = \"10NEW\";
elseif ($days_stale < 180) $stalerating = \"10\";
elseif ($days_stale < 360) $stalerating = \"9\";
elseif ($days_stale < 540) $stalerating = \"8\";
elseif ($days_stale < 720) $stalerating = \"7\";
elseif ($days_stale < 900) $stalerating = \"6\";
elseif ($days_stale < 1080) $stalerating = \"5\";
elseif ($days_stale < 1260) $stalerating = \"4\";
elseif ($days_stale < 1440) $stalerating = \"3\";
elseif ($days_stale < 1620) $stalerating = \"2\";
else $stalerating = \"1\";
?>
Mark Hensler
If there is no answer on Google, then there is no question.
fifeclub posted this at 01:35 — 15th June 2003.
He has: 688 posts
Joined: Feb 2001
I feel that I may be getting closer, but I'm getting
and all records show "10NEW". Line 21 in my page is the line....
<?phplist($days_stale) = mysql_fetch_row(mysql_query($stalequery));
?>
I left the last attempt link in tact and put this attempt here.
P.S. Looking over my first attempt I now see that I never actually did anything with "$staleresult". Oh well, I guess I'm past that attempt now.
P.P.S. I don't know much about this but is there a possible incompatibility with the query looking for a Unix Time Stamp (seconds since 1/1/1970) and my mySQL timestamp(
(which formats like this 20030614)
necrotic posted this at 04:39 — 15th June 2003.
He has: 296 posts
Joined: May 2002
Yes.
[James Logsdon]
Mark Hensler posted this at 04:50 — 15th June 2003.
He has: 4,048 posts
Joined: Aug 2000
Oh, I'm sorry. I forgot how timestamp formatted.
Try altering the query to match this:
SELECT (TO_DAYS(NOW())-TO_DAYS(lastupdated)) AS days_stale FROM alumni
Mark Hensler
If there is no answer on Google, then there is no question.
fifeclub posted this at 01:33 — 16th June 2003.
He has: 688 posts
Joined: Feb 2001
I'm so sorry. The last thing I want to do is keep bugging you when you've been going out of your way to help me, but it still didn't work. Now (click here) it gives "7" as the result for every row. This differs from other tries in that this is the first time is doesn't pick either the first of last possibility, but a choice in the middle. Anyway, I included all the relevant php code below in order to try one last attempt(?)
<?php
$db = mysql_connect(\"localhost\", \"XXXXXXXX\", \"XXXXXXXXX\");
mysql_select_db(\"pstvalu_directory\",$db);
if(!isset($page)) { $page = 0; }
$per_page = 25;
$limit = $page * $per_page;
$query = \"SELECT * FROM alumni WHERE lastname RLIKE '^[$lastname]' ORDER BY lastname LIMIT $limit,$per_page\";
$result = mysql_query ($query);
$stalequery = \"SELECT (TO_DAYS(NOW())-TO_DAYS(lastupdated)) AS days_stale FROM alumni\";
list($days_stale) = mysql_fetch_row(mysql_query($stalequery));
if ($days_stale < 0) $stalerating = \"error: <0\";
elseif ($days_stale < 30) $stalerating = \"10NEW\";
elseif ($days_stale < 180) $stalerating = \"10\";
elseif ($days_stale < 360) $stalerating = \"9\";
elseif ($days_stale < 540) $stalerating = \"8\";
elseif ($days_stale < 720) $stalerating = \"7\";
elseif ($days_stale < 900) $stalerating = \"6\";
elseif ($days_stale < 1080) $stalerating = \"5\";
elseif ($days_stale < 1260) $stalerating = \"4\";
elseif ($days_stale < 1440) $stalerating = \"3\";
elseif ($days_stale < 1620) $stalerating = \"2\";
else $stalerating = \"1\";
if (@mysql_num_rows($result))
{
print \"<table border=2 bordercolor=1 cellpadding=3 cellspacing=0>\n\";
print \"<tr>
<td bgcolor=#990000><font color=#ffffff><b><u>Name</u></b></font></td>
<td bgcolor=#990000><font color=#ffffff><b><u>Class</u></b></font></td>
<td bgcolor=#990000><font color=#ffffff><b><u>Location</u></b></font></td>
<td bgcolor=#990000><font color=#ffffff><b><u>(rating)</u></b></font></td>
</tr>\n\";
while($row = mysql_fetch_array($result)) {
print \"<tr>\n\";
print \"<td><a href=\\"info.php?id=\".$row['id'].\"\\">\".$row['lastname'];
if (!empty($row['maidenname'])) print \" (\".$row['maidenname'].\")\n\";
print \",_\".$row['firstname'].\"</a></td>\n\";
print \"<td>\".$row['classyear'].\"</td>\n\";
print \"<td>\".$row['city'];
if (!empty($row['city'])) print \",_\n\";
print \"\".$row['state'].\"</td>\n\";
print \"<td>\".$stalerating.\"</td>\n\";
print \"</tr>\n\"; } print \"</table>\n\";
} else { echo \"<p>Sorry, no records were found!</p>\"; }
?>
Mark Hensler posted this at 06:22 — 16th June 2003.
He has: 4,048 posts
Joined: Aug 2000
Is it possible that they all should be 7?
Try adding another column to print the days stale value, then we can see if/where the if/elseif/else is flawed.
<td bgcolor=#990000><font color=#ffffff><b><u>(days stale)</u></b></font></td>
print "<td>".$days_stale."</td>\n";
Mark Hensler
If there is no answer on Google, then there is no question.
Mark Hensler posted this at 06:27 — 16th June 2003.
He has: 4,048 posts
Joined: Aug 2000
Drat. I found my error. Sorry...
<?php
$db = mysql_connect(\"localhost\", \"XXXXXXXX\", \"XXXXXXXXX\");
mysql_select_db(\"pstvalu_directory\",$db);
if(!isset($page)) { $page = 0; }
$per_page = 25;
$limit = $page * $per_page;
$query = \"SELECT *, (TO_DAYS(NOW())-TO_DAYS(lastupdated)) AS days_stale FROM alumni WHERE lastname RLIKE '^[$lastname]' ORDER BY lastname LIMIT $limit,$per_page\";
$result = mysql_query ($query);
if (@mysql_num_rows($result))
{
print \"<table border=2 bordercolor=1 cellpadding=3 cellspacing=0>\n\";
print \"<tr>
<td bgcolor=#990000><font color=#ffffff><b><u>Name</u></b></font></td>
<td bgcolor=#990000><font color=#ffffff><b><u>Class</u></b></font></td>
<td bgcolor=#990000><font color=#ffffff><b><u>Location</u></b></font></td>
<td bgcolor=#990000><font color=#ffffff><b><u>(rating)</u></b></font></td>
</tr>\n\";
while($row = mysql_fetch_array($result)) {
if ($row['days_stale'] < 0) $stalerating = \"error: <0\";
elseif ($row['days_stale'] < 30) $stalerating = \"10NEW\";
elseif ($row['days_stale'] < 180) $stalerating = \"10\";
elseif ($row['days_stale'] < 360) $stalerating = \"9\";
elseif ($row['days_stale'] < 540) $stalerating = \"8\";
elseif ($row['days_stale'] < 720) $stalerating = \"7\";
elseif ($row['days_stale'] < 900) $stalerating = \"6\";
elseif ($row['days_stale'] < 1080) $stalerating = \"5\";
elseif ($row['days_stale'] < 1260) $stalerating = \"4\";
elseif ($row['days_stale'] < 1440) $stalerating = \"3\";
elseif ($row['days_stale'] < 1620) $stalerating = \"2\";
else $stalerating = \"1\";
print \"<tr>\n\";
print \"<td><a href=\\"info.php?id=\".$row['id'].\"\\">\".$row['lastname'];
if (!empty($row['maidenname'])) print \" (\".$row['maidenname'].\")\n\";
print \",_\".$row['firstname'].\"</a></td>\n\";
print \"<td>\".$row['classyear'].\"</td>\n\";
print \"<td>\".$row['city'];
if (!empty($row['city'])) print \",_\n\";
print \"\".$row['state'].\"</td>\n\";
print \"<td>\".$stalerating.\"</td>\n\";
print \"</tr>\n\";
}
print \"</table>\n\";
} else { echo \"<p>Sorry, no records were found!</p>\"; }
?>
(eliminated a query too
)
Mark Hensler
If there is no answer on Google, then there is no question.
fifeclub posted this at 16:12 — 16th June 2003.
He has: 688 posts
Joined: Feb 2001
The final problem is regarding a situation I neglected to mention. From the days before I switched to a mySQL database, some of my records do not have any "lastupdated" field. In the link above you can see they all come out as being new even though they are actually very old. This must be because with the "lastupdated" field blank in the database, it is being interpreted as zero days old (I guess). Anyway, how do you properly code "if no record found / if blank" to be used in the "elseif" area? I know how to code "if field IS there" but not "if field IS NOT there".
(I'm so happy it's working)
Mark Hensler posted this at 16:50 — 16th June 2003.
He has: 4,048 posts
Joined: Aug 2000
<?php
$query = \"SELECT *, if(lastupdated='', '?', (TO_DAYS(NOW())-TO_DAYS(lastupdated))) AS days_stale\"
.\" FROM alumni WHERE lastname RLIKE '^[$lastname]'\"
.\" ORDER BY lastname LIMIT $limit,$per_page\";
$result = mysql_query ($query);
////////////////////////////////////////////////////////////////////////////////
if ($row['days_stale'] == '?') $stalerating = \"?\";
elseif ($row['days_stale'] < 0) $stalerating = \"error: <-1\";
elseif ($row['days_stale'] < 30) $stalerating = \"10NEW\";
elseif ($row['days_stale'] < 180) $stalerating = \"10\";
elseif ($row['days_stale'] < 360) $stalerating = \"9\";
elseif ($row['days_stale'] < 540) $stalerating = \"8\";
elseif ($row['days_stale'] < 720) $stalerating = \"7\";
elseif ($row['days_stale'] < 900) $stalerating = \"6\";
elseif ($row['days_stale'] < 1080) $stalerating = \"5\";
elseif ($row['days_stale'] < 1260) $stalerating = \"4\";
elseif ($row['days_stale'] < 1440) $stalerating = \"3\";
elseif ($row['days_stale'] < 1620) $stalerating = \"2\";
else $stalerating = \"1\";
?>
Mark Hensler
If there is no answer on Google, then there is no question.
fifeclub posted this at 17:17 — 16th June 2003.
He has: 688 posts
Joined: Feb 2001
:jump: :jump:
I made one slight adjustment to this line by removing the question mark from between the two single-quotation marks.
<?phpif ($row['days_stale'] == '') $stalerating = \"?\";
?>
It works perfectly! A thing of beauty. Plus I learned new stuff along the way! Thank you so very much!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.