Messing with PHP and SQLite

Started working on a project in PHP. PHP was one of the first CS courses I took and at the time I was still learning how to learn languages so my notes weren’t good. Actually the project I was working on was a system to dynamically display my notes online from a source directory and using syntax highlighting.

This project is giving me an opportunity to create the PHP notes I need and also to refresh myself with the language. It’s such a cool language especially now at v.7.

So I kind of got sidetracked on my project and ended up spending a whole day figuring out how to use PHP with SQLite3. Here is some code. This page creates a sample SQLite3 database and then does a basic query.

For this to run you need both sqlite3 and the php-sqlite3 extension installed.

I deliberately left in some commented-out lines after I implemented other/better ways to accomplish the same thing.

= 100)
);
EOD;

$inserts = <<<'EOD'
insert into products (prod_id, catg_id,prod_list_price, prod_name, prod_desc) values
    (1000, 'HW',   125.00, 'Hand Mixer', 'Seven speed BnD hand mixer, black')
    ,(1010, 'SPG',  150.00, 'Weights',   'Set of 12 barbells 15 pounds')
    ,(1020, 'SPG',   12.95, 'Dartboard', 'Cork-backed dartboard with hanger')
    ,(1030, 'SPG',   29.95, 'Basketball',  'Basketball')
    ,(1070, 'HW',    25.50, 'Iron', 'Steam and dry iron SunShine, Yellow')
    ,(1071, 'HW',    25.50, 'Iron', 'Steam and dry iron SunShine, Blue')
    ,(1072, 'HW',    25.50, 'Iron', 'Steam and dry iron SunShine, Red')
    ,(1080, 'HW',    25.00, 'Cornpopper', 'Cornpopper 12 quart automatic shutoff')
    ,(1090, 'HW',   149.99, 'Gas grill', 'Gas grill')
    ,(1100, 'HW',    49.99, 'Blender', 'Twelve-speed Blender')
    ,(1140, 'PET',   14.99, 'Bird cage- simple', 'Bird cage- simple; wire frame twofeeder trays')
    ,(1141, 'PET',   99.99, 'Bird cage- deluxe', 'Bird cage- deluxe; floor model, with fly space');
EOD;


$dbFile = 'altgeld.sqlt';

// extend SQLite3 class and create new object
//  this is overkill though
/* class MyDB extends SQLite3 {
    function __construct($db) {
        $this->open('$db');
    }
}
$db = new MyDB($dbFile); */

// keep it simple
$db = new SQLite3($dbFile);

// note how PHP uses -> to demarcate between an object and it's method or property
//  similar to . in Java
$db->exec($creates);
$db->exec($inserts);

//$results = $db->query('select * from products');

// recommended way to do SQLite3 query is to use a statement
// check return value of prepare to prevent exec error for non-object
if ($stmt = $db->prepare('select * from products')) {
    $results = $stmt->execute();

    // number of columns
    $cols = $results->numColumns();

    // get column names
    echo "| ";
    for ($i = 0; $i < $cols; $i++) { 
        echo "" . $results->columnName($i) . " | "; 
        //echo $row[$i] . '
'; 
    }
    echo "

";

    // get items for each row
    while ($row = $results->fetchArray(SQLITE3_ASSOC)) {
        echo "| ";
        foreach ($row as $descr => $item) {
            echo "$descr: $item | ";
        }
        echo "

";
        //echo $row['prod_name'] . "
";
    }
}

// probably not necessary with sqlite but anyway
$db->close();

?>

You can see the script in action here.

Credit to the excellent official PHP documentation for SQLite3 and the many informative comments.

Also major credit to the extremely great book Learning PHP, MySQL, & JavaScript by Robin Nixon.

Special thanks to the totally kickass WordPress plugin Crayon Syntax Highlighter!