php查询错误sqlite附近的语法错误

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

嗨我是新的sqlite我试图用PHP执行sqlite数据库的查询,但**附近的语法错误**发生

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?

什么是错的?

1 个解决方案

#1

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

Sqlite支持复合主键,因此我通过此链接从手册中显示:

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

CREATE TABLE命令指定新表的以下属性:

...

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

可选地,表的PRIMARY KEY。支持单列和复合(多列)主键。

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

根据您的唯一键(主键或其他键,复合键或其他键),您可以使用OR IGNORE的sqlite功能忽略重复冲突,后跟更新语句。

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 *, 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.

为什么我说不管?因为INSERT由于唯一键冲突而失败,所以它会无声地失败。但是,该失败可能是由于单列唯一索引或两列的组合,但您需要设置第3列和第4列。因此,更新会将所有内容拖放,并确保行存在。

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

我将留给您考虑您的用例,以及更新声明(如果需要)。

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

重复冲突意味着您告诉数据库强制实现唯一性,无论是单列还是复合(列组合)。

#1