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");
                            $stmt->execute();
                            $total = $stmt->rowCount();
                                while ($row = $stmt->fetchObject()) {
                                            //$opvar = $row['mchq_date'];                                            
                                    echo "
                                        <tr>
                                          <td>{$row->mchq_date}</td>                                   
                                        </tr>                                                                                                                                                   
                                         ";

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

You can do something like this...

$stmt = $conn->prepare("SELECT * FROM tbl ORDER BY id DESC");
$stmt->execute();
$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

Member Avatar for diafol

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

@charis89 - This worked like a charm thank you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.