Timestamp display incorrect

[webguync]

Anyone know why this timestamp would display as December 31, 1969 7:00:00pm

 

In the PHP I have

 

echo "<tr><td class='timestamp'>Login Time: " . date('F j, Y g:i:sa', strtotime($row["login_timestamp"])) . "</td></tr>";

 

and the MySQL timestamp looks like this.

 

2010-06-21 16:17:27

 

the field name is login_timestamp set to type datetime with default of 0000-00-00 00:00:00

[PFMaBiSmAd]

$row["login_timestamp"] is either empty or not defined. Have you checked by echoing it (or use var_dump()) to see what exactly it is?

[webguync]

well there is definitely a timestamp there when I look at the MySQL table through PHPMyAdmin. How do I use var_dump()) ?

[Alex]

If could also contain your default value, 0000-00-00 00:00:00.

 

Use var_dump like this:

 

var_dump($row["login_timestamp"]);

[radar]

I personally find it helpful to do something like this @ the bottom of my index.php file.

 

echo "<pre>SESSION:";
print_r($_SESSION);
echo "<br>DATA:";
print_r($data);
echo "<br>POST:";
print_r($_POST);
echo "</pre>";

 

i just have to make sure that $data is always the result of my mysql_query so that i always have $data being output in the bottom of my page.  Works wonders when debugging.

[webguync]

looks like the mysql field 'login_timestamp' is showing up as NULL, but not sure why since the field is populated in MySQL.(?)

[Alex]

Are you sure that 1. You're looking at the correct row and 2. You've spelled the column name correctly?

[Pikachu2000]

Need to see the code that includes the query string to answer that.

[webguync]

yes, the field name is being spelled correctly. Here is the code (I'll go ahead and display all of it, sorry if it is too much). The field in question is login_timestamp and it is in the Editor_Candidates table.

 

<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
$conn = mysql_connect("localhost","uname","pw");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("DBName")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$sql = "SELECT Responses.editor_name,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10,Answer11,Answer12,Responses.submit_timestamp,Editor_Candidates.login_timestamp,
TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime
FROM Responses
LEFT JOIN Editor_Candidates
USING (user_id) ";
       

$result = mysql_query($sql);



if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}


while ($row = mysql_fetch_assoc($result)) {
   echo "<tr><td class='name'>{$row['editor_name']}</td></tr>";
echo "<tr><td class='section'><strong>Section 1</strong></td></tr>";
   
   for ($i =1;$i<9;++$i) {
echo "<tr><td><p>{$row['Answer'.$i]}</p></td></tr>";
  }
echo "<tr><td class='section'><strong>Section 2</strong></td></tr>";

echo "<tr><td><p>{$row['Answer10']}</p></td></tr>";
echo "<tr><td class='section'><strong>Section 3</strong></td></tr>";

echo "<tr><td><p>{$row['Answer11']}</p></td></tr>";
echo "<tr><td class='section'><strong>Section 4</strong></td></tr>";

echo "<tr><td><p>{$row['Answer12']}</p></td></tr>";
echo "<tr><td class='section'><strong>Time:</strong></td></tr>";
var_dump($row["login_timestamp"]);
echo "<tr><td class='timestamp'>Login Time: " . date('F j, Y g:i:sa', strtotime($row['login_timestamp'])) . "</td></tr>";
echo "<tr><td class='timestamp'>Submit Time: " . date('F j, Y g:i:sa', strtotime($row['submit_timestamp'])) . "</td></tr>";
$formatted_completion_time = formatTime($row['cTime']);
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $formatted_completion_time;
  echo "</td></tr>";


}
mysql_free_result($result);



?>

[radar]

get rid of the var_dump and see if that fixes it for you.

[webguync]

no, I didn't add that till after I knew it wasn't working properly. It didn't have an effect.

[radar]

okay so right before you free result, put in:

 

echo "<pre>";

print_r($row);

echo "</pre>";

 

and use that for debugging..  find where your timestamp is, and make sure that it is showing what it should and that it is infact located at $row['timestamp']

[PFMaBiSmAd]

There's nothing wrong with your query or code and it works as expected (just tested) provided you have data in the Editor_Candidates table so that the user_id matches up rows between the Responses and Editor_Candidates table.

[Pikachu2000]

As an aside, I usually find it easier to select the timestamp with MySQL's DATE_FORMAT() function in the query string.

 

SELECT DATE_FORMAT( field_name, '%c/%e/%Y, at $h:%i, %p' ) AS date, then you would access it through the array index $row['date']. (Should return "M/D/YYYY at H:MM, AM/PM")

 

MySQL manual page for the function is HERE.

[webguync]

I think that was my problem is that the user_id's weren't matching up in my two tables. When I match those up, the data displays correctly.