user4316754 user4316754 - 1 year ago 104
SQL Question

SQL Time Has Passed

I thought of making a time has passed since last time it has been inserted in SQLITE so my solution looks like this:

if($n==1) { $db->exec("INSERT INTO progress (user,time,count) VALUES ('$user',0,CURRENT_TIMESTAMP)"); }
//after 10 minutes $n becomes 2
if($n==2) { $db->exec("UPDATE progress SET time = CURRENT_TIMESTAMP - count"); }

The issue is that time remains to be 0 for some reason. Any ideas what I have wrong?

More details:

//what I display from this table is user and time
count = Time at creation of the row,
time = 0 at creation of the row,
//Updating the row after some time has passed
time = current_time - count

Answer Source


Your code is open to SQL injection! using prepared statements and binding your parameters is highly recommended to prevent malicious code being used on your database.

ANSWER for educational purposes:

  • you have a typo at: ('$user,0,. You should have two apostrophes, one before and one after: ('$user',0,

  • Also, after asking about the count word in your second query, you mentioned it was a column:

       "UPDATE progress SET time = CURRENT_TIMESTAMP - count"

Trying to subtract CURRENT_TIMESTAMP - count is like trying to subtract a number by an array: 4 - array("hi", 5);. It doesn't make sense and your database is probably misinterpreting what you are trying to do, hence causing your error.

But please use prepared statements and bind your parameters. Not only will then your code work, but it will be safe :)

On request by the OP, here is an example of a prepared statement:

Your first query $db->exec("... would turn into this:

$sql = "INSERT INTO progress (user,time,count) VALUES (:user, 0, CURRENT_TIMESTAMP)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':user', $user);

Here you are preparing your sql statement $sql and then binding your parameter $user. There are a lot of good tutorials online and it is highly recommended to learn this process well.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download