Syntax question, formatting date from a sql recordset...

[shamwowy]

Hi all, new to PHP and had a syntax question. I have the following code:

 

print "<p>{$row['comment_createdate']};

 

This prints out a date from my sqlquery as a database datetime. I need to format it to display "Monday June 15, 2010 5:15 pm". I'm not sure how/where to attach a date() function to the record. (If it matters, the line above is inside of a loop going through several records). This really applies to attaching functions to columns within a recordset in general.

 

Many thanks ahead of time.

[trq]

Format the date in your actual query.

 

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

[shamwowy]

Not quite sure how to do that inside of the query call....here's what I tried (which of course doesn't work). This query returns hundreds of rows.

 

$query = 'SELECT ' + DATE_FORMAT(comment_createdate, '%W %M %Y')  + ' , comment_text from comment';

if ($r = mysql_query($query)) {

while ($row = mysql_fetch_array($r)) {

print "<p>{$row['comment_createdate']}<p>{$row['comment_text']}";}}

 

The ColdFusion syntax for the formatting of the date would be <p>#dateFormat(comment_createdate, 'D, M, Y')# where it formats the date on the fly as it goes through each record. What I really need is just a working version of {$row[DATE_FORMAT('comment_createdate', '%W %M %Y')]}. Anyone know the correct syntax for this?

 

This line within the loop doesn't work at all:

print "<p>{$row[DATE_FORMAT('comment_createdate', '%W %M %Y')]}";

 

Thanks in advance for any and all assistance.

 

 

 

 

 

[spaceman12]

$time_now=mktime(date('h')+5,date('i')+30,date('s'));
    $time=date('h:i:s A',$time_now);
    $date=date('D, M,Y');
$insertDATE_TIME="INSERT INTO table(Date, Time, field1,field2,,,,,,,,,,) VALUES('".$date."', '$time', 'field1',,,,,,,,,,,,,,,,,,,)";
mysql_query($insertDATE_TIME) or die(mysql_query());

 

 

 

[trq]

DATE_FORMAT is a mysql function so you don't need to jump intop a raw string. You should aslo give it an alias so you can access it easily later in your php code.

 

$query = "SELECT DATE_FORMAT(comment_createdate, '%W %M %Y') as createddate , comment_text from comment";

 

Then you can use $row['createddate'] in your loop.

[Pikachu2000]

The syntax is off, you don't need to use any concatenation in a query string to include a MySQL function in it. (And the php concatenation operator is a period, not a plus.) It would also be easier in this case to alias the returned value AS `f_date`, so you can then use f_date as the array index when echoing it.

 

$query = "SELECT DATE_FORMAT(`comment_createdate`, '%W %M %Y') AS `f_date`, `comment_text` from `comment`";
if( $r = mysql_query($query) ) {
while( $row = mysql_fetch_assoc($r) ) {
	print "<p>{$row['f_date']}<p>{$row['comment_text']}";
}
}