Entering Content Into A MySQL Database Via A Form

Question

I've been working on creating a form that submits content into my database but I decided that rather than using a drop down menu to select the date I'd rather use a textfield. I was wondering what changes I will need to make to my table creation file.

<?php mysql_connect ('localhost', 'root', 'root') ;
mysql_select_db ('tmlblog');

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,
  timestamp int(20) NOT NULL,
  title varchar(255) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (id)
)";

$result = mysql_query($sql) or print ("Can't create the table 'php_blog' in the database.<br />" . $sql . "<br />" . mysql_error());

mysql_close();
if ($result != false) {
    echo "Table 'php_blog' was successfully created.";
}
?>

It's the timestamp that I need to edit to enter in via a textfield. The Title and Entry are currently being entered via that method anyway.

Whenever I use my form to update the database I get the following error message:

Can't insert into table php_blog. INSERT INTO php_blog (time_stamp,title,entry,) VALUES ('1270140960','kjkkj','jkjkjk') You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES ('1270140960','kjkkj','jkjkjk')' at line 1

Answer 1

You wouldn't have to make any changes to that part of your script. What you would need is to retrieve the users date input through a text input in your form and convert it (if its in valid format) to a timestamp.

Edit: unless of course you really want the timestamp to be a date in string or date format then you'd have to change int to varchar(9) (to contain xxxx-xx-xx or whatever format you prefer) or use date or datetime

Answer 2

You should probably use the MySQL standard DATETIME type for this. Get the textfield's content for a date and feed it to PHP's strtotime and date functions to get it into a format that matches what MySQL is looking for in a DATETIME object.

Answer 3

You shouldn't have to modify anything. Just make sure you correctly parse the input from the user and create a timestamp with mktime.

Answer 4

If you want to enter the current time stamp into the time_stamp field every time a row is being inserted you can change its definition to:

time_stamp  TIMESTAMP NOIT NULL DEFAULT CURRENT_TIMESTAMP

this way you need not do an explicit insert for time_stamp field.

EDIT:

If you want to edit the field from the form you can use the above def of time_stamp field and include the time_stamp value while doing the insert. It should be in the format:

'YYYY-MM-DD HH:MM:SS'

Note: timestamp is a MySQL reserved word. I've renamed it to time_stamp.

Answer 5

TIMESTAMP is a reserved MySQL word. You will need to place backticks around it if you wish to use it as a field name. However, I recommend changing it.

You should also make it a DATE or DATETIME field as that's the kind of data you're actually storing in it.