How should I handle INSERT if UPDATE fails

Question

This piece of code is giving me some issues now. For some reason when you do a submit with the checkbox checked, it will skip the UPDATE and go straight to the INSERT creating another entry when it should just UPDATE.

Is there a better way to handle scenarios like this where you want to INSERT if an entry is not existent?

if(isset($_POST['R_domain'])){
    $_reseller="20217";
    $_DOMAIN=$_POST['R_domain'];
    $_COMPANY_NAME=$_POST['company_name'];
    $_COMPANY_EMAILS=$_POST['company_email'];
    $_PHONE_NUMBER=$_POST['phone_number'];
    $_ADDRESS=$_POST['street_address'];
    $_CITY=$_POST['city'];
    $_STATE=$_POST['state'];
    $_ZIPCODE=$_POST['zipcode'];
    if(isset($_POST['hmbill'])) {
        $_HMBill = "1";
    }
    else if(!isset($_POST['hmbill'])) {
        $_HMBill = "0";
    }
    $_DEPT="NA";

    $conn = mysqli_connect("", "", "", "");
    $_sql_update="UPDATE company SET DOMAIN='$_DOMAIN', COMPANY_NAME='$_COMPANY_NAME', COMPANY_EMAILS='$_COMPANY_EMAILS', PHONE_NUMBER='$_PHONE_NUMBER', ADDRESS='$_ADDRESS', CITY='$_CITY', ZIPCODE='$_ZIPCODE',STATE='$_STATE',DEPT='$_DEPT',HMBILL='$_HMBill' WHERE `DOMAIN` = '$_DOMAIN'";
    mysqli_query($conn, $_sql_update);
    if(mysqli_affected_rows($conn)>0){}
    else {
        $_sql_insert = "INSERT INTO `company` (ID,RID,DOMAIN,COMPANY_NAME,COMPANY_EMAILS,PHONE_NUMBER,ADDRESS,CITY,ZIPCODE,STATE,DEPT,HMBILL) VALUES('','$_reseller','$_DOMAIN','$_COMPANY_NAME','$_COMPANY_EMAILS','$_PHONE_NUMBER','$_ADDRESS','$_CITY','$_ZIPCODE','$_STATE','$_DEPT','$_HMBill')";
        mysqli_query($conn, $_sql_insert);
    }

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, May 10 '17 at 21:04
Anybody know for sure which IF that IF ELSE belongs with? The OUTER IF or the INNER IF??? — RiggsFolly, May 10 '17 at 21:08

score 1 · Answer 1 · answered May 10 '17 at 21:09

I think what's happening is, you check after your update query, if any row has been updated. If all the data in the columns remains the same after update, the mysqli_affected_rows function will return 0, thereby moving your code to the insert query.

What you should do is do a Select query to check if a record exists WHERE DOMAIN = '$_DOMAIN'. If it does, do an update, else do an insert. Modify your current if else to this. Eg:

Samih · Answer 2 · 2017-05-10T21:30:12.700

If you want to update information if the record exists then you could use the "ON DUPLICATE" key word in your SQL. Here is an example:

 INSERT INTO
  `company`
(ID, RID, DOMAIN, COMPANY_NAME, COMPANY_EMAILS, PHONE_NUMBER, ADDRESS, CITY, ZIPCODE, STATE, DEPT, HMBILL)
VALUES(
  NULL,
  '$_reseller',
  '$_DOMAIN',
  '$_COMPANY_NAME',
  '$_COMPANY_EMAILS',
  '$_PHONE_NUMBER',
  '$_ADDRESS',
  '$_CITY',
  '$_ZIPCODE',
  '$_STATE',
  '$_DEPT',
  '$_HMBill'
)
ON DUPLICATE KEY UPDATE
`ADDRESS` = VALUES('$_ADDRESS'),
`CITY`    = VALUES(`$_CITY`),
`ZIPCODE` = VALUES(`$_ZIPCODE`)

This SQL would update address, city, zipcode if this insert is a duplicate. You should of course make changes to suit the SQL code to your needs.

I hope this helps.

How should I handle INSERT if UPDATE fails

2 Answers2