CODEWITHSUNDEEP
phpmysqlsql
Ben Paton
Ben Paton

Reputation: 1442

SQL to only insert a new row into MYSQL table if id does not already exist

I have a mysql database table called sales with the columns id | timeStamp | timeString | EAN where I want to insert new values if the ID does not already exist in the table. For example I may want to add:

 9997a04fe3f932bf6f8e9d88f4b8dc96 | 0000003082461 | 11:07 (Thu. 22 May. 2014 | 1400716800

to the database table if the id '9997a04fe3f932bf6f8e9d88f4b8dc96' has not already been entered before.

The SQL I have written so far looks like this: (using 1234 as dummy values, there is already a row in the table with and id of 1)

 INSERT INTO `sales`(`id`, `timeStamp`, `timeString`, `EAN`) VALUES (1,2,3,4)
    WHERE NOT EXISTS (
        SELECT `id` FROM `sales` WHERE `id` = '1'
    )

Please help me to get this SQL statement working. At the moment this reports a syntax error of: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT `id` FROM `sales` WHERE `id` = 1 ' at line 2

Upvotes: 0

Views: 913

Answers (2)

Raja
Raja

Reputation: 26

Before the INSERT Statement you need to check whether the data exist or not. If exits just give a message to user data already exist or whatever you want.

Like this

     $result = mysqli_query ($con,"SELECT COUNT(id) FROM sales WHERE (id='$id')");
     $row = mysqli_fetch_row($result);
     $total_records = $row[0];
     if($total_records == 0)

     {
       INSERT INTO sales(`id`, `timeStamp`, `timeString`, `EAN`) VALUES        
       ($id,$timestamp,$timestring,$Ean);   

      } else
      { 
             --------------Enter-----
            ------------Error message------
      }

Upvotes: 0

Petr Jirouš
Petr Jirouš

Reputation: 161

Add unique index to ID column and then use INSERT IGNORE statement

Upvotes: 4

Related Questions