This section steps through some of the basic tasks in working with databases, using the SQLite version of things. Before doing anything, you must create a database:

1. Open a new file in your text editor and start a PHP block:

2. 3. Use the sqlite_open() function to open a database called test.db one level up from the document root—do not worry that it has not been created yet, because this function will create the file if it does not already exist. The sql_error_string() function will display any error message if the command fails:

4. $db = sqlite_open(“../test.db”) or die(sql_error_string());
5.

Note
You might notice that no username or password is used to create or open a SQLite database. This is true, because the SQLite database is technically just a plain file, with no requirements for access other than the capability to read and write to the directory in which you are placing the file.
6. Close the PHP block:

7. ?>
Save this file as sqlite1.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite1.php to create the database.
Note
There will be no output if this script is successful. You will see a message only if the script fails to perform.
Now that you have a working SQLite database, you can move on to creating tables and issuing queries.

Creating a Table and Storing Data with SQLite
When you created tables using MySQL (and in fact when you create a table in any relational database system), you specifically defined the field types and field lengths. For example, you might have had a field called name that was a 25-character varchar field, or a field called start_date that was a datetime field. SQLite is loosely typed, meaning the contents of all fields, regardless of what type they actually are, are stored as strings. Thus, SQLite does not require you to define your fields when you create a table, and if you do, they will be ignored.

In the next example, you will create a table that just holds first names, last names, and e-mail addresses, and populates the table with a few records:

1. Open a new file in your text editor and start a PHP block:

2. 3. Use the sqlite_open() function to open the database previously created:

4. $db = sqlite_open(“../test.db”) or die(sql_error_string());
5. Use the sqlite_query() function to issue a table-creation command:

6. sqlite_query($db, “CREATE TABLE my_friends (first_name, last_name,
7. email)”);
8.
9. Use the sqlite_query() function to add a few entries using the SQL INSERT command:

10. sqlite_query($db, “INSERT INTO my_friends
11. VALUES (‘John’, ‘Smith’, ‘john@smith.com’)”);
12. sqlite_query($db, “INSERT INTO my_friends
13. VALUES (‘Jane’, ‘Doe’, ‘jane@doe.com’)”);
14. sqlite_query($db, “INSERT INTO my_friends
15. VALUES (‘Julie’, ‘Meloni’, ‘julie@thickbook.com’)”);
16.Close the PHP block:

17. ?>
The complete script should look like this:

$db = sqlite_open(“../test.db”) or die(sql_error_string());
sqlite_query($db, “CREATE TABLE my_friends (first_name, last_name, email)”);
sqlite_query($db, “INSERT INTO my_friends
VALUES (‘John’, ‘Smith’, ‘john@smith.com’)”);
sqlite_query($db, “INSERT INTO my_friends
VALUES (‘Jane’, ‘Doe’, ‘jane@doe.com’)”);
sqlite_query($db, “INSERT INTO my_friends
VALUES (‘Julie’, ‘Meloni’, ‘julie@thickbook.com’)”);
?>
Save this file as sqlite2.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite2.php to issue these SQLite commands. Again, there will be no output if this script is successful. You will see a message only if the script fails to perform one or more of the commands.

In the next section, you retrieve items from your table.

Retrieving Items with SQLite
Now that you have records in your SQLite table, you can retrieve them. Again, the process is quite similar to retrieving data from a MySQL table.

1. Open a new file in your text editor and start a PHP block:

2. 3. Use the sqlite_open() function to open the database you previously created:

4. $db = sqlite_open(“../test.db”) or die(sql_error_string());
5. Use the sqlite_query() function to issue a SELECT command, intended to retrieve records in ascending order by last name:

6. $r = sqlite_query($db, “SELECT * FROM my_friends ORDER BY last_name ASC”);
7. Check for a result and print a message if the query was not successful:

8. if (!$r) {
9. echo “Sorry, no records.”;
10. } else {
11.Add a while loop to handle a successful query. This loop will use the sqlite_fetch_array() function, which works just like the ysql_fetch_array() function you used throughout the book:

12. while ($record = sqlite_fetch_array($r)) {
13.Create variables for each field you’re pulling from your table:

14. $first_name = $record[first_name];
15. $last_name = $record[last_name];
16. $email = $record[email];
17.Print each record to the screen, and then close the while loop, the if…else block, and the PHP block:

18. echo “record: $last_name, $first_name ($email)
“;
19. }
20. }
21. ?>
Your entire code should look like this:

$db = sqlite_open(“../test.db”) or die(sql_error_string());
$r = sqlite_query($db, “SELECT * FROM my_friends ORDER BY last_name ASC”);
if (!$r) {
echo “Sorry, no records.”;
} else {
while ($record = sqlite_fetch_array($r)) {
$first_name = $record[first_name];
$last_name = $record[last_name];
$email = $record[email];
echo “record: $last_name, $first_name ($email)
“;
}
}
?>
Save this file as sqlite3.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite3.php to issue these SQLite commands and display the output.

Now that you’ve seen the process for working with SQLite is procedurally similar to working with other databases, you can glance through the next section to pick up some other tidbits of information.
Performing Other Tasks with SQLite
In the previous sections, you’ve seen how to create SQLite databases and tables, and insert and select elements into/from these tables. It’s safe to make the leap that you can issue DELETE and DROP commands similarly to remove records and tables, and you can also use UPDATE to change fields within a record—all of these actions are simply variations on the SQL query that is issued using the sqlite_query() function.

As to the tasks you can perform with SQLite, they’re the same tasks that you can perform with MySQL; all of the MySQL-based code in this book can be rewritten to use SQLite. However, there are two things the code relied on when using MySQL that haven’t been covered in this appendix: auto-incrementing fields and date-stamping.

To implement the use of auto-incrementing fields, you simply have to make some changes to the sqlite2.php script:

1. Open sqlite2.php in your text editor.

2. Change the table-creation command to:

3. sqlite_query($db, “CREATE TABLE my_friends2 (id INTEGER
4. PRIMARY KEY, first_name, last_name, email)”);
5. Change the record-insertion commands to:

6. sqlite_query($db, “INSERT INTO my_friends2 (first_name,
7. last_name, email) VALUES (‘John’, ‘Smith’,
8. ‘john@smith.com’)”);
9. sqlite_query($db, “INSERT INTO my_friends2 (first_name,
10. last_name, email) VALUES (‘Jane’, ‘Doe’,
11. ‘jane@doe.com’)”);
12. sqlite_query($db, “INSERT INTO my_friends2 (first_name,
13. last_name, email) VALUES (‘Julie’, ‘Meloni’,
14. ‘julie@thickbook.com’)”);
Save this file as sqlite4.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite4.php to issue these SQLite commands. Again, there will be no output if this script is successful. You will see a message only if the script fails to perform one or more of the commands.

To see if this script did the trick, modify the sqlite3.php script to retrieve and print the ID field:

1. Open sqlite3.php in your text editor.

2. Change the name of the table in the query:

3. $r = sqlite_query($db, “SELECT * FROM my_friends2 ORDER BY last_name ASC”);
4. Change the while loop:

5. while ($record = sqlite_fetch_array($r)) {
6. $id = $record[id];
7. $first_name = $record[first_name];
8. $last_name = $record[last_name];
9. $email = $record[email];
10. echo “record ID# $id: $last_name, $first_name ($email)
“;
11. }
Save this file as sqlite5.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite5.php to issue these SQLite commands and display the output.
Just like in MySQL, the ID fields are automatically incremented upon record insertion.

Next, let’s take a look at how to handle date-stamping of records, because there’s no now() function as there is in MySQL, nor are there particular methods for formatting date-related fields. The solution is simply to store an integer, the output of the PHP time() function. You can then format this stored value any way you want, using PHP, when you retrieve it for display.

The next steps will work again with the same tables and records used in this appendix, just building on the previous steps.

1. Open sqlite4.php in your text editor.

2. Change the table-creation command to:

3. sqlite_query($db, “CREATE TABLE my_friends3
4. (id INTEGER PRIMARY KEY, first_name, last_name, email,
5. date_added)”);
6. Change the record-insertion commands to:

7. sqlite_query($db, “INSERT INTO my_friends3
8. (first_name, last_name, email, date_added) VALUES
9. (‘John’, ‘Smith’, ‘john@smith.com’, ‘”.time().”‘)”);
10. sqlite_query($db, “INSERT INTO my_friends3
11. (first_name, last_name, email, date_added) VALUES
12. (‘Jane’, ‘Doe’, ‘jane@doe.com’, ‘”.time().”‘)”);
13. sqlite_query($db, “INSERT INTO my_friends3
14. (first_name, last_name, email, date_added) VALUES
15. (‘Julie’, ‘Meloni’, ‘julie@thickbook.com’, ‘”.time().”‘)”);
Save this file as sqlite6.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite6.php to issue these SQLite commands. Again, there will be no output if this script is successful. You will see a message only if the script fails to perform one or more of the commands.

To see if this script did the trick, modify the sqlite5.php script to retrieve, format, and print the values in the date_added field:

1. Open sqlite5.php in your text editor.

2. Change the name of the table in the query, and order the records by ID:

3. $r = sqlite_query($db, “SELECT * FROM my_friends3 ORDER BY id ASC”);
4. Add the following inside the while loop, after the line that defines the value of $email:

5. $date_added = date(“l, M d Y, h:i:s A”, $record[date_added]);
6.
7.

Note
In this example, the date() function formats the value of stored in the date_added field. You can learn more about the numerous formatting options for the date() function in Appendix B, “Basic PHP Language Reference,” and in the PHP manual at http://www.php.net/date.
8. Change the echo statement inside the while loop to:

9. echo “record ID# $id: $last_name, $first_name ($email)
10. added on $date_added
“;
Save this file as sqlite7.php and place it in the document root of your web browser. Access the script at http://127.0.0.1/sqlite7.php to issue these SQLite commands and display the output. You should see something like this figure, including the formatted version of the date-stamp stored in the SQLite table.
As you can see, virtually anything you can do with MySQL, you can do (with a little elbow grease in some instances) with SQLite. If you find yourself without a database server, nothing should keep you from utilizing this new feature of PHP 5.