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

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.

CodePudding user response：

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.