Hi everyone, Iam trying to figure out how to display the correct time and date format in the following piece of code

 $stmt = $conn->prepare("SELECT * FROM tbl ORDER BY id DESC");
                            $total = $stmt->rowCount();
                                while ($row = $stmt->fetchObject()) {
                                            //$opvar = $row['mchq_date'];                                            
                                    echo "

At the moment the date and time is being displayed as 2017-09-03 10:45:20 - Whilst its readable, I would like it to be displayed something like,
Sunday September 3rd

2 Weeks
Discussion Span
Last Post by Lloyd_4

You can do something like this...

$stmt = $conn->prepare("SELECT * FROM tbl ORDER BY id DESC");
$total = $stmt->rowCount();
while ($row = $stmt->fetchObject()) {
        $time = strtotime($row->mchq_date);
        echo "<tr><td>".date("l F jS",$time)."</td></tr>";

More php date formating info here


Another way - format date in to the db, e.g.

$stmt = $conn->prepare("SELECT DATE_FORMAT(`your_date_column`, '%W %M %D') FROM tbl ORDER BY id DESC");

More MySQL date formating info here here

Edited by AndrisP


BTW - $total = $stmt->rowCount(); isn't guaranteed to work - it was not meant for SELECT statements. From the manual:

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.

It continues...

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.

From http://php.net/manual/en/pdostatement.rowcount.php

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.