PHP PDO connecting to SQLITE db

Question

So this morning I try to connect to SQLite database using PDO and also create a table. Firstly, I create a file called db.sqlite, and then create my connection and execute a create table query, but the execute pdo function always returns false.

$pdo = new PDO("sqlite:db.sqlite");
$STH = $pdo->prepare(
'CREATE TABLE "users" (
     "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 
     "full_name" VARCHAR, 
     "description" TEXT, 
     "token" INTEGER);
    ');
 $STH->execute();

So what I did next is to remove the db.sqlite from the connection and replace with :memory: to create a db in memory, which works perfectly.

$pdo = new PDO("sqlite::memory:");

So I am confused, why can I use :memory: and not the file, and how do I fix it?

Below is the error I get when I enable exception:

To access a database on disk, append the absolute path to the DSN prefix. See - absolute. — u_mulder
– u_mulder, Commented Dec 21, 2015 at 10:25
@u_mulder, the absolute path? like right from the root dir, that doesn't seem so good, considering that my app might be distributed, and people will have different path where they will store the app — George
– George, Commented Dec 21, 2015 at 10:28
maybe useful? $dsn = "sqlite:" . __DIR__ .'/db.sqlite';. On a separate note: I define a constant for the home or top directory of my site., called APP_HOME_ROOT. All data can be found in a data directory under that root. so I define('APP_DATA_ROOT,' APP_DATA_ROOT . '/data');. And, yes - this works on windows as well. — Ryan Vincent
– Ryan Vincent, Commented Dec 21, 2015 at 11:04
oops - sould be : define('APP_DATA_ROOT', APP_HOME_ROOT . '/data'). whatever - you get the idea? ;-/ — Ryan Vincent
– Ryan Vincent, Commented Dec 21, 2015 at 14:14

George · Accepted Answer · 2015-12-31 00:03:39Z

1

The reason why it will not work is because the file database.sqlite was within the /var/www/ directory, this gives it an automatic RW-RW-R-- permission, meaning that you will not be able to change the structure of the file, I did two things and it worked, firstly I changed the owner of the www directory.

chown -R <your username>:<your username> www/*

Then changed the permission of the project directory

chmod -R 777 <project>/*

and it worked for me

answered Dec 31, 2015 at 0:03

George

3,9739 gold badges58 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

PHP PDO connecting to SQLITE db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related