Multiple INSERT queries using PDO & SQLite

Question

I have this sqlite3 db:

CREATE TABLE links (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT UNIQUE,
 link1 TEXT,
 link2 TEXT,
 link3 TEXT
);

And I am trying to insert multiple entries in the same query, this is my code:

$db = new PDO('sqlite:db.sqlite');

$sql = "INSERT INTO links VALUES";

$filelines = file('filename');

foreach($filelines as $key => $line)
{
 if(count($filelines)-1==$key)
  $sql .= "(NULL, '".trim($line)."', '', '', '');";
 else
  $sql .= "(NULL, '".trim($line)."', '', '', ''),";
}

$insert = $db->prepare($sql);

$insert->execute();

Single inserts works fine but with this code I get the php error:

Call to a member function execute() on a non-object

I've also tried creating the sql query like 'VALUES(...,...), VALUES(...,...),... but get the same error:

What am I doing wrong?

ircmaxell · Accepted Answer · 2010-12-16 19:21:28Z

2

SQLite doesn't support the VALUES (...), (...), (...) syntax (see the docs ). So you need to do only one row per INSERT query...

The reason for the error itself is that since there's a query error, PDO::prepare() returns false. You should check it to make sure the ->prepare() call worked:

$insert = $db->prepare($sql);

if (!$insert) {
    die($db->errorInfo());
    // Or you could throw an exception, or otherwise handle the error...
}

$insert->execute();

edited Dec 16, 2010 at 19:21

answered Dec 16, 2010 at 19:15

ircmaxell

166k36 gold badges269 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Justin Over a year ago

sqlite.org/lang_insert.html shows that multiple values should be able to be inserted. But when I'm testing it with SQLite3 and php it doesn't seem to.

mystery Over a year ago

@Justin That's because it was added in SQLite 3.7.11 and current releases have an older version: stackoverflow.com/a/19340769/736162

Collectives™ on Stack Overflow

Multiple INSERT queries using PDO & SQLite

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related