A PHP/MySQL question – geeks only need apply: how to get a formatted date from an associative array

(He writes, noting all the comments piling up about PKRSER.COM.. hmm…)

I’ve got an intriguing PHP problem. Well, I hope it’s intriguing. I’m trying to format a date that comes out of MySQL. Presently I have to do it as
"SELECT ID, post_title, post_date,MATCH (post_name, post_content) AGAINST ('$terms') AS score.. "

That produces post_date which is in the MySQL datetime format – 2007-03-08 07:55:37.

But I want to put that in the form of “8 March 2007”. Yes, I know, you’re saying “Use MySQL’s date_format command!” Not so fast. The query produces an associative array called (surprise!) $results, where the results are then called one by one in a foreach loop (foreach $results as $result) thus:

$title = htmlspecialchars(stripslashes($result->post_title));
$permalink = get_permalink($result->ID);

See? You asked it for ID and post_title and it comes directly out of $result. Trouble is, post_date comes out in that boring “2007-03-08 07:55:37” format.

Now, MySQL’s date_format could be used: SELECT ID, post_title, date_format(post_date, "%d %M %Y"),MATCH (post_name, post_content)...

And that would yield the post date nicely formatted. But how the hell do I call it?
$published = $result->date_format fails.
$published = $result[2] fails (which I found odd.)
How do I call the variable created by MySQL’s date formatting? Or am I doomed to use the rather boring substr on the boring date?

(I don’t really want to go off into parsing the months of the date, just to make the pages run quicker.)

You might be able to guess what this is for, though it requires some low-level tweaking too.

The original I’m working with is the tweaked Nice Related Posts plugin for WordPress, which I’m tweaking further. If you want, I can release the code, assuming I’m ever done.

(And you have to give its author kudos for the domain name. “Some fool with a .com”. Yeah.)

Update: solved – tip o’ the hat to Jason and L in the comments, who point out that one can use the SQL command SELECT date_format(post_date,"%e %M %Y") as publ_date… and then call $result->publ_date which will be formatted as 8 March 2007 (in that setting). Thank you, the lazyweb.

6 Comments

  1. does the pure SQL statement

    “SELECT ID, post_title, date_format(post_date, “%d %M %Y”),MATCH (post_name, post_content)…”

    work? And how does “$published = $result[2]” fail?

    But it looks to me as if you should do the conversion in PHP, rather than MySQL, using some mixture of the function to convert strings to time stamps and then Date which formats timestamps as strings. I have certainly done comparable things in python. You get a string out of MySQL, convert it to a date value in PHP, then reformat that date value to another string ….

  2. Whoops. Lost the hyperlink to Date()

  3. Can’t you use the AS operator to give the formatted date column a name.

    “SELECT ID, post_title, date_format(post_date, “%d %M %Y”) AS p_date, MATCH (post_name, post_content)…”

    Then reference it using $published = $result->p_date

    I remember doing something similar using MySQL, but it’s been a while since I’ve used it in anger…

  4. Two solutions :

    SELECT ID, post_title, date_format(post_date, “%d %M %Y”) AS datefmted,MATCH (post_name, post_content)…

    then just access $results->datefmted

    or else call the php date formatting function on the string

    date(“%d %M %Y”, $results->post_date)

    (I think its date – check the manual)

    There’s never a geek around when you need one is there?

  5. SELECT ID, post_title,UNIX_TIMESTAMP(post_date) AS pd, other,things,to,select

    $published=date (“format string”,$result->pd);

  6. Charles

    Sunday 18 March 2007 at 9:46 pm

    Jason (recovered from spam! No idea why you were put there!) and L are right – as I thought to myself while wandering around today in a prelude-to-cold style fug. (I have RTFM’d the MySQL manual, in bits, and had noticed the ..as..). Yup, it does work to do

    SELECT date_format(post_date,”%e %M %Y”) as publ_date,MATCH…) and saves any subsequent PHP farting around as you can call $result->publ_date directly.

    Silver medal to Steve – added an extra step of PHP formatting. Tch, optimise, optimise, optimise.
    Which means that presently, assuming I can create a FULLTEXT index, I’ll be able to link to “related” posts from any existing one. Good-o.

Comments are closed.