PHP

Connecting to SQLite

Starting with PHP 5, SQLite is bundled with the scripting language. This is a lightweight, file-based database. That allows very fast reading (in many cases, even faster than when using a "real" database), but writing can sometimes take longer than with other systems because file locking is an issue.

@sqlite_open('quotes.db', 0666, $error)

However, PHP 4 users can also use SQLite as there is a PECL (PHP Extension Community Library) module available at http://pecl.php.net/.

Connecting to SQLite
<?php
  if ($db = @sqlite_open('quotes.db', 0666, $error))
    {
    echo 'Connected to the database.';
    sqlite_close($db);
  } else {
    echo 'Connection failed: ' . htmlspecialchars
      ($error);
  }
?>

If you use PHP 5, SQLite support is already included; when using PHP 4, use pear install SQLite or, when Windows is the operating system of your choice, load the current SQLite binary extension at http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll, copy it to PHP's extension folder, and then load it using extension=php_sqlite.dll in your php.ini.

Then, you can connect to a SQLite data source using sqlite_open(). As the first parameter, you provide the name of the database file (it gets created if it doesn't exist yet). For this to work, the PHP process needs read and write privileges to the file. The second parameter is the file open mode (0666 is recommended); however, as of this writing, this parameter is ignored. The second parameter is a variable that contains any error messages (such as insufficient rights).

Just like ext/mysqli, the SQLite extension under PHP 5 can also be accessed using an object-oriented programming (OOP) approach:

<?php
  $db = new SQLiteDatabase('quotes.db');
  echo 'Connected to the database.';
  $db->close();
?>

For the sake of backward compatibility, this chapter uses the functional approach for the subsequent code.

Sending SQL to SQLite

sqlite_exec()

The PHP function sqlite_exec() sends an SQL statement to the database. As the first parameter, the database handlereturned by sqlite_open()is used; the second parameter is the SQL string. To avoid SQL injection, the PHP function sqlite_escape_string() escapes dangerous characters in dynamic data. The preceding code implements this for the sample table quotes that has also been used in the MySQL code.

Sending SQL to SQLite
<?php
  if ($db = @sqlite_open('quotes.db', 0666, $error))
    {
    require_once 'stripFormSlashes.inc.php';
    sqlite_exec($db, sprintf(
      'INSERT INTO quotes (quote, author, year)
         VALUES (\'%s\', \'%s\', \'%s\')',
      sqlite_escape_string($_POST['quote']),
      sqlite_escape_string($_POST['author']),
      intval($_POST['year'])));
    echo 'Quote saved.';
    sqlite_close($db);
  } else {
    echo 'Connection failed: ' . htmlspecialchars
      ($error);
  }
?>

If a table contains an identity column (data type INTEGER PRIMARY KEY when using SQLite), calling sqlite_last_insert_rowid() after sending an SQL statement returns the value this column has for the new entry in the database.

by BrainBellupdated
Advertisement: