PHP/MySQL Help.

[Intrepid]

Hi guys, I just found this site on google and registered about 10 seconds ago. I have a problem inserting the current date into a MySQL database when a form is submitted. The code is posted below:

 

$con = mysql_connect("localhost") or die('Could not connect: ' . mysql_error());
$sql = "create database IF NOT EXISTS Articles";
mysql_query($sql,$con) or die('Could not create database: ' . mysql_error());
mysql_select_db("Articles", $con) or die('Could not select database: ' . mysql_error());
$sql = "create table IF NOT EXISTS Info
(
pk_Id int unsigned auto_increment,
subject varchar(100) not null,
name varchar(100) not null,
picname varchar(100) not null,
date varchar(100) not null,
article mediumtext not null,
category varchar(100) not null,
primary key(pk_Id),
unique id(pk_Id)
)";
mysql_query($sql,$con) or die('Could not create table: ' . mysql_error());
$S = $_POST['Subject'];
$N = $_POST['Name'];
$P = $_POST['Picname'];
$D = $_POST['Date'];
$A = $_POST['Article'];
$C = $_POST['Category'];
$sql = "INSERT INTO Info (pk_Id, subject, name, picname, date, article, category) VALUES (0,'$S','$N','$P','$D','$A','$C')";
mysql_query($sql,$con) or die('Could not insert data: ' . mysql_error());

 

And here's the error message I get when I try and submit the data.

 

Could not insert data: Unknown column 'date' in 'field list'

 

To be honest I have no idea what the problem is or what to do, but I figure it's somewhere in the creating of the table. I'm supposed to have this website done by Monday, so I really could use any advice or help here. Thanks.

[toplay]

Issues:

 

1) You should not use just "date" for a column name because it's not meaningful enough and more importantly it's a MySQL reserved word. Change it to something else..i.e. date_created

 

2) When holding date data, use MySQL date, datetime, or timestamp data types and not a VARCHAR. This will then allow you to use many available date and time functions on that column.

 

3) You need to validate all input (i.e. from $_POST['Date']) to make sure it's in YYYY-MM-DD format (for a date data type). If you simply want to insert todays date, just use curdate() (without quotes) as the column value instead.

 

4) You should verify every query first before proceeding to execute subsequent commands. Your create probably didn't work (because you're using 'date' as the column name). If you insist on using 'date' as the name of your column, then you must enclose it in backtick marks (i.e. `date`).

 

Good luck.

 

 

[Intrepid]

Thanks!!