Home > Article > Backend Development > SQLite example tutorial with PHP_PHP tutorial
SQLite example tutorial with PHP_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:40:061029browse
This tutorial will introduce you to the important methods supported by the SQLite API, provide a simple script template that can be used in your development, and tell you how to use PHP to interact with the SQLite database. This article assumes that you have installed Apache and PHP.
You do not have to have the interactive SQLite program installed on your system; however, in order to simplify the creation of the initial series of tables required for this tutorial, you should download and install it. Then, create a sample table for your SQL query by creating a blank text file and executing the binary program at an interactive command prompt using the filename as argument to the following command (Listing A):
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, country TEXT);
sqlite> INSERT INTO users VALUES (1, john, IN);
sqlite> INSERT INTO users VALUES (2, joe, UK);
sqlite> INSERT INTO users VALUES (3, diana, US);
Once the table is created, the following is to create a script template using PHP's SQLite method.
// set access parameters
$db = "users.db";
// open database file
// make sure script has read/write permissions!
$conn = sqlite_open($db) or die ("ERROR: Cannot open database");
// create and execute INSERT query
$sql = "INSERT INTO users (id, username, country) VALUES (5, pierre, FR)";
sqlite_query($conn, $sql) or die("Error in query execution: " . sqlite_error_string(sqlite_last_error($conn)));
// create and execute SELECT query
$sql = "SELECT username, country FROM users";
$result = sqlite_query($conn, $sql) or die("Error in query execution: " . sqlite_error_string(sqlite_last_error($conn))) ;
// check for returned rows
// print if available
if (sqlite_num_rows($result) > 0) {
while($row = sqlite_fetch_array($result)) {
echo $row[0] . " (" . $row[1] . ") ";
}
}
// close database file
sqlite_close($conn);
?>
When using PHP's SQLite extension to execute SQL queries, follow the following four simple steps:
1. Call the sqlite_open() function to initialize the database handle. The path and filename of the database (remember, SQLite is file-based, not server-based like MySQL) are passed to the function as arguments.
2. Create a SQL query string and execute it with the sqlite_query() function. The result objects of these methods vary depending on the type of query and whether it was successful. A successful SELECT query will return a result object; a successful INSERT/UPDATE/DELETE query will return a resource identifier; an unsuccessful query will return "false". The sqlite_error_string() and sqlite_last_error() methods can be used to capture errors and display corresponding error messages.
3. For SELECT queries, the result object can be further processed to extract data from it. When the sqlite_fetch_array() function is used in a loop, each record will be retrieved as a PHP array. You can access the individual fields of each record by calling the appropriate key of the array.
4. Call the sqlite_close() function to end the session.
An innovative move of PHP 5.x is the addition of the SQLite database engine. SQLite is a file-based, full-featured, portable database engine that can be used to perform most SQL operations without burdening client-server communication. The SQLite API in PHP
5.x will be activated by default, which also means you can use SQLite immediately.
Hopefully this script module will save you some time the next time you sit down to write SQLite connection/interaction routines in PHP. Happy programming!