php query error sqlite near where syntax error

Question

hi i'm new in sqlite i'm trying to execute a query to sqlite database with php, but **near where syntax error ** occurs

here's the query:

"
  INSERT  INTO selectedObject ( museum , atwork , beaconCode , qrCode)
  VALUES ('".$museo."','" .$opera."','".$codiceBeacon."', '".$codice_qr_random."')
  WHERE NOT EXISTS(
                    SELECT museum , atwork
                    FROM selectedObject
                    WHERE museum = '".$museo."' AND atwork = '".$opera."'

                  )";

what's was wrong?

i try to modify the code without nested query and it works! how i can solve? — , Sep 10 '15 at 16:14
it's possible to put a row if not already exists when i put that row into db? — , Sep 10 '15 at 16:17
But why have you tagged this question `MySQL` if you're using SQLite? — Mark Baker, Sep 10 '15 at 16:18
@MarkBaker ok thank's....you suggest me to do another query to control it? — , Sep 10 '15 at 16:20

score 1 · Accepted Answer · edited May 23 '17 at 12:24

Sqlite supports composite primary keys, so I show this from the Manual from this link:

A CREATE TABLE command specifies the following attributes of the new table:

...

Optionally, a PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported.

Depending on your unique keys, primary or otherwise, composite or otherwise, you could use the sqlite functionality of OR IGNORE to ignore a duplicate clash followed by an update statement.

So it would become:

INSERT OR IGNORE INTO selectedObject ( museum , atwork , beaconCode , qrCode)
  VALUES ('".$museo."','" .$opera."','".$codiceBeacon."', '".$codice_qr_random."');

And (possibly) followed by an update regardless.

Why do I say regardless? Because if the INSERT fails due to unique key clash, it fails silently. But that failure may be due to a single column unique index or a composite of two columns, but you want columns 3 and 4 set. So the update would mop it all up and make sure that row is there.

I will leave it to you to think about your use-case, and the update statement if needed.

duplicate clash meaning you have told the database to enforce uniqueness, whether that is a single column or a composite (combination of columns).

php query error sqlite near where syntax error

1 Answers1