php and sqlite
Forum: myDSL Extensions (deprecated)
Topic: php and sqlite
started by: ypx
Posted by ypx on Oct. 21 2004,13:21
all is in the subtitle :does the php.tar.gz found in the repository provide sqlite support ?
i know that such a support isn't embeded by default in php 4.x but that it can be added via a php extension...
as DSL comes with sqlite packages i think that it should be very useful to add this, if it's not allready done... or perhaps upgrade the php.tar.gz to version 5.x
oh, i almost forgot : thanks a lot for the php package Robert...
Posted by roberts on Oct. 21 2004,13:58
Short answer no. When I first found sqlite, there was no support for sqlite in php. I don't think php was even aware of sqlite. And friend of mine and I together wrote our own sqlite library to work with php and sqlite. I am sure that our library is probably not as robust as what the php team is offering today. But we used own home grown library and it made it into several products that we later made for a company. I have been so busy with new features and planning for future versions of DSL, I have not been able to get back to update some of my early extensions. But, also, I wanted the original extensions that I started to be examples of what can be done, hoping that the user community would take over. I did not want to monolopize the creation of extensions. Of course, I am really pleased with the user community's contributions. A rebuild with sqlite support would be a good idea, especially since there are now many nice sqlite database admin tools written in php.
Posted by ypx on Oct. 22 2004,09:04
After googling to see what was required to include sqlite support into php4, i can see some problems coming.first, it seem that the php4-sqlite extension depends upon newer packages versions than the ones included in dsl.
then, it seems to require some sort of compilation, and i don't own a proper development environnement... or can the ramdisk be used in such a way ?
i'm now thinking about a php5 dsl extension as it comes with sqlite support allready embedded...
any idea about how i could do it ? as i'm fairly new to the linux world, and to the dsl one of course
Posted by RoGuE_StreaK on Oct. 22 2004,12:48
So do you interact with sqlite the same as you would mysql? I've been learning php and mysql, and thus wanted to have a live mysql extension, but apparently this is a no-go?With sqlite, do you need to re-learn how to interact with the database etc., or is it all very similar, just somehow more "lightweight"?
Pardon my ignorance of what it's capabilities, or lack thereof, are.
Posted by ypx on Oct. 22 2004,13:09
well, it's very similar in use... with some differences :- sqlite can be implemented in a procedural or object-oriented way (for mysql it's only procedural)
- locking the db when writing into it
- use of triggers
etc...
you should see by yourself < on Zend website >
i'll be glad if someone could help me in the task of making a php5 dsl extension as i don't know where to start from...
Posted by Max on Oct. 22 2004,16:40
I'm sure you found this, but just in case:Article:
SQLite Support for PHP4
< http://www.phpbuilder.com/columns/farell20040824.php3 >
Posted by roberts on Oct. 22 2004,19:27
Hmmm, what to do. I just tested my old sqlite.php, a 201 line php script that provides an interface to sqlite. Actually I found something on the net way back in November 2001. I added many functions to emulate what was being used in the then current php to talk to mysql. So, it has:sqlite_query
sqlite_fetch_row
sqlite_fetch_array
sqlite_num_rows
sqlite_num-cols
sqlite_field_name
sqlite_col_names
Anyway, it seems to work with the current php.tar.gz and sqlite in DSL.
The problem I see is in any support that may be needed. I wrote these function on 11/27/2001 so they are php 4 and not the object stuff that is the trend or that which might be in php5. I do not currently use php, so I am not uptodate. Would this be useful?
Posted by ypx on Oct. 25 2004,06:35
well, it seems to be all that's really needed to begin working with sqlite,and for the object stuff, it's mostly a matter of code clarity so it's okay as one should always comment his sources.
Posted by ypx on Oct. 25 2004,16:26
i've been looking in the official docs concerning the php sqlite extension to find the correspondances between your version and the standard :sqlite_query has the same name
sqlite_fetch_row seems to be named sqlite_fetch_object
sqlite_fetch_array has the same name
sqlite_num_rows has the same name
sqlite_num-cols seems to be named sqlite_num_fields
sqlite_field_name has the same name
sqlite_col_names seems to be unrivaled in the standard extension
i've done this to allow a "light" replacement of the standard extension by your version in DSL
however, there seems to be a huge use of sqlite_udf_encode and sqlite_udf_decode in the docs, so i assume these might be nearly mandatory to use a nearly standard code.
Hope this will lead to a successful php/sqlite implementation in dsl
Posted by roberts on Oct. 25 2004,18:20
OK, You have encouraged me to post this. Remember I wrote before there was an official php interface to sqlite. I tried to make it look and work like the mysql interface at that time.I have re-posted php.tar.gz in the repository. It includes my simple sqlite interface library.
The following is an ultra simple example:
From /home/dsl create a database and table
$ sqlite db
> create table addressbook(name,address,csz,phone);
> insert into addressbook values("Robert","123 Third","90210","123-555-1234");
> select * from addressbook;
> .exit
Next install php.tar.gz via the mydsl system.
Then start Monkey
Then in /opt/monkey/htdocs make a simple test.php
<?
require "sqlite.php"
$db = "/home/dsl/db";
sqlite_connect($db) or die("Count not connect to database");
$query = "select * from addressbook";
$myResult = sqlite_query($query,$db) or die("Query Failed");
$myRow = sqlite_fetch_array($myResult);
$name = $myRow["name"];
?>
<HTML>
<title>Test Sqlite Interface</title>
<body>
<? print $name; ?>
</body>
</html>
Have Fun. If you improve my library, please let me know.
Posted by ypx on Oct. 26 2004,06:05
woho ! thanks you very very much !i'll be very happy to let you know if i find something i can improve.
Posted by RoGuE_StreaK on Oct. 27 2004,01:06
Hi Robert, just tried converting over a mysql thing I've been working on, came across possibly one thing, the use of "%" in mysql as a wildcard, sqlite doesn't seem to like it (unless I've done something wrong):Code example:
$db = "/home/dsl/db";
$TableName="addressbook";
sqlite_connect($db) or die("Count not connect to database");
$sql="SELECT *
FROM $TableName
WHERE firstName LIKE \"%$firstName%\" AND lastName LIKE \"%$lastName%\" AND address LIKE \"%$address%\" AND telephone LIKE \"%$telephone%\"
AND email LIKE \"%$email%\" AND dob LIKE \"%$day%$month%$year%\"
";
$result = sqlite_query($sql,$db) or die("Query Failed");
sqlite_close($Link);
Result:
sqlite_exec:
SELECT * FROM addressbook WHERE firstName LIKE "%%%" AND lastName LIKE "%%" AND address LIKE "%%" AND telephone LIKE "%%" AND email LIKE "%%" AND dob LIKE "%%%%"
sqlite_query result:
SQL error: near "%": syntax error
SQL error: near "%": syntax error
SQL error: near "%": syntax error
Query Failed
MySQL would use the % as a wildcard, so you'd get all results in the table coming up.
Posted by roberts on Oct. 27 2004,06:22
LIKE and % work in sqlite and the sqlite.php library.Here is a simple example:
1 <?php
2 require "sqlite.php";
3 $db = "/home/dsl/db";
4 sqlite_connect($db) or die("Could not connect");
5 $query = "select * from addressbook where name like 'Robert%'";
6 $results = sqlite_query($query,$db) or die("Query Filed");
7 $row_count = sqlite_num_rows($results);
8 ?>
9 <HTML>
10 <title>Test Sqlite interface</title>
11 <body>
12 <TABLE>
13 <?php
14 for ($row_num=0; $row_num < $row_count; $row_num++) {
15 $row = sqlite_fetch_array($results);
16 $name = $row["name"];
17 $address = $row["address"];
18 print ("<TR>");
19 print ("<TD> $name </TD>\n");
20 print ("<TD> $address </TD>\n");
21 print ("</TR>\n");
22 }
23 ?>
24 </TABLE>
25 </body>
It can get confusing with the various quotes etc.