Geek asks and answers

Mysql: insert, if the line does not exist, else update .. is there a simpler command?

Learning Mysql and Having a Pile of Questions ...

I can achieve the desired effect by simple:

$mysqli->query("INSERT results SET user_id = '".$user_data[0]['user_id']."', logo_id = '".$mysqli->real_escape_string($_GET['logo_id'])."'");
$mysqli->query("UPDATE results SET result_tries = result_tries +1 WHERE logo_id = '".$mysqli->real_escape_string($_GET['logo_id'])."' AND user_id = '".$user_data[0]['user_id']."'");

I have a DB unique checkin the table, so logo_id and user_id must be unique. So if the line exists, the first request fails, and the second update is executed ... But this seems like a hack ... Is there a better way to do this?

+5
source share
2 answers

Try

$user_id = $user_data[0]['user_id'];
$logo_id = $mysqli->real_escape_string($_GET['logo_id']);

$sql = "INSERT INTO results (user_id, logo_id, result_tries) 
        VALUES ('$user_id', '$logo_id', 0)
            ON DUPLICATE KEY UPDATE result_tries = result_tries + 1";

$mysqli->query($sql);
+8
source

This is the most elegant solution I came up with, since I don't have a unique index column and I need to use the where clause to achieve my goal:

DELETE FROM assignment_status WHERE assignment_id = 114 AND user_id = 1;
INSERT INTO assignment_status (assignment_id, user_id, status, note) VALUES (114, 1, 2, "Some useful information goes here...");

Thanks to this thread (find an explanation there too).

+1

More articles:


All Articles