This tutorial will introduce you to the important methods supported by the SQLite API and provide a simple scripting template that you can use in your development to show 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 install an interactive SQLite program on your system, but you should download and install it in order to simplify the series of initial tables needed to create this tutorial. Then, create a sample table for your SQL query by creating a blank text file that executes the binary at an interactive command prompt with the file name as a parameter to the following command (List a):
Sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, username text, country text);
Sqlite> INSERT into the 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, here's how to create a script template using the SQLite method of PHP.
Set Access Parameters
$db = "Users.db";
Open database File
Make sure script have 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);
?>
To execute a SQL query using the sqlite extension of PHP, follow these four simple steps:
1. Call the Sqlite_open () function to initialize the database handle. The path and file name of the database (remember that SQLite is file-based, not server-based as MySQL) and is passed as an argument to the function.
2. Create the 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 succeeds. A successful select query returns a result object, and a successful Insert/update/delete query returns a resource identifier, and an unsuccessful query returns "pseudo". The sqlite_error_string () and Sqlite_last_error () methods can be used to catch errors and display corresponding error messages.
3. For select queries, the resulting object can be further processed to extract data from it. When the Sqlite_fetch_array () function is used in a loop, each record is retrieved as an array of PHP. You can access the individual fields of each record by invoking an array of appropriate keys.
4. Call the Sqlite_close () function to end the session.
One of the innovations of PHP 5.x is the addition of the SQLite database engine. SQLite is a file-based, fully-featured, portable database engine that can be used for most SQL operations without aggravating the load on client-server communications. Php
This SQLite API in 5.x is activated by default, which means you can use SQLite right away.
Hopefully this script module will save you some time in the next time you sit down and write SQLite connection/interaction routines in PHP. Happy programming!
http://www.bkjia.com/PHPjc/486249.html www.bkjia.com true http://www.bkjia.com/PHPjc/486249.html techarticle This tutorial will introduce you to the important methods supported by the SQLite API and provide a simple scripting template that can be used in your development to show you how to use PHP to interact with the SQLite database ...
