December 31st, 1999, 04:23 AM
I don't want to sound stupid about my lack knowledge of programing and PHP, I'm really new. So I have looked through all the PHP topics on this forum and searched places like devshed, PHPBuilder, the Wizards and Leon Atkinson's book it sitting in front of me. It's 3:3something a.m. and my brain has started to show signs of the Y2K bug.
I want to format a date queried from my DB. I've managed to get the typical 2000-3-21 date but I need to rearange it to look like Mar. 21 2000. It's probably very simple but I can't seem to get a clear and simple answer with an example. I'm also learning Cold Fusion and it's a piece-o-cake to do this in that, but I want to learn PHP.
The normal SELECT * from seminars_db stuff has been made and my WHILE loop is running. This is the code I have had the most success with.
$ID = $row;
$bdate = $row;
$edate = $row;
$presentation = $row;
$place = $row;
$city = $row;
$state = $row;
$sponsor = $row;
$connect = $row;
print date("M. j, Y",mktime($bdate));
I know, this is wrong. At least I got the format right, the date was Dec. 31, 1969 on all records (what's up with that).
I have learned that $bdate is retrieved as a string and that can't be used with the date function.? I have seen the UNIX_TIMESTAMP and the mktime commands mentioned here, and second queries for just the "bdate" field (not sure if that's effiecient) but Leon's book hasn't helped me and the forum has not answered it thorough enough that I could understand. There has been sucess with the basic date function . . .
$Today = date("F d,Y");
. . . and calling it later in the page, and I know I can use this against the "bdate" field. However, I don't need how many days until the event, I need the event M. j, Y pulled from the DB.
Hey, it's past 4:00 a.m. Shouldn't I be wishing someone on the rock Happy New Year?
December 31st, 1999, 07:15 AM
This is a bit of a kludge but:
$dbmonth = (int)substr($curdate, strpos($curdate, "-") + 1, strrpos($curdate, "-") - strpos($curdate, "-") - 1);
$dbday = (int)substr($curdate, strrpos($curdate, "-") + 1);
$dbyear = (int)substr($curdate, 0, 4);
print date("M. j, Y", mktime(0, 0, 0, $dbmonth, $dbday, $dbyear));
December 31st, 1999, 10:01 AM
The reason date() doesn't work for you is that date() requires a UNIX timestamp to work and you are using a MySQL timestamp. You can use the MySQL DATE_FORMAT function in your query to format the date for you.
$result=mysql_query("select DATE_FORMAT(bdate,%a. %e, %Y) from seminars_db");
Where bdate is the column name of the timestamp you wish to convert. The %a returns the abbreviated month name, the %e is the day of the month 1...31 and %Y returns the 4 digit year. The . and , are returned as is so from 2000-3-21 you'd get:
Mar. 31, 2000
You can see more formatting options in the mysql manual under date/time functions (section 7.3.10 of the 3.22 manual or section 7.4.11 of the 3.23 manual).
Of course, this means you won't be able to use * in your select but have to ask for each column.
December 31st, 1999, 12:07 PM
Ooops. I don't know why I assumed that you were using MySQL.... well, if you are, there you go. If not, ignore my previous post.
December 31st, 1999, 12:43 PM
Sorry to get so wordy in my question, I was getting a little slap-happy.
Am I right to assume that CBOLT's answer uses PHP to reformat the date by a generic manipulation of a string and ROD K uses MySQL to reformat the date from the query? I really want to learn this, so reply if you have the time.
BTW, I am using mySQL and I did read over the 3.23 manual section 7.4.11 but I didn't get how this was used. It didn't seem effecient to name all the fields in the query b/c Cold Fusion allows me to manipulate just about any feild with SELECT *. I'm not saying CF is better, it has just been my progression of learning languages.
December 31st, 1999, 06:49 PM
Yes, my answer was using string manipulation, and rod k's answer (or the right answer :-) used mysql... never knew about the DATE_FORMAT function until now.
January 1st, 2000, 02:37 PM
Well, I wouldn't say mine was any more right than cbolt's. Just a matter of opinion. Using PHP to format can be lengthy (and takes longer, usually) but can make your query easier to build. To each his own.
The added benefit to doing it in PHP as cbolt described is you can always make it a function and include() the function in the necessary scripts. Of course, the optimal solution would be a compiled plug in of a function that could be added to PHP that would allow date formatting of just about any MySQL timestamp. Unfortunately, I'm not enough of a C programmer to take that on.
January 1st, 2000, 03:16 PM