If not exist insert into table mysql problem

Question

I am trying to insert into my table, but only if the information doesn't exist already.

This works:

$sql = //"if not exists (select `id` from friends where `id` = :id) " . //ERROR!
"insert into `friends` (`id`, `fb_id`, `name`, `id2`, `fb_id2`, `name2`) " .
"values (:id, :fb_id, :name, :id2, :fb_id2, :name2)";
try 
{
  $stmt = $this->db->prepare($sql); 
  if (!$stmt)
  {
    $errorcode = (string)
    $stmt->errorCode(); 
    trigger_error("mysql errorcode : " . $errorcode, E_USER_ERROR);
    $data = array( "note" => "mysql error code: " . $errorcode );

    return json_encode( $data );    
  } 
  $stmt->bindParam(':id', $id, PDO::PARAM_INT);
  $stmt->bindParam(':fb_id', $fb_id, PDO::PARAM_INT);
  $stmt->bindParam(':name', $name, PDO::PARAM_STR);
  $stmt->bindParam(':id2', $id2, PDO::PARAM_INT); 
  $stmt->bindParam(':fb_id2', $fb_id2, PDO::PARAM_INT); 
  $stmt->bindParam(':name2', $name2, PDO::PARAM_STR); 
  $result = $stmt->execute();
  $stmt->closeCursor(); 
}       
catch (Exception $e)
{
  die ($e->getMessage() ); 
}

I tried if not exists ( select id ... etc as commeted out in the first line, but that didn't work. It returned error code 00000 with (string) $stmt->errorCode();, I googled it and it means: to many mysql connections, this can't be the problem as other queries work fine.

How do I insert into the table when not exist and how do I get mysql error information?

score 3 · Answer 1 · answered Apr 25 '11 at 17:25

3

I've never seen your syntax before, putting an exists clause before the insert clause, but the following should work.

INSERT INTO `friends`
SELECT :id, :fp_id, :name, :id2, :fb_id2, :name2
FROM `friends`
WHERE NOT EXISTS ( select `id` from friends where `id` = :id )
LIMIT 1;

Just realized that you'll need at least one record in your friends table before this'll go.

answered Apr 25 '11 at 17:25

Parris Varney

11,320
12
47
76

To add to the answer: if you do an insert ... select. MySQL will insert all rows that the select returns. If the select does not find anything, no insert will that place. – Johan Apr 25 '11 at 17:28
I am not an expert, but that looks like quite the roundabout way. It just feels funny. Is there no cleaner way? – MJB Apr 25 '11 at 17:29
@PMV, Nice code, but this will insert a 'random' row, don't you want to add an order by clause to the select to make sure you have some control over which row gets inserted? – Johan Apr 25 '11 at 17:30
@Johan - Your on dupe key solution is the correct one, but I don't think this will insert anything random, since it's selecting constant values. – Parris Varney Apr 25 '11 at 17:37
@PMV you are correct **assuming** id is a unique or primary key field, which we do not know for sure. – Johan Apr 25 '11 at 17:55

Johan · Answer 2 · 2011-04-25T17:53:40.413

insert IGNORE into `friends` (`id`, `fb_id`, `name`, `id2`, `fb_id2`, `name2`)
values (:id, :fb_id, :name, :id2, :fb_id2, :name2)

Or you want to prevent duplicate id's you can add a trigger

DELIMITER $$

CREATE TRIGGER bi_friends_each BEFORE INSERT ON friends FOR EACH ROW
BEGIN
  DECLARE TestId integer;

  SELECT id INTO TestId FROM friends WHERE id = new.id LIMIT 1;
  IF (id IS NOT NULL) THEN BEGIN
    /*force an error to prevent insert*/
    SELECT * FROM force_an_error_table_unwilling_to_insert_dup_key;
  END; END IF;
END $$

DELIMITER ;

Note that if id is a UNIQUE or PRIMARY KEY field inserting a duplicate key will create an error and rollback any pending transaction.
If you are using a non-transactional engine, MySQL will stop inserting rows at the error point leaving only part of your rows inserted.

Your question is vague as to why you want to do the IGNORE test. So I'll recap the options and the implications:

If you want a transaction to fail, set id as primary key and just insert as normal, the resulting conflict will generate an error and rollback the transaction.
If you want to just insert stuff with no worries about duplicate id's set field is to AUTOINCREMENT and replace the value for id with null. INSERT INTO table (id, fb_id,name,id,fb_id2,name2) VALUES (null,:fb_id,:name,:id2,:fb_id2,:name2)
If you just want the one insert not to succeed, do the INSERT IGNORE ... as stated above.
If you do not have transactions (MyISAM et al), you may have to do a test SELECT first and only INSERT a bunch of values after the test shows it is save to proceed.

@ganjan, please read updated answer. Anyway, making a field called 'id' **not** a primary key, is a bad idea! — Johan, Apr 25 '11 at 17:57

score 1 · Answer 3 · edited May 23 '17 at 10:24

1

You could use INSERT IGNORE assuming you have a unique index on id. See this post for discussion about the pros/cons: "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

edited May 23 '17 at 10:24

Community

1
1

answered Apr 25 '11 at 17:32

Brad

5,428
1
33
56

I was using `insert ignore`, did not work. Might be because the id is not unique like you said – ganjan Apr 25 '11 at 17:35
Yes, id needs to be unique. I would suggest adding that index so long as it makes sense across all applications that are going to use this table. – Brad Apr 25 '11 at 17:37

Interfector · Answer 4 · 2012-06-19T12:13:01.857

This solution works even if you do not have any rows in your table.

INSERT INTO t (
    x,
    y,
    z) (
        SELECT
            '00000',
            '11111',
            '22222'
        FROM
            DUAL
        WHERE
            NOT EXISTS (
                SELECT
                    1
                FROM
                    t
                WHERE
                        c = '00000'
                    AND x = '11111'
                    AND y = '22222'
            )
        LIMIT 1
    );

If not exist insert into table mysql problem

4 Answers4