Example of using PHP and SQLite

Explanation of the simple PHP/SQLite code

This is the code behind the simple SQLite/PHP example. As with most code, a large portion is error-checking is so that intelligible error messages can be returned. I have highlighted the meat of the code in red below. You should be able to copy this code onto any web server, edit the location of the database file ($DBdir in the code below), if necessary. Finally go to the corresponding URL and it will start working immediately with an empty database.

Workings of the code

The first thing the code does is output the typical HTML pre-cursor. I embedded the Cascading Style Sheet just to keep all the code in the same place, but this would normally be in a separate file. After setting the database location variables it outputs the PHP version, using CSS to place it at the bottom right. This is purely for debugging; indeed in a real web page it would probably be a good idea to omit this, as the less information you give the hackers the better. It also outputs the web user details and the version number of the script.

Next it does a few checks on the database directory, creating it if necessary. This is very handy where you are developing on a local machine and then uploading your code to a hosting provider. I develop on Mac and Linux and the web server runs under a dedicated web user so the database directory must be writable by that user. A hosting provider will normally (although not always) set your web server running as your user so there won't be an ownership problem in that case.

With new PDO("sqlite:".$PathName) it finally does something useful. SQLite will check to see if the file exists, if doesn't it will create it. This can be both a blessing and a curse, in fact in all other code I have written for SQLite I circumvent this 'feature' by checking to see if the file exists first. However for the purposes of this code it is very handy. The method used here (PHP Data Objects) is a generic way of opening any database so you can use this method to open a MySQL database for instance, however that is beyond the scope of this discussion.

Next it creates the StudentNames table, if it's not already there. When setting up SQL tables I use a naming convention I found very handy in C structures, although I haven't seen this practice in my limited exposure to SQL in industry: I put a prefix before each member of the table so if I they are referenced independent of the table it will still be clear which table they belong to.

CREATE TABLE IF NOT EXISTS StudentNames (
	std_Name	TEXT,
	std_Counter	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
) ;

The counter isn't strictly speaking necessary, but this is common practice in SQL, assuring each row in the table has its own unique ID. For SQL queries from which there will be no output—apart from success/failure—I created a separate function called RunSQLcmds(). It just loops through the statements in an array. If there are more than five (an arbitrary number on my part) statements it puts a BEGIN TRANSACTION ; at the start and COMMIT ; at the end. A few years ago I had a dramatic demonstration of the importance of wrapping SQL like this. I had to add 14,000 items to a SQLite database and initially I put them in as 14,000 INSERT statments, without the transaction lines and it took twenty minutes. Once I wrapped the statements as a transaction that time fell to 0.3 seconds!

Once the table is created without a problem the script is now in to standard HTML form territory. As is common practice, the form and its action script are one and the same, it uses $_SERVER['REQUEST_METHOD'] to figure whether this is the form or its action script. Once it outputs the form it does a bit of housekeeping, trimming the database with TrimDB(), and then the code does its first extraction of data from the database. Note that since we are interested only in the student's name I could have just done SELECT std_Name instead of SELECT *.

Once the submit button is pressed the same script comes around this time in 'action script' mode. The code is a lot briefer here, but needs some careful explanation: I first use prepare() on the INSERT statement which generates a template. This has a few advantages:

It's very efficient for repeat commands, however that doesn't apply here.
We don't have to escape special characters like quotes.
It stops SQL Injection.

Wikipedia has more details on prepared statements.

We then execute() the statement using an array containing just one one item: the string submitted on the form. If the SQL was successful then use JavaScript to bounce back to the form. If there was a problem we will call QueryErrDetails() to determine what went wrong and stay on the page.

<?php // Output the standard header

print "<!DOCTYPE html>

<head>

<title>Example of using PHP and SQLite</title>

<style>

.sqlerr {color:red}

.nosqlerr {color:grey}

.limitwidth {width: 500px;}

.limitwidth h2 {text-align: center}

#phpinfo {width: 500px; text-align: right; font-size: 13px; color: grey; position: absolute; top: 400px}

#userinfo {width: 500px; font-size: 13px; color: grey; position: absolute; top: 400px}

#rcsinfo {width: 500px; text-align: center; font-size: 13px; color: grey; position: absolute; top: 400px}

#author {text-align: right; font-size: 14px; color: grey;}

#author a {color: grey;}

</style>

</head>

<body>

\n" ;

$DBdir = "../../Web-not-shared/ApacheTerritory/DatabaseDir" ;// Directory where the DB file will be created, should be a relative path. If you want to store the database in the same place as the script set this to "."

$PathName = $DBdir."/StudentDB.sqlite3" ;

printf("<div id=phpinfo>PHP version: %s</div>\n",phpversion()) ; // Useful in this example if you are seeing differences between servers $UserInfo = posix_getpwuid(posix_getuid()) ; // Find out what user and group the web server is running under $GroupInfo = posix_getgrgid($UserInfo['gid']); $RCSinfo = explode(' ','$Id: SQLiteExample.php,v 3.1 2013/06/18 16:14:09 pkearns Exp $') ; // RCS automatically updates this string if(sizeof($RCSinfo)<2) $Version = "" ; // The version number is missing for some reason, no worries else $Version = $RCSinfo[2] ; // Extract the version number printf("<div class=limitwidth> <h2>Simple example of PHP/SQLite code</h2> <div id=author>by <a href=http://linuxtech.ie/>Philip Kearns</a></div> <p>Minimal code which creates a SQLite database, collects data and displays it. <a href=explainSQLiteExample.php>Follow this link to find the code and a detailed explanation</a>.</p> <div id=rcsinfo>%s</div> <div id=userinfo><tt>%s</tt> (<tt>%d</tt>):<tt>%s</tt> (<tt>%d</tt>)</div>",$Version,$UserInfo['name'],$UserInfo['uid'],$GroupInfo['name'],$UserInfo['gid']) ; print("</div> \n") ; if(($ErrStr=checkDBdir($DBdir))!="") { printf("<div class=limitwidth>ERROR: %s<br /><hr />\n",$ErrStr) ; // It's a common mistake to forget that the web processes normally run as a separate user, so print this information to help debugging. printf("The user name of the web process is <tt>%s</tt> (<tt>%d</tt>) and the group ID of the web process is <tt>%s</tt> (<tt>%d</tt>)<br />\n",$UserInfo['name'],$UserInfo['uid'],$GroupInfo['name'],$UserInfo['gid']) ; print("</div> \n") ; printf("Current working directory is:<pre>%s</pre>\n",posix_getcwd()) ; } else {

$DBhandle = new PDO("sqlite:".$PathName) ; // Open the database file. This will create the file if it doesn't exist

if($DBhandle==FALSE) { printf("Cannot open/create %s\n",$PathName) ; } else { // Create the table if it doesn't exist

$CreateTbl[] = "CREATE TABLE IF NOT EXISTS StudentNames (

std_Name TEXT, std_Counter INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE -- *Every* table should have its own counter ) ;" ;

if(RunSQLcmds($CreateTbl,$DBhandle)==TRUE) {

// The form and its action script are the same, first get the name of the current script:

$ThisScript = $_SERVER['PHP_SELF'] ;

if($_SERVER['REQUEST_METHOD'] != 'POST') { // We are in form mode. Display the form and the existing data.

printf("<div class=limitwidth>

<p>

<label for=\"StudentName\">Student's name</label>

</form>

</p>",$ThisScript) ;

$TrimCnt = 5 ; // The trimming is only for this example code printf("Each time this script runs it deletes all but the %s most recent entries, this is to stop this sample database growing indefinitely.<br /><hr />\n",$TrimCnt) ; if(TrimDB($DBhandle,$TrimCnt)==TRUE) { // Continue only if there wasn't a problem trimming the database

print("Here are the contents of the database (if any):\n") ;

$SelQuery = "SELECT * FROM StudentNames" ;

$DBquery = $DBhandle->query($SelQuery) ; // Run the SQL query to select all the contents

if($DBquery==FALSE) {

QueryErrDetails($DBhdl,$SelQuery) ; // If the query failed print the errors

}

else {

$RowNumber = 1 ;

print("<blockquote>\n") ; // A HTML table would be prettier of course :-)

while(($row=$DBquery->fetch())!=FALSE) {// Step through the rows (if any)

printf("%d. %s<br />\n",$RowNumber,$row[0]) ; // Print out the student's name ($row[1] contains the counter).

$RowNumber++ ;

}

print("</blockquote>\n") ;

}

print("</div> \n") ;

} else { // We are in 'action script' mode if(get_magic_quotes_gpc()) // Some older servers still use the deprecated 'magic quotes' feature $StudentName = stripslashes(trim($_POST["StudentName"])) ; else $StudentName = trim($_POST["StudentName"]) ; if(strlen($StudentName)==0) { // Ideally this should be done by JavaScript print("<p>The name field cannot be blank</p>") ; printf('<a href="%s">Go back to the form</a>',$ThisScript) ; } else {

// The command to add a name into the table, the '?' will be filled in at execute() time below

$PreProc = $DBhandle->prepare($SQLcmd="INSERT INTO StudentNames VALUES(?,NULL)") ;

// The array($Student) fills in the '?'

if($PreProc->execute(array($StudentName))==FALSE)

QueryErrDetails($DBhandle,$SQLcmd) ; // The insert failed, find out why

else // The insert worked, so bounce back to the form

printf("<script LANGUAGE=\"JavaScript\">window.location = '%s'</script>\n",$ThisScript) ;

} } $DBhandle = NULL ; // Close the database } } } // Close off the body and HTML print "</body> </html>" ; // Run a series of SQL commands that don't return data, so DELETE, DROP, etc. // Don't use for INSERT: instead use the prepare()/execute() format above which // is a very secure guard against SQL injection.

function RunSQLcmds($SQLcmds,$DBhdl)

{

if(sizeof($SQLcmds)>5) { // SQLite really benefits from BEGIN/COMMIT for a large number of commands

array_unshift($SQLcmds,"BEGIN TRANSACTION ;") ; // Prepend

array_push($SQLcmds,"COMMIT ;") ; // Append

}

$GoodQuery = TRUE ;

// Keep stepping through the commands until we have processed them all or there's an error

for($Count=0;$Count<sizeof($SQLcmds) && $GoodQuery==TRUE;$Count++) {

$DBquery = $DBhdl->query($SQLcmds[$Count]) ;

if($DBquery==FALSE) {

QueryErrDetails($DBhdl,$SQLcmds[$Count]) ; // The query failed, find out why

$GoodQuery = FALSE ;

}

return($GoodQuery) ;

} // In the event of an error call this function to show the details of the error.

function QueryErrDetails($DBhdl,$DBcommand)

{

print("<p>SQLite rejected this command:</p>\n") ;

printf("<blockquote class=sqlerr><i>%s</i></blockquote>\n",str_replace("\n","<br/>\n",$DBcommand)) ;

$ErrorInfo = $DBhdl->errorInfo() ;

printf("<b>Driver-specific error message:</b> %s<br/>\n",$ErrorInfo[2]) ;

printf("<b>Driver-specific code:</b> %d<br/>\n",$ErrorInfo[1]) ;

printf("<b>SQLSTATE error code:</b> %s<br/>\n",$ErrorInfo[0]) ;

}

// Trim the database except for the $Count newest function TrimDB($DBhdl,$Count) { $ClrDwn[] = sprintf(" DELETE FROM StudentNames WHERE std_Counter NOT IN (SELECT std_Counter FROM StudentNames ORDER BY std_Counter DESC LIMIT %s)",$Count) ; return(RunSQLcmds($ClrDwn,$DBhdl)) ; } // Check the database directory, try to create it if it doesn't exist // This function tries to cater for the two possible web server setups: // // - The web server is running as some dedicated user, e.g. apache // - The web server is running as same user that owns the files/directories // function checkDBdir($chkDBdir) { $ErrStr = "" ; if(file_exists($chkDBdir)) { // Does $chkDBdir exist if(!is_dir($chkDBdir)) { $ErrStr = sprintf("<tt>'%s'</tt> is not a directory",$chkDBdir) ; } else { if(!is_writable($chkDBdir)) { $ErrStr = sprintf("<tt>'%s'</tt> is not writable by the web process",$chkDBdir) ; } } } else { // $chkDBdir doesn't exist, lets see if we can create it $ParentDir = dirname($chkDBdir) ; // Get its parent if(!file_exists($ParentDir)) $ErrStr = sprintf("<tt>'%s'</tt> does not exist;<br />the parent <tt>'%s'</tt>doesn't exist either.<br />Methinks there is something seriously wrong with the name.",$chkDBdir,$ParentDir) ; else { if(!is_writable($ParentDir)) { $ErrStr = sprintf("<tt>'%s'</tt> does not exist, and the web process does not have write permission to its parent, <tt>'%s'</tt>.<br />You might want to temporarily open permissions on <tt>'%s'</tt>, you can close them after <tt>%s</tt> has been created",$chkDBdir,$ParentDir,$ParentDir,$chkDBdir) ; } else { if(!mkdir($chkDBdir)) { $ErrStr = sprintf("'%s' does not exist, and the web process got an error when it tried to create it",$chkDBdir) ; } } } } return($ErrStr) ; } //////////////////////////////////////////////////////////////////////////////// // Change log // // $Log: SQLiteExample.php,v $ // Revision 3.1 2013/06/18 16:14:09 Philip Kearns // Switch to prepare()/execute() for adding data to the database. // Make sure the name entered into the form isn't blank, complain if it is. // Improve checkDBdir() so it checks for existance of the parent directory of // the database directory. // Strip/trim leading and trailing spaces from the input. // Add row numbers to the output. // Remove an extraneous space in the database directory name. // Rename the variable $me to $ThisScript. // // Revision 2.6 2013/06/17 15:41:26 Philip Kearns // The database is in a directory which cannot be reached except by PHP. // // Revision 2.5 2013/02/18 15:19:13 Philip Kearns // Put single quotes around the $RCSinfo string so it won't interpret the $. // Clarify some of the error messages. // // Revision 2.4 2012/09/07 10:37:53 Philip Kearns // Rearrange the code to make sure the link to the documentation is shown even if // there is an error at the start. // // Revision 2.3 2012/09/06 23:48:32 Philip Kearns // Change the location of the database directory. // Change the link under my name. // Add some more comments. // // Revision 2.2 2012/09/06 22:13:20 Philip Kearns // Get the web user information earlier in the code. // Add RCS and web user information to the bottom of the page. // Add a few comment lines to explain checkDBdir(). // // Revision 2.1 2012/09/06 11:50:19 Philip Kearns // Create a new function checkDBdir() which will do a more thorough check on the // chosen database directory, creating it if necessary/possible. // Call checkDBdir() at the start of the main code instead of is_writable(). // In the event of problems with the database directory use the error string // returned by checkDBdir() to inform the user what went wrong. // Change the name of the database directory. // // Revision 1.10 2012/08/14 10:41:28 Philip Kearns // Add my name as the author. Tidy up text. // // Revision 1.9 2012/08/13 21:39:53 Philip Kearns // Add a link to the documentation. // Highlight more lines and change them all to the same form so // they can be easily edited out. // // Revision 1.8 2012/08/13 15:26:02 Philip Kearns // Add comments with 'HL' so the meat of the code can be highlighted. // // Revision 1.7 2012/08/13 12:32:36 Philip Kearns // Tidy up the HTML and its content. // // Revision 1.6 2012/08/13 12:25:23 Philip Kearns // Use get_magic_quotes_gpc() to decide if we need to remove backslashes. // Add BEGIN/COMMIT when RunSQLcmds() is processing more than five SQL // statements. // Print more information on the user in the event of the target directory's not // being writable. // Expand the SQL command for the table creation to make it more readable. // Correct the title of the page. // Add more PHP comments. // // Revision 1.5 2012/08/12 23:31:21 Philip Kearns // Create the table earlier in the code. // Tidy up C code. // // Revision 1.4 2012/08/12 22:44:45 Philip Kearns // Make sure the directory for the database is writable and output detailed // information if it isn't. // Move the database-trimming code to a separate function, TrimDB(). // Use $DBhandle->quote() on the form input. // Change the range of the limitwidth class. // Tidy up the HTML. // // Revision 1.3 2012/08/12 15:16:19 Philip Kearns // Open the database at the start of the code. // Use RunSQLcmds() for some of the queries. // Tidy up HTML, SQL and PHP, adding comments. // // Revision 1.2 2012/08/12 14:07:41 Philip Kearns // Move the definition of QueryErrDetails() down to the end of the code. // Remove the fieldset from around the form. // Add a new CSS class to set the width of the page, making it easier to read. // Add code to display and trim the database. // // Revision 1.1 2012/08/12 11:19:08 Philip Kearns // Initial revision // // ?>