CODEWITHSUNDEEP
phpmysqldatabasetimestamp
Lucy Weatherford
Lucy Weatherford

Reputation: 5534

Retrieving timestamp from mysql row upon creation of new row entry

Is this valid?

    <?php
        $timestamp = mysql_query("INSERT INTO column1 (entry1, entry2) 
                                  VALUES ('$entry1', '$entry2') 
                                  AND SELECT timestamp");
    ?>

timestamp is a mysql column which generates a timestamp upon row creation.

e.g. -do I need to specify creation of "timestamp" row? (I think it is okay, configured to do it on its own) -can these two actions be executed within the same query? if not, how can I do it? I want to make sure that I get the timestamp, as I use it later in the code also as a unique identifier as this entry. how can I otherwise get it? (assuming there may be entries which are multiple and similar in many of the other row entries).

Thanks!

Upvotes: 0

Views: 494

Answers (4)

Falcon
Falcon

Reputation: 259

If you want to get the timestamp value exactly that is in your timestamp cell, you have to get insert id and then use that last insert id to get the time stamp for that id. Lets say,

$query = mysql_query('INSERT INTO my_table("username","password") VALUES("falcon","test") ');
$last_id = mysql_insert_id($query);  // This gets your id of the row inserted by $query
$timstamp = mysql_query('SELECT timestamp FROM my_table where id = "'.$last_id.'"')

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6106

I depends on what you need the info for. If you need it to be stored against other rows/entities and you want it to be consistent then I would store the date in PHP (possible once per request in a web app) and then use that throughout, e.g.

$currentTimestamp = time();
$timestamp = mysql_query("INSERT INTO column1 (entry1, entry2, timestamp_col) 
                              VALUES ('$entry1', '$entry2', $currentTimestamp);

That way everywhere you use the timestamp it will be consistent. Your request doesn't have to take seconds for the timestamp to vary within it.

Even if you're not storing it against anything else you still might want to use this method if you need to retrieve that info as the only other way is two queries and this would most likely be more efficient.

That doesn't mean it never makes sense to auto-populate a MySQL date field as it you're just logging that info for retrieving later then it of course makes sense to do it within MySQL.

Upvotes: 2

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39981

No, that isn't a valid syntax.

One alternative is to assign now() to a variable and use that in the timestamp column instead of the value automatically generated.

set @now = now();
insert into your_table (timestamp_column) values (@now);
select @now();

Upvotes: 1

Placido
Placido

Reputation: 1385

This is not a valid mysql statement. As an option you could use PHP function instead.

$sql = "INSERT INTO column1 (entry1, entry2) VALUES ('$entry1', '$entry2')";
$timestamp = mysql_query($sql) ? time() : null;

Upvotes: 1

Related Questions