List:General Discussion« Previous MessageNext Message »
From:J.R. Bullington Date:May 9 2006 5:09pm
Subject:RE: inserting server time into mysql
View as plain text  
The now() function would be used in the INSERT/UPDATE statement, not the
form field.

$query = "INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', now())";
mysql_query($query) or die('Error, insert query failed');

See the change to your variable $signin. Change that to now() and then
remove the form field signin.

To make your life a lot easier, you really should use the TIMESTAMP field.

ALTER TABLE staffs MODIFY `signin` `signin` timestamp default
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

This will ensure that you have the server's timestamp, not the client's
(timezones are a b**ch sometimes...)

J.R.

-----Original Message-----
From: Alla-amin [mailto:ibzaneleazar@stripped] 
Sent: Tuesday, May 09, 2006 12:41 PM
To: mysql@stripped
Subject: Re: inserting server time into mysql


Thanks for your help,

How can I capture this information from a php form into a mysql table. 
This is what I did:

1. I created the table and the user to access the database the table is in
create table staffs ( id int not null auto_increment primary key, firstname
varchar(20) not null, lastname varchar(20) not null, signin datetime not
null );

2. I created a php form to insert data into this table. The form works but
the datetime field isn't populated

<html>
<head>
<title>Staff Detail</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
if(isset($_POST['add']))
{
include 'config.php';
include 'opendb.php';

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$signin = $_POST['signin'];

$query = "INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', '$signin')";
mysql_query($query) or die('Error, insert query failed');

include 'closedb.php';
echo "New staff detail added";
}
else
{
?>
<form method="post">
<table width="400" border="0" cellspacing="1" cellpadding="2"> <tr> <td
width="100">First Name</td> <td><input name="firstname"
type="text"></td>
</tr> <tr> <td width="100">Last Name</td> <td><input
name="lastname"
type="text"></td> </tr> <tr> <td width="100">Sign-In
Time</td> <td><input
name="signin" type="text"></td> </tr> <tr> <tr> <td
width="100">&nbsp;</td>
<td>&nbsp;</td> </tr> <tr> <td
width="100">&nbsp;</td> <td><input name="add"
type="submit" id="add" value="Submit"></td> </tr> </table>
</form> <?php }
?> </body> </html>

Am I doing something wrong?

You mentioned that I can insert the now() function into a regular datetime
field, how can I do that?


In the last episode (May 09), Alla-amin said:
> I am trying to capture my server time automatically using php and 
> insert it in a mysql table.
> 
> Can the timestamp or time data type capture this information 
> automatically without having me code anything else?

You can use the 'timestamp' type to automatially insert the current
date/time when inserting or updating, or you can insert now() into a regular
'datetime' field.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

Attachment: [application/x-pkcs7-signature] smime.p7s
Attachment: [application/x-pkcs7-signature] smime.p7s
Thread
inserting server time into mysqlAlla-amin9 May
  • Re: inserting server time into mysqlDan Nelson9 May
    • Re: inserting server time into mysqlAlla-amin9 May
      • RE: inserting server time into mysqlJ.R. Bullington9 May
        • RE: inserting server time into mysqlAlla-amin9 May
RE: inserting server time into mysqlGeorge Law9 May