0

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

4
Contributors
4
Replies
40
Views
2 Weeks
Discussion Span
Last Post by Lloyd_4
4

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

4

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

3

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.