Date

[ping_nz]

Hiyas, over the last few days ive been working with a simple databse I made which contains a listing of all of my audio cds. It has just two fields 'Artist' and 'CD Name'. What im trying to do is to create another field with date on it in the form of 28/03/2002 which gets entered everytime I add another cd to the DB. I spent the few days mostly trying to figure out how to do it but im stumped. I know how to do it so that I could enter the date manually through a http form (how artist and cd name gets entered) but id like it to get added automatically. Below is my code, if anyone could make the necceesary modifications or tell me what needs be added it would be greatly appreciated:

$db = mysql_connect('localhost','user','password');
mysql_select_db("db", $db);
$sql = "INSERT INTO CDS (Artist, CD Name) VALUES ('$artist','$cd_name')";
$result = mysql_query($sql,$db);
if ($submitted) {
echo "Artist = $artist <BR>";
echo "Album name = $cd_name <BR>";
echo "";
echo "Submission Successful";
}

Regards,

Stewart

[Anon]

Go to mysql

mysql>ALTER TABLE cds ADD COLUMN <column-name> TIMESTAMP;

Replace <column-name> with the name you want to give to your new date column. Avoid reserved words like "date" !

From the mysql documentation:

TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value. See section 6.2.2 Date and Time Types. A TIMESTAMP is always stored in 4 bytes. The M argument only affects how the TIMESTAMP column is displayed. Note that TIMESTAMP(X) columns where X is 8 or 14 are reported to be numbers while other TIMESTAMP(X) columns are reported to be strings. This is just to ensure that one can reliably dump and restore the table with these types! See section 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types.