Create Table with TIMESTAMP and MYSQLI Query

[MatthewPatten]

Hello everyone! I am trying to insert a student into a table (with TIMESTAMP; works with VARCHAR, not TIMESTAMP). Can anyone help?

 

Variable

$time_stamp = date("D M j G:i:s T Y"); 

Populate DB Query

("DROP TABLE IF EXISTS enrolled") || !$link->query("CREATE TABLE enrolled(course_id VARCHAR(50), student_id VARCHAR(50), user_ip VARCHAR(50), time_stamp TIMESTAMP(6))

Insert Query

INSERT INTO enrolled(course_id,student_id,user_ip,time_stamp) VALUES('$course','$number','$user_ip','$time_stamp')

Edited December 12, 2014 by MatthewPatten

[cyberRobot]

It looks like TIMESTAMP is formatted as "YYYY-MM-DD HH:MM:SS". More information can be found here:

http://dev.mysql.com/doc/refman/4.1/en/datetime.html

 

Also note that MySQL has some built-in functions for adding timestamps. Have you tried using NOW():

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now

 

Note that you can find more time-related functions here:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

[MatthewPatten]

 

It looks like TIMESTAMP is formatted as "YYYY-MM-DD HH:MM:SS". More information can be found here:

http://dev.mysql.com/doc/refman/4.1/en/datetime.html

 

Also note that MySQL has some built-in functions for adding timestamps. Have you tried using NOW():

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now

 

Note that you can find more time-related functions here:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

 

Finally got the NOW() function working!

Edited December 12, 2014 by MatthewPatten

[Barand]

The point of using a timestamp column is so you do not have to enter a datetime value - it is automatically entered.

 

For example

CREATE TABLE student (
  student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30),
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO student (name) VALUES ('Barand');

Then

mysql> SELECT * FROM student;
+------------+--------+---------------------+
| student_id | name   | created             |
+------------+--------+---------------------+
|          1 | Barand | 2014-12-12 14:54:09 |
+------------+--------+---------------------+