Can't create and insert data into a table in SQLite

Question

I pasted three SQLite files into D:/Server/sqlite of my Windows-based local Apache server, opened sqlite3 and created test.sqlite by .open --new test.sqlite.

I'm trying to create a table and insert data by the following PHP code :

$pdo = new PDO('sqlite:D:\Server\sqlite\test.sqlite');

$pdo->exec('CREATE TABLE IF NOT EXISTS `tablename` (`id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL)');

$insert = $pdo->prepare("INSERT INTO tablename (id, name) VALUES (1, 'Test')");
$insert->execute();
$pdo->lastInsertId();

But I get the error :

Fatal error: Uncaught Error: Call to a member function execute() on bool

And test.sqlite is still empty. What's wrong? With MySQL this works fine.

Basically: You should be [checking for PDO errors](https://www.php.net/manual/en/pdo.error-handling.php). — KIKO Software, Oct 17 '22 at 17:40
The table probably already exists but has different columns. `CREATE TABLE IF NOT EXISTS` leaves the old table definition there. — Barmar, Oct 17 '22 at 17:43
@Barmar As I mentioned, the file `test.sqlite` is empty, so it has no data — stckvrw, Oct 17 '22 at 17:45

forpas · Accepted Answer · 2022-10-17T18:12:33.810

The CREATE TABLE statement is invalid, so the table is not created, which means that the INSERT statement fails.

The correct syntax to define an integer autoincremented column in SQLite is:

id INTEGER PRIMARY KEY

or:

id INTEGER PRIMARY KEY AUTOINCREMENT

The differences with your code are:

the use of the data type INTEGER (anything else will throw an error when used in conjunction with the keyword AUTOINCREMENT or if that is missing it will not create an autoincremented column)
the use of the keyword AUTOINCREMENT (and not AUTO_INCREMENT which works in MySql) after the keywords PRIMARY KEY

See the demo.

Can't create and insert data into a table in SQLite

1 Answers1