Delete the record if its exist , else insert

Question

I have a click favorite star , i want to check in data base if the record is exist it will delete the record , if it not exist it will insert the record ,what is the problem the record not inserted

     <?php
    include "config.php";
    header('Content-Type: application/json');
    $landmarkid = $_GET['landmarkid'];
    $userid = $_GET['userid'];


    try {
        $query = mysqli_query($con,"SELECT * from favourite WHERE userid =$userid AND L_ID = $landmarkid");



        if(mysqli_num_rows($query) > 0)
        {
            $q1 = mysqli_query($con,"DELETE from favourite WHERE userid =$userid AND L_ID = $landmarkid");

            if($q1){
                echo '{"Deleted":"true"}';
            }
            else {
                echo '{"Deleted":"false"}';
            }
       } 
       else {
            $q2 = mysqli_query($con,"INSERT INTO favourite (userid,L_ID) VALUES ( $userid, $landmarkid) ");

            if($q2){
                echo '{"inserted":"true"}';
            }
            else {
                echo '{"inserted":"false"}';
            }
        }

        } catch (Exception $e) {
        echo 'Caught exception: ',  $e->getMessage(), "\n";
    }
?>

Really? Make __sure__ that u understand difference between `mysqli_query` and `mysql_query` and then come again. — u_mulder, May 06 '17 at 08:02
And now you have to understand what this line means `$query = ($con,"SELECT * from favourite where userid ='$userid' AND L_ID = '$landmarkid'");` — u_mulder, May 06 '17 at 08:21
@u_mulder to see if there is a row or not and then store the value and check if its exist or not — roz ab, May 06 '17 at 08:28

score 0 · Answer 1 · answered May 06 '17 at 08:40

0

Try to add single quotation marks to your insert statement and see if it works. Change this statement;

$q2 = mysqli_query($con,"INSERT INTO favourite (userid,L_ID) VALUES ( $userid, $landmarkid) ");

To this;

$q2 = mysqli_query($con,"INSERT INTO favourite (userid,L_ID) VALUES ( '$userid', '$landmarkid') ");

Let me know if it helps or if you find a problem.

answered May 06 '17 at 08:40

Douglas Hosea

1,002
13
30

you do not need quote marks when insterting interger values. the *id*s I assume are integers. – Martin May 06 '17 at 10:52

score 0 · Answer 2 · answered May 06 '17 at 10:49

I have rewritten your code below.

Some points:

Your code was vulerable to SQL injection so assuming id is a numeric value I forced the input vars ($userid and $landmarkid) to be integers using (int) casting.
Your first checking query can return a COUNT value, it's better than returning a * and then you can check a specific value for your if statements, $result['numb'].
I have properly escaped your php variables in the SQL, but you really should be trying to use Prepared Statements for this.
I dont think you need the try{} catch {} here as your current code will never throw exceptions (as far as I'm aware)
Add a LIMIT to your delete statements so you can never delete more than an intended number of row. This acts as a failsafe so you don't inadvertantly manage to delete the whole table.

 <?php
 include "config.php";
 header('Content-Type: application/json');
 $landmarkid = (int)$_GET['landmarkid'];
 $userid = (int)$_GET['userid'];

 try {
    $query = mysqli_query($con,"SELECT COUNT(*) as numb FROM 
             favourite WHERE userid = ".$userid." AND 
             L_ID = ".$landmarkid);
    $result = mysqli_fetch_aray($query);

    if($result['numb'] > 0)
    {
        $q1 = mysqli_query($con,"DELETE FROM favourite 
              WHERE userid = ".$userid." AND L_ID = ".$landmarkid." 
              LIMIT 1");
       print "deleted";
    } 
    else {
        $q2 = mysqli_query($con,"INSERT INTO favourite (userid,L_ID)
              VALUES ( ".$userid", ".$landmarkid.") ");
        print "inserted";
     }
  } 
  catch (Exception $e) {
      echo 'Caught exception: ',  $e->getMessage(), "\n";
  }
  ?>

Delete the record if its exist , else insert

2 Answers2