insert a record or if exist then update in mysql doesnt work

Question

i want that if a record doesnt exist i add it otherwise update it... but it doesnt work, whats the wrong with this code:

<?php
      $user_id=$_POST['user_id'];
      $user_email="user_email";
      $last_stage=$_POST['last_stage'];
      $score=$_POST['score'];
      $note=$_POST['note'];

      $con=mysqli_connect("localhost","ferfer","Drfrj","ferfw");
      $result = mysqli_query($con,"SELECT user_email FROM rating WHERE user_email='".$user_email."'");
      $num_rows = mysqli_num_rows($result);

      if ($num_rows > 0) {
        //echo "exist";
        mysqli_query($con,"UPDATE rating SET user_id=".$user_id.", user_email='".$user_email."', last_stage=".$last_stage.", score=".$score.", note='".$note."'  WHERE user_email='".$user_email."'";
        mysqli_close($con);
      }else{
        //echo "does not exist";
        mysqli_query($con,"INSERT INTO rating(user_id, user_email, last_stage, score, note)VALUES (".$user_id.",'".$user_email."',".$last_stage.",".$score.",'".$note."') ");          
        mysqli_close($con);
      }
 ?>

what do u mean?? doesn't work mean doesn't update or doesn't insert? — DevT, Apr 30 '13 at 07:25
Why you doing multiple queries? Use INSERT OR ON DUPLICATE KEY UPDATE like this `INSERT INTO tblclientoptions (ClientID,LateTarget) VALUES ($lid,$latetarget) ON DUPLICATE KEY UPDATE LateTarget=$latetarget` — Dave, Apr 30 '13 at 07:28

score 9 · Accepted Answer · edited May 23 '17 at 10:25

You can actually do it in a single query since MySQL has implemented INSERT ... ON DUPLICATE KEY UPDATE which basically INSERTs a record if it does not exists otherwise UPDATEs it.

The first thing you need to do is to add a UNIQUE column on the table. In your example I see that user_email is the column you are searching for existence. If this is not unique, you need to alter the table for UNIQUE constraint

ALTER TABLE rating ADD CONSTRAINT tb_uq UNIQUE(user_email)

after it has been implement, build a query like this,

INSERT INTO rating(user_id, user_email, last_stage, score, note)
VALUES($user_id, '$user_email', last_stage, score, '$note')
ON DUPLICATE KEY UPDATE
   user_id = $user_id, 
   last_stage = $last_stage, 
   score = $score, 
   note= '$note'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

score 3 · Answer 2 · answered Apr 30 '13 at 07:26

3

$user_email="user_email";

should be changed to

$user_email=$_POST['user_email'];

And missing ( simbol, as @Yogesh Suthar said. You should also consider escaping characters in strings, using i.e. mysql_real_escape_string function.

answered Apr 30 '13 at 07:26

Marek Lewandowski

3,291
1
20
26

score 2 · Answer 3 · answered Apr 30 '13 at 07:25

2

you forgot ) here

mysqli_query($con,"UPDATE rating SET user_id=".$user_id.", user_email='".$user_email."', last_stage=".$last_stage.", score=".$score.", note='".$note."'  
WHERE user_email='".$user_email."'");
                                   ^ // here

answered Apr 30 '13 at 07:25

Yogesh Suthar

30,424
18
72
100

Robert · Answer 4 · 2013-04-30T07:34:27.900

Better way is to use

REPLACE INTO `rating` (user_id,user_email,last_stage,score,note)
VALUES(@user_id,@user_email,@last_stage,@score,@note) WHERE user_email=@email

use also binding and prepared statements to make it more secure. Your code is very insecure because you have nor escape functions neither casting.

Example of using binding with PHP. $dbh is PDO object.

$stmt = $dbh->prepare("REPLACE INTO `rating` (user_id,user_email,last_stage,score,note)
VALUES(@user_id,@user_email,@last_stage,@score,@note) WHERE user_email=@email");
$stmt->bindParam('@name', (int)$user_id);
$stmt->bindParam('@user_email', $user_email);
$stmt->bindParam('@last_stage', $last_stage);
$stmt->bindParam('@score', $score);
$stmt->bindParam('@note', $note);

insert a record or if exist then update in mysql doesnt work

5 Answers5