Changing timestamp date format in table display

OzWaz2 · March 20, 2021

I have a simple mysql database holding information about an image upload including its 'name' and 'date uploaded' [current_timestamp()] to a folder 'image'

I then display it into a table using the php ‘foreach’ facility.

I want the date to be displayed in the ‘dd/mm/yyyy’ format. not the default yyyy/mm/dd.

Here is the relevant code

<?php

 $conn = mysqli_connect("localhost", "root", "", "dbname");

  $results = mysqli_query($conn, "SELECT * FROM tablename");

  $image = mysqli_fetch_all($results, MYSQLI_ASSOC);

?>

<?php foreach ($image as $user): ?>

<td><p>NAME</p><?php echo $user['name']; ?></td>

<td><p>COMMENT</p><?php echo $user['comment']; ?></td>

<td><p>DATE</p><?php echo $user['date']; ?></td>

What do I need to do?

THANKS- Warren

Barand · March 20, 2021

55 minutes ago, OzWaz2 said:

What do I need to do?

Stop using "SELECT *" and specify the fields you need.
Use mysql DATE_FORMAT() give the required format, or format it in php with date() function

SELECT name 
     , comment
     , DATE_FORMAT(date, '%d/%m/%Y') as date 
FROM tablename

Edited March 20, 2021 by Barand

OzWaz2 · March 22, 2021

Thanks for your help but I do not possess enough knowledge to correctly apply your suggested fix

i have interpreted it as this:

 $results = mysqli_query($conn, "SELECT 

     , name
     , comment
     , DATE_FORMAT(date, '%d/%m/%Y') as date
FROM tablename);

but it doesn't work

What am i not understanding?

Thanks

Warren

Barand · March 22, 2021

If we just get the data straight from the table we get

SELECT name
     , comment
     , date
FROM date_example;

+-------+--------------------------------------------------------+------------+
| name  | comment                                                | date       |
+-------+--------------------------------------------------------+------------+
| Peter | Lorem ipsum dolor sit amet                             | 2005-05-21 |
| Paul  | Nunc viverra imperdiet enim                            | 2005-06-22 |
| Curly | Suspendisse dui purus, scelerisque at, vulputate vitae | 2006-11-01 |
| Larry | Etiam eget dui. Aliquam erat volutpat.                 | 2006-11-02 |
| Mo    | Etiam at ligula et tellus ullamcorper ultrices.        | 2019-09-28 |
+-------+--------------------------------------------------------+------------+

However, formatting the date as I showed gives

SELECT name
     , comment
     , DATE_FORMAT(date, '%d/%m/%Y') as date
FROM date_example;

+-------+--------------------------------------------------------+------------+
| name  | comment                                                | date       |
+-------+--------------------------------------------------------+------------+
| Peter | Lorem ipsum dolor sit amet                             | 21/05/2005 |
| Paul  | Nunc viverra imperdiet enim                            | 22/06/2005 |
| Curly | Suspendisse dui purus, scelerisque at, vulputate vitae | 01/11/2006 |
| Larry | Etiam eget dui. Aliquam erat volutpat.                 | 02/11/2006 |
| Mo    | Etiam at ligula et tellus ullamcorper ultrices.        | 28/09/2019 |
+-------+--------------------------------------------------------+------------+

So exactly what does "not working" mean?

PS How are you currently storing your dates? It should be TYPE DATE (or DATETIME) with a format of YYYY-MM-DD

Edited March 22, 2021 by Barand

dodgeitorelse3 · March 22, 2021

@OP, you have a comma before name in your query

OzWaz2 · March 23, 2021

This is my display page that works

<?php
 $conn = mysqli_connect("localhost", "root", "", "china");
  $results = mysqli_query($conn, "SELECT * FROM image");
 
  $image = mysqli_fetch_all($results, MYSQLI_ASSOC);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="css/basic.css" rel="stylesheet" type="text/css" />
<link href="css/img.css" rel="stylesheet" type="text/css" />
</head>

<table class="show" width="90%" border="0" cellspacing="5" cellpadding="5">
   <tbody>
   <?php foreach ($image as $user): ?>
  <tr>
    <td width="14%" align="left" valign="top"><p class ="dates">Uploaded</p><p class ="date"><?php echo $user['date']; ?></p></td>
    <td width="33%" rowspan="3" valign="top" class="this"><img class="b" src="<?php echo 'uploads/' . $user['image'] ?>" width="500" height="250" ></td>
    <td width="53%" rowspan="3" valign="top" class="this"><br /><p class="quote"><?php echo $user['comment']; ?> </p></td>
  </tr>
  <tr>
    <td align="left" valign="top"><p class ="dates">From:</p><p class ="date"><?php echo $user['pname']; ?></p></td>
  </tr>
  <tr>
    <td align="left" valign="top" class="this" ><p class="dates">Title</p><p class="date"><?php echo $user['fname']; ?></p></td>
  </tr>

            <?php endforeach; ?>
   </tbody>
</table>

</body>
</html>

The date is stored as 'current_timestamp()'

It displays as '2021-03-23'

However when I change this section

<?php
 $conn = mysqli_connect("localhost", "root", "", "china");
  $results = mysqli_query($conn, "SELECT * FROM image");
 
  $image = mysqli_fetch_all($results, MYSQLI_ASSOC);
?>

To when I interpret your instructions to be - as this:

<?php
 $conn = mysqli_connect("localhost", "root", "", "china");
  $results = mysqli_query($conn,
" SELECT name
         , comment
         , DATE_FORMAT(date, '%d/%m/%Y') as date
    FROM image");
 
  $image = mysqli_fetch_all($results, MYSQLI_ASSOC);
?>

I get this error

Fatal error: Uncaught TypeError: mysqli_fetch_all(): Argument #1 ($result) must be of type mysqli_result, bool given in C:\xampp\htdocs\china\viewA.php:9 Stack trace: #0 C:\xampp\htdocs\china\viewA.php(9): mysqli_fetch_all(false, 1) #1 {main} thrown in C:\xampp\htdocs\china\viewA.php on line 9

Thanks

Barand · March 23, 2021

That error means your query returned false (failed) instead of a valid result.

My guess would be that the column names in my example are not the same as those in your table.

OzWaz2 · March 23, 2021

Of course - must be my age? So it is Great - Thank you BARAND. - IT WORKS

Always good to have a fresh set of eyes from someone with more knowledge than me

Can I stretch the friendship?

The table displays in alphabetical order for the the NAME (pname) input. I would like it to display in chronologist order for the DATE (date)

What would I need to do to get it to display in DATE order - with most recent on top?

Thanks

Barand · March 23, 2021

We need to change the alias of the date column to something other than "date" otherewise it willl try to sort using the d/m/Y format, which doesn't work. We need to sort by the original date (Y-m-d)

Therefore the query will become

SELECT name
     , comment
     , DATE_FORMAT(date, '%d/%m/%Y') as formatted
FROM image
ORDER BY date DESC;

(Don't forget to use your column names and where you had $row['date'] you now need $row['formatted'])

OzWaz2 · March 26, 2021

Again Thanks Barand -Worked perfectly

Appreciate you assistance and patience - as only us "old fogies" understand.

Warren

Sign In

Changing timestamp date format in table display

Recommended Posts

OzWaz2

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

OzWaz2

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

dodgeitorelse3

Link to comment

Share on other sites

OzWaz2

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

OzWaz2

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

OzWaz2

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information