MySQL Timestamp Field Not Updating on Form Submission

Question

I have a Contact Us submission form on my web site (mconchicago.com/Contact_Us).

It is supposed to be set up so that every submission gets an autonumber and a current timestamp. Everyting is working now except the timestamp, which is bringing me to the verge of madness. Each time it is all zeros.

This is what the records look like when uploaded:

http://www.mconchicago.com/Screenshots/Records.jpg

This is how I have the timestamp field configured:

http://www.mconchicago.com/Screenshots/Structure.jpg

And here is my last attempt at making the PHP work:

<?   
$id=$_POST['mysql_insert_id()']; 
$first_name=$_POST['first_name'];
$last_name=$_POST['last_name']; 
$company=$_POST['company'];
$email=$_POST['email'];
$address=$_POST['address']; 
$city=$_POST['city']; 
$state=$_POST['state'];
$zip=$_POST['zip'];
$phone=$_POST['phone'];
$reason=$_POST['reason'];
$comments=$_POST['comments'];
$timestamp=$_POST['UNIX_TIMESTAMP()'];
mysql_connect("emellis2002.db.9243147.hostedresource.com", "emellis2002", "Newpwd99@") or die(mysql_error()); 
mysql_select_db("emellis2002") or die(mysql_error()); 
mysql_query("INSERT INTO `data` VALUES ('$id', '$first_name', '$last_name', '$company', '$email', '$address', '$city', '$state', '$zip', '$phone', '$reason', '$comments', 'timestamp')");  
Print "Your information has been received.  Thank you for getting in touch.<br><br>"; 
Print "<a href=http://www.mconchicago.com/Contact_Us.html>CLICK HERE</a> to return to our web site." 
?>

You can see from the Structure screenshot that I have tried many different syntax combinations without success, looking at many forum postings in the process. What am I missing?

Note that your code is vulnerable to SQL injection, which is a serious security problem that you should fix by escaping your POST variables using PHP's inbuilt mysql_real_escape_string function before putting them into the INSERT query :) — Daan
– Daan, Commented May 20, 2012 at 19:29
No, using HTTPS does not prevent SQL injections. Basically what's happening in your code is that you are taking user input from the POST superglobals and then inserting that input directly into a MySQL query. HTTPS only ensures the security of the connection between your server and the client. It doesn't guarantee that clients won't be sending you bad input - only that all input, good or bad, is sent to you securely without the possibility for anyone to eavesdrop. — Daan
– Daan, Commented May 20, 2012 at 20:52

JHS · Accepted Answer · 2012-05-20 19:08:33Z

1

It is better to use the MYSQL DEFAULT_VALUE property.

Edit the table and put CURRENT_TIMESTAMP in the DEFAULT_VALUE.

This will insert the current timestamp of the MYSQL Server in the timestamp column everytime there is an insert done in the table.

answered May 20, 2012 at 19:08

JHS

7,8812 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Eric Ellis Over a year ago

I am confused. Is that not what I have activated? I checked the CURRENT_TIMESTAMP box in the default column when I set everything up.

JHS Over a year ago

Can you show me the table creation script? Also if you have already setup CURRENT_TIMESTAMP in DEFAULT_VALUE of the table then you do not need to insert it explicitly. It will be inserted automatically.

Eric Ellis Over a year ago

I just tried removing all insertion code from my PHP. Now when I submit through the contact form no record at all is completed in the database, even though I was given a positive upload confirmation message and no error. The table was created in pieces. So would the original creation script still be a valid reference?

JHS Over a year ago

Use this alter command to alter the table - 'ALTER TABLE <tablename> CHANGE COLUMN timestamp timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;' Modify your insert to be as - 'INSERT INTO data VALUES ($id, $first_name, $last_name, $company, $email, $address, $city, $state, $zip, $phone, $reason, $comments);'

Eric Ellis Over a year ago

Here is the syntax I am using. It is being rejected. What am I doing wrong? 'ALTER TABLE data CHANGE COLUMN timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;'

|

Ian Gregory · Accepted Answer · 2012-05-20 19:22:16Z

1

A couple of points:

$timestamp=$_POST['UNIX_TIMESTAMP()'];

You're looking in the POSTed values for something called UNIX_TIMESTAMP(). You probably intended:

$timestamp = 'UNIX_TIMESTAMP()';

Also:

mysql_query("INSERT INTO `data` VALUES ('$id', '$first_name', '$last_name', '$company', '$email', '$address', '$city', '$state', '$zip', '$phone', '$reason', '$comments', 'timestamp')");

You're trying to insert the string timestamp into the field. You probably meant $timestamp

answered May 20, 2012 at 19:22

Ian Gregory

5,8361 gold badge31 silver badges42 bronze badges

7 Comments

Eric Ellis Over a year ago

Thanks for your reply. I made the two changes you mention above. But it is still coming up all zeros.

Eric Ellis Over a year ago

Just replaced it so that it reads $timestamp= 'NOW()'; But it is still coming up all zeros.

Ian Gregory Over a year ago

I see it - change your call to

mysql_query("INSERT INTO `data` VALUES ('$id', '$first_name', '$last_name', '$company', '$email', '$address', '$city', '$state', '$zip', '$phone', '$reason', '$comments', $timestamp)");

- you don't want single quotes on the final field

Eric Ellis Over a year ago

I just removed the single quotes while retaining $timestamp= "NOW(); At this point no database record at all is created on the back end. The form data seems to have been discarded although I received no error message.

Ian Gregory Over a year ago

You might want to look into printing/echoing mysql_error to see what the database is telling you - php.net/manual/en/function.mysql-error.php

|

Collectives™ on Stack Overflow

MySQL Timestamp Field Not Updating on Form Submission

2 Answers 2

13 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related