Jump to content

simple date() time() question


tomfmason

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
Link to comment
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.

Link to comment
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.
Link to comment
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.
Link to comment
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]
Link to comment
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.

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.