Connect to SQLite using PHP - Cannot Connect

Question

I'm trying to use PHP to connect to SQLite. I created a database by importing a CSV file into the tables for three tables. However, I'm unable to connect using the following code:

$dbhandle = sqlite_open('db/pokedex.db', 0666, $error);
if(!$dbhandle) die ($error);

This returns the following error:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in /pokedex/configpokedexdb-sqlite.php on line 12 file is encrypted or is not a database

Googling told me I might have a version mismatch. Despite finding some SQLite3 mentions in my phpinfo(), I decided it might still be a problem so I tried the following suggested code:

try
{
    //connect to SQLite database

    $dbhandle = new PDO("sqlite:db/pokedex.db"); //sqlite:VPN0.sqlite
   // echo "Handle has been created ...... <br><br>";

}
catch(PDOException $e)
{
    echo $e->getMessage();
    echo "<br><br>Database -- NOT -- loaded successfully .. ";
    die( "<br><br>Query Closed !!! $error");
}

After which I received the following error:

Warning: sqlite_exec() expects parameter 1 to be resource, object given in /home/rawdco81/public_html/pokedex/index-sqlite.php on line 53

Before this, I tried running new PDO("sqlite:VPN0.sqlite"); which was what the site provided, but that was obviously wrong because it didn't point to my .db file at all. You'll see this piece of code in the comments beside the function call.

I'm having a hard time just connecting to the database...What is the proper way to do this?

Also, I'm running PHP Version 5.2.13.

EDITED: I pasted the wrong error message in the wrong place.

A few quick questions: 1. Is this an sqlite3 file 2. Are you able to connect w/ sqlite3 on the CLI (assuming answer to 1. is yes) 3. What are the permissions of the database file? — quickshiftin
– quickshiftin, Commented Oct 31, 2012 at 2:59
1. Yes, it is an sqlite3 file. 2. I was able to connect with it before I uploaded it to my site. I don't have command line access to my website, though. 3. 644 at the time of writing, but I'm going to try 777 now that you mention it. — muttley91
– muttley91, Commented Oct 31, 2012 at 3:06
aannnd 777 didn't change anything still getting file is encrypted or is not a database — muttley91
– muttley91, Commented Oct 31, 2012 at 3:10

quickshiftin · Accepted Answer · 2012-10-31 03:48:27Z

1

Now that I look more closely.., I think you are connecting successfully in the second code segment! You shouldn't be using sqlite_exec in tandem w/ PDO though; those are two different PHP interfaces into SQLite.

The reason the first bit of code bombed is because the legacy library doesn't support PDO v3. The second bit of code is bombing once you try to run sqlite_exec against the PDO object I presume.

I bet if you put a var_dump($dbhandle); after the try/catch you'll see you've got an initialized PDO object.

Just read up on using PDO to run queries and you should be golden!

answered Oct 31, 2012 at 3:48

quickshiftin

70.4k12 gold badges72 silver badges99 bronze badges

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

4 Comments

muttley91 Over a year ago

So far, it seems like what you say is correct. It's a shame as the other way looked far neater, but this works as well. Thanks!

muttley91 Over a year ago

Perhaps it's my inexperience, but PDO seems pretty unfriendly. MySQL and SQLite are both nicely featured by PHP. PDO, on the other hand, doesn't seem to have a simple function for numrows.

quickshiftin Over a year ago

PDO is an abstraction mainly focused on a uniform API across various database backends. It doesn't provide some of the convenience methods like the original mysql library. AFAIK, there is no db lib other than PDO to hit sqlite3 through PHP.

quickshiftin Over a year ago

Regarding the num rows, have a look at PDOStatement::rowCount, there's a warning that it's not entirely portable for SELECT statements, but if it works for SQLite, should be good enough for the time being.

Collectives™ on Stack Overflow

Connect to SQLite using PHP - Cannot Connect

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related