CODEWITHSUNDEEP
phppdosqlitesql-insert
clueless.noob
clueless.noob

Reputation: 23

Multi-valued INSERT for SQLite3 using PDO

I am currently trying to use the multi-valued INSERT queries with SQLite3 and PDO.

I did some research and found that before SQLite version: 3.7.11 the multi-valued INSERT syntax was not supported. This has since (2012) changed.

My phpinfo() is informing me that: 

PDO Driver for SQLite 3.x   enabled
SQLite Library  3.7.7.1

Regardless of that, PDO doesn't seem to support using these kinds of INSERT queries using SQLite3.

My question is if there is any workaround this issue. I am working on an application that is compatible with both SQLite3 and MySQL. Seeing that both of them support multi-value inserts, I would hate to use two kinds of query and INSERT logic only because PDO is not up-to-date.

Some edits - adding code specifics:

Opening the DB connection:

public function useSQLite3($file)
{
    $dsn = "sqlite:$file";
    $this->dbService = new PDO ($dsn);
    $this->dbService->query('PRAGMA journal_mode=WAL;');
    $this->dbService->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
}

Method that handles the bulk insert to the DB:

public function bulkInsertLink(array $links)
{
    $insertRows = array();
    $placeholders = array();
    $j = 0;
    $i=0;
    foreach($links as $linkData) {
        $placeholders[$j] = '(';
        foreach($linkData as $columnData) {
            $placeholders[$j] .= '?,';
            $insertRows[$i] = $columnData;
            $i++;
        }
        $placeholders[$j] = rtrim($placeholders[$j], ',');
        $placeholders[$j] .= ')';
        $j++;
    }
    $query = 'INSERT INTO links (status, date, lang, group_ID, group_link_ID, link, sitemap_link) VALUES ';
    $query .= implode(',', $placeholders);
    $preparedQuery = $this->dbService->prepare($query);
    $preparedQuery->execute($insertRows);
}

$links is an array, where each element represents the information for one row to be inserted.

Upvotes: 2

Views: 622

Answers (1)

Magomogo
Magomogo

Reputation: 954

Using PDO you can do multi values inserts like this:

$statement = $pdo->prepare('INSERT INTO t VALUES (?, ?), (?, ?)');
$pdo->execute([1, 2, 3, 4]);

But I'd personally prepared a single insert statement and executed it multiple times with different parameters.

Upvotes: 1

Related Questions