Penn State

Web Conference 2005

Writing PHP for ITS/ASET Web services

Saving Data in SQLite Database Files
<- Back - Saving Data in Files|Up |Tips on Using Remote Databases - Next ->

See also: Lesson on using SQLite from Perl/CGI

Today, the most popular and powerful method of storing data server-side is in relational databases. The most popular method to access and manipulate data in a database is via the Structured Query Language (SQL). The vendors of the various Relational Database Management Systems (RDBMS) each implement their own version of SQL, which largely resemble each other but still contain significant differences. Still, a database programmer versed in one variant can easily learn another.

While there is no fully featured RDBMS yet provided for use with php.scripts.psu.edu, a database library that implements most of the SQL92 standard features is available from php.scripts.psu.edu without need for a database server. SQLite is a database library that does this by storing data directly into files stored on disk. This lesson will explore using it.

  1. Create a new file with the following content:

    Update (2008/03/31): NOTE, this file is out of date. Please consider using the new PDO version.

    <html>
    <body>

    <?php

    // The file to be used as the database:
    $databasefile = "data/sqlite";

    // We will recreate the table after the first time so we can easily
    // update the table with new collumns in a single page load
    if( file_exists($databasefile) ){
                $file_exists=1;
    }
     
    // This will open a datase for use.  If the file does not yet exist,
    // it will create it.  Note: the script must have write access to
    // create the file and update it (it also creates temporary lock files).
    if( $db_handle = sqlite_open($databasefile, 0666, $sqlite_error) ){
        if(isset(    $file_exists)){
            sqlite_query($db_handle, "DROP TABLE table1");
        }
            sqlite_query($db_handle, "CREATE TABLE table1 (fname varchar(32), lname varchar(32), roster int)");
            sqlite_query($db_handle, "INSERT INTO table1 VALUES ('Jeff','D''Angelo',1)"
    );
            $query = sqlite_query($db_handle, "SELECT lname,fname,roster from table1 WHERE roster < 3");
            $result = sqlite_fetch_all($query);
        echo     "<table border=1><tr><th>Last Name</th><th>First Name</th><th>Roster #</th></tr>\n";
        foreach (    $result as $row){
            echo     "<tr><td>$row[lname]</td><td>$row[fname]</td><td>$row[roster]</td></tr>\n";
        }
        echo     "</table>\n";
            sqlite_close($db_handle);
    }else{
        die(    $sqlite_error);
    }
    ?>

    </body>
    </html>

  2. Save it as sqlite.php in your space. If you do not have a "data" folder as set in a former lession, you may get an error relating to creation of the database file. Make sure the "data" folder exists, is local to your script (in the same folder), and is writable:

  3. View the page. You should see a simple table rendered showing the contents of a table you created in a new database. Successive loads will recreate the table, so you can modify it and reload the script to apply changes.

  4. To practice learning the SQL as understood by SQLite, create a new file with the following content:

    <html>
    <body>

    <?php

    // Relative linked target of <form> action
    $pagename = basename($_SERVER['PHP_SELF']);
    if(isset(    $_REQUEST['q'])){
            $q = $_REQUEST['q'];
    }else{
            $q = '';
    }
    // The file to be used as the database:
    $databasefile = "data/sqlite";

    if(     $db_handle = sqlite_open($databasefile, 0666, $sqlite_error) ){ ?>
        <p>Query you want to do (using <a href="http://sqlite.org/lang.html">
        SQL as understood by SQLite</a>):</p>
        <form action="<?=$pagename;?>" method="GET">
        <input type="text" name="q" value="<?=htmlspecialchars($q);?>" size=100>
        <input type="submit">
        </form>
        <form action="<?=$pagename;?>" method="GET">
        <input type="hidden" name="q" value="">
        <input type="submit" value="Clear">
        </form>
        <?php 
            if(preg_match("/\w+/",$q)){
            echo     "<table border=1>\n";
                $query = sqlite_query($db_handle, $q);
                $result = sqlite_fetch_all($query);
            foreach (    $result as $row){
                for(    $i=0;isset($row[$i]);$i++){
                    echo     "<td>" . htmlspecialchars($row[$i]) . "</td>"; 
                }
                echo     "</tr>\n";
            }
            echo     "</table>\n";
        }
            sqlite_close($db_handle);
    }else{
        die(    $sqlite_error);
    }
    ?>
     
    <p>cheat sheet:</p>
    <ul>
    <li>create table bob (id int(32), name varchar(128))
    <li>insert into bob values ('1','one')
    <li>select * from bob
    <li>delete from bob where id='1'
    <li>update bob set name='one' where id='1'
    <li>drop table bob
    <li>select sqlite_version()
    <li>select name,sql from sqlite_master where type='table'
    </ul>

    </body>
    </html>
<- Back - Saving Data in Files|Up |Tips on Using Remote Databases - Next ->

If you have any questions, feel free to ask me.

Content by: Jeff D'Angelo <jcd@psu.edu> © 2005

See the source for this page

Last update on: Mon Jun 29, 2009, 8:10:40 PM