sqlite php getting unrecognized token: ":" error while inserting and parameterized query

Question

I am working with sqlite for the first time.

Preparing a query string like

$articleInsertQuery = "INSERT INTO Articles VALUES (?, ?, ?, ?, ?)", ($i, $title, $content, $date, 93,);

It returns "Parse error". I also tried without passing parametrized query like

$articleInsertQuery = "INSERT INTO Articles VALUES ($i, $title, $content, $date, 93)";

ANd getting "Unable to prepare statement: 1, unrecognized token: ":" "

Any idea where I am doing wrong?

$query = $dbObject->prepare($articleInsertQuery); $query->execute(); — arnold, Jun 08 '13 at 13:41
class AppDBClass extends SQLite3{ function __construct() { $this->open('../Server/db/sqlite3_database.db'); } } And $dbObject = new AppDBClass(); — arnold, Jun 08 '13 at 13:52

Luigi Siri · Accepted Answer · 2013-06-08T15:31:58.097

2

@arnoldIf you are using PDO for that.

The way to prepare and execute your query would be as follows.

$dbObject = new PDO('sqlite:sqlitedb');// NEW LINE
$articleInsertQuery = "INSERT INTO Articles VALUES (?, ?, ?, ?, ?)";
$query = $dbObject->prepare($articleInsertQuery);
$query->execute(array($i, $title, $content, $date, 93));

EDIT:

See sqlite3 prepare.

$articleInsertQuery = "INSERT INTO Articles VALUES (:i, :title, :content, :date, :int)";
$query = $dbObject->prepare($articleInsertQuery);
$query->bindValue(':i', $i, SQLITE3_INTEGER);
$query->bindValue(':title', $title, SQLITE3_TEXT);
$query->bindValue(':content', $content, SQLITE3_TEXT);
$query->bindValue(':date', $date, SQLITE3_TEXT);
$query->bindValue(':int', 93, SQLITE3_INTEGER);    

$result = $query->execute();

Hope this helps.

edited Jun 08 '13 at 15:31

answered Jun 08 '13 at 13:59

Luigi Siri

2,068
2
19
27

For first solution it returns PHP Warning: SQLite3Stmt::execute() expects exactly 0 parameters, 1 given – arnold Jun 08 '13 at 14:36
@arnold... Try the lower one. – Luigi Siri Jun 08 '13 at 14:38
It will be like $query->execute(); Right? – arnold Jun 08 '13 at 14:41
Yeah, sorry... the copy/paste issue. – Luigi Siri Jun 08 '13 at 14:48
And I am getting this SQLite3Stmt::execute(): Unable to execute statement: constraint failed – arnold Jun 08 '13 at 14:48
Is date a date-type in the table scheme? – Luigi Siri Jun 08 '13 at 14:55
On the `date` line change _SQLITE3_TEXT_ to _SQLITE3_BLOB_. – Luigi Siri Jun 08 '13 at 15:28
Also, try to use the first solution with the new line I just added. Using PDO would be a better option. – Luigi Siri Jun 08 '13 at 15:37
Changed it getting same SQLite3Stmt::execute(): Unable to execute statement: constraint failed – arnold Jun 08 '13 at 15:38

sqlite php getting unrecognized token: ":" error while inserting and parameterized query

1 Answers1