[Resolved] Returning "Last Date Used" information

Mr_Mako

Hi...

I have a site where I need to have the last date a user entered data returned on the user's information page. I tried using the MAX command to get this in the following format (note that I have the "lastdate" being entered into the db in the format "Jun 04 2003"):

while ($row = mysql_fetch_array($result)) {
  $callsign = $row['callsign'];
  $ahref = "<a href=pilotscbdmissions.php?callsign=$callsign>$callsign</a>";
  $aa = $row['SUM(aa_kills)'];
  $ag = $row['SUM(ag_kills)'];
  $av = $row['SUM(av_kills)'];
  $an = $row['SUM(an_kills)'];
  $ft = $row['SUM(flight_time)'];
  $date = $row['MAX(mission_date)'];

  $display_block_score .= "
    <tr>
      <td align=\"left\"><font size=2><b>$ahref</b></font></td>
      <td align=\"center\"><font size=2>$aa</font></td>
      <td align=\"center\"><font size=2>$ag</font></td>
      <td align=\"center\"><font size=2>$av</font></td>
      <td align=\"center\"><font size=2>$an</font></td>
      <td align=\"center\"><font size=2>$ft</font></td>
      <td align=\"center\"><font size=2>$date</font></td>
    </tr>";
}

Today, I was looking at the table and noticed that the last bit of data I entered for myself (which would have been yesterday, Aug 24 2003) wasn't showing up on my user information page. I checked the db itself and my lastdate data was there.

After checking on PHP.Net for more information on the MAX command... I realized my problem. 🙂

Apparently, the MAX is formatting the result output according to the alphebetized month name rather than the date like I had originally thought. So, with August being an "A", none of the August entries are showing up.

So... is there a way to keep my current format more or less intact, or with as few changes as possible, and have my lastdate show up? Or do I need to format my date in another way?

Thanks for any advice...

barand

The best way to store dates is in a DATE field.

hth

Pig

The best way to keep your date flexible and have absolute control over the format at any time is to store it as a unix timestamp, and use date() to format it as needed.

Mr_Mako

Ok... I figured that was probably the case. I'll have to go back and edit all the entries already made to the unix timestamp... but better now than later.

Thanks...

barand

Originally posted by Pig
The best way to keep your date flexible and have absolute control over the format at any time is to store it as a unix timestamp, and use date() to format it as needed.

That is if you don't mind the date/time changing to the current date/time evry time you update the record.

Pig

WTF are you talking about? Why would you do that? Maybe I'm missing something, but that makes no sense at all.

What makes you think that using a unix timestamp format forces you to use the current date?

Pig

Mr Mako, check out strtotime(). You can write a very simple script to go through the entire table and convert it to unix timestamp.

$result = mysql_query("SELECT id, time FROM table");
while( $row = mysql_fetch_array($result) )
{ mysql_query("UPDATE table SET time='" . strtotime($row[time]) . "' WHERE id='$row[id]'"); }

barand

Pig,
From MySql Manual

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.

The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)

You explicitly set the TIMESTAMP column to NULL.

hth

Pig

I never use a timestamp column for timestamps. I always use varchar or tinytext, both of which do not have those issues. I will agree that the column format is bad (obviously since I don't use it), but the date format is superior.