CODEWITHSUNDEEP
phpsqlite
Doe
Doe

Reputation: 35

php sqlite - copy table to another database

I want to copy table from another db file but I fail and I can't get why. This is my code:

 $db = new SQLite3($_SERVER['DOCUMENT_ROOT']."/db/098765.db");
 $sql = "ATTACH DATABASE 'admin.db' AS admin ;
         INSERT INTO 'table-1' SELECT * FROM 'admin.table-1';";
 $db->query($sql);

I've read all the questions on this topic on this site, but no answer helped me.

Giving the full path to ATTACH DATABASE doesn't work. Creating table before inserting data also doesn't work.

Upvotes: 0

Views: 1221

Answers (3)

hongkilldong
hongkilldong

Reputation: 63

You can get exact copy of table by performing the following set of SQL statements:

(In context of connection to destination database)

attach '<source-db-full-name>' as sourceDb;
select sql from 'sqlite_master' where type = 'table' and name = '<name-of-table>';

// Execute result of previous statement. 
// It will create empty table with 
// schema identical to schema of source table

insert into '<name-of-table>' select * from sourceDb.[<name-of-table>];
detach sourceDb;

Upvotes: 0

redneb
redneb

Reputation: 23850

The sqlite3 command line tool has a handy command called .dump that makes this task trivial:

sqlite3 admin.db '.dump "table-1"' | sqlite3 098765.db

This will create the table, all associated indexes and of course it will copy all the data.

Edit: For a more general solution, create a shell script (let's call it copy-table.sh) as follows:

#!/bin/bash
$src_db="$1"
$dst_db="$2"
$table="$3"

sqlite3 "$src_db" ".dump \"$table\"" | sqlite3 "$dst_db"

Then you can execute the script as follows

./copy-table.sh 'admin.db' '098765.db' 'table-1'

Obviously, you can execute the script anyway you want, e.g. from cron or from php.

Upvotes: 1

peterm
peterm

Reputation: 92785

  1. Properly quote database object identifiers (table/column names etc) in your INSERT statement. Use use double quotes instead of single ones, which are for string literals. Better yet don't use dashes or other restricted characters in object names if possible (stick with alphanumerics and underscore).

  2. Use exec() instead of query()

$dbPath = $_SERVER['DOCUMENT_ROOT'];
$adminDbPath = $dbPath; // Or something else

$db = new SQLite3("$dbPath/db/098765.db");
$db->exec("ATTACH DATABASE '$adminDbPath/admin.db' AS admin");
$db->exec('INSERT INTO "table-1" SELECT * FROM admin."table-1"');
     ^^^^              ^       ^                     ^       ^

Upvotes: 0

Related Questions