How to insert timestamp into mysql with php properly?


Problem :

Here is my table num structure:

mysql> show columns from num;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| ip    | char(20)  | YES  |     | NULL              |                             |
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

I insert record with the following codes:

<?php   
    $ip = $_SERVER['REMOTE_ADDR'];
    $time=$_SERVER['REQUEST_TIME'];
    $db=mysql_connect("localhost","root","passwd");
    $query="insert into  num(ip,time) values('$ip','$time')";
    mysql_select_db('numdb');
    mysql_query($query, $db);
    mysql_close();
    echo "ok";
?>      

The time is wrong after i inserted two records into table num,
What is matter with my database or php code?

mysql> select * from num;
+-----------+---------------------+
| ip        | time                |
+-----------+---------------------+
| 127.0.0.1 | 0000-00-00 00:00:00 |
| 127.0.0.1 | 0000-00-00 00:00:00 |
+-----------+---------------------+
2 rows in set (0.00 sec)


Solution :

To insert the current timestamp, use:

$query="INSERT INTO num(time) VALUES (CURRENT_TIMESTAMP)";

If your server time is different from what you want, you can add hours (or minutes, etc) to the timestamp:

$query="INSERT INTO num(time) VALUES (CURRENT_TIMESTAMP + INTERVAL 6 HOUR)";

Mysql Tutorials

Mysql Howto..

how to make quote of the day using php and mysql in order by id

How do you select records with a certain time range from MySQL using PHP?

How to add all list of values to mysql database and keep adding even if there are duplicate primary keys?

How to implement mysql compress() function in php

How to count a row in group but one number one time php mysql

how to join three or more tables in mysql

How to open remote mysql database on windows from command prompt

How to use LIKE for mysql search with JOIN and ORDER BY the count of most rows/votes in the vote table?

How to skip intermediate rows when importing .csv to MySQL

how to make a mysql query with 2 tables and foreign keys

How to return a JSON object from a mySQL query in node.js

How to join models in Laravel 5?

How can i count transactions executed in a day in mysql workbench?

MySQL: Two tables share common “date” column, how to ORDER dates together after a JOIN?

How to escape 'LIKE' statement in custom PDO query

How do I enlarge an EER Diagram in MySQL Workbench?

How mapping of cities in a country-map is done? ( so users can search for ads nearby their cities )

Mysql show data in Pivot View

How Can I make portable xampp project

How to convert Microsoft Excel xlsx file to mysql database .sql file [closed]

How to select unique value randomly from a table in heavy load server

How to run Django's test database only in memory?

How to create a MySQL user without password - needed for remote login - to be used in bash insert script?

How to set group_concat_max_len with MySQL and Hibernate

how to use regex in mysql UPDATE query?

How to “package” an SQL database

MySQL: how to subtract an update

How select these specific rows in MySQL?

How do I insert a horizontal rule after a set of PHP MySQL Select Queries?

How do I connect to MySQL from Ruby?