SQLite 3 with PHP Essentials

Introduction
Welcome
What is SQLite and what are the prerequisites?
Using the exercise files

1. Quick Start
Getting the most out of the quick start
Creating and using a database
Inserting data into a table
Getting data from a table
Updating data in a table
Deleting data from a table
Creating a database library

2. Getting Started
Installing XAMPP on a Mac
Installing XAMPP on a PC
Setting up SID and exercise files on a Mac
Setting up SID and exercise files on a PC
Using the command-line tools

3. Creating a Database
Understanding databases and tables in SQLite
Creating a database
Setting permissions for a database
Defining a table in SQL
Creating a table in PHP
Creating indexes
Indexing ID fields

4. SQLite Data Types
Taking care with data types in SQLite
Storing numbers with INTEGER
Storing numbers with REAL
Storing text
Storing large data with BLOB
Storing booleans
Storing dates and times

5. Storing and Reading Data
Storing data with INSERT
Updating data with UPDATE
Reading data with SELECT
Reading data from multiple tables with JOIN
Deleting data with DELETE

6. SQLite Expressions
Understanding expressions in SQLite
Comparing values with comparison operators
Matching patterns with LIKE
Building simple math with arithmetic operators
Matching values in a list with IN
Choosing from multiple conditions with the CASE expression
Forcing a data type with CAST

7. SQLite Core Functions
Finding the LENGTH of a string
Changing case with UPPER and LOWER
Reading parts of a string with SUBSTR
Changing parts of a string with REPLACE
Trimming blank spaces with TRIM
Reading absolute values with ABS
Rounding values with ROUND
Finding the data type of an expression with TYPEOF
Finding the last inserted record with LAST_INSERT_ROWID
Getting the version of your SQLite library
Creating user-defined functions
Building aggregate user-defined functions

8. SQLite Aggregate Functions
Understanding aggregate functions
Counting rows with COUNT
Building with the SUM and TOTAL functions
Finding minimum and maximum values with MIN and MAX
Finding averages with AVG
Grouping results with GROUP BY
Selecting for aggregation with HAVING

9. SQLite Date and Time Functions
Understanding SQLite support for dates and times
Getting readable, sortable dates and times
Getting high-resolution dates and times with JULIANDAY
Formatting dates and times with STRFTIME

10. Sorting and Indexing
Understanding collation
Sorting results with ORDER BY
Removing duplicate results with DISTINCT
Understanding indexes
Working with primary key indexes
Understanding how to use the INTEGER PRIMARY KEY function

11. Transactions
Understanding transactions
Using transactions in SQLite

12. Subselects and Views
Understanding subselects
Creating a simple subselect
Searching within a result set
Searching within a joined result
Creating a view
Searching within a joined view

13. Triggers
Understanding triggers in SQLite
Automatically updating a table with a trigger
Logging transactions with triggers
Improving performance with triggers
Preventing unintended updates with triggers
Adding automatic time stamps

14. PHP Interfaces
Choosing an interface
Using the SQLite3 interface
Using the PDO interface
Creating a library

15. A Simple CRUD Application
Defining CRUD
Using PHP to open and use an SQLite database
Using PHP to insert into an SQLite database
Using PHP to read from an SQLite database
Using PHP to update an SQLite database
Using PHP to delete from an SQLite database

16. An Application for Web Site Testimonials
An overview of the testimonials application
Managing the database in PHP
Displaying the testimonials using PHP

Conclusion
Goodbye