Display correct DateTime from db in php

Question

I'm having a problem with the date time presentation. I want to display the time of entries into db in a list with title of the entry next to it. But at the moment, whenever I try to format the time properly, the code only displays one entry instead of all of them.

<?php
    $result = mysql_query("SELECT * FROM posts");

    while ($row = mysql_fetch_array($result)){  
        echo "<li>{$row["date"]}". "&nbsp; &nbsp; &nbsp; &nbsp;". $row["title"]. "</li>";
    }
?>

like this it obviously just shows whats in the db :

2014-09-08 07:09:24.476246       this is it !!
2014-09-05 06:20:20.317560       So nun endlich die Website online

but when I format the dates somehow like this ...

<?php
    $result = mysql_query("SELECT * FROM posts");

    $new_date = mysql_fetch_row($result);
    $date = date_create($new_date[3]);

    while ($row = mysql_fetch_array($result)){  
        echo "<li>". date_format($date, 'Y-m-d H:i:s'). "&nbsp; &nbsp; &nbsp; &nbsp;". $row["title"]. "</li>";
    }
?>

I only get the last row from the db.

Not an answer to your question but check out http://php.net/manual/en/function.mysql-query.php. You see that big red box? If you're writing new PHP code, you should pay attention to what it says. In short, use the `mysqli_` or `PDO` functions instead. — Tom Fenech, Sep 11 '14 at 13:18

score 0 · Answer 1 · edited May 23 '17 at 10:28

By calling mysql_fetch_row() outside of your loop you are popping the first record of the recordset off of the stack leaving only the second record. Just create your DateTime() object in your loop.

<?php
    $result = mysql_query("SELECT * FROM posts");

    while ($row = mysql_fetch_array($result)){  
        $date = date_create($row[3]);
        echo "<li>". date_format($date, 'Y-m-d H:i:s'). "&nbsp; &nbsp; &nbsp; &nbsp;". $row["title"]. "</li>";
    }
?>

I also do not recommend mixing numerical and associative keys when accessing the same data. Stick with one. I recommend associative arrays since they are clearer to the reader what is going on.

<?php
    $result = mysql_query("SELECT * FROM posts");

    while ($row = mysql_fetch_assoc($result)){  
        $date = date_create($row['date_col']);
        echo "<li>". date_format($date, 'Y-m-d H:i:s'). "&nbsp; &nbsp; &nbsp; &nbsp;". $row["title"]. "</li>";
    }
?>

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Rakesh Sharma · Accepted Answer · 2014-09-11T13:23:37.370

0

Try to format with date() directly in loop so no need to $date var

echo "<li>".date('Y-m-d H:i:s', strtotime($row["date"]))."&nbsp; &nbsp; &nbsp; &nbsp;". $row["title"]. "</li>";

or just make your

 $date = date_create($new_date[3]);

inside the loop

Also mysql_* has been deprecated for more follow @john Conde answer.

edited Sep 11 '14 at 13:23

answered Sep 11 '14 at 13:18

Rakesh Sharma

13,680
5
37
44

Display correct DateTime from db in php

2 Answers2