0

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
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Ben Paton
  • 1,432
  • 9
  • 35
  • 59
  • Why you try to do this in SQL und not in php (read record with id and if not exists insert it). – Jens May 22 '14 at 10:29
  • 2
    you cant have a where clause in insert . You may use INSERT ... ON DUPLICATE KEY http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html or INSERT IGNORE – Abhik Chakraborty May 22 '14 at 10:30
  • What do you want to happen if there is a record already? Just skip the insert completely, or update some values? – Cylindric May 22 '14 at 10:31
  • There is a nice solution/explanation [here](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) – georstef May 22 '14 at 10:33
  • @Jens - just for future reference, doing things like this in PHP or any other language that needs to confirm data integrity on behalf of the database is bad beyond words. You do this on db level using unique constraint(s). With MySQL we have shortcuts `INSERT IGNORE` and `INSERT ... ON DUPLICATE KEY...` as mentioned previously. – N.B. May 22 '14 at 10:37

2 Answers2

4

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

Petr Jirouš
  • 161
  • 1
  • 15
0

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------
      }
Raja
  • 26
  • 4