fifeclub — Mon, 16 Jun 2003 17:17:30 +0000

:jump: :jump:

I made one slight adjustment to this line by removing the question mark from between the two single-quotation marks.

<?php
if ($row['days_stale'] == '')      $stalerating = \"?\";
?>

It works perfectly! A thing of beauty. Plus I learned new stuff along the way! Thank you so very much!

Mark Hensler — Mon, 16 Jun 2003 16:50:44 +0000

<?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\";
    
?>

fifeclub — Mon, 16 Jun 2003 16:12:18 +0000

That worked (link) Thanks! There are just two problems but the script is working so it's just a matter of adjusting. One is just that I have to choose different numbers to judge ratings by because nobody got a rating under 7. The script worked and that 7 is correct. I just need to adjust it so that amount of days gets a lower score.

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 — Mon, 16 Jun 2003 06:27:09 +0000

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>\"; }
?>

Give that a try. Let me know how it goes.

(eliminated a query too )

Mark Hensler — Mon, 16 Jun 2003 06:22:59 +0000

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

'Keep asking questions untill the problem is solved. That's what we're here for.

fifeclub — Mon, 16 Jun 2003 01:33:08 +0000

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 — Sun, 15 Jun 2003 04:50:24 +0000

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

necrotic — Sun, 15 Jun 2003 04:39:45 +0000

Quote: Originally posted by fifeclub
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)

Yes.

fifeclub — Sun, 15 Jun 2003 01:35:52 +0000

I feel that I may be getting closer, but I'm getting

Quote: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/pstvalu/public_html/directory/name3.php on line 21

and all records show "10NEW". Line 21 in my page is the line....

<?php
list($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)

Mark Hensler — Sat, 14 Jun 2003 19:30:47 +0000

<?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\";
?>