Jump to content


Photo

simple date() time() question


  • Please log in to reply
9 replies to this topic

#1 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 13 July 2006 - 02:33 AM

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


$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());

I was thinking I would do it like this
now('%M %D %Y)


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

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#2 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 13 July 2006 - 02:46 AM

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:

$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()); 

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


Life's too short for arguing.

#3 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 13 July 2006 - 02:59 AM

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

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#4 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 13 July 2006 - 03:03 AM

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.
Life's too short for arguing.

#5 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 13 July 2006 - 03:27 AM

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.

#6 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 13 July 2006 - 03:38 AM

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

$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);

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.

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#7 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 13 July 2006 - 03:43 AM

$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?

#8 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 13 July 2006 - 03:44 AM

$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);


#9 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 13 July 2006 - 03:51 AM

I tried that already and got the following error.

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

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


I guess it wasn't so simple after all.


Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#10 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 13 July 2006 - 04:54 AM

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



From:
"SELECT message_id, email, subject, date, status, color From messages WHERE status ='new' LIMIT 0, 30"


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

Thanks for the suggestions.

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users