Can't update then insert in SQL

Question

I am trying to write PHP code to update a value once it exist, and if it doesn't then insert it. Part of this function works which is the insert part however the update doesn't seem to work my code is below:

<?php
$id = $_GET['Track'];
$user = $_GET['User'];
$rating = $_GET['Rating'];
include("connect.php");
$query = "UPDATE `rating` SET `rating` = '$rating' WHERE track_id = '$id' AND user_id = '$user' AND rating_set=NOW()";
$sth = $dbc->query($query);
$rows = $sth->rowCount();  

if ($rows == 0) {
$query = "INSERT INTO rating values ('$id','$rating','$user',NOW())";
$results = $dbc->query($query); 
$rows = $results->rowCount(); 
}

/* output in necessary format */

header('Content-type: application/json; charset=utf-8');
echo $_GET['onJSONPLoad'];
echo "(" . json_encode($rows) . ")";


$dbc = null;
?>

rating_set most probably is the fault here because you try to match it by current time, im pretty sure inserted time differs from current time so you never getting a match. — arma
– arma, Commented Aug 10, 2013 at 16:23
Are you trying to do this: $query = "UPDATE rating SET rating = '$rating', rating_set=NOW() WHERE track_id = '$id' AND user_id = '$user'"; — Sameer Mahant
– Sameer Mahant, Commented Aug 10, 2013 at 16:29

juergen d · Accepted Answer · 2013-08-10 16:31:44Z

2

Your update query can't work.

You are only updating data where rating_set is exactly NOW(). That will most likely not fit on any data record. You probably only want to use the date part.

edited Aug 10, 2013 at 16:31

answered Aug 10, 2013 at 16:17

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ivan Yonkov Over a year ago

you are not forced to, if you use all of them

Kern Elliott Over a year ago

how can I then save the time when the query has been updated?

juergen d Over a year ago

@KernElliott: You should use NOW() to set the data, but you put it in the where clause to filter the data you want to update.

pinkal vansia · Accepted Answer · 2013-08-10 16:26:56Z

0

you are using NOW() in update query, which gives current time. Which will never be same!!! try removing '....AND rating_set=NOW()' from your update query.

answered Aug 10, 2013 at 16:26

pinkal vansia

10.4k7 gold badges52 silver badges63 bronze badges

2 Comments

Kern Elliott Over a year ago

how can I then save the time when the query has been updated?

pinkal vansia Over a year ago

In your original update query you are only updating 'rating' field. You are not updating 'rating_set', instead you are using it in 'where' clause. you should write your update query like..... "UPDATE rating SET rating = '$rating', rating_set = NOW() WHERE track_id = '$id' AND user_id = '$user'"

Collectives™ on Stack Overflow

Can't update then insert in SQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related