myDSL Extensions (deprecated) :: php and sqlite
woho ! thanks you very very much !
i'll be very happy to let you know if i find something i can improve.
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.
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.
original here.