SQLite Using

PHP & PDO

Reference






NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' of *!
by Jan Zumwalt - NeatInfo.com

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' + of *!
Table of Contents

Section - 1 Introduction ........................................................................................................................................... ,
-bout .his Reference ......................................................................................................................................................................................... ,
/hat is )01 ........................................................................................................................................................................................................ ,
"2$ite 3" "2$ite+ ............................................................................................................................................................................................. ,
/hat database does )01 su&&ort4 .................................................................................................................................................................. 5
"ettin' u& a develo&ment system ..................................................................................................................................................................... 5
6-M)) confi' files .............................................................................................................................................................................................. *
0atabase mana'ers ............................................................................................................................................................................................ *
0o I have )01 on my com&uter4 ...................................................................................................................................................................... 7
8ood )ro'rammin' "ynta9 .............................................................................................................................................................................. 1!
Single Row Data .......................................................................................................................................................................................... 10
Multiple Row Data ...................................................................................................................................................................................... 10
"ection - "im&le 0atabase :9am&le ................................................................................................................................ 1
%onnect and error handlin' ............................................................................................................................................................................. 1
%reate and Insert Into 0atabase ..................................................................................................................................................................... 1
3iew and "earch 0ata ...................................................................................................................................................................................... 1+
;&date 0ata ...................................................................................................................................................................................................... 1<
0elete 0ata ....................................................................................................................................................................................................... 15
"ection - + 0atabase =-dd-%o&y-0elete-Rename> ............................................................................................................. 1,
-dd 0atabase .................................................................................................................................................................................................... 1,
%o&y 0atabase .................................................................................................................................................................................................. 1,
0elete 0atabase ............................................................................................................................................................................................... 15
Rename 0atabase ............................................................................................................................................................................................ 15
0atabase 0escri&tions ..................................................................................................................................................................................... 1*
Master 0atabase .............................................................................................................................................................................................. 1*
Command Line ............................................................................................................................................................................................. 18
"ection - < .able =-dd-%o&y-0elete-Rename> ...................................................................................................................
-dd .able ..........................................................................................................................................................................................................
0oes .able :9ist ................................................................................................................................................................................................
%o&y .able /ith 0ata ...................................................................................................................................................................................... +
%o&y .able "tructure ........................................................................................................................................................................................ <
0elete .able ...................................................................................................................................................................................................... 5
Rename .able ................................................................................................................................................................................................... 5
3iew .able "tructures ...................................................................................................................................................................................... ,
.able 0escri&tion .............................................................................................................................................................................................. ,
"ection - 5 Row =-dd-%o&y-0elete-Rename> ..................................................................................................................... *
Insert Row =add> ............................................................................................................................................................................................... *
Insert - Multi&le Rows ...................................................................................................................................................................................... *
%o&y Row .......................................................................................................................................................................................................... *
0elete -ll Rows ................................................................................................................................................................................................. *
0elete Row=s> Matchin' %ondition ................................................................................................................................................................. *
%ount Rows ....................................................................................................................................................................................................... 7
Inde9 0escri&tion .............................................................................................................................................................................................. +!
"ection - , %olumn =-dd-%o&y-0elete-Rename>................................................................................................................ +1
-dd %olumn ...................................................................................................................................................................................................... +1
%o&y %olumn and data ..................................................................................................................................................................................... +1
0elete %olumn .................................................................................................................................................................................................. +1
Rename %olumn ............................................................................................................................................................................................... +1
%ount %olumns ................................................................................................................................................................................................. +

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' < of *!
"ection - 5 "how and "earch 0ata ..................................................................................................................................... ++
"im&le "elect "ynta9......................................................................................................................................................................................... ++
.he four fetch ty&es ......................................................................................................................................................................................... ++
WHERE ......................................................................................................................................................................................................... 3
L!"E ............................................................................................................................................................................................................... 3
#$D .............................................................................................................................................................................................................. 3%
&R ................................................................................................................................................................................................................. 3%
-dvanced "elect "ynta9 ................................................................................................................................................................................... +5
L!M!' ............................................................................................................................................................................................................ 3%
&RDER () ..................................................................................................................................................................................................... 3*
+R&,- () .................................................................................................................................................................................................... 3*
H#.!$+ ........................................................................................................................................................................................................ 3*
"ection - * 3iews ................................................................................................................................................................ +*
"ection - 7 .ri''ers ............................................................................................................................................................ +7
Insert Row .imestam& ..................................................................................................................................................................................... +7
$o''in' -ll Inserts, ;&dates, and 0eletes ...................................................................................................................................................... +7
"ection - 1! .ransactions ................................................................................................................................................... <
"ection - 11 Joins ............................................................................................................................................................... <+
%R1"" J1IN ....................................................................................................................................................................................................... <<
INN:R J1IN ........................................................................................................................................................................................................ <5
$:?. 1;.:R J1IN .............................................................................................................................................................................................. <5
RI8@. 1;.:R J1IN and ?;$$ 1;.:R J1IN ..................................................................................................................................................... <,
;NI1N ............................................................................................................................................................................................................... <,
"ection - 1 0ate and .ime ?unctions ............................................................................................................................... <5
?unction ?ormat ............................................................................................................................................................................................... <5
Modifiers ........................................................................................................................................................................................................... <*
.imestam& ........................................................................................................................................................................................................ <7
:9am&les ........................................................................................................................................................................................................... <7
Sa/e current or future date ........................................................................................................................................................................ %0
Le00 t1an time 0pan..................................................................................................................................................................................... %0
Date e2ual to time 0pan ............................................................................................................................................................................. %0
:rrors and Au's ................................................................................................................................................................................................. 5!
"ection B 1+ "trin's ............................................................................................................................................................ 5
$ist of "trin' ?unctions ..................................................................................................................................................................................... 5
s#liteCesca&eCstrin' =Dstrin'> ......................................................................................................................................................................... 5<
.e9t "crubbin' .................................................................................................................................................................................................. 5<
"ection B 1< Math .............................................................................................................................................................. 5,
$ist of Math ?unctions ...................................................................................................................................................................................... 5,
Math :9am&les ................................................................................................................................................................................................. 55
Money ............................................................................................................................................................................................................... 55
"ection - 15 "avin' and 0is&layin' Ima'es ......................................................................................................................... 5*
.est Ima'e ......................................................................................................................................................................................................... 5*
"avin' an Ima'e ................................................................................................................................................................................................ 57
0is&lay an Ima'e............................................................................................................................................................................................... ,!
"ection - 1, %ry&t, @ash, etc .............................................................................................................................................. ,+
?unction "ynta9 ................................................................................................................................................................................................ ,+
?ile I1................................................................................................................................................................................................................. ,+
-&&endi9 - - :rror @andlin' .............................................................................................................................................. ,<
:rror %odes ....................................................................................................................................................................................................... ,<

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5 of *!
13 !f 4ou copied code from a page of t1i0 reference ............................................................................................................................... *
53 Some S6Lite /er0ion0 1a/e incompati7ilitie0 ...................................................................................................................................... *
33 8!n/alid re0ource8 error ........................................................................................................................................................................ *%
3 Sen0iti/it4 to w1ite 0pace ..................................................................................................................................................................... *%
%3 9una7le to open file: ............................................................................................................................................................................. *%
*3 9S6L!'E;C&RR,-' error: ..................................................................................................................................................................... *%
<3 9WHERE Clau0e error: .......................................................................................................................................................................... *%
83 ,ne=pected return /alue> i.e. 9#rra4: ................................................................................................................................................. *%
?3 S)$'#@ ERR&R> ,ne=pected 9End:A .................................................................................................................................................. **
103 9une=pected ';.#R!#(LE: ................................................................................................................................................................. **
-&&endi9 - A )rintin' .able 0ata ....................................................................................................................................... ,5
.able &rint ......................................................................................................................................................................................................... ,5
"im&le &rintCr ................................................................................................................................................................................................... ,5
$oo& &rintCr ...................................................................................................................................................................................................... ,*
3ar dum&........................................................................................................................................................................................................... ,7
Inde9 E associative table out&ut ..................................................................................................................................................................... 5!
-&&endi9 - % .est 0atabases .............................................................................................................................................. 51
.est database &ro'ram .................................................................................................................................................................................... 51
-&&endi9 B 0 %ommand Reference ................................................................................................................................... 5+
)01 %ommands ................................................................................................................................................................................................ 5+
"2$ite+ %ommands .......................................................................................................................................................................................... 5<
Reserved /ords ............................................................................................................................................................................................... 55
Documenation ............................................................................................................................................................................................. 80
Reference0 ................................................................................................................................................................................................... 80

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' , of *!
Section - 1
Introduction
About This Reference
.his reference was written durin' the desi'n of a &roFect that did not need the hu'e overhead of a My"#l database. /hen
I researched "2$ite, I found the e9istin' e9am&les very confusin'. It was hard to tell the difference between code
desi'ned for %, the command line interface, "2$ite, or "2$ite+.
I decided to use PDO because I already had e9&erienced in procedural database &ro'rammin' and wanted to widen out
with object oriented database &ro'rammin'.
.his reference has s&ecific e9am&les tested in the XAMPP develo&ment environment but should be able to be used on
Fust about any HTTP server with out modification.
SQite! is used for the database, HTM and "avaScript are used for the user browser interface, PHP is used for server file
access and )@) PDO allows )@) to communicate with the database. /hewG .hat was a mouthful, but it all worHs easily
and very well to'ether.
.he methodolo'y Fust mentioned is by far the most common in use on the internet today. .hanHfully, only basic com&uter
sHills and terminolo'y will be needed. :9am&les are s&ecifically desi'ned for ease of understandin'. ?lashy cosmetic web
desi'n has been left out for clarity.
Ae sure to review the most common errors in the -&&endi9 before &roceedin'.
#hat is PDO
PDO $P@) Data ObFects% is a )@) e9tension that su&&orts database connections with a unifor& co&&and structure. .his
allows develo&ers to create code which is &ortable across many databases and system o&eratin' &latforms. "im&ly
e9&lained, )01 lets you use the same code =obFect oriented> or commands for a &roFect that could be used on a cell
&hone, -&&le com&uter, /indows, or an IAM mainframe. .hat maHes &ro'rammin' a lot easierG
SQite' (S SQite!
Now the (#D newsG
.he volunteers that maintain SQite wanted a small li'htwei'ht feature rich database that would com&are to My"#l.
"2$ite taHes less than 1mb of disH stora'e s&ace com&ared to *!-1!!mb for My"#l. .o Hee& "2$ite lean and mean the
develo&ers chose to forfeit co&plicated &ultitas)in* and &ultiuser criteria that loaded down My"#l. .his is not a
&roblem, it is a feature. -t some &oint, some of the ori'inal ways of doin' thin's in earlier versions of "2$ite became such
a &roblem that they decided to create an entirely new command and database structure between version E +.
.he lar'est chan'es started with "2$ite+ and thatIs a (!+ &roblem. .he chan'es in "2$ite+ cause much of the source code
of earlier versions to be inco&patibilit+ ,ith ne,er versions of SQ-teJ neither the database or command structures
worH smoothly across these versions.
.he solution chosen by "2$ite volunteers was to start SQite! as a .ne,/ D0 lan*ua*eJ hence we ended u& with )01. -s
a conse#uence of these differences, we must use caution in choosin' the commands and database structure. .he 'ood
news is the commands are easily reco'niKedJ SQite! co&&ands use .12. object s+nta3.
/hat is not always easy to reco'niKe is the database structure =ty&e>. Many "2$Ite databases have no e9tension or
'eneric e9tensions that do not offer any clue as to what set of commands should be used on them, so be careful!
"2$ite =liHe most other 0A lan'ua'es> offers a set of command line o&tions. .he co&&and line and PDO commands are
not interchan*eable so watch outG

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5 of *!
@ere is a com&arison of commands to o&en or create a database.
Default SQLite2: $db = new SQLiteDatabase('test.sqlite2', 0666, $error);
PDO SQLite2: $db = new PDO("sqlite2:test.sqlite2");
Command line SQLite2: sqlite test. Sqlite2
Command line SQLite3: sqlite3 test. sqlite3
PDO SQLite3: $db = new PDO("sqlite:test.sqlite3");
Li0ting 1.1 B .er0ion command compari0on.
#hat database does PDO support4
)01 su&&orts many of the &o&ular databases. @ere is a &artial list...
0A$IAL ?ree.0" M Microsoft "2$ "erver M "ybase
?irebird =htt&LMMfirebird.sourcefor'e.netM>L ?irebirdMInterbase ,
IAM =IAM 0A>
IN?1RMI6 - IAM Informi9 0ynamic "erver
MN"2$ =htt&LMMwww.mys#l.comM>L My"2$ +.9M<.!
1%I =htt&LMMwww.oracle.com>L 1racle %all Interface
10A%L 10A% v+ =IAM 0A and uni910A%>
)8"2$ =htt&LMMwww.&ost'res#l.or'M>L )ost're"2$
"2$I.: =htt&LMMs#lite.or'M>L "2$ite +.9
Settin* up a develop&ent s+ste&
Nou will need a develo&ment environment. I stron'ly recommend usin' XAMPP for either #indo,s or inu3. )rior to
about !!5, /-M) had more features and less installation &roblems than 6-M)), but that is no lon'er true. I have used
both XAMPP and #AMP for many years and find that 6-M)) has less installation &roblems. - few years a'o 6-M)) had
some control &anel issues which seem to have all been fi9ed. ?or downloads and further readin', see the linHs below.
http://www.apachefriends.org/en/xampp.html
http://www.wampserver.com/en/
Most =but not all> inu3 develo&ment systems come with )@) su&&ort for "2$ite. 1n windows machines, 6-M)) installs
)@) su&&ort for "2$ite but not necessarily SQLite3.
If you have trouble with SQite!, the first thin' you should checH is the PHP confi* file. 0onIt for'et to checH all the
&ossible locations 'iven below. -lso, you should stop the Apache server before editin' any file that affects -&ache, and
then restart after you are done.
Windows: xampp/php/php.ini.
Some Linux xampp/apache/bin/php.ini
.he #indo,s confi* file should have a cou&le areas with lines that looH liHe the followin'O
extension=php_pdo_sqlite.dll
extension=php_pdo_sqlite_external.dll
extension=php_sqlite.dll
extension=php_sqlite3.dll
sqlite.assoc_case = 0
.hese lines should not have the 5 =semi-colon> at the start of the line. .he semicolon comments out the code and &revents
it from worHin'. If it has the semi-colon 5 it should be edited out. $inu9 may have sli'htly different values and file names
that end ,ith 6so on the end.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' * of *!
XAMPP confi* files
Just to maHe this section com&lete, a list of confi'uration files for 6-M)) are &rovide below. -s we already mentioned,
there may be sli'ht differences in location or names de&endin' on whether it is a /indows or $inu9 installation. Many
$inu9 develo&ers sli'htly modify the directory structure.
* Apache basic configuration: .\xampp\apache\conf\httpd.conf
* Apache SSL: .\xampp\apache\conf\ssl.conf
* Apache Perl (addon): .\xampp\apache\conf\perl.conf
* Apache Tomcat (addon): .\xampp\apache\conf\java.conf
* Apache Python (addon): .\xampp\apache\conf\python.conf
* PHP: .\xampp\php\php.ini
.\xampp\apache\bin\php.ini
* MySQL: .\xampp\mysql\bin\my.cnf
* phpMyAdmin: .\xampp\phpMyAdmin\config.inc.php
* FileZilla FTP: .\xampp\FileZillaFTP\FileZilla Server.xml
* Mercury Mail: .\xampp\MercuryMail \MERCURY.INI
* Sendmail: .\xampp\sendmail\sendmail.ini
$inu9 may have sli'htly different values such as names with P.soQ on the end.
Database &ana*ers
%ustom &ro'rammin' is &robably only about 1!-!R of a database &ro'rammerIs worH. .he other *!R is done usin' a
convenient database manager &ro'ram. "everal 'ood ones are listed below.

SQLite2009 Pro Enterprise Manager http://osenxpsuite.net/
Firefox SQLITE plugin module http://code.google.com/p/sqlite-manager/
SQLIite Administrator http://sqliteadmin.orbmu2k.de/
SQLite Browser http://sqlitebrowser.sourceforge.net/ great for beginners
SqliteRoot http://sqliteroot.com/
Phpliteadmin http://code.google.com/p/phpliteadmin/

-t the time of this writin', https788sourcefor*e6net had over 5!! hi'h #uality free S9ite utilities. .he 8oo'le code
develo&er site http788code6*oo*le6co& is another 'ood &lace to looH. .hey are all worth checHin' out. -n internet search
will certainly turn u& more treasure. ?or e9am&le, there are several utilities that maHe mana'in' a database over the
internet much easier.

PhpiteAd&in is very small )@) "#lite mana'er that is easy to use. It does not re#uire installation or confi'uration. It is a
small sin'le file that is &laced in the directory where your database is and run from there. - reco&&end this for the
be*inner and it is what I use most of the time. It su&&orts both "#$ite and "#$ite+ =several other mana'ers donIt>. It is
very intuitive. It has a sim&le to& menu. No searchin' is re#uired to find the command or tool you need. ItIs only
disadvanta'e at the time of this writin' is that it only looHs in the current directory for databases. No directory browsin'
feature is available. .his handica& will liHely 'o away in the near future. .he readme doc that comes with it e9&lains a
sim&le way to manually insert a directory &ath in the source code.

I use SQite'::; Pro =freeware version> some of the time. SQite'::; Pro has all the features you could &ossibly want
includin' built-in documentation. It taHes some 'ettin' use to - es&ecially fi'urin' out your data hierarchy and where your
table information is located. /indows allows you to create a "2$ite file e9tension so that a mouse selection o&ens the
mana'er immediately.

.he SQite Ad&inistrator is one of the easiest mana'ers to use and I would &refer it e9ce&t that it is missin' a cou&le Hey
features. It is not easy to add or edit a record. I would not be sur&rised if a year or more of develo&ment fi9es most of itIs
issues. .he installation creates an "2$ite file e9tension hooH that o&ens the mana'er immediately if the mouse clicHs on a
database file.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 7 of *!

.he <irefo3 Plu*=in would be a su&erior choice e9ce&t that the ?irefo9 browser must be runnin' before you select a
database file to worH on. /ith the browser runnin', you must select a menu then a tool button, afterwards you can close
the ?irefo9 browser and the mana'er will remain inde&endent. .his is a bit clumsy and &revents an association of the
"2$ite file e9tension to be made that attaches to the mana'er tool. .hat bein' said, it is easy to use and is well laid out.

SQite 0ro,ser is a very clean easy to use mana'er. It is intuitive and thin's are e9tremely easy to find. .he user has the
o&tion of selectin' from a set of wiKards and it &rovides a &leasin' s&readsheet-liHe interface. /indows allows you to
create a "2$ite file e9tension so that a mouse selection o&ens the mana'er immediately.
Do - have PDO on &+ co&puter4
1nce you have a develo&ment environment u& and runnin', you should checH if the )@) )01 driver is installed for your
database. %hecH phpinfo$% for a section named .SQ-T>/ and .PDO/6 Nou may also checH the available drivers with the
static method PDO77*etAvailableDrivers$%, e9am&les are 'iven below
<?php
echo '<h2>PHP Info</h2>';
phpinfo();
?>
Li0tingC 1.5 D #ll t1e -H- info 4ou could po00i7l4 wantE
<?php
echo '<h2>PDO Info</h2>';

foreach(PDO::getAvailableDrivers() as $driver) {
echo $driver.'<br />';
}
?>
Li0tingC 1.3 B S1ow a li0t of -D& 0upported data7a0e0 on 4our computer.
<html>
<body style="font-family:arial;">
<h2>PDO & PHP Info</h2>
</blockquote>
<span style="color:red;">
<?php
foreach(PDO::getAvailableDrivers() as $driver) {
echo $driver.'<br />';
}
?>
</span>
</blockquote>
If you see "<span style="color:red;">sqlite2</span>" listed, there is support for sqlite2.<br><br>
If you see "<span style="color:red;">sqlite</span>" listed, there is support for sqlite3.<br><br>

PHP version: <span style="color:red;"><?php echo phpversion(); ?></span><br>
SQLite library version: <span style="color:red;"><?php echo sqlite_libversion(); ?></span><br>
SQLite library character encoding: <span style="color:red;"><?php echo sqlite_libencoding();
?></span><br>
<?php
echo "If you dont see an error message, a <span style='color:red;'>SQLite3</span> test database was
created.<br><br>";

# create test SQLite3 database
$query = " test.sqlite3;

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1! of *!
$db = new PDO("sqlite:$query") or die("Could not create: <span style='color:red;'>$query</span>");
?>

<!-- uncomment the next line to see the comprehensive PHP information -->
<!-- PhpInfo: <span style="color:red;"><?php echo phpinfo(); ?></span><br><br> -->

</body>
</html>
Li0tingC 1. B Fanc4 report wit1 an option for a compre1en0i/e -H- li0ting.
PHP and SQLite3 Information
Supported PHP Data Object (PDO) drivers.
mysql
odbc
sqlite
sqlite2
Note: If you see "sqlite", then there is support for sqlite3.

PHP version: 5.3.1
SQLite library version: 2.8.17
SQLite library character encoding: iso8859
If you dont see an error message, a SQLite3 test database was created.
&,'-,' from li0ting 1. B .iew generated on a Win @- @#M-- 040tem. G'1e p1p info i0 not 01own and i0 a7out 3 page0
long3.
?ood Pro*ra&&in* S+nta3
- fre#uently used standard is to assi'n a variable named Drow for sin'le row data retrieval and Drows =notice the SsS> for
multi&le row data. Multi&le rows of data are sometimes called a data set or result set.
S-@?> RO# DATA
- sin'le row of data is retrieved by usin' the fetch$% statement. Ae careful not to confuse it with the fetchall$% statement.
.he followin' is a 'eneral e9am&le.
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$row = $result->fetch();
Li0tingC 1.% B +etting a 0ingle row of information a0 an arra4.
:ven thou'h there may be more than one match, only the first match one will be returned in Drow.
/e can access this sin'le row of data by usin' the associative field name.
echo $row['name'];
Li0tingC 1.* B S1owing a column from row data.
.his would return T?ranHT because that has the lowest I0 number and would be the first match.
MAT-P> RO# DATA
Multi&le rows of data can be retrieved at one time by usin' the fetchall$% statement. Ae careful not to confuse it with the
fetch=> statement. .he result is returned as an array of arrays. .he followin' is a 'eneral e9am&le.
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall();
Li0tingC 1.< B +etting multiple row0 of information a0 an arra4 of arra40.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 11 of *!
/e can access any row usin' array matri9 addressin'.
echo $rows['3'],['name'];
Li0tingC 1.8 B S1owing an4 column of an arra4 of arra40.
.his would access the third row of information and return the PnameQ data.

:nd 1f "ection


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1 of *!
Section = '
Si&ple Database >3a&ple
.his section &rovides a sim&le e9am&le of the most command database )01 command usa'e. .he e9am&le &rovides the
sHeletal frameworH on which many com&any em&loyee databases or &hone contact list is based. It is a very valuable
reference on itIs own. .he ste&s to creatin' a sima&le database are covered hereO
0esi'n
%reate db ?ile
%reate table and column definitions
Insert data
3iew and search data
;&date data
0elete data
Connect and error handlin*
In all our e9am&les, we assume that we have successfully created andMor connected to the database and set u& our error
handlin'. - 'ood 'eneric tem&late is 'iven below
/* Listing 3.6 - create "people db */
echo '<body style="font-family:arial;">';
echo '<h2>Create "people" database</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "people.sqlite3";

# connect to SQLite3 database
$query = "$dbPath$dbFile";
try { $db = new PDO("sqlite:$query"); }
catch(PDOException $e) { echo $e->getMessage()." Error: <span style='color:red;'>$query</span>"; }

# enhanced error messages
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Li0tingC 5.1 B +eneric connection and error 1andling.
Create and -nsert -nto Database
1nce the error definition is made, we donIt need to use the PtryU VQ, PcatchU VQ, or PdieQ code anymore. .he )01 error
re&ortin' does a wonderful Fob on fatal errors, but can be a little cry&tic for warnin's. I have found a combination of the
two techni#ues worHs best.
<?php
/* Listing 3.6 - create "people db */
echo '<body style="font-family:arial;">';
echo '<h2>Create "people" database</h2>';

# connect to database
# ... code to setup, connect to database, and handle errors goes here

# create table
$query = "CREATE TABLE friends (
id INTEGER PRIMARY KEY,
name TEXT(25),

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1+ of *!
phone TEXT(25)
)";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");

echo "<i>Database successfully initialized.<br></i> ";

# multiple row data insert
$query = "insert into 'friends' (name, phone)
select 'James Bond','007-1234'
union select 'Mickey Mouse','(200) 100-1122'
union select 'Daffy Duck','200 100-1122' ";

$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");

# single row data insert
$query = "insert into 'friends' (name, phone) values ('Porky Pig', '200) 100-1122') ";

$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");

# show success
echo "<i>Data successfully inserted.</i><br>";

# show number of rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0tingC 5.5 B create and in0ert data
(ie, and Search Data
@ere is our com&leted code to view all table data.
<?php
/* Listing 6.1 search techniques */
echo '<body style="font-family:arial;">';
echo '<h2> Search Techniques </h2>';

# connect to database
# ... code to setup, connect to database, and handle errors goes here

# query using simple SELECT
$query = "SELECT * FROM 'friends'";
$result = $db->query($query) or die("Error in query: <span style='color:red;'>$query</span>");
$row = $result->fetchall(PDO::FETCH_ASSOC);

# show table data
foreach($row as $array) {
echo "<pre>";
print_r($array);
echo "</pre>";
}
echo 'Result for: $query = "SELECT * FROM \'friends\' ";'. "<br>";

# show number of rows affected by last operation

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1< of *!
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected<br><br>";

echo "------<br>";

# query using specific fields and SELECT with WHERE
$query = "SELECT 'phone' FROM 'friends' WHERE name='James%' ";
$result = $db->query($query) or die("Error in query: <span style='color:red;'>$query</span>");
$row = $result->fetchall(PDO::FETCH_ASSOC);

# show table data
foreach($row as $array) {
echo "<pre>";
print_r($array);
echo "</pre>";
}
echo 'Result for: $query = "SELECT phone FROM 'friends' WHERE name=\'James%\' ";. "<br>";

# show number of rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected<br><br>";

# show success
echo "<i>Search successful.</i><br>";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0ting 5.3 B 0earc1 tec1ni2ue0
Apdate Data
If we use #H>R> na&e -B> C"a&esDC then any name startin' with PJamesQ will be selected.
"o here is what we need
<?php
/* Listing 7.2.- updating James */

# connect to database
# ... code to setup, connect to database, and handle errors goes here

# update table data
$query = "UPDATE 'friends' SET phone='911-007-1234' WHERE name LIKE 'James%'";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");

# show success
echo "<i>Update data was successful.</i><br>";

# Return rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

# close db, PHP will also do this automatically
$db = NULL;
?>

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 15 of *!
Li0tingC 5. B final /er0ion of updating a record
Delete Data
If we use #H>R> na&e -B> C"a&esDC then any name startin' with "a&es will be selected. Instead we will use #H>R>
na&e -B> CD0ondDC then only a name with this last name would be deleted.
"o here is what we need
<?php
/* Listing 2.4 - delete James Bond */
echo '<body style="font-family:arial;">';
echo '<h2>Update friends table data</h2>';

# connect to database
# ... code to setup, connect to database, and handle errors goes here

# delete row ofdata
$query = "DELETE from 'friends' WHERE name LIKE '%Bond%'";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");

# show success
echo "<i>Data deleted successful.</i><br>";

# Return rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0tingC 5.% B delete a row of information 7a0ed on 0earc1 criteria




:nd 1f "ection



NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1, of *!
Section = !
Database $Add=Cop+=Delete=Rena&e%
-n often over looHed as&ect of database &ro'rammin' is the maintenance that usually continues to be an on'oin' after
the database is created. %ommands to mani&ulate the whole database file or reveal the definitions of its tables or
columns are necessary to do this.
Add Database
Remember that if "2$ite does not find the database 'iven in the file name, it will attem&t to maHe it. "o all that is needed
to create a new db is to have a valid &ath and su&&ly a new file name.
If you do not want )01 to create a em&ty db if none is found, you can use &h&Is fileEe3ists command to test for the file.
$datafile = '/path/to/test.sqlite3';

if (file_exists($datafile)) {
... code to
... connect to database
} else {
echo "Error, did not find dataabse at $datafile.";
exit;
}
Li0tingC 3.1 B 'e0ting if a data7a0e e=i0t0
@e is a &ro'ram listin' that o&ens a db or creates a new one if none is found.
<?php
/* Listing 8.1 add db */
echo '<body style="font-family:arial;">';
echo '<h2>Add or connect to database</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/path/to/db/";
$dbFile = "db_name.sqlite3";

# make or connect to SQLite3 database
$query = "$dbPath$dbFile";
try { $db = new PDO("sqlite:$query"); }
catch(PDOException $e) { echo $e->getMessage(); }

echo "<i>Database successfully initialized.<br></i> ";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0tingC 3.5 D Creating a new data7a0e
Cop+ Database
-t the time of writin' this reference, I was unable to find a )01 db co&y command. @owever, there are still several
o&tions available.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 15 of *!
1- all the db mana'ers &rovide an Pim&ortQ feature
- a database could be o&ened and it contents meticulously co&ied
+- use &h& to maHe a co&y of the file
I favor method three =+>. @ere is an e9am&leO
<?php
/* Listing 8.2 copy a db */
echo '<body style="font-family:arial;">';
echo '<h2>Copy database</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "people.sqlite3";
$dbNew = "test.sqlite3";

# copy SQLite database
copy("$dbPath$dbFile", "$dbPath$dbNew")
or die("Error copying db: <span style='color:red;'>$dbFile to $dbNew</span>");

# show success
echo "<i>Database copied successfully.</i><br>";
?>
Li0tingC 3.3 B Cop4 a data7a0e
Delete Database
-t the time of writin' this reference, I was unable to find a )01 db delete or dro& command. @owever, there are still
several o&tions available.
1- db mana'ers may &rovide a PdeleteQ feature
- a database could be o&ened and it contents meticulously deleted
+- use &h& to delete the database file.
I favor method three =+>. @ere is an e9am&leO
<?php
/* Listing 8.3 delete a db */
echo '<body style="font-family:arial;">';
echo '<h2>Delete database</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "test.sqlite3";

# delete file (database)
unlink("$dbPath$dbFile")
or die("Error deleting dataebase: <span style='color:red;'>$dbPath$dbFile</span>");

# show success
echo "<i>Database deleted successfully.</i><br>";
?>
Li0tingC 3. B Delete a data7a0e
Rena&e Database

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1* of *!
-t the time of writin' this reference, I was unable to find a )01 db rename command. @owever, there are still several
o&tions available.
1- db mana'ers may &rovide a PrenameQ feature
- a database could be o&ened and it contents co&ied to a new database, then the old db deleted
+- use &h& to rename the database file
I favor method three =+>. @ere is an e9am&leO
<?php
/* Listing 8.4 rename db */
echo '<body style="font-family:arial;">';
echo '<h2>Rename database</h2>';

$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "people.sqlite3";
$dbNew = "test.sqlite3";

# copy SQLite database
rename("$dbPath$dbFile", "$dbPath$dbNew")
or die("Error renaming db: <span style='color:red;'>$dbFile to $dbNew</span>");

# show success
echo "<i>Database renamed successfully.</i><br>";
?>
Li0tingC 3.% B Delete a data7a0e
Database Descriptions
Master Database
:very "2$ite database has an "2$I.:CM-".:R table that defines the schema for the database. .he "2$I.:CM-".:R table
is read-only. Nou cannot chan'e this table usin' ;)0-.:, IN":R., or 0:$:.:. .he table is automatically u&dated when you
use %R:-.: .-A$:, %R:-.: IN0:6, 0R1) .-A$:, and 0R1) IN0:6 commands.
1ne thin' that is &articularly useful about usin' this command is that we are 'iven the "2$ #uery needed to create the db
structure. 3ery nice indeedG ?or automatically created indices =used to im&lement the )RIM-RN W:N or ;NI2;:
constraints> the s#l field is N;$$.
COMMA@D -@>
If you are runnin' the s#lite+ command-line &ro'ram you can 'et a host of information usin' the 6du&p or 6sche&a to see
the com&lete database schema includin' all tables and indices tables to 'et a list of all tables. .y&in' 6tables will &rovide a
com&lete list of tables. .hese commands can be followed by a $IW: &attern that will restrict the tables that are dis&layed.
.he "2$I.:CM-".:R table looHs liHe thisL
CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);

.o view the database structure, we need to connect to the database then #uery the master table. .o 3iew all the
available information we would use the followin' select statementJ

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 17 of *!
$query = "SELECT * FROM sqlite_master";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchAll();
echo"<pre>"; print_r($rows); echo"<pre>"; # show data
Li0tingC 3.* B .iew ma0ter data7a0e de0criptor information

Array
(
[type] => table
[name] => friends
[tbl_name] => friends
[rootpage] => 2
[sql] => CREATE TABLE friends (
id INTEGER PRIMARY KEY,
name TEXT(25),
phone TEXT(25)
)
)
:9am&le out&ut

.o see both the master and tem&Cmaster
$query = "SELECT * FROM sqlite_master
UNION SELECT * FROM sqlite_temp_master ";
Li0tingC 3.< B ma0ter and temp;ma0ter data de0criptor0
.o see Fust table descri&tions use thisO.
$query = "SELECT sql FROM sqlite_master WHERE type='table'";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall(PDO::FETCH_ASSOC);
echo"<pre>"; print_r($rows); echo"<pre>"; # show data
Li0tingC 3.8 B #ll ta7le data de0criptor information
.o see a s&ecific tableIs descri&tion use thisO.
$result = $db->query("SELECT * FROM sqlite_master WHERE type='table' AND name='t1'");
$rows = $result->fetchall(PDO::FETCH_ASSOC);
echo "</pre>"; echo"<pre>"; print_r($rows); echo"<pre>"; # show data
Li0tingC 3.? B Specific ta7le de0criptor information
Multi&le 0atabases
The ATTACH DATABASE statement connects one or more database files to the current database
connection.
or the e!am"le belo#$ su""ose #e #ant the current and last %ear&s sales total for one of the
em"lo%ees. Each %ears data is arran'ed b% em"lo%ee name. (e #ill use t#o db files arran'ed li)e
this*

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ! of *!
?ileL dbF.s#lite+
.ableL sales
%ol1 X smith 1ther em&loyeesO
5! more data
55 more data
more data more data

?ileL db'.s#lite+
.ableL sales
%ol1 X smith 1ther em&loyeesO
!! more data
N;$$ more data
more data
<?php
echo '<body style="font-family:arial;">';
echo '<h2>Use Multiple Database</h2>';
// uses db1 & db2 as test databases
// note that "main" is a reserved word for referancing the primary db
// "SELECT * FROM t1" is the same as "SELECT main.* FROM t1"

// connect to SQLite3 database
$db = new PDO("sqlite:db1.sqlite3");

// enhanced error messages
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// query 1st database
$result = $db->query("SELECT smith FROM 'sales' WHERE 1");
$rows = $result->fetchall(PDO::FETCH_ASSOC);
echo "<pre>"; print_r($rows); echo "</pre>";

// access second database, ATTACH needs the FULL path
$result = $db->query("ATTACH database '$_SERVER[DOCUMENT_ROOT]/astrohex_parts/db2.sqlite3' AS
lastyear");

// query 2nd database
$result = $db->query('SELECT * FROM lastyear.sales');
$rows = $result->fetchall(PDO::FETCH_ASSOC);
echo "<pre>"; print_r($rows); echo "</pre>";

// add columns from both databases, as is required
$result = $db->query('SELECT SUM(Total) -- SUM pulls total values
FROM (
SELECT SUM(smith) as Total FROM main.sales -- as is required so sum can pull values
UNION
SELECT SUM(smith) as Total FROM lastyear.sales
)');

$rows = $result->fetchall(PDO::FETCH_ASSOC);
echo "<pre>"; print_r($rows); echo "</pre>";
?>

Result
Database 1
Array (
[0] => Array
([smith] => 50.0)

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 1 of *!

[1] => Array
([smith] => 75.0)
)

Database 2
Array(
[0] => Array
([smith] => 200.0)
)

Total
Array(
[0] => Array
([SUM(Total)] => 325.0)
)

:nd 1f "ection



NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' of *!
Section = G
Table $Add=Cop+=Delete=Rena&e%
Add Table
/e already learned how to add a table to a db. "o that our reference is com&lete, we &rovide the code a'ain. 0onIt for'et
that the db PconnectQ &rocess will create a new db if it does not already e9ist. .herefore, addin' a table to a non e9istent
db will usually result in the db and table bein' created even if it does not e9ist.
<?php
/* Listing 9.1 - Add table to db */
echo '<body style="font-family:arial;">';echo '<h2>Add Table</h2>';

... code to connect to database, and handle errors

# create table
$query = "CREATE TABLE test (
id INTEGER PRIMARY KEY,
editdate TEXT DEFAULT (datetime('now','localtime')),
col1name TEXT(25),
col2name TEXT(25)
)";
$result = $db->query("$query") or die(" Error adding table: <span style='color:red;'>$query</span>");

# show success
echo "<i>Table added successfully.</i><br> ";

# Return rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0tingC .1 B #dd ta7le to a data7a0e
Add Table
Table added successfully.
0 records affected
&,'-,' B for li0ting .1
Does Table >3ist
If we need to checH and see if the table e9ists, here is an enhanced version.
<?php
/* Listing 8.2 - Check if table exists */
echo '<body style="font-family:arial;">';
echo '<h2>Check if table exist</h2>';

... code to connect to database, and handle errors

# check master table for table name
$query = "SELECT name FROM sqlite_master WHERE type='table' and name='table_to_make'";

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' + of *!

$db->query("$query") or die(" Error adding table: <span style='color:red;'>$query</span>");
$totaltables = sqlite_num_rows($query);

# does table exist
if ($totaltables<1) {
# table does not exist, create table
$query = "CREATE TABLE test (
id INTEGER PRIMARY KEY,
col1name TEXT(25),
col2name TEXT(25) )";
}
else{
# table exists...don't create again , do something else
}
. . . program continues
Li0tingC .5 B C1ecH if ta7le e=i0t0
Cop+ Table #ith Data
"2$ite -$.:R .-A$: does not su&&ort Pco&y tableQ. @owever, there are still several o&tions available.
1- db mana'ers may &rovide this feature
- table data could be e9&orted to file then im&orted under a new table name
+- a database could be o&ened and itIs table contents co&ied
<- we can use a combination of %R:-.: and ":$:%. to du&licate a table
I favor method four =<>. /e can tricH the database into creatin' a new table filled with the contents of an old table with
the followin' commandO
Hresult 1 Hdb=2#uery$T%R:-.: .-A$: SDnewtableS -" ":$:%. Y ?R1M SDoldtableST%5
Li0tingC .3 B Cop4 ta7le0
@ere is a com&lete test &ro'ram.
<?php
/* Listing 9.3 Copy table with data */
echo '<body style="font-family:arial;">';
echo '<h2>Copy table with data</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "sample.sqlite3";
$oldtable = "test";
$newtable = "newtbl";

... code to connect to database, and handle errors

$result = $db->query("CREATE TABLE '$newtable' AS SELECT * FROM '$oldtable'");
$rows = $result->fetchAll();

# show table data
$query = "SELECT * FROM '$newtable'";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall(PDO::FETCH_ASSOC);


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' < of *!
# show new table
foreach($rows as $array) {
echo "<pre>";
print_r($array);
echo "</pre>";
}
?>
Li0ting . D Cop4 ta7le and data to new ta7le
Cop+ Table Structure
.o 'et the table structure we can use the followin' code.
<?php
/* Listing 9.4 Copy table */
echo '<body style="font-family:arial;">';
echo '<h2>Copy table structure</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "sample.sqlite3";
$oldtable = "test";
$newtable = "newtbl";

... code to connect to database, and handle errors

# get table structure
$result = $db->query("SELECT * FROM sqlite_master WHERE type='table' and name='$oldtable'");
$row = $result->fetch(SQLITE_ASSOC);
$structure = $row['sql'];
...
Listing: 9.4 Get table structure from MASTER TABLE
Next we substitute the old table name with the new table name in the structure statement using PHP
preg_replace.
# replace old table name with new table name in the structure statement
$search = "/CREATE TABLE $oldtable/";
$replace = "CREATE TABLE $newtable";
$string = $structure;
$query = preg_replace($search, $replace, $string);
Li0tingC .% B Replace old ta7le name wit1 new ta7le name in 0tructure from M#S'ER '#(LE
Now we can create the new table with the same structureO
...
# replace old table name with new table name in the structure statement
$search = "/CREATE TABLE $oldtable/";
$replace = "CREATE TABLE $newtable";
$string = $structure;
$query = preg_replace($search, $replace, $string);

# create new table
$result = $db->query("$query") or die(" Error adding table: <span
style='color:red;'>$query</span>");
...
Li0tingC .* B Create new ta7le
<?php

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5 of *!
/* Listing 9.4 Copy table structure */
echo '<body style="font-family:arial;">';
echo '<h2>Copy table structure</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "sample.sqlite3";
$oldtable = "test";
$newtable = "newtbl";

... code to connect to database, and handle errors

# get table structure
$result = $db->query("SELECT * FROM sqlite_master WHERE type='table' and name='$oldtable' ");
$row = $result->fetch(PDO::FETCH_ASSOC);
$structure = $row['sql'];

# replace old table name with new table name in the structure statement
$search = "/CREATE TABLE $oldtable/";
$replace = "CREATE TABLE $newtable";
$string = $structure;
$query = preg_replace($search, $replace, $string);
# create new table
$result = $db->query("$query") or die(" Error adding table: <span
style='color:red;'>$query</span>");

# show success
echo "<i>Table added successfully.</i><br> ";

# Return rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0tingC .< B Complete li0ting for cop4ing ta7le 0tructure
Delete Table
.o dro& =delete> a table, we use this commandO
$query = "DROP TABLE 'test' ";
Li0tingC .8 B Delete ta7le
Rena&e Table
.he synta9 used to rename a table is
$query = "ALTER TABLE 'oldTablename' RENAME TO 'newTablename' ";
Li0tingC .? B Rename ta7le
.his command cannot be used to moe a table between two different databases, only to rename a table within the
same database.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' , of *!
(ie, Table Structures
.he synta9 used to rename a table is
<?php
/* Listing 9.7 - View table structures */
echo '<body style="font-family:arial;">';
echo '<h2>View table structures</h2>';

# setup
$dbPath = $_SERVER['DOCUMENT_ROOT'] . "/contacts/";
$dbFile = "sample.sqlite3";

... code to connect to database, and handle errors

$result = $db->query("SELECT * FROM sqlite_master WHERE type='table'"); #Lists all tables
$rows = $result->fetchall(PDO::FETCH_ASSOC);

# print_r output
echo "<br><span style='color:red;'>Table structures for $dbFile</span>";
foreach($rows as $array) {
echo "<pre>";
print_r($array);
echo "</pre>";
}
?>
Li0tingC .10 B Delete ta7le
View table structures

Table structures for sample.sqlite3
Array
(
[type] => table
[name] => test
[tbl_name] => test
[rootpage] => 2
[sql] => CREATE TABLE test (
id INTEGER PRIMARY KEY,
name TEXT(25),
phone TEXT(25)
)
)
Array
(
[type] => table
[name] => new
[tbl_name] => new
[rootpage] => 3
[sql] => CREATE TABLE new (c1 TEXT, c2 TEXT)
)
&,'-,' B For li0ting .10
Table Description
.y&e field will always be StableS and the name field will be the SnameS of the table. .o 'et a list of all tables in the database,
use the followin' ":$:%. commandL

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5 of *!
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
Li0tingC .11 B ta7le de0criptione

?or indices, ty&e is e#ual to Sinde9S, name is the name of the inde9, and tblCname is the name of the table to which the
inde9 belon's.
?or both tables and indices, the s#l field is the te9t of the ori'inal %R:-.: .-A$: or %R:-.: IN0:6 statement that created
the table or inde9.


:nd 1f "ection


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' * of *!
Section = I
Ro, $Add=Cop+=Delete=Rena&e%
-nsert Ro, $add%
-s we have done before, we will &ut our #uery statement in a variable so that we can have a better error messa'e.
I in0ert data D all column0 are filled
$query = "INSERT INTO 'tablename' VALUES('col1Data','col2Data','col3Data')";
$result = $db->query("$query") or die(" Error: <span style='color:red;'>$query</span>");
Li0tingC %.1 B in0ert data B in0erting a 0ingle row Grecord3 of information.
Notice that we did not s&ecify an id. Aecause id is a primary field, "2$ite is smart enou'h to automatically increment a
uni#ue number for us.
$istin'-1!.1 is a short form of the IN":R. command and e9&ects a value for every column =field>. If we intend to leave
some columns blanH or are 'oin' to edit or u&date certain columns in a row, we must s&ecify which column names we are
'oin' to be worHin' with.
$on' version of IN":R.
I in0ert data D0pecif4ing w1ic1 field0 to fill
$query = "INSERT INTO 'tablename' (field_x,field_y) VALUES('col1Data',' col2Data')";
$result = $db->query("$query") or die(" Error: <span style='color:red;'>$query</span>");
Li0tingC %.5 B in0ert data B column name0 0pecified.
-nsert = Multiple Ro,s
insert into mytable (col,col2,col3)
select 'a1', 'b1', 'c1'
union
select 'a2', 'b2', 'c2'
union
select 'a3', 'b3', 'c3'
...
Cop+ Ro,
.o co&y a row we first ":$:%. the row to co&y then IN":R. it as a new row.
Delete All Ro,s
.o delete all the rows in a table we can use thisO
$query = "DELETE FROM 'tablename'";
Li0tingC %.3 B
Delete Ro,$s% Matchin* Condition
If we Hnow the record id then we can use thisO
$query = "DELETE FROM tablename WHERE id='xxx' ";

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 7 of *!
Li0tingC %. B
If we do not Hnow the record id then we must search for the record on one of its fields and return the id for itO
$query = "DELETE FROM test WHERE id=(SELECT id FROM test WHERE fieldName LIKE 'condition') ";
Li0tingC %.% B
@ere is a com&lete e9am&le that deletes the record that starts with a name od PJamesQ.
<?php
/* Listing 10.3 Search and delete row */
echo '<body style="font-family:arial;">';
echo '<h2>Search and delete row</h2>';

... code to connect to database, and handle errors

# delete row
$query = "DELETE FROM test WHERE id=(SELECT id FROM test WHERE name LIKE 'James%') ";
$result = $db->query("$query") or die(" Error deleting row: <span
style='color:red;'>$query</span>");

# show success
echo "<i>Row deleted successfully.</i><br> ";

# Return rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

# close db, PHP will also do this automatically
$db = NULL;
?>
Li0tingC %.* B Searc1 and delete a row
Delete row
Row deleted successfully.
1 records affected
&,'-,' B re0ult of running li0tingC %.*
Count Ro,s
.here is no way for )01 to Hnow the row%ount of a ":$:%. result because the "2$ite -)I itself doesnSt offer this ability.
.herefore row%ont=> does not worH. /e can count columns usin' )01Is built in column%ount=> function. @ere is how to
'et these counts.
1. ;se )01Ss fetchCall=> function to fetch all the rows into an array, then use count=> on it.
. 0o an e9tra #uery to ":$:%. %1;N.=Y>.
$query = "SELECT * FROM 'tablename'";
$result = $db->query("$query") or die(" Error deleting row: <span style='color:red;'>$query</span>");
$rowcount = count($result->fetchAll());
echo "Total rows = $rowcount<br>";

$query = "SELECT * FROM 'tablename'";
$result = $db->query("$query") or die(" Error deleting row: <span style='color:red;'>$query</span>");
$rowcount = count($result);
echo "Total rows = $rowcount<br>";

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +! of *!

$query = "SELECT COUNT(*) FROM 'tablename'";
$result = $db->query("$query") or die(" Error deleting row: <span style='color:red;'>$query</span>");
$rows = count($result->fetchAll());
$rowcount = $rows[0][0];
echo "Total rows = $rowcount<br>";

$colcount = $result->columnCount();
echo "Column count = $colcount<br>";
Li0tingC %.< B Count row0 and column0
-nde3 Description
.y&e field will always be StableS and the name field will be the SnameS of the table. .o 'et a list of all tables in the database,
use the followin' ":$:%. commandL
SELECT name FROM sqlite_master
WHERE type='index'
ORDER BY name;
Li0tingC %.8 B
?or indices, ty&e is e#ual to Sinde9S, name is the name of the inde9, and tblCname is the name of the table to which the
inde9 belon's.
?or both tables and indices, the s#l field is the te9t of the ori'inal %R:-.: .-A$: or %R:-.: IN0:6 statement that created
the table or inde9.


:nd 1f "ection


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +1 of *!
Section = J
Colu&n $Add=Cop+=Delete=Rena&e%
Add Colu&n
$query = "ALTER TABLE '[database.]tablename' ADD COLUMN 'newColumn' TEXT ";
Li0tingC *.1 D Creating a new data7a0e
Cop+ Colu&n and data
S6Lite #L'ER '#(LE onl4 0upport0 9add column: to t1e end of a ta7le> or 9c1ange ta7le name:. !f 4ou want to maHe ot1er
c1ange0 t1an t1e ta7le will 1a/e to 7e copied in t1e proper order and renamed.
Hresult 1 Hdb=2#uery$T%R:-.: .-A$: SDnewtableS -" ":$:%. Scol.o%o&y S ?R1M SDoldtableST%5
Li0tingC *.5 B
Delete Colu&n
Su""ose %ou ha+e a table named ,t1, #ith columns names ,a,$ ,b,$ and ,c,. If %ou #ant to delete
column ,c, from this table$ than the follo#in' ste"s illustrate ho# this could be done-
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
Li0tingC *.3 D Creating a new data7a0e
Rena&e Colu&n
"ay you have a table and need to rename ToldCbT to TnewCbTL
1) Rename the old table:
ALTER TABLE orig_table_name RENAME TO tmp_table_name;

2) Create a new table, based on the old table but with the updated column name:

CREATE TABLE orig_table_name (
col_a INT,
new_b INT
);

3) Copy the contents across from the original table.

INSERT INTO orig_table_name(col_a, new_b)
SELECT col_a, old_b
FROM tmp_table_name;

4) Drop the old table.
DROP TABLE tmp_table_name;
Li0tingC *. D Creating a new data7a0e

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' + of *!
Count Colu&ns
We can count columns using PDOs built in columnCount() function. There is no way for PDO
to know the rowCount of a S!CT result because the S"!ite #P$ itself %oesn&t offer this
ability. Therefore rowCont() %oes not work. 'ere is how to get these counts.

$rowcount = count($result->fetchAll());
echo "Total rows = $rowcount<br>";

$colcount = $result->columnCount();
echo "Column count = $colcount<br>";
Li0tingC *.% B Count row0 and column0

:nd 1f "ection


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ++ of *!
Section = K
Sho, and Search Data
Si&ple Select S+nta3
Recall from section-5 that we can search our database usin' ":$:%. then e9tract the information usin' ?:.%@. .he sim&le
form of this command is shown below.

$query = "SELECT <column(s)> FROM 'table' ";
$result = $db->query($query, PDO::FETCH_ASSOC)
$row = $result->fetchall(PDO::FETCH_ASSOC);
Li0tingC <.1 B Simple 0earc1e0 u0ing SELEC'
/e often use an asterisH Y for the column information so that all columns in the row=s> are returned.
$query = "SELECT * FROM 'table' ";
Li0tingC <.5 B +eneric form of SELEC'
The four fetch t+pes
/* PDOStatement::fetch styles */
$query = "select * from t1";

print("<br><br>PDO::FETCH_ASSOC: next row as an array indexed by column name.<br>");
$result = $db->query($query);
$row = $result->fetch(PDO::FETCH_ASSOC);
echo "<pre>"; print_r($row); echo "</pre>";
print("<br><br>");

print("PDO::FETCH_BOTH: next row as an array indexed by both column name and number<br>");
$result = $db->query($query);
$row = $result->fetch(PDO::FETCH_BOTH);
echo "<pre>"; print_r($row); echo "</pre>";
print("<br><br>");

print("PDO::FETCH_LAZY: next row as an anonymous object with column names as properties<br>");
$result = $db->query($query, PDO::FETCH_LAZY);
$row = $result->fetch(PDO::FETCH_LAZY);
echo "<pre>"; print_r($row); echo "</pre>";
print("<br><br>");

print("PDO::FETCH_OBJ: next row as an anonymous object with column names as properties<br>");
$result = $db->query($query, PDO::FETCH_OBJ);
$row = $result->fetch(PDO::FETCH_OBJ);
echo "<pre>"; print_r($row); echo "</pre>";
print("<br><br>");
Li0tingC <.3 B
Re0ult0 from t1e a7o/e li0ting
PDO::FETCH_ASSOC: next row as an array indexed by column name.
Array
(

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +< of *!
[id] => 1
[name] => Frank
[dob] => 2011.12.09
)


PDO::FETCH_BOTH: next row as an array indexed by both column name and number
Array
(
[id] => 1
[0] => 1
[name] => Frank
[1] => Frank
[dob] => 2011.12.09
[2] => 2011.12.09
)


PDO::FETCH_LAZY: next row as an anonymous object with column names as properties PDORow Object
(
[queryString] => select * from t1
[id] => 1
[name] => Frank
[dob] => 2011.12.09
)


PDO::FETCH_OBJ: next row as an anonymous object with column names as properties stdClass Object
(
[id] => 1
[name] => Frank
[dob] => 2011.12.09
)

#H>R>
.he WHERE clause includes a com&arison &redicate, which restricts the rows returned by the #uery. .he WHERE clause
eliminates all rows from the result set for which the com&arison &redicate does not evaluate to .rue. Remember that if
we use the /@:R: statement by itself, it re#uires an e9act match.
$query = "SELECT * FROM 'table' WHERE name='James Bond'";
Li0tingC <. B Simple 0earc1e0 u0ing WHERE
If we want to use wild cards then we must add the $IW: statement. /hen usin' $IW:, we have the followin' rulesL

_ = under score _ will match any single character (like the ? in batch files, dos, or unix)
% = percent % will match zero or more of any character (like the * in batch files, dos, or
unix)
-B>
.he $IW: clause allows use to use wildcards or less s&ecific match criteria
/e can also use more than one condition.
$query = "SELECT * FROM 'table' WHERE name LIKE 'James%'";

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +5 of *!
Li0tingC <.% B Simple 0earc1e0 u0ing L!"E
A@D
/e can use -N0 to re#uire more than one condition. %onsider this e9am&leO
$query = "SELECT * FROM 'table' WHERE name LIKE 'James%' AND WHERE phone LIKE '007%' ";
Li0tingC <.* B Simple 0earc1e0 u0ing #$D for multiple condition0
OR
/e also could use 1R to select between different conditions. %onsider this e9am&leO
$query = "SELECT * FROM 'table' WHERE name LIKE 'James%' OR WHERE name LIKE 'Jane%' ";
Li0tingC <.< B Simple 0earc1e0 u0ing #$D for multiple condition0
/e have several other o&tions and that leads us to our section on advanced searches.
Advanced Select S+nta3
-M-T
/hat if we need a set of rows from a 'iven offset such as the nt1 record4 /e can use the $IMI. statement to draw results
startin' with a 'iven inde9 and 'et the s&ecified number of rows from that &oint. .he synta9 isO
LIMIT index, count
@ere is a ty&ical e9am&le where we want the third record from the match setO
$query = "SELECT * FROM 'tablename' LIMIT 2,1";
Li0tingC <.8 B
.his returns the third record because ewe used an inde9 of PQ and inde9es start with Kero =!,1,O> /e will 'et 1 record
bacH because we 'ave a record count of 1. If we wanted the first 5 records after 1!! then we would use $IMI. 77,5.
.here are two very common uses for $IMI.. .he first is to show a reasonable number of records on the screen or &rinter
&a'e. If we were 'oin' to list 1!! records from a certain date, we mi'ht want half of them on the first &a'e and the other
half &rinted on the second &a'e.
1ur statements mi'ht looH somethin' liHe thisO
$query = "SELECT * FROM 'tablename' WHERE 'dob'='1975' LIMIT 0, 49";
Li0tingC <.? B
1ur ne9t #uery would beO
$query = "SELECT * FROM 'tablename' WHERE 'dob'='1975' LIMIT 50, 50";
Li0tingC <.10 B
.he other common use of limit is to 'et the &revious or ne9t record from the current one. If we are at inde9 ,5, we can
add or subtract 1 from this inde9 and 'et a record in either direction.
# get next record
$rowindex = 65;
$query = "SELECT * FROM 'tablename' LIMIT ($rowindex+1), 1";

# more...code


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +, of *!
# get previous record
$rowindex = 65;
$query = "SELECT * FROM 'tablename' LIMIT ($rowindex-1), 1";
Li0tingC <.11 B
/e could use $IMI. Drowinde9Z1,1 but I thinH the = > maHes the code more readable so I use $IMI. =Drowinde9Z1>,1. 1f
course we can have more than one row returned by usin' different values for count.
ORD>R 0L
.he ORDER BY clause identifies which columns are used to sort the resultin' data, and in which direction they should be
sorted =o&tions are ascendin' or descendin'>. /ithout an ORDER BY clause, the order of rows returned by an "2$ #uery
usually correlates to the pri&ar+ )e+ field such as an I0 number.
.he synta9 isO
ORDER BY column
or
ORDER BY column DIRECTION

%onsider this e9am&leO

# sort records by last name in DESCENDING order
$query = "SELECT * FROM 'tablename' ORDER BY 'lname''DESC';
# more...code
# sort records by last name in ASCENDING order (default)
$query = "SELECT * FROM 'tablename' ORDER BY 'lname''ASC';
Li0tingC <.15 B
If no sort order is s&ecified, it will be -"%:N0IN8 which is al&habetical andMor small to lar'e numbers. .o reverse the
default sort order we must use P0:"%Q. /e can use more than one column id there is a match. ?or e9am&le su&&ose we
are sortin' student names in a school and there is more than one P"mithQ. /e can use the date of birth tooO

# sort records by last name in ASCENDING order (default)
$query = "SELECT * FROM 'tablename' ORDER BY 'lname''DESC','dob''ASC';
Li0tingC <.13 B
.his sorts the last names al&habetically =normal order>, but the CdobCCASCC lists the students with the same nameJ oldest to
youn'est accordin' to date of birth =o&&osite the normal order>.
?ROAP 0L
1nly shows fields e9&licitly mentioned. /hen the records are 'rou&ed, all fields e9ce&t those which are e9&licitly 'iven in
8R1;) AN are removed. .he GROUP BY clause is used to &roFect rows havin' common values into a smaller set of rows.
GROUP BY is often used in conFunction with "2$ a''re'ation functions or to eliminate du&licate rows from a result set.
.he WHERE clause is a&&lied before the GROUP BY clause.
HA(-@?
.he HAVING clause includes a &redicate used to filter rows resultin' from the GROUP BY clause. Aecause it acts on the
results of the GROUP BY clause, a''re'ation functions can be used in the HAVING clause &redicate. - HAVING clause in

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +5 of *!
"2$ s&ecifies that an "2$ SELECT statement should only return rows where a''re'ate values meet the s&ecified
conditions. It was added

to the "2$ lan'ua'e because the WHERE Heyword could not be used with a''re'ate functions
.o return a list of de&artment I0s whose total sales e9ceeded D1!!! on the date of January 1, !!!, alon' with the sum of
their sales on that dateL

SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000
Li0tingC <.1 B
.he followin' #uery will return the list of de&artments who have more than 1 em&loyeeL
SELECT DepartmentName, COUNT(*)
FROM employee,department
WHERE employee.DepartmentID = department.DepartmentID
GROUP BY DepartmentName
HAVING COUNT(*)>1;
Li0tingC <.1% B


:nd 1f "ection

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +* of *!
Section = M
(ie,s
- vie, is a s&ecific looH on data in from one or more tables. It can arran'e data in some s&ecific order, hi'li'ht or hide
some data. - view consists of a stored #uery accessible as a virtual table com&osed of the result set of a #uery. ;nliHe
ordinary tables a view does not form &art of the &hysical schema. It is a dynamic, virtual table com&uted or collated from
data in the database.
In the ne9t e9am&le, we create a sim&le view.
sqlite> SELECT * FROM Cars;
Li0tingC 8.1 B
Id Name Cost
---------- ---------- ----------
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
&utputC 8.1 B
.his is our data, u&on which we create the view.

sqlite> CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Cost < 30000;
sqlite> SELECT * FROM CheapCars;
Li0tingC 8.5 B
Name
----------
Skoda
Volvo
Citroen
Volkswagen
&utputC 8.5 B

.echnically a view is a virtual table. "o we can list all views with a 6tables command. .o remove a view, we use the DROP
(-># "2$ statement.
sqlite> .tables
Books CheapCars Friends Names Reservations
Cars Customers Log Orders Testing
sqlite> DROP VIEW CheapCars;
sqlite> .tables
Books Customers Log Orders Testing
Cars Friends Names Reservations

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' +7 of *!
Section = ;
Tri**ers
Tri**ers are database o&erations that are automatically &erformed when a s&ecified database event occurs.
-nsert Ro, Ti&esta&p
It is a 'ood &ractice to Hee& tract of the last time a file was edited. /e do this by savin' a time stam&. -n insert tri''er is
created below in the file Ttri''er1T. .he %oordinated ;niversal .ime =;.%> will be entered into the field Ttime:nterT, and
this tri''er will fire after a row has been inserted into the table t1.
-- ********************************************************************
-- Creating a trigger for timeEnter
-- Run as follows:
-- $ sqlite3 test.db < trigger1
-- ********************************************************************
CREATE TRIGGER update_editdate AFTER INSERT ON t1
BEGIN
UPDATE t1 SET editdate = DATETIME('NOW') WHERE rowid = new.rowid;
END;
-- ********************************************************************
Li0tingC ?.1 B
-nother way of Hee&in' a timestam& =&erha&s easier than a tri''er> is to use the default value of the table scheme. .he
field definition is 'iven below.
editdate TEXT DEFAULT (datetime('now','localtime')),
Li0tingC 1%.5 B
o**in* All -nsertsN ApdatesN and Deletes
.he scri&t below creates the table e9amlo' and three tri''ers =u&dateCe9amlo', insertCe9amlo', and deleteCe9amlo'> to
record u&dates, inserts, and deletes made to the e9am table. In other words, whenever a chan'e is made to the e9am
table, the chan'es will be recorded in the e9amlo' table, includin' the old value and the new value. If you are familiar
with My"2$, the functionality of this lo' table is similar to My"2$Ss binlo'. "ee .i&s , <, and 5 if you would liHe more
information on My"2$Ss lo' file.
-- Create an update trigger
CREATE TRIGGER update_examlog AFTER UPDATE ON exam
BEGIN

INSERT INTO examlog (ekey,ekeyOLD,fnOLD,fnNEW,lnOLD,
lnNEW,examOLD,examNEW,scoreOLD,
scoreNEW,sqlAction,examtimeEnter,
examtimeUpdate,timeEnter)

values (new.ekey,old.ekey,old.fn,new.fn,old.ln,
new.ln,old.exam, new.exam,old.score,
new.score, 'UPDATE',old.timeEnter,
DATETIME('NOW'),DATETIME('NOW') );

END;
--
-- Also create an insert trigger
-- NOTE AFTER keyword ------v
CREATE TRIGGER insert_examlog AFTER INSERT ON exam

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <! of *!
BEGIN
INSERT INTO examlog (ekey,fnNEW,lnNEW,examNEW,scoreNEW,
sqlAction,examtimeEnter,timeEnter)

values (new.ekey,new.fn,new.ln,new.exam,new.score,
'INSERT',new.timeEnter,DATETIME('NOW') );

END;

-- Also create a DELETE trigger
CREATE TRIGGER delete_examlog DELETE ON exam
BEGIN

INSERT INTO examlog (ekey,fnOLD,lnNEW,examOLD,scoreOLD,
sqlAction,timeEnter)

values (old.ekey,old.fn,old.ln,old.exam,old.score,
'DELETE',DATETIME('NOW') );

END;
Li0tingC ?.5 B
In the followin' e9am&le, we will use the ?riends table and create a new $o' table.
CREATE TABLE Log(Id integer PRIMARY KEY, OldName text, NewName text, Date text);
Li0tingC ?.3 B
/e will create new tables for the ne9t e9am&le with tri''ers.
CREATE TRIGGER mytrigger UPDATE OF Name ON Friends
BEGIN
INSERT INTO Log(OldName, NewName, Date) VALUES(old.Name, new.Name, datetime('now'));
END;
Li0tingC ?. B
/e create a tri''er called mytri''er with the %R:-.: .RI88:R statement. .his tri''er will launch a IN":R. statement
whenever we u&date the name column of the ?riends table. .he IN":R. statement will insert the old name, the new
name and the time stam& into the $o' table.
sqlite> SELECT * FROM Friends;
Id Name Sex
---------- ---------- ----------
1 Jane F
2 Thomas M
3 Franklin M
4 Elisabeth F
5 Mary F
6 Lucy F
7 Jack M
.his is our data.
Ne9t, we are 'oin' to u&date one row of the ?riends table.
sqlite> UPDATE Friends SET Name='Frank' WHERE Id=3;
Li0tingC ?.% B
/e u&date the third row of the table. .he tri''er is launched.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <1 of *!
sqlite> SELECT * FROM Log;
Id OldName NewName Date
---------- ---------- ---------- -------------------
1 Franklin Frank 2009-11-14 16:36:28
/e checH the $o' table. .his lo' confirms the u&date o&eration we &erformed.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' < of *!
Section = F:
Transactions
- transaction is an atomic unit of database o&erations a'ainst the data in one or more databases. .he effects of all the
"2$ statements in a transaction can be either all committed to the database or all rolled bacH.
In "2$ite, any command other than the ":$:%. will start an im&licit transaction. Manual transactions are started with the
0>?-@ TRA@SACT-O@ statement and finished with the COMM-T 1R RO0ACB statements.
BEGIN TRANSACTION;
CREATE TABLE Test(Id integer NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
COMMIT;
Li0tingC 10.1 B
/e have a N1. N;$$ constraint set on the Id column. .hus, the fourth insert will not succeed. "2$ite does transactions
s&ecifically. ?or some errors, it reverts all chan'es. ?or others, it reverts only the last statement and leaves other chan'es
intact. In our case, the table is created and the first three inserts are written into the table. .he fourth one is not.
"ay, we already had an em&ty table named .est. :9ecutin' the above transaction would fail com&letely. No chan'es
would be written. If we chan'ed the CR>AT> TA0> statement into CR>AT> TA0> -< @OT >X-STS, the first three
statements would e9ecute.
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Test(Id integer NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
ROLLBACK;
Li0tingC 10.5 B
- transaction can end with a COMM-T or a RO0ACB statement. .he RO0ACB reverts all chan'es.


:nd 1f "ection

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <+ of *!
Section = FF
"oins
"O-@7 %ombine data from two or more tables
-@@>R "O-@7 0efault Join ty&e. %reate new table where one or more table rows meet criteria.
@ATARA "O-@7 %ombines all columns in tables with same column-name.
><T OAT>R "O-@7 Retain all records in table on the left side =re'ardless of match>, mer'e only matches from ri'ht
side.
R-?HT OAT>R "O-@7 Retain all records in table on the ri'ht side =re'ardless of match>, mer'e only matches from left
side.
$oteC Left and rig1t Join refer to t1e two 0ide0 of t1e J&!$ He4word. Left and R!+H' Join0 are
identical 74 re/er0ing ta7le po0ition0.
<A "O-@7 Mer'e all tables re'ardless of match.
S>< "O-@7 "earch one table for matches in a column with two ore more identical entries. i.e. "ame de&t
name Be9cludes sin'le entries.
CROSS "O-@7 8ets a record from the first table and then creates a new row for every row in the nd table. It
then does the same for the ne9t record in the first table etc. %aution should be used, when
there is no /@:R: filter a&&lied the number of records returned will be .1 Y . records.
M>R?> RO#7 %oncatenates field data a'ainst match column
A@-O@7 .he ;NI1N o&erator is used to combine the result-set of two or more ":$:%. statements.


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' << of *!
"u&&ose we have the followin' tables
sqlite> .headers ON
sqlite> SELECT * FROM Artists;
ArtistID|ArtistName
1 |Peter Gabriel
2 |Bruce Hornsby
3 |Lyle Lovett
4 |Beach Boys
5 |Supernatural

sqlite> SELECT * FROM CDs;
CDID|ArtistID|Title |Date
1 |1 |So |1984
2 |1 |Us |1992
3 |2 |The Way It Is |1986
4 |2 |Scenes from the Southside|1990
5 |1 |Security |1990
6 |3 |Joshua Judges Ruth |1992
7 |4 |Pet Sounds |1966
CROSS "O-@
It is also called the Tdirect FoinT or %artesian &roductL
sqlite>SELECT * FROM Artists, CDs;
Li0tingC 11.1 B
In some "2$ dialect it can be also used
sqlite>SELECT * FROM Artists CROSS JOIN CDs;
Li0tingC 11.5 B
$et us try to filter the result with the /@:R: clauseL
sqlite>SELECT ArtistName, Title FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID;
Li0tingC 11.3 B
It 'ives
Peter Gabriel|So
Peter Gabriel|Us
Peter Gabriel|Security
Bruce Hornsby|The Way It Is
Bruce Hornsby|Scenes from the Southside
Lyle Lovett |Joshua Judges Ruth
Beach Boys |Pet Sounds
.o avoid confusion, use this statement instead
sqlite>SELECT Artists.ArtistName, CDs.Title FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID;
or with aliases
sqlite>SELECT a.ArtistName, c.Title FROM Artists a, CDs c WHERE a.ArtistID=c.ArtistID;
Li0tingC 11. B

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <5 of *!

-@@>R "O-@
.he same result can be achieved with the INN:R J1IN clauseL
sqlite> SELECT Artists.ArtistName, CDs.Title FROM Artists INNER JOIN CDs ON
Artists.ArtistID=CDs.ArtistID;
Li0tingC 11.% B
.he INN:R J1IN returns all rows from both tables where there is a match. If there are rows in -rtists that do not have
matches in %0s, those rows will not be listed.
><T OAT>R "O-@
.he $:?. 1;.:R J1IN o&erator ensures that all rows on the TleftT side of the Foin, in this case the -rtists table, will be
included.
sqlite>SELECT * FROM Artists LEFT OUTER JOIN CDs ON Artists.ArtistID = CDs.ArtistID;
Li0tingC 11.* B
.here is no "u&ernatural in %0s but you can still see "u&ernatural with em&ty fields from -rtistsL
ArtistID|ArtistName |CDID|ArtistID|Title |Date
1 |Peter Gabriel|1 |1 |So |1984
1 |Peter Gabriel|2 |1 |Us |1992
1 |Peter Gabriel|5 |1 |Security |1990
2 |Bruce Hornsby|3 |2 |The Way It Is |1986
2 |Bruce Hornsby|4 |2 |Scenes from the Southside|1990
3 |Lyle Lovett |6 |3 |Joshua Judges Ruth |1992
4 |Beach Boys |7 |4 |Pet Sounds |1966
5 |Supernatural | | | |
"ome "2$ dialects use $:?. J1IN.
In other dialects =1racle> instead of
sqlite> SELECT Artists.ArtistName, CDs.Title
------> FROM Artists
------> LEFT OUTER JOIN CDs
------> ON Artists.ArtistID = CDs.ArtistID;
Li0tingC 11.< B
the followin' statement is used
SELECT Artists.ArtistName, CDs.Title
FROM Artists, CDs
WHERE Artists.ArtistID = CDs.ArtistID(+)
Li0tingC 11.8 B
.he =Z> symbol denotes the table =side> that may have no matchin' rows to the other table =side>. .hinH of this as
everythin' from the left &lus matchin' values from the ri'ht.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <, of *!
R-?HT OAT>R "O-@ and <A OAT>R "O-@
"imilarly this "2$
SELECT Artists.ArtistName, CDs.Title
FROM Artists
RIGHT OUTER JOIN CDs
ON Artists.ArtistID = CDs.ArtistID;
Li0tingC 11.? B
1r
SELECT Artists.ArtistName, CDs.Title
FROM Artists, CDs
WHERE Artists.ArtistID(+) = CDs.ArtistID
Li0tingC 11.10 B
would return all records from the ri'ht side.
%urrently "2$ite su&&orts neither of the above synta9. @owever there is easy worHaroundL use $:?. 1;.:R J1IN with
interchan'ed tables. .he ?;$$ 1;.:R J1IN clause is not su&&orted as well.
A@-O@
.he ;NI1N o&erator is used to combine the result-set of two or more ":$:%. statements.
Notice that each ":$:%. statement within the ;NI1N must have the same number of columns. .he columns must also
have similar data ty&es. -lso, the columns in each ":$:%. statement must be in the same order.
"2$ ;NI1N "ynta9
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Li0tingC 11.11 B
@ote7 .he ;NI1N o&erator selects only distinct values by default. .o allow du&licate values, use ;NI1N -$$.
"2$ ;NI1N -$$ "ynta9
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
Li0tingC 11.15 B


:nd 1f "ection

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <5 of *!
Section = F'
Date and Ti&e <unctions
<unction <or&at
"2$ite su&&orts si9 date and time functions as followsL
1. now() same as datetime(now,localtime)
2. date(timestring, modifier, modifier, ...)
3. time(timestring, modifier, modifier, ...)
4. datetime(timestring, modifier, modifier, ...)
5. julianday(timestring, modifier, modifier, ...)
6. strftime(format, timestring, modifier, modifier, ...)
Li0tingC 15.1 B
-ll five date and time functions taHe a time strin' as an ar'ument. .he time strin' is followed by Kero or more modifiers.
.he strftime=> function also taHes a format strin' as its first ar'ument.
.he date and time functions use a subset of I"!-*,!1 date and time formats. .he date=> function returns the date in this
formatL NNNN-MM-00. .he time=> function returns the time as @@LMML"". .he datetime=> function returns PNNNN-MM-00
@@LMML""Q. .he Fulianday=> function returns the Julian day - the number of days since noon in 8reenwich on November
<, <51< A.%. =)role&tic 8re'orian calendar>. .he strftime=> routine returns the date formatted accordin' to the format
strin' s&ecified as the first ar'ument. .he format strin' su&&orts the most common substitutions found in the strftime=>
function from the standard % library &lus two new substitutions, Rf and RJ. .he followin' is a com&lete list of valid
strftime=> substitutionsL
%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %
Li0tingC 15.5 B
Notice that all other date and time functions can be e9&ressed in terms of strftime=>L
?unction :#uivalent strftime=>
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)
Li0tingC 15.3 B
.he only reasons for &rovidin' functions other than strftime=> is for convenience and for efficiency.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <* of *!
.ime "trin's - time strin' can be in any of the followin' formatsL
1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD
Li0tingC 15. B
In formats 5 throu'h 5, the P.Q is a literal character se&aratin' the date and the time, as re#uired by I"1-*,!1. ?ormats *
throu'h 1! that s&ecify only a time assume a date of !!!-!1-!1. ?ormat 11, the strin' SnowS, is converted into the
current date and time as obtained from the 9%urrent.ime method of the s#lite+Cvfs obFect in use. ;niversal %oordinated
.ime =;.%> is used. ?ormat 1 is the Julian day number e9&ressed as a floatin' &oint value.
Modifiers
.he time strin' can be followed by Kero or more modifiers that alter the date and time strin'. :ach modifier is a
transformation that is a&&lied to the time value to its left. Modifiers are a&&lied from left to ri'htJ order is im&ortant.
.he available modifiers are as follows.
1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10.weekday N
11.unixepoch
12.localtime
13.utc
Li0tingC 15.% B
.he first si9 modifiers =1 throu'h ,> sim&ly add the s&ecified amount of time to the date and time s&ecified by the
&recedin' timestrin' and modifiers. Note that QZM- NNN monthsQ worHs by renderin' the ori'inal date into the NNNN-MM-
00 format, addin' the ZM- NNN to the MM month value, then normaliKin' the result. .hus, for e9am&le, the data !!1-!+-
+1 modified by SZ1 monthS initially yields !!1-!<-+1, but -&ril only has +! days so the date is normaliKed to !!1-!5-!1. -
similar effect occurs when the ori'inal date is ?ebruary 7 of a lea&year and the modifier is ZM- N years where N is not a
multi&le of four.
.he Pstart ofQ modifiers =5 throu'h 7> shift the date bacHwards to the be'innin' of the current month, year or day.
.he PweeHdayQ modifier advances the date forward to the ne9t date where the weeHday number is N. "unday is !,
Monday is 1, and so forth.
.he Puni9e&ochQ modifier =11> only worHs if it immediately follows a timestrin' in the 0000000000 format. .his
modifier causes the 0000000000 to be inter&reted not as a Julian day number as it normally would be, but as ;ni9 .ime

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' <7 of *!
- the number of seconds since 175!. If the Puni9e&ochQ modifier does not follow a timestrin' of the form 0000000000
which e9&resses the number of seconds since 175! or if other modifiers se&arate the Puni9e&ochQ modifier from &rior
0000000000 then the behavior is undefined. 0ue to &recision limitations im&osed by the im&lementations use of ,<-bit
inte'ers, the Puni9e&ochQ modifier only worHs for dates between !!!!-!1-!1 !!L!!L!! and 5+5-11-!1 1!L5L<5 =uni9
times of -,1,517!! throu'h 1!,551771,5>.
.he PlocaltimeQ modifier =1> assumes the time strin' to its left is in ;niversal %oordinated .ime =;.%> and adFusts the
time strin' so that it dis&lays localtime. If PlocaltimeQ follows a time that is not ;.%, then the behavior is undefined. .he
PutcQ is the o&&osite of PlocaltimeQ. PutcQ assumes that the strin' to its left is in the local timeKone and adFusts that strin'
to be in ;.%. If the &rior strin' is not in localtime, then the result of PutcQ is undefined.
Ti&esta&p
It is a 'ood &ractice to Hee& tract of the last time a file was edited. /e do this by savin' a time stam&. .he field definition
is 'iven below.
editdate TEXT DEFAULT (datetime('now','localtime')),
>3a&ples
Compute the current date.
SELECT date('now');
2009-11-05

Compute the current date & time.
SELECT datetime('now');
2009-11-05 20:01:07

Compute the last day of the current month.
SELECT date('now','start of month','+1 month','-1 day');
26

Compute the date and time given a unix timestamp 1092941466.
SELECT datetime(1092941466, 'unixepoch');
2009-11-05 20:01:07

Compute date and time given a unix timestamp 1092941466, and compensate for your local timezone.
SELECT datetime(1092941466, 'unixepoch', 'localtime');
2009-11-05 20:01:07

Compute the current unix timestamp.
SELECT strftime('%s','now');
1092941466

Subtract one timestamp from the other, use this only for dates after 1970!
SELECT trip_id, start_time, end_time,STRFTIME('%s',end_time)-STRFTIME('%s',start_time) FROM t1; #
2:56:22

Compute number of days since signing the US Declaration of Independence.
SELECT julianday('now') - julianday('1776-07-04');
76543

Compute the number of seconds since a particular moment in 2004:
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
476977647

Compute the date of the first Tuesday in October for the current year.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5! of *!
SELECT date('now','start of year','9 months','weekday 2');
2009-10-05

Compute time since unix epoch in seconds (like strftime('%s','now') except includes fractional part):
SELECT (julianday('now') - 2440587.5)*86400.0;

Compute date in different format
SELECT "Current day " || strftime('%d');
Current day 05

Compute days till Christmas. The %j modifier gives the day of the year for the time string.
SELECT 'Days to XMas:' || (strftime('%j', '2009-12-24') - strftime('%j', 'now'));
Days to XMas:49

Save the current date & time to a database
$query = "INSERT INTO 't1' ('name', 'mydate') VALUES ('John',datetime('now') )";
or
$query = "INSERT INTO 't1' ('name', 'mydate') VALUES ('John',date("Y-m-d H-i-s") )";
2009-11-05 20:01:07
Li0tingC 15.* B
SA(> CARR>@T OR <ATAR> DAT>
"ave the current date and calculate the date +! days from nowO
$query = "INSERT INTO 't1' ('datenow', 'date30') VALUES (datetime('now'),datetime('now', '30 days') )";
Li0tingC 15.< B
>SS THA@ T-M> SPA@
.o 'et the rows where there is less than +! days between two dates
where SnewdateS and SolddateS are date fieldsO
$query = "SELECT * FROM 't1' WHERE julianday(newdate) < julianday(olddate)+30";
Li0tingC 15.8 B
DAT> >QAA TO T-M> SPA@
.o 'et the rows where a date e#uals a s&an of +! days,
where SsomedateS and SotherdateS are date fieldsO
$query = "SELECT * FROM 't1' WHERE julianday(somedate) = julianday(otherdate)+30";
Li0tingC 15.? B
>rrors and 0u*s
.he com&utation of local time de&ends heavily on the whim of &oliticians and is thus difficult to 'et correct for all locales.
In this im&lementation, the standard % library function localtimeCr=> is used to assist in the calculation of local time. .he
localtimeCr=> % function normally only worHs for years between 175! and !+5. ?or dates outside this ran'e, "2$ite
attem&ts to ma& the year into an e#uivalent year within this ran'e, do the calculation, then ma& the year bacH.
.hese functions only worH for dates between !!!!-!1-!1 !!L!!L!! and 7777-1-+1 +L57L57 =Fulidan day numbers
151!57.5 throu'h 5+5+<*<.5>. ?or dates outside that ran'e, the results of these functions are undefined.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 51 of *!
Non-3ista /indows &latforms only su&&ort one set of 0". rules. 3ista only su&&orts two. .herefore, on these &latforms,
historical 0". calculations will be incorrect. ?or e9am&le, in the ;", in !!5 the 0". rules chan'ed. Non-3ista /indows
&latforms a&&ly the new !!5 0". rules to all &revious years as well. 3ista does somewhat better 'ettin' results correct
bacH to 17*,, when the rules were also chan'ed.
-ll internal com&utations assume the 8re'orian calendar system. It is also assumed that every day is e9actly *,<!!
seconds in duration.


:nd 1f "ection




NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5 of *!
Section O F!
Strin*s
ist of Strin* <unctions
ascii$char% return ascii code
char$code% return char fro& ascii code
coalesce$!N"N666% Return a cop+ of the first non=@A ar*u&ent6 -f all ar*u&ents are @A then @A is
returned6 There &ust be at least ' ar*u&ents6
concat$ar* 4N ar* 6664% join ar*s
concatE,s$sepN ar* 4N ar*4% join ar*s ,ith sep
convert$strN fro&N to% encodin* converter6 fro& and to &ust be tclCs encodin* na&e6
elt$nN ar* 4N ar* 6664% M+SQCs elt6
*lob$!N"% This function is used to i&ple&ent the PX ?O0 LP s+nta3 of SQite6 The
s9lite!EcreateEfunction$% interface can be used to override this function and thereb+
chan*e the operation of the ?O0 operator6
he3$n% return he3 value6
he3$!% The ar*u&ent is interpreted as a 0O06 The result is a he3adeci&al renderin* of the
content of that blob6
ifnull$!N"% Return a cop+ of the first non=@A ar*u&ent6 -f both ar*u&ents are @A then @A is
returned6 This behaves the sa&e as coalesce$% above6
initcap$str% OracleCs initcap6
insert$sN posN lenN ns%
instr$strN sstrN 4st 4n44% search sstr in str6
lastEinsertEro,id$% Return the RO#-D of the last ro, insert fro& this connection to the database6 This is the
sa&e value that ,ould be returned fro& the s9liteElastEinsertEro,id$% AP- function6
left$strN n%
len*th$str% SQite 0uilt=in
len*th$!% Return the strin* len*th of ! in characters6 -f SQite is confi*ured to support AT<=MN then
the nu&ber of AT<=M characters is returnedN not the nu&ber of b+tes6
li)e$!N"%
li)e$!N"NZ%
This function is used to i&ple&ent the PX -B> L Q>SCAP> RSP s+nta3 of SQ6 -f the optional
>SCAP> clause is presentN then the user=function is invo)ed ,ith three ar*u&ents6
Other,iseN it is invo)ed ,ith t,o ar*u&ents onl+6 The s9liteEcreateEfunction$% interface
can be used to override this function and thereb+ chan*e the operation of the -B>
operator6 #hen doin* thisN it &a+ be i&portant to override both the t,o and three
ar*u&ent versions of the li)e$% function6 Other,iseN different code &a+ be called to
i&ple&ent the -B> operator dependin* on ,hether or not an >SCAP> clause ,as
specified6

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5+ of *!
loadEe3tension$!%
loadEe3tension$!N"%
oad SQite e3tensions out of the shared librar+ file na&ed ! usin* the entr+ point "6 The
result is a @A6 -f " is o&itted then the default entr+ point of s9lite!Ee3tensionEinit is
used6 This function raises an e3ception if the e3tension fails to load or initialiTe correctl+6
locate$sstrN strN pos% return first sstr position in str6 start fro& pos6
lo,er$str% SQite 0uilt=in
lo,er$!% Return a cop+ of strin* ! ,ill all characters converted to lo,er case6 The C librar+ tolo,er$%
routine is used for the conversionN ,hich &eans that this function &i*ht not ,or)
correctl+ on AT<=M characters6
lpad$strN len 4N pad4%
ltri&$!%
ltri&$!N"%
Return a strin* for&ed b+ re&ovin* an+ and all characters that appear in " fro& the left
side of !6 -f the " ar*u&ent is o&ittedN spaces are re&oved6
&a3$!N"N666% Return the ar*u&ent ,ith the &a3i&u& value6 Ar*u&ents &a+ be strin*s in addition to
nu&bers6 The &a3i&u& value is deter&ined b+ the usual sort order6 @ote that &a3$% is a
si&ple function ,hen it has ' or &ore ar*u&ents but converts to an a**re*ate function if
*iven onl+ a sin*le ar*u&ent6
&id$strN posN len%
&in$!N"N666% Return the ar*u&ent ,ith the &ini&u& value6 Ar*u&ents &a+ be strin*s in addition to
nu&bers6 The &ini&u& value is deter&ined b+ the usual sort order6 @ote that &in$% is a
si&ple function ,hen it has ' or &ore ar*u&ents but converts to an a**re*ate function if
*iven onl+ a sin*le ar*u&ent6
nullif$!N"% Return the first ar*u&ent if the ar*u&ents are differentN other,ise return @A6
position$sstrN -@N str% return first sstr position in str6
9uote$!% This routine returns a strin* ,hich is the value of its ar*u&ent suitable for inclusion into
another SQ state&ent6 Strin*s are surrounded b+ sin*le=9uotes ,ith escapes on interior
9uotes as needed6 0O0s are encoded as he3adeci&al literals6 The current i&ple&entation
of (ACAAM uses this function6 The function is also useful ,hen ,ritin* tri**ers to
i&ple&ent undo8redo functionalit+6
rando&$U% Return a pseudo=rando& inte*er bet,een =;''!!K':!JMIGKKIM:M and
V;''!!K':!JMIGKKIM:K6
rando&blob$N% Return a N=b+te blob containin* pseudo=rando& b+tes6 N should be a positive inte*er6
repeat$strN n%
replace$!N"NZ% Return a strin* for&ed b+ substitutin* strin* Z for ever+ occurrence of strin* " in strin* !6
The 0-@ARL collatin* se9uence is used for co&parisons6
reverse$str% reverse strin*6
ri*ht$strN n%
round$!%
round$!N"%
Round off the nu&ber ! to " di*its to the ri*ht of the deci&al point6 -f the " ar*u&ent is
o&ittedN : is assu&ed6
rpad$strN n 4N pad4%

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5< of *!
rtri&$str 4N chars4% tri&ri*ht chars$or space% fro& str6
rtri&$!%
rtri&$!N"%
Return a strin* for&ed b+ re&ovin* an+ and all characters that appear in " fro& the ri*ht
side of !6 -f the " ar*u&ent is o&ittedN spaces are re&oved6
sounde3$!% Co&pute the sounde3 encodin* of the strin* !6 The strin* P4:::P is returned if the
ar*u&ent is @A6 This function is o&itted fro& SQite b+ default6 -t is onl+ available the =
DSQ-T>ESOA@D>X1F co&piler option is used ,hen SQite is built6
space$n% return space6
s9liteEversion$U% Return the version strin* for the SQite librar+ that is runnin*6 >3a&ple7 P'6M6:P
substr$!N"NZ% Return a substrin* of input strin* ! that be*ins ,ith the "=th character and ,hich is Z
characters lon*6 The left=&ost character of ! is nu&ber F6 -f " is ne*ative the the first
character of the substrin* is found b+ countin* fro& the ri*ht rather than the left6 -f ! is
strin* then characters indices refer to actual AT<=M characters6 -f ! is a 0O0 then the
indices refer to b+tes6
toEchar$nu&N f&t% Support onl+ 0N<MNSN?NDNPNPN;N:6 Onl+ nu&ber for&at6 -f +ou ,ant to for&at dateti&eN use
so&e s9lite built=in functions li)e strfti&e or dateti&e6
translate$strN fro&N to% Post*reSQCs translate6
tri&$!%
tri&$!N"%
Return a strin* for&ed b+ re&ovin* an+ and all characters that appear in " fro& both ends
of !6 -f the " ar*u&ent is o&ittedN spaces are re&oved6
t+peof$!% Return the t+pe of the e3pression !6 The onl+ return values are PnullPN Pinte*erPN PrealPN
Pte3tPN and PblobP6 SQiteCs t+pe handlin* is e3plained in Datat+pes in SQite (ersion !6
upper$!% Return a cop+ of input strin* ! converted to all upper=case letters6 The i&ple&entation of
this function uses the C librar+ routine toupper$% ,hich &eans it &a+ not ,or) correctl+
on AT<=M strin*s6
Teroblob$N% Return a 0O0 consistin* of @ b+tes of :3::6 SQite &ana*es these Teroblobs ver+
efficientl+6 Reroblobs can be used to reserve space for a 0O0 that is later ,ritten usin*
incre&ental 0O0 -8O6

s9liteEescapeEstrin* $Hstrin*%
s#liteCesca&eCstrin'=> will correctly #uote the strin' s&ecified by item for use in an "2$ite "2$ statement. .his includes
doublin' u& sin'le-#uote characters =S> and checHin' for binary-unsafe characters in the #uery strin'. @owever, it does
not catch all @.M$ characters that may conflict with a browser dis&lay. Notice the difference with the custome routine
below.
-lthou'h the encodin' maHes it safe to insert the data, it will render sim&le te9t com&arisons and $IW: clauses in your
#ueries unusable for the columns that contain the binary data. In &ractice, this shouldnSt be a &roblem, as your schema
should be such that you donSt use such thin's on binary columns =in fact, it mi'ht be better to store binary data usin'
other means, such as in files>.
.str / ,Hello0 I 1ust don2t 3care40,5
.str / s6lite7esca"e7strin'8.str95
echo .str5
Te3t Scrubbin*

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 55 of *!
.he followin' function will &revent forei'n characters from bein' misinter&reted in te9t. It converts the most common
&unctuation characters into html e#uivalents.
function cleantxt($str) {
$replace = array(
'&' => '&amp;',
'"' => '&quot;',
"'" => '&#39;',
';' => '&#59;',
'<' => '&lt;',
'>' => '&gt;'
);
$str = str_replace(array_keys($replace), array_values($replace), $str);
return $str;
}
Li0tingC 1%.1 B


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5, of *!
Section O FG
Math
ist of Math <unctions
abs(X) Return the absolute value of argument X.
acos(x)
asin(x)
atan(x)
atan2(x)
avg(X) Return the average value of all non null X within the group.
ceil(x)
cos(x)
cot(x)
count(*) The number of times X is non null in the group.
count(X) The number of times X is non null in the group.
croup_count(X) The number of times X is non null in the group.
croup_count(X,Y) The number of times X is non null in the group.
degrees(x)
exp(x)
floor(x)
greatest(x)
least(x)
log(x)
log10(x)
max(X)
max(X,Y,...) Return the argument with the maximum value. Arguments may be strings in addition to numbers.
The maximum value is determined by the usual sort order. Note that max() is a simple function
when it has 2 or more arguments but converts to an aggregate function if given only a single
argument.
min(X)
min(X,Y,...) Return the argument with the minimum value. Arguments may be strings in addition to numbers.
The minimum value is determined by the usual sort order. Note that min() is a simple function
when it has 2 or more arguments but converts to an aggregate function if given only a single
argument.
mod(x)
pi(x)
pow(x)
radians(x)
rand(x)
random(*) Return a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
round(X)
round(X,Y)
Round off the number X to Y digits to the right of the decimal point. If the Y argument is omitted, 0
is assumed.
sign(x)
sin(x)
sqrt(x)
sum(X)
tan(x)
total(X)
trunc(x)
typeof(X) Return the type of the expression X. The only return values are "null", "integer", "real", "text", and
"blob". SQLite's type handling is explained in Datatypes in SQLite Version 3.


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 55 of *!
Math >3a&ples
Examples
return the number of rows in the table. (includes NULL values)
SELECT count(*) AS '# of rows' FROM Testing;
# of rows
----------
5

return the number of rows in table. (does not include NULL values)
SELECT count(id) AS '# of non NULL values' FROM Testing;
# of non NULL values
--------------------
3

Return average of values. (does not include NULL values)
SELECT avg(cost) AS 'Average price' FROM Cars;
Average price
----------------
88528.1666666667

Return the sum() of values. (does not include NULL values)
SELECT sum(OrderPrice) AS Sum FROM Orders;
Sum
----------
4930

// add columns from two databases or tables, note: as is required
$result = $db->query('SELECT SUM(Total) -- SUM pulls total values
FROM (
SELECT SUM(smith) as Total FROM main.sales -- as is required so sum can pull values
UNION
SELECT SUM(smith) as Total FROM lastyear.sales
)');

Return maximum value of a column value
SELECT max(cost) FROM t1;
max(cost)
------------
3500

Return minimum value of a column value
SELECT min(cost) FROM t1;
min(cost)
------------
750

Return random number
SELECT random() AS Random;
Random
-------------------
1056892254869386643
Mone+
"#lite does not su&&ort any s&ecial currency format. Nou can use inte'er =1+.!!> or *-byte floatin' &oint number
=1+.<5> with 15 si'nificant di'its.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5* of *!
Section = FI
Savin* and Displa+in* -&a*es
80 command reference htt&LMM&h&.netMmanualMenMbooH.ima'e.&h&
.he e9am&le shown here saves an ima'e in a file to a database then retrieves and dis&lays it. .he database only has one
row so that this e9am&le is not cluttered by for loo&s and multi&le entries.
Test -&a*e
/hile this document will not cover 80 'ra&hic ima'es made with )@), it is handy to be able to maHe a #uicH test ima'e
usin' the )@) 80 ima'e &acHa'e. -lso note that this &ro'ram uses a sim&lified a&&roach to dis&layin' the &icture in the
browser usin' the header=T%ontent-.y&eL ima'eM&n'T>. .his &ro'ram will only allow the &icture to be show, so you will
not be able to use )@)Is echo command to add other te9t to the browser view.
.o dis&lay @.M$ te9t on the same &a'e as this ima'e, the ima'e must be saved as a file then the @.M$ [im'O\ ta' used
to im&ort it. -n e9am&le of how to do this is shown in a later e9am&le.

<?php
header("Content-Type: image/png");
$im = @imagecreate(200, 200) or die("Cannot Initialize new GD image stream");

#create colors
$white = imagecolorallocate($im, 255, 255, 255);
$yellow = imagecolorallocate($im, 255, 255, 200);
$red = imagecolorallocate($im, 255, 0, 0);
$black = imagecolorallocate($im, 0, 0, 0);

# Make the background transparent
imagecolortransparent($im, $white);

# Draw transparent background
imagefilledrectangle($im, 0, 0, 200, 200, $white);

imagesetthickness($im, 2);
imagefilledarc($im, 100, 100, 190, 190, 0, 360, $yellow, IMG_ARC_PIE); # head
imagearc($im, 100, 100, 190, 190, 0, 360, $black); # edge
imagefilledarc($im, 60, 75, 30, 20, 0, 360, $black, IMG_ARC_PIE); # left eye
imagefilledarc($im, 140, 75, 30, 20, 0, 360, $black, IMG_ARC_PIE); # right eye
imagesetthickness($im, 4);
imagearc($im, 100, 125, 125, 50, 0, 180, $red); # mouth
imagestring($im, 5, 67, 165, "Testing!", $black); # text
imagepng($im); show in browser, picture only!
imagepng($im, 'test.png'); # save to file
imagedestroy($im); # recover memory
?>
Li0tingC 13.1 B

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 57 of *!

Savin* an -&a*e
.o save an ima'e, we need to have a s&ecial data ty&e called a blob. - blob is a si'nal to the database en'ine that it
should not try to fi'ure out what the data isJ for e9am&le it is not a strin' with line ends. Aecause a database does not
Hnow what ty&e of information is in a blob, we have to Hee& tract of what it is. /e should always have a field that Hee&s
the media content ty&e =&n', F&', 'if, video, sound, etc>. /e will need this ima'e ty&e later so we can dis&lay it.
?or this sam&le &ro'ram we Hnow what the ima'e is. ?or sim&licity saHe we donIt show the ste&s needed to retrieve the
content t+pe information. .he content t+pe field is created in this e9am&le for 'ood &ractice. @ere is an e9am&le of how a
table mi'ht looH for someone savin' &hoto'ra&hs.
# create table
$query = create table sample (
id integer primary key,
description text(25),
contenttype text(25),
imagedata blob
);
$result = $db->query($query) or die(Error in query: <span style=color:red;>$query</span>);
Li0tingC 13.5 B
/ith a table &ro&erly desi'ned, we can now save the data. ?or our e9am&le we assume that the &icture is a pn* file
named .test6pn*/6 /e must use obFect oriented &ro'rammin' and a transaction =e9&lained later> to do our worH.
# insert row of data to table
$stmt = $db->prepare(insert into sample (id, description, contenttype, imagedata)
values (?,?,?,?));

# assign data for each field
$id = NULL;
$describe = Sample image file; # row data
$type = image/png;
$imagefile = fopen(test.png, rb);

# build the transaction for each field
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $describe);
$stmt->bindParam(3, $type);
$stmt->bindParam(4, $imagefile, PDO::PARAM_LOB);

# perform transaction
$db->beginTransaction(); # create transaction
$stmt->execute(); # do it
$db->commit(); # finished
Li0tingC 13.3 B
.hatIs itG .he data with ima'e has been saved.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,! of *!
Displa+ an -&a*e
.o 'et the ima'e from the database and dis&lay it, we must reverse the above &rocess. /e 'rab the row data usin' a
normal #uery, then save the ima'e data as a file that can be retrieved usin' the @.M$ [im'O\ ta'.
# retrive row of data
$query = SELECT * FROM sample;
$result = $db->query($query); # get a row of data
$row = $result->fetch(PDO::FETCH_ASSOC); # assign to array value

# save image data to file
$image = $row[imagedata];
file_put_contents(temp.png, $image);
Li0tingC 13. B
1nce the ima'e is in a file that we can access, we use normal html code to dis&lay the ima'e file.
# show data
echo <img src=temp.png> <br/>;
echo Fig-1 <i>. $row[description] .</i><br/>;
Li0tingC 13.% B
@ere is a com&lete worHin' &ro'ram that assumes a file named Ptest.&n'Q is in the current directory.
<?php
# setup
/* $dbpath = $_SERVER['DOCUMENT_ROOT'] . "/some path/"; */
$dbpath = "";
$dbfile = "sqlite_image.sqlite3";
$testimage = "test.png";
date_default_timezone_set('America/Boise');
$today = date("Y/m/d H:i:s"); # date w/leading zeros, dec 1 = 2010.12.01 00:00:00

# title
echo '<body style="font-family:arial;">';
echo "<h2>Blob Image</h2>";
echo "$today<br><br>";

# check for image file
if (!file_exists($testimage)) {
echo "<i>image file &quot;$testimage&quot; does not exist.</i><br>";
echo "<span style='color:red;'><i>I am quiting!</i></span><br>";
exit;
} else {
echo "<i>image file found &quot;$testimage&quot;...</i><br>";
}

# delete existing db
if (file_exists("$dbpath$dbfile")) {
unlink("$dbpath$dbfile");
echo "<i>existing db &quot;$dbpath$dbfile&quot; removed.</i><br>";
}

# new sqlite3 db and connection
$query = "$dbpath$dbfile";
try { $db = new PDO("sqlite:$query");
echo "<i>new db created &quot;$dbfile&quot;...</i><br>";

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,1 of *!
echo "<i>connecting...</i><br>"; }
catch(PDOException $e) { echo $e->getMessage()." Error: <span style='color:red;'>$query</span>"; }

# enhanced errors
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

# create table
$query = "create table 'sample' (
id integer primary key,
description text(25),
contenttype text(25),
imagedata blob
)";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
echo "<i>new table and data initialized...</i><br>";

# setup transaction to insert row of data to table,
$stmt = $db->prepare("insert into sample (id, description, contenttype, imagedata) values
(?,?,?,?)");

$id = NULL;
$describe = "Sample image file";
$type = "image/png";
$imagefile = fopen($testimage, 'rb');

# perform transaction
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $describe);
$stmt->bindParam(3, $type);
$stmt->bindParam(4, $imagefile, PDO::PARAM_LOB);

$db->beginTransaction(); # create transaction
$stmt->execute(); # do it
$db->commit(); # finished
echo "<i>data saved...</i><br>";

# retrive row of data
$query = "SELECT * FROM 'sample'";
$result = $db->query($query); # get row of data
$row = $result->fetch(PDO::FETCH_ASSOC); # assign to array of values
$image = $row['imagedata'];
echo "<i>data retrieved...</i><br><br>";

# save image data to file
file_put_contents("temp.png", $image);

# show data
echo "<img src='temp.png'> <br/>";
echo "Fig-1 <i>". $row['description'] ."</i><br/>";

# close db, PHP will also do this automatically
$db = NULL;

?>
Li0tingC 13.* B


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' , of *!

:nd of "ection

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,+ of *!
Section = FJ
Cr+ptN HashN etc
<unction S+nta3
Re#uires .cllib and .rf
md5=data 4, binary4> M05
md5Chmac=Hey, data 4, binary4> @M-%-M05
md5Ccry&t=&ass, salt 4, binary4> A"0 com&atible M05 cry&t
a&rCcry&t=&ass, salt 4, binary4> -&ache com&atible M05 cry&t
sha1=data 4, binary4> "@-1
sha1Chmac=Hey, data 4, binary4> @M-%-"@-1
aesCencry&t=Hey, data 4, binary4> -": encry&tion
aesCdecry&t=Hey, data 4, binary4> -": decry&tion
blowfishCencry&t=Hey, data 4, binary4> Alowfish encry&tion
blowfishCdecry&t=Hey, data 4, binary4> Alowfish decry&tion
desCencry&t=Hey, data 4, binary4> 0:" encry&tion
desCdecry&t=Hey, data 4, binary4> 0:" decry&tion
base,<Cencode=data 4, binary4> Aase,< encode
base,<Cdecode=data 4, binary4> Aase,< decode
com&ress=data 4, binary4> com&ress data by Ki&
decom&ress=data 4, binary4> decom&ress data by Ki&
uuid=>
Li0tingC 1.1 B
<ile -O



Li0tingC 1.5 B


:nd of "ection


writeCfile=&ath, data 4,binary4> Aase,< decode
readCfile=&ath 4,binary4> Aase,< encode

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,< of *!
Appendi3 = A
>rror Handlin*
>rror Codes
The error co%es for S"!ite (ersion ) are unchange% from (ersion *.
They are as follows+
O, - Successful result
..O. / S"! error or missing %atabase
$0T.0#! * #n internal logic error in S"!ite
P.1 ) #ccess 2ermission %enie%
#3O.T 4 Callback routine re5ueste% an abort
36S7 8 The %atabase file is locke%
!OC,D 9 # table in the %atabase is locke%
0O11 : # malloc() faile%
.#DO0!7 ; #ttem2t to write a rea%only %atabase
$0T..6PT < O2eration terminate% by s5lite=interru2t()
$O.. /- Some kin% of %isk $>O error occurre%
CO..6PT // The %atabase %isk image is malforme%
0OT?O60D /* ($nternal Only) Table or recor% not foun%
?6!! /) $nsertion faile% because %atabase is full
C#0TOP0 /4 6nable to o2en the %atabase file
P.OTOCO! /8 Database lock 2rotocol error
1PT7 /9 ($nternal Only) Database table is em2ty
SC'1# /: The %atabase schema change%
TOO3$@ /; Too much %ata for one row of a table
CO0ST.#$0T /< #bort %ue to constraint (iolation
1$S1#TC' *- Data ty2e mismatch
1$S6S */ !ibrary use% incorrectly
0O!?S ** 6ses OS features not su22orte% on host
#6T' *) #uthoriAation %enie%
.OW /-- s5lite=ste2() has another row rea%y
DO0 /-/ s5lite=ste2() has finishe% eBecuting
F% -< LOA COP->D COD> <ROM A PA?> O< TH-S R><>R>@C>
If you co&ied and &asted code from this M" /ord document. Ae forewarned that it trans&oses strai'ht ticHs =S>,
a&ostro&hes ], and #uotes =TT> into forward and reverse versions which are incom&atible with the )@) &arser. I have
made an attem&t to remove as many as &ossible, but a few may lin'er.
- full set of &roven routines should have accom&anied this document.
'% SOM> SQ-T> (>RS-O@S HA(> -@COMPAT-0--T->S
N1.:L 0atabases are unsu&&orted between +.1.9 and +..9 versions of "2$ite.
fileCformat 1 3ersion +.!.!.
fileCformat 3ersion +.1.+.
fileCformat + 3ersion +.1.<.

3ersion +.! can only use files with fileCformat 1.
3ersion +.1.+ can read and write files with fileCformat 1 or fileCformat .
3ersion +.1.< can read and write file formats 1, and +.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,5 of *!
- ty&ical error is somethin' liHe this, Tmalformed database schema - unable to o&en a database fileOQ
!% P-@(A-D R>SOARC>P >RROR
If you 'et an error re&ortin' Tinvalid resourceT when attem&tin' a #uery on a database table and loo&in' throu'h it,
the version of the "2$ite e9tension com&iled in to )@) mi'ht be incom&atible with the version that was used to
create the database =liHe "2$ite .9 vs +.9>.

.he database mi'ht o&en =connect> oH, but will fail when #ueryin'.

Ddb X new )01=Ss#liteLMtm&Mfoo.dbS>J MM success
foreach =Ddb-\#uery=S":$:%. Y ?R1M barS> as Drow> MM &rints invalid resource
G% S>@S-T-(-TL TO #H-T> SPAC>
In certain circumstances white s&ace can cause errors, be carefulG
Correct -> $db = new PDO("sqlite:$dbPath$dbFile");
Error -> $db = new PDO("sqlite: $dbPath$dbFile");

Correct -> $db = new PDO("sqlite:$dbPath$dbFile");
Error -> $db = new PDO("sqlite:$dbPath$dbFile ");

Correct -> ")
Error -> " )

I% .A@A0> TO OP>@ <->/
If you receive an error while tryin' to write to a s#lite database =u&date, delete, dro&>L
Warning: PDO::query() [function.query]: SQLSTATE[HY000]: General error: 1 unable to open database.

# uni= 040tem ma4 1a/e folder or file permi00ion pro7lem0. .he folder containin' the database file must be writeable.
J% .SQ-T>ECORRAPT >RROR/
-n "2$I.:C%1RR;). error is returned when "2$ite detects an error in the structure, format, or other control elements of
the database file. Nour can use )R-8M- inte'rityCchecH to do a thorou'h an intensive test of the database inte'rity or
you can use )R-8M- #uicHCchecH to do a faster but less thorou'h test of the database inte'rity.
K% .#H>R> CAAS> >RROR/
.he "2$ standard re#uires sin*le=9uotes, not double-#uotes, around strin* literals. - /@:R: clause e9&ression should
readL column1XScolumnS not column1X PcolumnQ.
M% A@>XP>CT>D R>TAR@ (AA>N -6>6 .ARRAL/
-fter #uery usin' somethin' similar toO
$result = $db->query("SELECT * FROM 'test'");
$row = $result->fetchAll();
echo "row = $row";

echo showsO

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,, of *!
Array

.he result array has not been fully &rocessed. In other words, the result is an array and needs to be accessed usin' inde9
values such as foreach$Hro, as Harra+% clause or Hvar 1 Hro,Q33S6
;% SL@TAX >RRORN A@>XP>CT>D .>@D/W
- bracHet is missin' from a forne9t or foreach constructO
foreach($rows as $row){
do something...;

missing closing }

F:% .A@>XP>CT>D TE(AR-A0>/
.his error usually indicates a synta9 error. .he most common cause is a missin' P5Q in a &revious line.



NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,5 of *!
Appendi3 = 0
Printin* Table Data
Table print
.he followin' code creates the formatted table out&ut shown below. Notice that the &ro'rammer must hard code the
number of rows in the loo&. @ard codin' the loo& sim&lifies the readability of the code.
// output table data to a simple html table...
echo "<table cellpadding=5 border=1>";
echo "<tr>"; # bold table header
echo " <th>ID</th>";
echo " <th>NAME</th>";
echo " <th>DOB</th>";
echo "</tr>";

$result = $db->query("SELECT * FROM 't1' ");
foreach($result as $row)
{
echo "<tr>"; // table data
echo "<td>".$row['id']."</td>";
echo "<td>".$row['name']."</td>";
echo "<td>".$row['dob']."</td>";
echo "</tr>";
}
echo "</table>";


Si&ple printEr

.he followin' code creates the te9t out&ut shown here. .his is the sim&lest and most often used method to &rint data
information.
// show data
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall();

// print_r output
echo "<br><span style='color:red;'><h1>Print_r Output</h1></span>";
echo "<pre>"; print_r($rows); echo "</pre>";



NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,* of *!

oop printEr


.he followin' code creates the te9t out&ut shown here. /hile the out&ut looHs identical to the &revious sim&le routine,
this modified version has the advanta'e of 'ivin' access to each row of information within the SforS loo&.
/ show data
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall();

// get each row as an array and print values
// print_r output
echo "<br><span style='color:red;'><h1>Print_r Output</h1></span>";
foreach($rows as $array) {
echo "<pre>";
print_r($array);
echo "</pre>";
}


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' ,7 of *!

(ar du&p
.he ne9t code sni&&et creates the te9t out&ut shown here. Notice that fetchall=)01LL?:.%@C-""1%> was used. .his
su&&resses the numbered array inde9 definition which causes a cluttered a&&earance.
# show data
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall(PDO::FETCH_ASSOC);

# get each row as an array and print values
# print_r output
echo "<br><span style='color:red;'><h1>Var Dump Output</h1></span>";
echo "<pre>";
var_dump($result);
var_dump($rows);
echo "</pre>";



NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5! of *!

-nde3 X associative table output
.he ne9t code sni&&et creates the te9t out&ut shown here. .his creates both the inde9 and associtive header columns for
the data. 1f interest is &lacein' the use of fetchall=)01LL?:.%@C-""1%> fetchall=)01LL?:.%@CN;M> will re-format the
table to show the sim&ler information &ro&erly

# show data
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchall(); # usually use fetchall(PDO::FETCH_ASSOC)

# Table Output, get each row as an array and print values
echo "<br><span style='color:red;'><h1>Pretty Table Output</h1></span>";
echo "<table border=1 width=\"100%\">\n"; # open the table
print "<tr>\n";

foreach ($rows[0] as $key => $useless){ # add the table headers
print "<th>$key</th>";
}

print "</tr>";
foreach ($rows as $row){ # display data
print "<tr>";
foreach ($row as $key => $val){
print "<td>$val</td>";
}
print "</tr>\n";
}
print "</table><br>"; # close the table



NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 51 of *!
Appendi3 = C
Test Databases
.he followin' code creates test databases.
Test database pro*ra&

<?php
/* Initalize "test.sqlite3" database */
echo '<body style="font-family:arial;">';
echo '<h2>Initalize "test.sqlite3" database</h2>';

# setup
$dbFile = "test.sqlite3";
$today = date("Y.m.d"); # date w/leading zeros, dec 1 = 2010.12.01

# if db already exists delete it
unlink("$dbFile");

# create SQLite3 database
$query = "$dbFile";
try { $db = new PDO("sqlite:$query"); }
catch(PDOException $e) { echo $e->getMessage()." Error: <span style='color:red;'>$query</span>"; }

# set the PDO error mode to EXCEPTION, gives lots of information
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

# create t1 table
$query = "CREATE TABLE 't1' (
'id' INTEGER PRIMARY KEY,
'name' TEXT(10),
'dob' DATE
)";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
echo "<i>Created 't1' table...<br></i> ";

# create t2 table
$query = "CREATE TABLE 't2' (
'id' INTEGER PRIMARY KEY,
'name' TEXT(10),
'phone' TEXT(15)
)";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
echo "<i>Created 't2' table ...<br></i> ";

# fill t1 data
$query = "INSERT INTO 't1' ('id','name','dob')
SELECT '1' ,'Frank' ,'$today'
UNION SELECT '2' ,'John' ,'1972.09.22'
UNION SELECT '3' ,'' ,'62.03.15'
UNION SELECT '6' ,'John' ,''
UNION SELECT '10','Henry' ,'1973.12.01'
";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5 of *!
echo "<i>Created 't1' data... </i> ";

# show number of rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected <br>";


# fill t2 data
$query = "INSERT INTO 't2' ('id','name','phone')
SELECT '12' ,'Frank' ,'(100)-123-4567'
UNION SELECT '32' ,'John' ,'200-234-5678'
UNION SELECT '28' ,'' ,'890-1234'
UNION SELECT '66' ,'John' ,''
UNION SELECT '61' ,'Henry' ,'ph. 321.2112'
";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
echo "<i>Created 't2' data... </i> ";

# Return rows affected by last operation
$rows_affected = $result->rowCount();
echo "<span style='color:red;'>$rows_affected</span> records affected ";

/* +----------------------------------------+
| Test Code Goes Here |
+----------------------------------------+
*/

# show data
$query = "SELECT * FROM t1";
$result = $db->query("$query") or die("Error in query: <span style='color:red;'>$query</span>");
$rows = $result->fetchAll();
echo "<pre>";
print_r($rows);
echo "</pre>";
echo "Access array row information, 2nd row DOB info...<br>";
echo "Row ID = ".$rows['1']['id']." DOB = ".$rows['1']['dob']."<br><br>"; # array [0,1,...]

# close db, PHP will also do this automatically
$db = NULL;
?>




NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5+ of *!
Appendi3 O D
Co&&and Reference
PDO Co&&ands

Introduction
Installin':Confi'urin'
;e6uirements
Installation
;untime Confi'uration
;esource T%"es
<redefined Constants
Connections and Connection mana'ement
Transactions and auto-commit
<re"ared statements and stored "rocedures
Errors and error handlin'
=ar'e >b1ects 8=>Bs9
<D> ? The <D> class
<D>--be'inTransaction ? Initiates a transaction
<D>--commit ? Commits a transaction
<D>--construct ? Creates a <D> instance re"resentin' a connection to a database
<D>--errorCode ? etch the S@=STATE associated #ith the last o"eration on the
database handle
<D>--errorInfo ? etch e!tended error information associated #ith the last
o"eration on the database handle
<D>--e!ec ? E!ecute an S@= statement and return the number of affected ro#s
<D>--'etAttribute ? ;etrie+e a database connection attribute
<D>--'etA+ailableDri+ers ? ;eturn an arra% of a+ailable <D> dri+ers
<D>--lastInsertId ? ;eturns the ID of the last inserted ro# or se6uence +alue
<D>--"re"are ? <re"ares a statement for e!ecution and returns a statement ob1ect
<D>--6uer% ? E!ecutes an S@= statement$ returnin' a result set as a
<D>Statement ob1ect
<D>--6uote ? @uotes a strin' for use in a 6uer%.
<D>--rollBac) ? ;olls bac) a transaction
<D>--setAttribute ? Set an attribute
<D>Statement ? The <D>Statement class
<D>Statement-4bindColumn ? Bind a column to a <H< +ariable
<D>Statement-4bind<aram ? Binds a "arameter to the s"ecified +ariable name
<D>Statement-4bindAalue ? Binds a +alue to a "arameter
<D>Statement-4closeCursor ? Closes the cursor$ enablin' the statement to be
e!ecuted a'ain.
<D>Statement-4columnCount ? ;eturns the number of columns in the result set
<D>Statement-4debu'Dum"<arams ? Dum" an S@= "re"ared command
<D>Statement-4errorCode ? etch the S@=STATE associated #ith the last
o"eration on the statement handle
<D>Statement-4errorInfo ? etch e!tended error information associated #ith the
last o"eration on the statement handle
<D>Statement-4e!ecute ? E!ecutes a "re"ared statement
<D>Statement-4fetch ? etches the ne!t ro# from a result set
<D>Statement-4fetchAll ? ;eturns an arra% containin' all of the result set ro#s
<D>Statement-4fetchColumn ? ;eturns a sin'le column from the ne!t ro# of a
result set
<D>Statement-4fetch>b1ect ? etches the ne!t ro# and returns it as an ob1ect.

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5< of *!
<D>Statement-4'etAttribute ? ;etrie+e a statement attribute
<D>Statement-4'etColumnBeta ? ;eturns metadata for a column in a result set
<D>Statement-4ne!t;o#set ? Ad+ances to the ne!t ro#set in a multi-ro#set
statement handle
<D>Statement-4ro#Count ? ;eturns the number of ro#s affected b% the last S@=
statement
<D>Statement-4setAttribute ? Set a statement attribute
<D>Statement-4setetchBode ? Set the default fetch mode for this statement
<D>E!ce"tion ? The <D>E!ce"tion class
<D> Dri+ers
BS S@= Ser+er 8<D>9 ? Bicrosoft S@= Ser+er and S%base unctions 8<D>7DB=IB9
irebird:Interbase 8<D>9 ? irebird:Interbase unctions 8<D>7I;EBI;D9
IBB 8<D>9 ? IBB unctions 8<D>7IBB9
Informi! 8<D>9 ? Informi! unctions 8<D>7IC>;BID9
B%S@= 8<D>9 ? B%S@= unctions 8<D>7BES@=9
>racle 8<D>9 ? >racle unctions 8<D>7>CI9
>DBC and DBF 8<D>9 ? >DBC and DBF unctions 8<D>7>DBC9
<ost'reS@= 8<D>9 ? <ost'reS@= unctions 8<D>7<GS@=9
S@=ite 8<D>9 ? S@=ite unctions 8<D>7S@=ITE9
HD 8<D>9 ? HD unctions 8<D>7HD9
SQite! Co&&ands
Introduction
Installin':Confi'urin'
;e6uirements
Installation
;untime Confi'uration
;esource T%"es
<redefined Constants
S@=iteI ? The S@=iteI class
S@=iteI--chan'es ? ;eturns the number of database ro#s that #ere chan'ed 8or
inserted or deleted9 b% the most recent S@= statement
S@=iteI--close ? Closes the database connection
S@=iteI--77construct ? Instantiates an S@=iteI ob1ect and o"ens an S@=ite I
database
S@=iteI--createA''re'ate ? ;e'isters a <H< function for use as an S@= a''re'ate
function
S@=iteI--createunction ? ;e'isters a <H< function for use as an S@= scalar
function
S@=iteI--esca"eStrin' ? ;eturns a strin' that has been "ro"erl% esca"ed
S@=iteI--e!ec ? E!ecutes a result-less 6uer% a'ainst a 'i+en database
S@=iteI--lastErrorCode ? ;eturns the numeric result code of the most recent failed
S@=ite re6uest
S@=iteI--lastErrorBs' ? ;eturns En'lish te!t describin' the most recent failed
S@=ite re6uest
S@=iteI--lastInsert;o#ID ? ;eturns the ro# ID of the most recent ICSE;T into the
database
S@=iteI--loadE!tension ? Attem"ts to load an S@=ite e!tension librar%
S@=iteI--o"en ? >"ens an S@=ite database
S@=iteI--"re"are ? <re"ares an S@= statement for e!ecution
S@=iteI--6uer% ? E!ecutes an S@= 6uer%
S@=iteI--6uer%Sin'le ? E!ecutes a 6uer% and returns a sin'le result
S@=iteI--+ersion ? ;eturns the S@=iteI librar% +ersion as a strin' constant and as
a number
S@=iteIStmt ? The S@=iteIStmt class

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 55 of *!
S@=iteIStmt--bind<aram ? Binds a "arameter to a statement +ariable
S@=iteIStmt--bindAalue ? Binds the +alue of a "arameter to a statement +ariable
S@=iteIStmt--clear ? Clears all current bound "arameters
S@=iteIStmt--close ? Closes the "re"ared statement
S@=iteIStmt--e!ecute ? E!ecutes a "re"ared statement and returns a result set
ob1ect
S@=iteIStmt--"aramCount ? ;eturns the number of "arameters #ithin the
"re"ared statement
S@=iteIStmt--reset ? ;esets the "re"ared statement
S@=iteI;esult ? The S@=iteI;esult class
S@=iteI;esult--columnCame ? ;eturns the name of the nth column
S@=iteI;esult--columnT%"e ? ;eturns the t%"e of the nth column
S@=iteI;esult--fetchArra% ? etches a result ro# as an associati+e or numericall%
inde!ed arra% or both
S@=iteI;esult--finaliJe ? Closes the result set
S@=iteI;esult--numColumns ? ;eturns the number of columns in the result set
S@=iteI;esult--reset ? ;esets the result set bac) to the first ro#

Reserved #ords
.here are ++5 reserved words, they areO
-A"1$;.:, -%.I1N, -00, -?.:R, -$$, -$$1%-.:, -$.:R, -N0, -NN, -R:, -RR-N, -", -"%, -":N"I.I3:, -"":R.I1N,
-"NMM:.RI%, -., -.1MI%, -;[email protected], -38, A:?1R:, A:8IN, A:./::N, AI8IN., AIN-RN, AI., AI.C$:N8.@,
A$1A, A11$:-N, A1.@, AR:-0.@, AN, %-$$, %-$$:0, %-"%-0:, %-"%-0:0, %-":, %-"., %-.-$18, %@-R,
%@-R-%.:R, %@-R-%.:RC$:N8.@, %@-RC$:N8.@, %@:%W, %$1A, %$1":, %1-$:"%:, %1$$-.:, %1$$-.I1N, %1$;MN,
%1MMI., %1N0I.I1N, %1NN:%., %1NN:%.I1N, %1N".R-IN., %1N".R-IN.", %1N".R;%.1R, %1N.-IN", %1N.IN;:,
%1N3:R., %1RR:")1N0IN8, %1;N., %R:-.:, %R1"", %;A:, %;RR:N., %;RR:N.C0-.:,
%;RR:N.C0:?-;$.C.R-N"?1RMC8R1;), %;RR:N.C)-.@, %;RR:N.CR1$:, %;RR:N.C.IM:,
%;RR:N.C.IM:".-M), %;RR:N.C.R-N"?1RMC8R1;)C?1RC.N):, %;RR:N.C;":R, %;R"1R, %N%$:, 0-.-, 0-.:,
0-N, 0:-$$1%-.:, 0:%, 0:%IM-$, 0:%$-R:, 0:?-;$., 0:?:RR-A$:, 0:?:RR:0, 0:$:.:, 0:).@, 0:R:?, 0:"%,
0:"%RIA:, 0:"%RI).1R, 0:.:RMINI".I%, 0I-8N1".I%", 0I"%1NN:%., 0I".IN%., 01, 01M-IN, 01;A$:, 0R1),
0NN-MI%, :-%@, :$:M:N., :$":, :$":I?, :N0, :2;-$", :"%-):, :6%:)., :6%:).I1N, :6:%, :6:%;.:, :6I".", :6I.,
:6.:RN-$, :6.R-%., ?-$":, ?:.%@, ?I$.:R, ?IR"., ?$1-., ?1R, ?1R:I8N, ?1;N0, ?R::, ?R1M, ?;$$, ?;N%.I1N,
8:N:R-$, 8:., 8$1A-$, 81, 81.1, 8R-N., 8R1;), 8R1;)IN8, @-N0$:R, @-3IN8, @1$0, @1;R, I0:N.I.N, I?,
IMM:0I-.:, IN, IN0I%-.1R, INI.I-$$N, INN:R, IN1;., IN);., IN":N"I.I3:, IN":R., IN., IN.:8:R, IN.:R":%.,
IN.:R3-$, IN.1, I", I"1$-.I1N, I.:R-.:, J1IN, W:N, $-N8;-8:, $-R8:, $-"., $-.:R-$, $:-0IN8, $:-3:, $:?.,
$:3:$, $IW:, $1%-$, $1%-$.IM:, $1%-$.IM:".-M), $1%-.1R, $11), $1/:R, M-), M-.%@, M-6, M:MA:R, M:R8:,
M:.@10, MIN, MIN;.:, M10I?I:", M10;$:, M1N.@, M;$.I":., N-M:", N-.I1N-$, N-.;R-$, N%@-R, N%$1A,
N:/, N:6., N1, N1N:, N1., N;$$, N;$$I?, N;M:RI%, 1AJ:%., 1%.:.C$:N8.@, 1?, 1$0, 1N, 1N$N, 1):N, 1).I1N,
1R, 1R0:R, 1R0IN-$I.N, 1;., 1;.:R, 1;.);., 13:R, 13:R$-)", )-0, )-R-M:.:R, )-R.I-$, )-R.I.I1N, )-.@,
)1"I.I1N, )R:%I"I1N, )R:)-R:, )R:":R3:, )RIM-RN, )RI1R, )RI3I$:8:", )R1%:0;R:, );A$I%, R-N8:, R:-0,
R:-0", R:-$, R:%;R"I3:, R:?, R:?:R:N%:", R:?:R:N%IN8, R:$-.I3:, R:$:-":, R:):-., R:"I8N-$, R:".RI%.,
R:";$., R:.;RN, R:.;RN", R:31W:, RI8@., R1$:, R1$$A-%W, R1$$;), R1;.IN:, R1/, R1/", "-3:)1IN.,
"%@:M-, "%1):, "%R1$$, ":-R%@, ":%1N0, ":%.I1N, ":$:%., ":N"I.I3:, ":""I1N, ":""I1NC;":R, ":., ":.",
"I8N-$, "IMI$-R, "IZ:, "M-$$IN., "1M:, ")-%:, "):%I?I%, "):%I?I%.N):, "2$, "2$%10:, "2$:RR1R, "2$:6%:).I1N,
"2$".-.:, "2$/-RNIN8, ".-R., ".-.:, ".-.I%, ";AM;$.I":., ";A".RIN8, ";M, "NMM:.RI%, "N".:M,
"N".:MC;":R, .-A$:, .-A$:"-M)$:, .:M)1R-RN, .@:N, .IM:, .IM:".-M), .IM:Z1N:C@1;R,
.IM:Z1N:CMIN;.:, .1, .R-I$IN8, .R-N"-%.I1N, .R-N"$-.:, .R-N"$-.I1N, .R:-., .RI88:R, .RIM, .R;:,
;N0:R, ;N01, ;NI1N, ;NI2;:, ;NWN1/N, ;NN:"., ;N.I$, ;)0-.:, ;)):R, ;"-8:, ;":R, ;"IN8, 3-$;:,
3-$;:", 3-R%@-R, 3-RNIN8, 3I:/, /@:N, /@:N:3:R, /@:R:, /@I$:, /IN01/, /I.@, /I.@IN, /I.@1;.,
/1RW, /RI.:, N:-R, Z1N:


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5, of *!


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 55 of *!
Notes
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 5* of *!
Notes
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC


NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' 57 of *!
Notes
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

NeatInfo.com - by Jan Zumwalt Revised March 15, !1
"#$ite Reference %o&yri'ht ( !!5-!1
)' *! of *!



DOCAM>@AT-O@
htt&LMMwww.&h&.netM
htt&LMMwww.s#lite.or'M
htt&LMM&h&builder.comMmanualMenMref.s#lite.&h&
htt&LMMwww.w+schools.comMs#lM
htt&LMMwww.firsts#l.comM

R><>R>@C>S
8ettin' the Ri'ht 0ata with "2$ Joins. htt&LMMwww.dev9.comMdbKoneM-rticleM15<!+M!M&a'eM1
"2$ Join. htt&LMMwww.w+schools.comMs#lMs#lCFoin.as&
"2$ite home &a'e, htt&LMMwww.s#lite.or'M
"2$ ?eatures .hat "2$ite 0oes Not Im&lement, htt&LMMwww.s#lite.or'Momitted.html
- command-line &ro'ram to administer "2$ite databases, htt&LMMwww.s#lite.or'Ms#lite.html
"2$ -s ;nderstood Ay "2$ite, htt&LMMwww.s#lite.or'Mlan'.html
?re#uently -sHed 2uestions, htt&LMMwww.s#lite.or'Mfa#.html
"2$ite Reference by MiHe %hirico mchirico^users.sourcefor'e.net,
htt&LMMsou&tonuts.sourcefor'e.netMreadmeCs#liteCreference.html