Dates are not being very nice

TheMayhem

I don't know why I'm not getting this but I have looked at the date functions, getdate, and so forth from the php manual and searched here... there... and everywhere but I just can't seem to understand this. I store in the mysql database of my data a row called pm_date and I am using the timestamp(14) as the type. My problem is and for some reason I just can't figure out whenever I run a query to connect to the db it will be $pm_message[pm_date] and I want to convert that timestamp from all the numbers stored in the db to an actual date and time. Such as January 5, 2005 at 6:05 PM. Like I said I'm very frustrated with this and hope someone can help.

devinemke

SELECT DATE_FORMAT(timestamp, '%M %e, %Y at %h:%i %p') AS date_formatted FROM table

TheMayhem

This is what my query looks like

$pm_date = mysql_fetch_array(mysql_query("SELECT DATE_FORMAT(pm_date, '%M %e, %Y at %h:%i %p') AS date_formatted FROM private where pm_id='$pm_message[pm_id]'"));

All it is giving me is an Array message instead of where the text should be for the date

devinemke

Originally posted by TheMayhem
...All it is giving me is an Array message instead of where the text should be for the date

...because that is precisely what you asked for, you are using [man]mysql_fetch_array[/man] which, as the name suggests, returns an array.

TheMayhem

Honestly I'm somewhat lost on if their should be a specific type of query command I should be using. I just tried plain good old

mysql_query("SELECT DATE_FORMAT(pm_date, '%M %e, %Y at %h:%i %p') AS date_formatted FROM private where pm_id='$pm_message[pm_id]'");

And it gives me this beauty Resource id #15.... Any ideas on what the exact query should look like?

devinemke

if you only expect one row to be returned from your query (which seems to be the case):

$result = mysql_query("SELECT DATE_FORMAT(pm_date, '%M %e, %Y at %h:%i %p') AS date_formatted FROM private where pm_id = '" . $pm_message['pm_id'] . "'");
echo mysql_result($result, 0);

TheDefender

TheMayhem, it sounds like you don't quite have a grasp on what [man]mysql_query[/man] and [man]mysql_fetch_array[/man] really do. I have conveniently linked them to the manual for you so you can read up on them. I recommend you take a gander at them (for your own future sanity)

In both of your examples, you got the expected results for what you coded for. In other words, they both worked perfectly to do what you told them to do. It may not be what YOU expected, but that's where your understanding of those functions come in. Once you understand them, you will know what to expect from them, and how to get the data you truley want out of them.