Converting dates from the database in While Loop

[marcbraulio]

Hello everyone,

 

I am trying to convert dates directly from the database but still keep it within the same array so that I can conveniently access it later on. I am not sure how to explain this better, but I believe the code is pretty self explanatory:

$stmt = $this->db->query("SELECT title,slug,content,author_id,created FROM article");

$stmt->setFetchMode(PDO::FETCH_ASSOC);

$i = 0;

while($var = $stmt->fetch())
{
$this->data[] = $var;
$this->data[$i]['date'] = date("F j, Y", strtotime($var['created']));
$i++;
}

print_r($this->data);

/* produced array
Array
(
    [0] => Array
        (
            [title] => PHP Security Book
            [slug] => php-security-book
            [content] => Lorem ipsum dolor sit amet, consectetur adipisicing elit.
            [author_id] => 3
            [created] => 2012-03-13 12:34:42
            [date] => March 13, 2012
        )

    [1] => Array
        (
            [title] => Something To Do
            [slug] => somthing-to-do
            [content] => You know what a dolor sit amet, consectetur adipisicing elit.
            [author_id] => 3
            [created] => 2012-03-13 12:35:46
            [date] => March 13, 2012
        )
)
*/

I access it like so:

foreach ($_data as $var) 
{
echo '<h2>' . $var['title'] . '</h2> <br />';
echo '<b>' . $var['date'] . '</b> <br />';
echo '<p>' . $var['content'] . '</p> <br />';
}

It works perfectly but my question is:

Is there a better or more efficient way to do this? I think that my while loop could use some improvement.

I was also thinking of maybe fetching the results into a class using PDO::FETCH_CLASS, but it seems like a bit of a hassle for just one modification.

 

[Pikachu2000]

Don't use php's slow date() and strtotime() functions. Assuming the date is stored as YYYY-MM-DD, format the date in the query string with MySQL's DATE_FORMAT() function.

 

SELECT DATE_FORMAT(created, '%M %e, %Y') AS created

[Psycho]

Edit: What he ^^ said

 

You could use the MySQL date functions to have the date returned in the query in the format you want it. Then you don't need to do any post-processing of the data in PHP.

[marcbraulio]

Don't use php's slow date() and strtotime() functions. Assuming the date is stored as YYYY-MM-DD, format the date in the query string with MySQL's DATE_FORMAT() function.

SELECT DATE_FORMAT(created, '%M %e, %Y') AS created

Simply perfect. Many thanks!

 

Edit: What he ^^ said

 

You could use the MySQL date functions to have the date returned in the query in the format you want it. Then you don't need to do any post-processing of the data in PHP.

Pikachu2000's solution worked out perfectly, but I am curious about this method that you mentioned, could you elaborate by giving me an example?

 

Yeah I was about to say, "isn't technically Pikachu2000's solution a MySQL date function?" lol Thanks anyway!