simple date() time() question

[tomfmason]

I am wondering how do I format the date and time in a mysql query? Can I format it before I post the information to the database or do I format it when I request it from the database?

here is how I post the information to the database


[code=php:0]$sql = mysql_query("INSERT INTO messages (first_name, last_name, email, subject, message, date, status, color)
        VALUES('$first_name', '$last_name', '$email', '$subject', '$message', now(), '$status', '$color')") or die (mysql_error()); [/code]

I was thinking I would do it like this [code=php:0]now('%M %D %Y)[/code]

Any suggestions would be great.
I said it was going to be simple..lol

[hvle]

yes it is simple indeed.

it is depend on how your format your date in mysql database.
normally, date format like this: YYYY-MM-DD like (2006-07-13)

so, you will get a date in the same format using php:

[code]
$now = date('Y-m-j');  // get the date base on current time
and that's it, insert them:

$sql = mysql_query("INSERT INTO messages (first_name, last_name, email, subject, message, date, status, color)
        VALUES('$first_name', '$last_name', '$email', '$subject', '$message', $now, '$status', '$color')") or die (mysql_error());
[/code]

if it doesn't work, it's probably the wrong format, you need to find out the format of date in your mysql.

[tomfmason]

yes YYYY-MM-DD is the format of my database.  I want to switch that to %M %D %Y and set time to hh:mm:ss %p

Any suggestions on how to do this

[hvle]

well then you need to change the field type to datetime instead of date.

However, I would never recommend using the database format. 
Instead, you should change the field type to int(10) and store a unix timestamp.
why? unix timestamp is much easier to manipulate in PHP.

[hostfreak]

It's not too hard. Some where in your code after you call it from the mysql database add:

$variable = date("F, j, Y" ,strtotime($variable));

Just change "variable" to what you need it to be.

[tomfmason]

See that might be alittle bit difficult. Here is a relevant piece of code.

[code=php:0]$q = "SELECT message_id, email, subject, date, status, color From messages WHERE status ='new' LIMIT 0, 30";
$get_inbox = mysql_query($q);
if (!$get_inbox) {
  echo "Could not successfully run query ($sql) from DB: " . mysql_error();
  exit;
}
if (mysql_num_rows($get_inbox) == 0) {
  echo "
        <td><font color=\"#FF0000\">*Your Inbox is empty</font></td>";
  exit;
}       
while ($rw = mysql_fetch_assoc($get_inbox)) {
    echo '
<tr>
<td width="20%">' . $rw['email'] . '</td>
<td width="20%">' . $rw['subject'] . '</td>
<td width="10%">&nbsp;</td>
<td width="10%">' . $rw['date'] . '</td>
<td width="10%"><b><font color="' . $rw['color'] . '">' . $rw['status'] . '</font></b></td>
<td width="10%"><a href="read.php?message_id=' . $rw['message_id'] . '" TITLE="Read"><img border="0" src="read.gif" width="20" height="20" align="middle"><font size="2"><i>Read</i></font></a></td>
<td width="10%"><a href="reply.php?message_id=' . $rw['message_id'] . '" TITLE="Reply"><img border="0" src="reply.gif" width="20" height="20"><i><font size="2">Reply</font></i></a></td>
<td width="10%"><a href=process.php?action=delete_message?message_id="' . $rw['message_id'] . '" TITLE="Delete"><img border="0" src="delete.gif" width="20" height="20"><i><font size="2">Delete</font></i></a></td>
</tr>';
}
mysql_free_result($get_inbox);[/code]

It would be easy, I agree if it were just a variable but I am lost when it comes to displaying a different date format with this.

[hostfreak]

$date = date("F, j, Y" ,strtotime($date));

Somwhere in your code before you call date. It's just changing the date format of "date".

"date" is the variable that you are displaying the date with right?

[hostfreak]

[code]
$q = "SELECT message_id, email, subject, date, status, color From messages WHERE status ='new' LIMIT 0, 30";
$date = date("F, j, Y" ,strtotime($date));
$get_inbox = mysql_query($q);
if (!$get_inbox) {
  echo "Could not successfully run query ($sql) from DB: " . mysql_error();
  exit;
}
if (mysql_num_rows($get_inbox) == 0) {
  echo "
        <td><font color=\"#FF0000\">*Your Inbox is empty</font></td>";
  exit;
}       
while ($rw = mysql_fetch_assoc($get_inbox)) {
    echo '



<tr>





<td width="20%">' . $rw['email'] . '</td>





<td width="20%">' . $rw['subject'] . '</td>





<td width="10%">&nbsp;</td>





<td width="10%">' . $rw['date'] . '</td>





<td width="10%"><b><font color="' . $rw['color'] . '">' . $rw['status'] . '</font></b></td>





<td width="10%"><a href="read.php?message_id=' . $rw['message_id'] . '" TITLE="Read"><img border="0" src="read.gif" width="20" height="20" align="middle"><font size="2"><i>Read</i></font></a></td>





<td width="10%"><a href="reply.php?message_id=' . $rw['message_id'] . '" TITLE="Reply"><img border="0" src="reply.gif" width="20" height="20"><i><font size="2">Reply</font></i></a></td>





<td width="10%"><a href=process.php?action=delete_message?message_id="' . $rw['message_id'] . '" TITLE="Delete"><img border="0" src="delete.gif" width="20" height="20"><i><font size="2">Delete</font></i></a></td>



</tr>';
}
mysql_free_result($get_inbox);
[/code]

[tomfmason]

I tried that already and got the following error.

[code]Warning: strtotime() [function.strtotime]: Called with an empty time parameter in inbox.php on line on line 37[/code]

[code]Warning: date() [function.date]: Windows does not support dates prior to midnight (00:00:00), January 1, 1970 in inbox.php on line on line 37[/code]


I guess it wasn't so simple after all.

[tomfmason]

[b]The Fix:[/b]
I just had to change the way that I was selecting the date from the database



[b]From:[/b]
[code=php:0]"SELECT message_id, email, subject, date, status, color From messages WHERE status ='new' LIMIT 0, 30"[/code]


[b]To:[/b]
[code=php:0]"SELECT message_id, email, subject, DATE_FORMAT(date, '%M %e %Y') as formated_date, status, color From messages WHERE status ='new' LIMIT 0, 30"[/code]

Thanks for the suggestions.