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.