Convert timestamp from SQL in PHP

[JanMolendijk]

Well some new problem. I have a simple code to convert timestamp so it`s able to read.

Code: Select all

timestamp 1537939165

<?php echo date(DATE_RFC822, 1537939165); ?>

result: Wed, 26 Sep 18 07:19:25 +0200

But now I would like to have the same result into this SQL document.

Code: Select all

<?php
// connect to the "tests" database
$conn = new mysqli('127.0.0.1', 'system_chat', '123456', 'system_chat');
// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}
// SELECT sql query
$sql = "SELECT * FROM `blab_lines`   ORDER BY `line_id` DESC LIMIT 10"; 
// perform the query and store the result

$result = $conn->query($sql);

// if the $result contains at least one row
if ($result->num_rows > 0) {
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
    echo '<br><div class="venster">
'. $row['from_name']. '
<br>
'. $row['line_txt']. '
  </div> ' ;
  }
}
else {
  echo 'No-one added yet a @1';
}
$conn->close();
?>

I want to add '. $row['timestamp']. ' into this document
but when I enter it I can`t read it. example: 1537899452

Do you have a column with "timestamp" name in your mysql table, or what type of column with date-time is in that table?
If yes, it is better to change its name because timestamp is a reserved keyword.
If that column is a type of Date-Time, you can apply the UNIX_TIMESTAMP(col_date) function in the sql statement.

Code: Select all

$sql ='SELECT from_name, line_txt, UNIX_TIMESTAMP(col_date) AS timest FROM blab_lines ORDER BY line_id DESC LIMIT 10';

Then, in php use this code:

Code: Select all

echo date(DATE_RFC822, $row['timest']);

The code is from a chat-script if it is able I would like to keep it how it is ?

Code: Select all

  1  line_id int(11)      AUTO_INCREMENT 
  2  from_id  int(11)    
  3  from_name  varchar(64)   utf8_general_ci  
  4  timestamp  int(11)     
  5  line_txt  text   utf8_general_ci   

(LOL almost whole week I`m busy with it)

I turnt your suggestion into this but it is still not working

Code: Select all

$sql = "SELECT from_name, line_txt, UNIX_TIMESTAMP(timestamp) AS timestamp FROM blab_lines ORDER BY line_id DESC LIMIT 10";

Code: Select all

echo date(DATE_RFC822, $row['timestamp']);

Since the type of the "timestamp" column is INT(11), you not need to apply the UNIX_TIMESTAMP() function (which is used for DateTime type).
Try the following code:

Code: Select all

$sql = "SELECT from_name, line_txt,`timestamp` FROM blab_lines ORDER BY line_id DESC LIMIT 10";

And

Code: Select all

echo date(DATE_RFC822, $row['timestamp']);