Accessing SQLite from PHP5?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Accessing SQLite from PHP5?

Gilles Ganault-3
Hello

I'd like to use SQLite from some PHP5 scripts, either on Linux or
Linux.

What is the right way to set things up so that I can use a recent
release of SQLite (apparently, the SQLite that comes with PHP by
default is a 2.x release)?

Thank you.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Swithun Crowe
Hello

GG> I'd like to use SQLite from some PHP5 scripts, either on Linux or
GG> Linux.

A wise choice either way!

GG> What is the right way to set things up so that I can use a recent
GG> release of SQLite (apparently, the SQLite that comes with PHP by
GG> default is a 2.x release)?

If you use PDO, then you get access to Sqlite 3.x databases:

http://www.php.net/manual/en/ref.pdo-sqlite.php

Swithun.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Gilles Ganault-3
On Tue, 18 May 2010 14:30:06 +0100 (BST), Swithun Crowe
<[hidden email]> wrote:
>If you use PDO, then you get access to Sqlite 3.x databases:
>
>http://www.php.net/manual/en/ref.pdo-sqlite.php

Thanks for the input. After following the following article, I
successfully installed Lighttpd in FastCGI mode + PHP5 and PDO to
access SQLite:

www.kernelhardware.org/lighttpd-and-php/

I'm having a problem, though, writing to a SQLite database located in
Lighttpd's www directory (/srv/www/lighttpd).

Could it be that the Www directory is off-limit to PHP scripts in
write mode? FWIW, PDO can succesfully find and open db.sqlite:
=============
<?php

try {
        #TEST $dbh = new PDO("sqlite:./doesnt.exist.sqlite");
        #SQLSTATE[HY000] [14] unable to open database file

        #$dbh = new PDO("sqlite:./db.sqlite");
        $dbh->exec("CREATE TABLE IF NOT EXISTS customer (id INTEGER
PRIMARY KEY, name VARCHAR(255))");
        $dbh->exec("INSERT INTO customer VALUES (NULL,'Dummy')");
        $dbh = null;

        print "Done";

} catch(PDOException $e) {
        echo $e->getMessage();
}

?>
=============

After running this script, the browser simply displays "Done", but
db.sqlite is 0-byte :-/

FYI, here's what I tried after successfully installing Lighttpd +
Lighttpd-fastci + PHP5 + PDO:
=============
# pwd
/srv/www/lighttpd

# ll
total 8
-rw-r--r-- 1 root root   0 May 18 15:27 db.sqlite
-rw-r--r-- 1 root root  20 May 18 15:15 info.php
-rw-r--r-- 1 root root 646 May 18 15:34 sqlite.php

# chown lighttpd.lighttpd ./*

<here, browser runs script>
http://localhost/sqlite.php:
"Done"

# ll
total 8
-rw-r--r-- 1 lighttpd lighttpd   0 May 18 15:27 db.sqlite
-rw-r--r-- 1 lighttpd lighttpd  20 May 18 15:15 info.php
-rw-r--r-- 1 lighttpd lighttpd 298 May 18 15:37 sqlite.php
=============

Any idea what it could be? Could it be because I'm using the FastCGI
mode?

Thank you for any hint.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Michal Seliga
I think this is not really sqlite related, i am using fastcgi with nginx
server and when security became in issue i had similar problems
quick solution - do 'chmod -R 777  /srv/www' and you will see if problem
is related to rights or if its something else

also i think you should use absolute paths, or make sure that active
directory is set where you need. my cgi scripts run for user www-data
and active directory was set to /var/www and not to script location

On 18. 5. 2010 15:49, Gilles Ganault wrote:

> On Tue, 18 May 2010 14:30:06 +0100 (BST), Swithun Crowe
> <[hidden email]> wrote:
>  
>> If you use PDO, then you get access to Sqlite 3.x databases:
>>
>> http://www.php.net/manual/en/ref.pdo-sqlite.php
>>    
> Thanks for the input. After following the following article, I
> successfully installed Lighttpd in FastCGI mode + PHP5 and PDO to
> access SQLite:
>
> www.kernelhardware.org/lighttpd-and-php/
>
> I'm having a problem, though, writing to a SQLite database located in
> Lighttpd's www directory (/srv/www/lighttpd).
>
> Could it be that the Www directory is off-limit to PHP scripts in
> write mode? FWIW, PDO can succesfully find and open db.sqlite:
> =============
> <?php
>
> try {
>         #TEST $dbh = new PDO("sqlite:./doesnt.exist.sqlite");
>         #SQLSTATE[HY000] [14] unable to open database file
>
>         #$dbh = new PDO("sqlite:./db.sqlite");
>         $dbh->exec("CREATE TABLE IF NOT EXISTS customer (id INTEGER
> PRIMARY KEY, name VARCHAR(255))");
>         $dbh->exec("INSERT INTO customer VALUES (NULL,'Dummy')");
>         $dbh = null;
>
>         print "Done";
>
> } catch(PDOException $e) {
>         echo $e->getMessage();
> }
>
> ?>
> =============
>
> After running this script, the browser simply displays "Done", but
> db.sqlite is 0-byte :-/
>
> FYI, here's what I tried after successfully installing Lighttpd +
> Lighttpd-fastci + PHP5 + PDO:
> =============
> # pwd
> /srv/www/lighttpd
>
> # ll
> total 8
> -rw-r--r-- 1 root root   0 May 18 15:27 db.sqlite
> -rw-r--r-- 1 root root  20 May 18 15:15 info.php
> -rw-r--r-- 1 root root 646 May 18 15:34 sqlite.php
>
> # chown lighttpd.lighttpd ./*
>
> <here, browser runs script>
> http://localhost/sqlite.php:
> "Done"
>
> # ll
> total 8
> -rw-r--r-- 1 lighttpd lighttpd   0 May 18 15:27 db.sqlite
> -rw-r--r-- 1 lighttpd lighttpd  20 May 18 15:15 info.php
> -rw-r--r-- 1 lighttpd lighttpd 298 May 18 15:37 sqlite.php
> =============
>
> Any idea what it could be? Could it be because I'm using the FastCGI
> mode?
>
> Thank you for any hint.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>  
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Swithun Crowe
In reply to this post by Gilles Ganault-3
Hello

GG> Could it be that the Www directory is off-limit to PHP scripts in
GG> write mode? FWIW, PDO can succesfully find and open db.sqlite:

The directory itself should be writeable by the lighttpd user, as sqlite
will want to create temporary journal files in the same directory. So
being able to write to the database file isn't enough.

You don't get the exception message because the exec method doesn't throw
an exception. It puzzled me for a bit.

Swithun.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Gilles Ganault-3
In reply to this post by Michal Seliga
On Tue, 18 May 2010 15:57:37 +0200, Michal Seliga
<[hidden email]> wrote:
>I think this is not really sqlite related, i am using fastcgi with nginx
>server and when security became in issue i had similar problems
>quick solution - do 'chmod -R 777  /srv/www' and you will see if problem
>is related to rights or if its something else

Thanks a lot. It was indeed due to . being owned by "root" instead of
"lighttpd" (it would be a good thing if the RPM had created and
chowned the www directory this way). I didn't think of this because
"ll" doesn't show hidden files, unlike "ls -al"

===========
# ls -al
drwxr-xr-x 2 root root  4096 May 18 16:02 .
===========
#chown lighttpd.lighttpd ./.
===========
<?php
try {
    #$dbh = new PDO("sqlite:/srv/www/lighttpd/db.sqlite");
    $dbh = new PDO("sqlite:db.sqlite");

    $dbh->exec("CREATE TABLE IF NOT EXISTS customer (id INTEGER
PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))");
    $dbh->exec("INSERT INTO customer (name) VALUES ('Dummy')");
    $sql="SELECT name FROM customer";
    foreach ($dbh->query($sql) as $row) {    
       print $row['name'] . "<p>\n";
    }  
   
    $dbh = null;
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>
===========

Works fine now, even with a path-less, relative filename. Thank you.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Gilles Ganault-3
In reply to this post by Swithun Crowe
On Tue, 18 May 2010 15:04:35 +0100 (BST), Swithun Crowe
<[hidden email]> wrote:
>You don't get the exception message because the exec method doesn't throw
>an exception. It puzzled me for a bit.

Right, it doesn't raise an exception :-/ I had to look at
/var/log/lighttpd/error.log.

Thank you.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

J. King-3
In reply to this post by Gilles Ganault-3
On Tue, 18 May 2010 09:49:49 -0400, Gilles Ganault  
<[hidden email]> wrote:

> On Tue, 18 May 2010 14:30:06 +0100 (BST), Swithun Crowe
> <[hidden email]> wrote:
>> If you use PDO, then you get access to Sqlite 3.x databases:
>>
>> http://www.php.net/manual/en/ref.pdo-sqlite.php
>
> Thanks for the input. After following the following article, I
> successfully installed Lighttpd in FastCGI mode + PHP5 and PDO to
> access SQLite:

FYI, PHP 5.3 provides an SQLite3 module <http://php.net/sqlite3> which  
offers a version of SQLite more up-to-date than that exposed by PDO.  
Whereas PDO is an abstracted interface, though, SQLite3 appears to be a  
slightly more direct mapping of the SQLite API, exposing more  
SQLite-specific features.

--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Gilles Ganault-3
On Wed, 19 May 2010 20:50:01 -0400, "J. King"
<[hidden email]> wrote:
>FYI, PHP 5.3 provides an SQLite3 module <http://php.net/sqlite3> which  
>offers a version of SQLite more up-to-date than that exposed by PDO.  
>Whereas PDO is an abstracted interface, though, SQLite3 appears to be a  
>slightly more direct mapping of the SQLite API, exposing more  
>SQLite-specific features.

Thanks for the tip. Does someone know if this direct access to SQLite
is kept up-to-date with SQLite?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Simon Slavin-3

On 20 May 2010, at 10:42am, Gilles Ganault wrote:

> Thanks for the tip. Does someone know if this direct access to SQLite
> is kept up-to-date with SQLite?

Releases of SQLite are far more frequent than new releases of PHP, so the answer won't help you much.  In addition, there's a policy about which version the three ways of using SQLite functions implement.  See the first comment, by Andrew Paul Dickey, on

<http://www.php.net/manual/en/book.sqlite.php>

There is documentation for direct support for SQLite version 3 at

<http://www.php.net/manual/en/intro.sqlite3.php>


You can find the version of SQLite supported by the sqlite_ calls using

<http://www.php.net/manual/en/function.sqlite-libversion.php>

You can find the version of SQLite supported by the sqlite3:: calls using

<http://www.php.net/manual/en/sqlite3.version.php>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Accessing SQLite from PHP5?

Gilles Ganault-3
On Thu, 20 May 2010 11:42:31 +0100, Simon Slavin
<[hidden email]> wrote:
>Releases of SQLite are far more frequent than new releases of PHP, so the answer won't help you much.

Thanks a bunch for the links.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users