Forum

Posted: **26 Sep 2018, 10:58**

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

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

Posted: **26 Sep 2018, 14:27**

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']);

Posted: **27 Sep 2018, 09:47**

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']);

Posted: **27 Sep 2018, 14:51**

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']);

Forum

Convert timestamp from SQL in PHP

Convert timestamp from SQL in PHP

Convert timestamp from SQL in PHP

Convert timestamp from SQL in PHP

Convert timestamp from SQL in PHP