Jump to content

Changing timestamp date format in table display


OzWaz2

Recommended Posts

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

Link to comment
Share on other sites

55 minutes ago, OzWaz2 said:

What do I need to do?

  1. Stop using "SELECT *" and specify the fields you need.
  2. 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 by Barand
Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.