PHP PDO INSERT WHERE NOT EXISTS statement not working

Question

I have very strange error with insert query to MySQL throught PDO.

I want insert record in table if this record doesnt exists in this table yet.

$query = "INSERT INTO Phrases (KeyText)
            SELECT * FROM (SELECT :key_text) as tmp WHERE NOT EXISTS (SELECT 1 FROM Phrases WHERE KeyText = :key_text)";

    try
    {
        $preparedStatement = $db->prepare($query);
        foreach ($phrases as $phrase)
        {
            $preparedStatement->execute(array(':key_text' => $phrase));
            echo "-";
        }
    }
    catch(PDOException $e)
    {  
        echo $e->getMessage();  
    }

It throws exeption: Call to a member function execute() on a non-object.

I dont understand where is my error in SQL command. Other pieces of code is correct because when I changed SQL command to other (for example: SELECT :key_text as kt) it works correctly.

Thanks for answers.

UPDATE: The error is:

[0] => HY000 [1] => 1096 [2] => No tables used

How can I make correct SQL command for MySQL? Variant with

INSERT INTO Phrases (KeyText)
SELECT :key_text WHERE NOT EXISTS (SELECT 1 FROM Phrases WHERE KeyText = :key_text)

doesnt work in MySQL too.

UPDATE 2:

INSERT INTO Phrases (KeyText)
SELECT :key_text FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM Phrases WHERE KeyText = :key_text)

This query with DUAL table works!

Marc B · Accepted Answer · 2012-11-14T16:39:38.667

5

Your prepare call is failing, and you've simply assumed it succeeded.

Try

    $preparedStatement = $db->prepare($query) or die($db->errorInfo());

to catch the error.

edited Nov 14 '12 at 16:39

answered Nov 14 '12 at 16:23

Marc B

356,200
43
426
500

whoops, sorry. should be $db->errorInfo(). I'll edit the answer. – Marc B Nov 14 '12 at 16:39
The error is: [0] => HY000 [1] => 1096 [2] => No tables used – Dmitriy Nov 14 '12 at 16:41
Solution was founded in DUAL table. Thanks! – Dmitriy Nov 14 '12 at 17:14

score 2 · Answer 2 · answered Nov 14 '12 at 17:14

your update2 should work.

ALTERNATIVE:

have unique or primary key on field KeyText

ALTER TABLE `Phrases` ADD UNIQUE INDEX (`KeyText`);

and then use query:

INSERT IGNORE INTO Phrases (KeyText) VALUES (:key_text);

Regarding no changes being recorder please check that you have autocommit on or do commit

score 1 · Answer 3 · answered Nov 14 '12 at 16:22

1

Sounds like the prepare failed, hence you're attempting to call execute on an invalid object (not a prepared statement object like you're expecting). Check that the return value of prepare is in fact a PDOStatement object and not FALSE

answered Nov 14 '12 at 16:22

Cody Caughlan

32,456
5
63
68

score 1 · Answer 4 · edited May 23 '17 at 11:59

1

INSERT INTO Phrases (KeyText)
    SELECT :key_text
WHERE NOT EXISTS (SELECT * FROM Phrases WHERE KeyText = :key_text)

SQL Server - How to insert a record and make sure it is unique

edited May 23 '17 at 11:59

Community

1
1

answered Nov 14 '12 at 16:33

Jacob

1,052
8
10

I know, but your variant is not valid for mysql. You can check it owns. – Dmitriy Nov 14 '12 at 16:36

score 0 · Answer 5 · edited Mar 01 '13 at 08:39

0

wrong:

insert into table 
  select * from (select 1 a, 2 b ) t 
  where not exists(select 1 from t2 where t2.c =2)

right:

insert into table
  select * from (select 1 a, 2 b union select 1 a, 2 b) t
  where not exists(select 1 from t2 where t2.c =2)

edited Mar 01 '13 at 08:39

Andrew Barber

39,603
20
94
123

answered Mar 01 '13 at 08:24

myheadachecase

1

PHP PDO INSERT WHERE NOT EXISTS statement not working

5 Answers5

Linked