Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
PHP 101 (part 9): SQLite My Fire!
Get to grips with SQLite, the new database in PHP 5.x.

| Anatomy Class |

Now, use PHP to communicate with SQLite, generate the same result set and format it as an HTML page. Here's the code:


<html>
<head></head>
<body>

<?php
// set path of database file
$db = $_SERVER['DOCUMENT_ROOT'] . "/../library.db";

// open database file
$handle = sqlite_open($db) or die("Could not open database");

// generate query string
$query = "SELECT * FROM books";

// execute query
$result = sqlite_query($handle, $query) or die("Error in query: ".sqlite_error_string(sqlite_last_error($handle)));

// if rows exist
if (sqlite_num_rows($result) > 0) {
    // get each row as an array
    // print values
    echo "<table cellpadding=10 border=1>";
    while($row = sqlite_fetch_array($result)) {
        echo "<tr>";
        echo "<td>".$row[0]."</td>";
        echo "<td>".$row[1]."</td>";
        echo "<td>".$row[2]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}

// all done
// close database file
sqlite_close($handle);
?>

</body>
</html>
[code]

If all goes well, you should see something like this:

Output image

If you remember what you learned last time, the PHP script above should be easy to decipher. In case you don't, here's a fast rundown:

1. The ball starts rolling with the sqlite_open() function, which accepts the name of the database file as argument and attempts to open it. If this database file cannot be found, an empty database file will be created with the supplied name (assuming the script has write access to the directory).

[code]
<?php
$db = $_SERVER['DOCUMENT_ROOT'] . "/../library.db";
$handle = sqlite_open($db) or die("Could not open database");
?>


The database file needs to be kept somewhere it can't be accessed through the browser by visitors to your site. That means that you need to create it outside your Web server document root, in a directory that allows your scripts read/write permissions. Web hosting companies generally will offer a space above your Web-visible directory where you can do this. In this case, $_SERVER['DOCUMENT_ROOT'] . "/.." is the directory directly above your Web-visible directory.

If successful, the sqlite_open() function returns a handle to the file, which is stored in the variable $handle and is used for all subsequent communication with the database.
     
2. The next step is to create and execute the query, with the sqlite_query() function.


<?php
$query = "SELECT * FROM books";
$result = sqlite_query($handle, $query) or die("Error in query: ".sqlite_error_string(sqlite_last_error($handle)));
?>


This function also needs two parameters: the database handle and the query string. Depending on whether or not the query was successful, the function returns true or false; in the event of a failure, the sqlite_error_string() and sqlite_last_error() functions can be used to display the error that took place.

3. If sqlite_query() is successful, the result set returned by the query is stored in the variable $result. You can retrieve the records in the result set with the sqlite_fetch_array() function, which fetches a single row of data as an array called $row. Fields in that record are represented as array elements, and can be accessed using standard index notation.

Each time you call sqlite_fetch_array(), the next record in the result set is returned. This makes sqlite_fetch_array() very suitable for use in a while() loop, in much the same way as mysql_fetch_row() was used earlier.


<?php
if (sqlite_num_rows($result) > 0) {
  echo "<table cellpadding=10 border=1>";
  while($row = sqlite_fetch_array($result)) {
      echo "<tr>";
      echo "<td>".$row[0]."</td>";
      echo "<td>".$row[1]."</td>";
      echo "<td>".$row[2]."</td>";
      echo "</tr>";
  }
  echo "</table>";
}
?>


The number of records returned by the query can be retrieved with the sqlite_num_rows() function. Or, if what you're really interested in is the number of fields in the result set, use the sqlite_num_fields() function instead. Of course, these are only applicable with queries that actually return records; it doesn't really make sense to use them with INSERT, UPDATE or DELETE queries.

4. Once you're done, it's a good idea to close the database handle and return the used memory to the system, with a call to sqlite_close():


<?php
sqlite_close($handle);
?>


In PHP 5.x, you can also use the SQLite API in an object-oriented way, wherein each of the functions above becomes a method of the SQLiteDatabase() object. Take a look at this next listing, which is equivalent to the one above:

[code]
<html>
<head></head>
<body>

<?php
// set path of database file
$file = $_SERVER['DOCUMENT_ROOT'] . "/../library.db";

// create database object
$db = new SQLiteDatabase($file) or die("Could not open database");

// generate query string
$query = "SELECT *  FROM books";

// execute query
// return result object
$result = $db->query($query) or die("Error in query");

// if rows exist
if ($result->numRows() > 0) {
    // get each row as an array
    // print values
    echo "<table cellpadding=10 border=1>";
    while($row = $result->fetch()) {
        echo "<tr>";
        echo "<td>".$row[0]."</td>";
        echo "<td>".$row[1]."</td>";
        echo "<td>".$row[2]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}

// all done
// destroy database object
unset($db);
?>

</body>
</html>
[code]

Here, the new keyword is used to instantiate an object of the class SQLiteDatabase() by passing the object constructor the name of the database file. If the database file does not already exist, a new database file is created. The resulting object, stored in $db, then exposes methods and properties to perform queries. Every query returns an instance of the class SQLiteResult(), which in turn exposes methods for fetching and processing records.

If you look closely at the two scripts above, you'll see the numerous similarities between the procedural function names and the object method names. While the correspondence between the two is not perfect, it's usually close enough to make it possible to guess the one if you know the other.


How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback