How do I make my MySQL table automatically make a timestamp when new data is added?
I am trying to create a MySQL table with three columns. Two colummns are for data and a third the time at which the data is inserted. When I create the table, how do I write out the command to tell MySQL to create a timestamp column?
<?php
if (!isset($_POST['submit'])) {
?>
<form action="" method="post">
Table Name: <input type="text" size="40" maxlength="90" name="table"><br>
Column 1: <input type="text" size="40" maxlength="90" name="col1"><br>
Column 2: <input type="text" size="40" maxlength="90" name="col2"><br>
Column 3: <input type="text" size="40" maxlength="90" name="col3"><br>
<input type="submit" name="submit" value="Create Table!">
</form>
<?php
} else {
$table = $_POST['table'];
$col1 = $_POST['col1'];
$col2 = $_POST['col2'];
$col3 = $_POST['col3'];
mysql_query("CREATE TABLE $table(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
$col1 VARCHAR(50),
$col2 VARCHAR(90),
$col3 VARCHAR(200))")
or die(mysql_error());
echo "Success! Your table \"$table\" has been created!";
}
?>
in your SQL query, use the mysql function curdate()
example:
INSERT INTO table (column, date, column) VALUES (‘value’, curdate(), ‘value’)
First, don’t use "timestamp": that is UNIX time stamp: the number of seconds since January 1 1970 00:00:00 GMT
Set the column variable as "datetime" type with the default value 0000-00-00 00:00:00 (for a format YYYY:MM:DD HH:MM:SS), and call this column "datum" (don’t use "date", "datetime" or "timestamp"!: reserved words)
Advantage of this format: easy sorting!
When inserting, use:
$datum = date ( "Y-m-d H:i:s");
This will get current time and date, correctly formatted.
Your sql:
$sql = "insert into `tablename` (`field1`,`field2`,`datum`)
values (‘ " . $f1val . " ‘ , ‘ " . $f2val . " ‘ , ‘ " . $datum . " ‘ ) ";