34,333
edits
Changes
no edit summary
== Creating an SQLite Datbase with PHP ==
An SQLite datbase can be PHP ''sqlite_open()'' function. This function accepts one mandatory and two optional arguments. The first argument is the database name (which, by convension, is given a .sqlite file extension). The second argument specifies option UnIX pfile UNIX file permission settings. The final argument represnets an error message to diaplay if the file cannot be opened.
The ''sqlite_open()'' returns a datbase handle on success, or a boolean ''false'' value on failure. A memory resiodent database can be created by passing in the string '':memory'' as the datbase file name argument.
SQLite databases are closed using the ''sqlite_close() '' function.
The following example opens a database called ''phptest.sqlite'':
if ($dbhandle == false)
{
} else {
echo 'Database created.';
}
sqlite_close($dbhandle)
?>
</pre>
== Using PHP to Add Records to an SQLite Database ==
Records are added to a SQLite database using the PHP ''sqlite_query()'' function. The ''''sqlite_query()'' function takes two arguments, the first being teh handle returned by the call to ''sqlite_open()'' and the second representing the SQL command to run on the database. Records are added using the SQL insert command:
<pre>
<?php
$dbhandle = sqlite_open('phptest.sqlite');
if ($dbhandle == false)
{
die ('Unable to open database');
} else {
echo 'Database created.';
}
$dbquery = 'INSERT INTO customer (name, account) VALUES ("James Wilson", "12345678")';
$dbresult = sqlite_query($dbhandle, $dbquery);
sqlite_close($dbhandle)
?>
</pre>
== Using PHP to Select Records from an SQLite Database ==
As with adding records, selecting records simply consists of constructing a suitable SQL SELECT statement and passing it through to the ''sqlite_query()'' function:
<pre>
?php
$dbhandle = sqlite_open('phptest.sqlite');
if ($dbhandle == false)
{
die ('Unable to open database');
} else {
echo 'Database created.';
}
$dbquery = 'SELECT * FROM customer;
$dbresult = sqlite_query($dbhandle, $dbquery);
sqlite_close($dbhandle)
?>
</pre>
The ''sqlite_query()'' function returns all the selected records. These can be extracted as an array of arrays using the ''sqlite_fetch_all()'' function which takes the handle returned from the query as an argument. Whilst this approach is fine for small amounts of data it tends to be inefficient when dealing wiht large data sets. It is preferable, in these circumanstances, to use the ''sqlite_fetch_single()'' function in conjunction with the ''sqlite_has_more()'' function as follows:
<pre>
?php
$dbhandle = sqlite_open('phptest.sqlite');
if ($dbhandle == false)
{
die ('Unable to open database');
} else {
echo 'Database created.';
}
$dbquery = 'SELECT * FROM customer;
$dbresult = sqlite_query($dbhandle, $dbquery);
while (sqlite_has_more($dbresult))
{
$dbrow = sqlite_fetch_single($dbquery);
print_r ($dbrow);
}
sqlite_close($dbhandle)
?>
</pre>