CHAPTER 6 – SQLite – Simple Queries

When the database is opened, we can start execut- ing queries on the database. Because no tables are available in a new data- base, we have to create them first. The following example explains how to do this: <?php ... $create_query = " CREATE TABLE document ( id INTEGER PRIMARY KEY, title, intro, body ); CREATE TABLE dictionary ( id INTEGER PRIMARY KEY, word ); CREATE TABLE lookup ( document_id INTEGER, word_id INTEGER, position INTEGER ); CREATE UNIQUE INDEX word ON dictionary(word); "; $db->query($create_query); ?> If you are familiar with other database systems, you will most likely notice the absence of types for some of the field definitions in the CREATE TABLE queries shown earlier. SQLite actually has only two types internally: INTEGER, which is used to store numbers, and "something else", which can be compared to a VARCHAR field in other databases. SQLite's VARCHAR can store more than 255 characters, though, which is sometimes a limitation in other database sys- tems. You can also make an INTEGER field auto-increment by adding "PRIMARY KEY" as a postfix to the field definition. Of course, you can do this for only one field per table. Something else that you might notice is that we execute multiple CREATE TABLE queries with one function call to the query() method. This is often not possible with other PHP interfaces to other database systems, such as the MySQL (not MySQLi) extension.