Insert into MYSQL Database if not exists

Question

I'm trying to add a customer to the MySQL database I created. Whenever somebody orders an item on the online store, the customer is added to the database (I dont want duplicates). Here is my php code:

$sqlInsert = "INSERT INTO Customers (FirstName, Address, Phone)
VALUES (".$userName.",".$address.",".$phone.")";

if(mysqli_query($conn, $sqlInsert)) {
    echo "new member registered successfully!";
} else {
    echo "Error: " . $sqlInsert . "<br>" . $mysqli_error($conn);
}

I have looked into queries such as INSERT INTO... WHERE NOT EXISTS. But I don't understand the syntax for my case, and don't know if it would work. here is my MYSQL customer table code:

CREATE TABLE IF NOT EXISTS Customers (
PersonID INT(11) NOT NULL AUTO_INCREMENT, 
Email VARCHAR(100),
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(90),
Zip INT(10),
CustomerState VARCHAR(50),
Address VARCHAR(200),
Country VARCHAR(20),
Phone VARCHAR(50) NOT NULL,
PRIMARY KEY (PersonID)
);

What is the unique value ? name can`t be unique. is it phone number ? — User123456
– User123456, Commented Oct 17, 2017 at 7:20

Danyal Sandeelo · Accepted Answer · 2017-10-17 05:35:12Z

5

INSERT INTO  Customers (FirstName, Address, Phone) 
SELECT * FROM (SELECT '$firstName', '$address', '$phone') AS tmp
WHERE NOT EXISTS (
    SELECT FirstName from Customers WHERE FirstName= '$firstName'
)   LIMIT 1;

This will prevent based on the first name, you may use all these columns for checking, I assume the matching column should be email, you can use that.

I just added the parameters within the query for you to get an idea, use parameter binding to avoid sql injection.

OR

  select * from customers where .... //

Get the size of result set and if size > 0 that means there is a row already, so do not insert it.

Sql statement taken from MySQL: Insert record if not exists in table and modified.

answered Oct 17, 2017 at 5:35

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nawin Over a year ago

Nice Explanation @Danyal

rolinger Over a year ago

@DanyalSandeelo - could you, or someone, provide this example with parameter binding please. I see 4 variables in the above example...would that be 4 bindings, wiht the $firstname repeated twice? iE: array($firstName,$address,$phone,$firstName)

Nawin · Accepted Answer · 2017-10-17 05:35:40Z

Try select query before insert and check the rows...Try to use Mysqli Prepared statement. I do this code for your way...

    <?php 
    $sqlselect = "SELECT * FROM Customers WHERE FirstName = ".$userName." AND Address = ".$address." AND Phone = ".$phone;
    $exqry = mysqli_query($conn, $sqlselect);
    $cnt = count($exqry);
    if($cnt == 0){
      $sqlInsert = "INSERT INTO Customers (FirstName, Address, Phone)
      VALUES (".$userName.",".$address.",".$phone.")";

      if(mysqli_query($conn, $sqlInsert)) {
        echo "new member registered successfully!";
      } else {
        echo "Error: " . $sqlInsert . "<br>" . $mysqli_error($conn);
      }  
    }else{
echo "Member already in table.";
//do your update or other stuff.
}
    ?>

Collectives™ on Stack Overflow

Insert into MYSQL Database if not exists

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related