Time & mySQL

This is probably a simple thing, but I couldn't find it in the APLUS KB or
on the http://us2.php.net/manual/en/function.date.php area either.  What I
find is this script doesn't  set the time in the mySQL database.  I 'm
assuming I need to convert the PHP time format to a mySQL forum.  The
display of the mySQL is yyyy-mm-dd hh:mm:ss, but I don't know if this is
just a display format or one mySQL wants the input in.

$time = time();
$time2 = $time + 14400;

$name_db = "mokenabarber";
$name_table = "notices";

$name_connect = mysql_connect("localhost", "abcdefg", "1234567") or
die(mysql_error());
$db_connect = mysql_select_db($name_db,$name_connect) or
die(mysql_error());

$sql_code  = "insert into $name_table ";
$sql_code .= "(ID, Expires, Message, AddDate)";
$sql_code .= " values (\"Dave\", $time2, ";
$sql_code .= "\"this is the message\", $time)";
echo $sql_code . "<br><br>";
$sql_result = mysql_query($sql_code,$name_connect) or die(mysql_error());

The PK is ID and AddDate.  No FK or UK.  As the Message field might be
greater than 255 characters, I made it a blob, but then you can't see it
properly in the control panel the host provides.

And a style question.  Do people build individual pages to view, edit or
delete records?  Same for editing the table structure?

TIA,
Lee

Lee David wrote:
> This is probably a simple thing, but I couldn't find it in the APLUS KB or
> on the http://us2.php.net/manual/en/function.date.php area either.  What I
> find is this script doesn't  set the time in the mySQL database.  I 'm
> assuming I need to convert the PHP time format to a mySQL forum.  The
> display of the mySQL is yyyy-mm-dd hh:mm:ss, but I don't know if this is
> just a display format or one mySQL wants the input in.
>
> $time = time();
>  $time2 = $time + 14400;
>
>  $name_db = "mokenabarber";
>  $name_table = "notices";
>
>  $name_connect = mysql_connect("localhost", "abcdefg", "1234567") or
> die(mysql_error());
>  $db_connect = mysql_select_db($name_db,$name_connect) or
> die(mysql_error());
>
>  $sql_code  = "insert into $name_table ";
>  $sql_code .= "(ID, Expires, Message, AddDate)";
>  $sql_code .= " values (\"Dave\", $time2, ";
>  $sql_code .= "\"this is the message\", $time)";
>  echo $sql_code . "<br><br>";
>  $sql_result = mysql_query($sql_code,$name_connect) or die(mysql_error());
>
> The PK is ID and AddDate.  No FK or UK.  As the Message field might be
> greater than 255 characters, I made it a blob, but then you can't see it
> properly in the control panel the host provides.
>
> And a style question.  Do people build individual pages to view, edit or
> delete records?  Same for editing the table structure?
>
> TIA,
> Lee
>
>
Lee -

I assume your AddDate column is of mySQL DATETIME type?
According to the manual [
http://dev.mysql.com/doc/mysql/en/datetime.html ] the DATETIME type can
expect input in "yyyy-mm-dd hh:mm:ss" as you said.  But your $time var
in the above script is just a unix timestamp, and I dont think mysql
would like that input.
You could use the php strftime(). [
http://us4.php.net/manual/en/function.strftime.php ]

To steal your own code:

> $time = time();
> $time2 = $time + 14400;

so $time2 is an hour ahead of time();

so one more line and I think you'd be OK right after your "+14400" line...

$time2 = strftime('%G-%m-%d %T', $time2);
//convert unix timestamp to mysql formatted timestamp

HTH

-peter