Jump to content

Archived

This topic is now archived and is closed to further replies.

tomfmason

simple date() time() question

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
$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?

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.