MYSQL if exists update, else insert. Almost works, except for 1 thing

Question

I'm working on a Flex/Php project. I have written this php script so that if an artist is not in the database yet, i insert him and put the like_score to 1. If he does already exists, we update the like_score by adding +1. This is what i have so far:

// Variables
$php_artist = $_POST["rma_artist"];


/*
* A simple query to know if the artist exists in the DB by its name. We can't use id since we won't get that value.
* if $numrows > 0, we have a match. We calculate the number of likes and then, we update.
* else, we insert.
*/
$query = "SELECT like_score FROM rma_likelist WHERE like_artist = '$php_artist'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$numrows = mysql_num_rows($result);

// UPDATE if there is a row that matches, we update
if ($numrows > 0) {
$newLikeScore = $row["like_score"] + 1;
$query = "UPDATE rma_likelist SET like_score = '$newLikeScore' like_artist = $php_artist";

if ( !mysql_query($query, $mysql_connection) ){
    die('ERROR: '. mysql_error() );
}

// id, name, likes, operation
$response = $php_artist_id.":::".$php_artist.":::".$newLikeScore.":::MYSQL UPDATE SUCCESSFULL";
}
// INSERT if no rows match, we insert a new record
else {
$query = "INSERT INTO rma_likelist (like_artist, like_score) VALUES ('$php_artist', '1')";

$message = "INSERT SUCCESSFULL, 1 Record Added";

if ( !mysql_query($query, $mysql_connection) ){
    die('ERROR: '. mysql_error() );
}

$new_id = mysql_insert_id();

// id, name, likes, operation
$response = $new_id.":::".$php_artist.":::1:::MYSQL INSERT SUCCESSFULL";
}



echo $response;

?>

(or see pastebin: http://pastebin.com/HwHYXaGP)

It almost works: If an artist isn't in the database yet, it will insert it and give it a like_score of 1. It also recognizes if an artist is already in the database, because it will not duplicate any artists. However, it will never update the like_score of a band that is already in the database, it always stays 1. Why does that happen? I've been looking for a looong time =/

Yes, artist name is unique. I can't use an unique id because i get the artist names from an api (aka i don't get id's, only artist names). — Kevin Verhoeven, Dec 18 '11 at 12:34

score 4 · Accepted Answer · edited May 23 '17 at 11:48

Your code is probably not working because your UPDATE statement is incomplete:

$query = "UPDATE rma_likelist SET like_score = '$newLikeScore' 
  /* missing WHERE keyword */ like_artist = $php_artist";

The statement should include ... WHERE like_artist = ...

I would have expected your mistake to cause a syntax error, and I see you're checking for a false result from mysql_query(), so I'm not sure why you haven't detected it.

Also if $php_artist is a string, you didn't quote it in the UPDATE statement.

You should also be careful of creating an SQL injection vulnerability.

That said, I suggest you can simplify your code a lot by using INSERT... ON DUPLICATE KEY UPDATE.

$query = "INSERT INTO rma_likelist (like_artist, like_score) VALUES (?, 1)
    ON DUPLICATE KEY UPDATE like_score = like_score + 1";

$stmt = $pdo->prepare($query);

$stmt->execute( array($php_artist) );

PS: I would also use PDO with query parameters, as shown above, instead of the obsolete ext/mysql.

score 1 · Answer 2 · answered Dec 18 '11 at 00:40

i think like_score will be in $row[0]["like_score"]

you are missing WHERE in update query $query = "UPDATE rma_likelist SET like_score = '$newLikeScore' where like_artist = $php_artist";

Where as in these cases , good practice is to make like_artist fied unique and using on duplicate key update feature of mysql.

fge · Answer 3 · 2011-12-18T00:26:39.810

0

You should use MERGE for what you want to do:

MERGE into rma_likelist using (select pk_artist_id from rma_likelist where pk_artist_id = theid)
when not matched then insert into rma_likelist values (...)
when matched then update rma_likelist set like_score = like_score + 1 where ...

(query not complete are there are some parts of your script which I don't understand)

The obvious advantage here is that you don't even need the query to check for the artist's existence in the first place -- one less source for bugs ;)

edited Dec 18 '11 at 00:26

answered Dec 18 '11 at 00:21

fge

110,072
26
223
312

I think you mean the $response? That is to return info to my Flex project. But the error is certainly not in that line since i can just check the database. I'll try MERGE and see if it works. Thanks. – Kevin Verhoeven Dec 18 '11 at 00:26
fwiw, MySQL does not support the standard SQL `MERGE` statement. – Bill Karwin Dec 18 '11 at 01:48

MYSQL if exists update, else insert. Almost works, except for 1 thing

3 Answers3