I chose SQLite to use as an introduction to SQL because of its simplicity. SQLite comes bundled with PHP5 and stores data as a text file. If you have PHP4, you probably won't have SQLite. SQLite is simpler but is not as powerful as mySQL but it is powerful enough to have many well known uses, including Google.
As the name implies, SQLite uses a version of SQL so you have to learn new language. The good news is that SQL looks a lot like English. An SQL statement is called a query. A query is set as a PHP string variable and a PHP function sends the query to the database. First, let's create a database:
<?
$db = sqlite_open("my_database");
sqlite_close($db);
?>
$db = sqlite_open("my_database"); does two things. If connects to a database named "my_database" if it exists or creates it if it does not exist and creates a resource named $db that is used much like an image resource. Note: Since this creates a file, the directory it is to be in has to have adequate permisson. Chmode to 777. The next line, sqlite_close($db);, closes the conection. You now have a database with nothing in it.
When you create your data table, each column is assigned a data type. PHP is much looser with data types than some other languages. Take for example:
<?
$x = 2;
$y = "2";
$z = $x+$y;
echo $z # echos 4;
?>
$x is assigned as a number, $y is assigned as a string but when you add them $y is treated as a number. SQLite is stricter and mySQL is even more strict. If you enter data that is the wrong type, it may not be accepted and even if it is accepted, it may not be treated the way that you expect it to be. With SQLite, the data type is more like a recommendation than a requirement. When you move on to mySQL, you have to be more careful.
mySQL has a long list of data types but SQLite has just five:
A data table is created with a SQL query. For this example, I will make a four column table to use for a list of links. The SQL query to make it looks like this:
CREATE TABLE links (
id INTEGER AUTOINCREMENT PRIMARY KEY,
url TEXT,
name TEXT,
catagory TEXT )
The capitalized words are reserved words That have meaning to SQL. You cannot use them to name tables or columns. Capitalization and line breaks are not required; they are just conventions that makes it easier to read. The name of the table is "links". Although the SQL reserved words are not case sensitive, the table name is. "Links" is not the same as "links". The parentheses contain the column names and data type which are separated from the other name/data types by commas. The first columns, "id" is an integer that autoincrements when I add data
To communicate the query to SQLite you assign it int a string variable and use it in the function sqlite_query(). sqlite_query() has two arguments, a database resource and a query string.
<?
$db = sqlite_open("my_database");
# query to string
$create_table = "CREATE TABLE links (
id INTEGER AUTOINCREMENT PRIMARY KEY,
url TEXT,
name TEXT,
catagory TEXT )";
# use string in query
@sqlite_query( $db, $create_table );
?>
The first time the script is run, it will create the table. Every time after that, it will generate an error message because the table already exists so I use a @ to suppress the error message
If you make a mistake setting up your table, you can destroy it and start form scratch by using the keyword "DROP"
<?
$db = sqlite_open("my_database");
$droptable = "DROP TABLE links";
sqlite_query( $db, $droptable );
?>
 |
|
 |