How do I connect to an SQLite database with PHP?

Question

I have an SQLite database and am trying to connect to it with PHP. This is what I'm using:

<?php
    $dbconn = sqlite_open('combadd.sqlite');

    if ($dbconn) {
        $result = sqlite_query($dbconn,  "SELECT * FROM combo_calcs WHERE options='easy'");
        var_dump(sqlite_fetch_array($result, SQLITE_ASSOC));
    } else {
        print "Connection to database failed!\n";
    }
?>

However, I get this error:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in C:\xampp\htdocs\deepthi\combadd\combadd_db.php on line 4
Connection to database failed!

What's wrong and how can I fix it?

Hi @user, welcome to SO. I *strongly* recommend you use PDO - it's really easy, flexible, and more secure than whatever you (or I) will come up with by yourself (ourselves). http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ — Ben, May 24 '13 at 05:38

score 36 · Accepted Answer · edited Mar 29 '18 at 21:22

36

Try to use PDO instead of sqlite_open:

$dir = 'sqlite:/[YOUR-PATH]/combadd.sqlite';
$dbh  = new PDO($dir) or die("cannot open the database");
$query =  "SELECT * FROM combo_calcs WHERE options='easy'";
foreach ($dbh->query($query) as $row)
{
    echo $row[0];
}
$dbh = null; //This is how you close a PDO connection

edited Mar 29 '18 at 21:22

jsherk

6,128
8
51
83

answered May 24 '13 at 05:38

Antonio Carlos Ribeiro

86,191
22
213
204

it was amazing Antonio. i need to insert data from excel sheet into sqlite using php. can you please help me? thanks – user2412936 May 24 '13 at 06:27
3

You'll find some examples here: http://www.if-not-true-then-false.com/2012/php-pdo-sqlite3-example/. Also, you should consider using a framework to help doing those repetitive tasks like connecting to a database, insert, update, delete records. Take a look at Laravel: http://four.laravel.com/ – Antonio Carlos Ribeiro May 24 '13 at 06:34
do i need to install that one to read xls data from php code – user2412936 May 24 '13 at 06:49
I think you can use this library: https://code.google.com/p/php-excel-reader/. – Antonio Carlos Ribeiro May 24 '13 at 06:58
http://php-drops.blogspot.in/2011/04/read-excel-sheet-xls-file-with-php.html i am trying to use reader.php will it work? – user2412936 May 24 '13 at 07:30
How can I add `PRAGMA` to this query? – Hossein Dec 17 '14 at 09:32
Added `$dbh = null;` to close PDO connection. – jsherk Mar 29 '18 at 21:22
" sqlite: " at the begining on address is very important – faryar76 Dec 21 '21 at 07:47

score 10 · Answer 2 · answered May 24 '13 at 05:42

Connecting To Database Following PHP code shows how to connect to an existing database. If database does not exist, then it will be created and finally a database object will be returned.

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('combadd.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
?>

Now let's run above program to create our database test.db in the current directory. You can change your path as per your requirement. If database is successfully created then it will give following message:

Open database successfully

SELECT Operation

Following PHP program shows how we can fetch and display records

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('combadd.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      SELECT * FROM combo_calcs WHERE options='easy';
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "ID = ". $row['ID'] . "\n";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

score 3 · Answer 3 · answered May 24 '13 at 05:39

<?php

    if ($db = sqlite_open('sampleDB', 0666, $sqliteerror) ) { 
        $result = sqlite_query($db, 'select bar from foo');
        var_dump(sqlite_fetch_array($result) ); 
    } else {
        die($sqliteerror);
    }

?>

Make sure sqlite support is enable, check phpinfo()

One another solution to your problem is: Using sqlite3 module instead

class DB extends SQLite3
{
        function __construct( $file )
        {
            $this->open( $file );
        }
}

$db = new DB( 'sampleDB.sqlite' );

How do I connect to an SQLite database with PHP?

3 Answers3

Linked

Related