In this tutorial I will show you how to make a database using SQLite, enter some data into it and then use new exciting features in PHP 5.0 to read that data from the database. First of all you will need SQLite which can be downloaded from www.sqlite.org and PHP 5.0 off course (also can be downloaded from the php official site www.php.net).
SQLite
First of all we need to know what SQLite is before we can use it. Well, as you have guesed SQLite is a database, something like MySQL, its free, its fast and its very accessible. First of all SQLite work trough Virtual Database Engine of SQLite (VDBE for short), that works as a virtual computer that runs a program in its virtual machine language. The great thing about this is that you are able to produce a single database file, that can be uploaded to remote server, or you can even use PHP 5.0 to create database file and databases on the fly, and then download it from the server for analysis.
Start
OK. If you have downloaded SQLite from (www.sqlite.org) you should unpack it to some directory, open up youre command shell (MSDOS) and then navigate to the directory you unpacked SQLite and then type: Sqlite mydb.
With sqlite you start sqlite, and mydb is the name of youre database, after which you will get:
sqlite version 2.8.6
Enter .help for instructions
sqlite>
Here we declared a table named users with 3 columns (id, username and password) ID is an integer value that we are going to use as our primary key. The interesting stuff about primary key in SQLite is that auto increments by default, so if we insert a NULL value to this field it will automatically increment to 1, 2, 3,...,2147483647. Username and password is a character value that can hold up to 12 characters.
If you type:
sqlite>.tables
You will get the name of our table users.
So the next thing we have to do is fill our table with some data, and the way to do that is this:
sqlite>INSERT INTO users VALUES (NULL, user1, pass1);
Then were going to insert another user the same way:
sqlite>INSERT INTO users VALUES(NULL, user2, pass2);
Notice that we supplied the ID field with NULL value, thats because we want it to be auto incremented, to prove this point, we will display the inserted values by using SELECT *.
sqlite>SELECT * FROM users;
You should get something like this:
1|user1|pass1
2|user2|pass2
And thats what we wanted!
PHP
Now we are going to combine PHP and SQLite to make a simple page to display users.
First of all fire up youre notepad or any other text editor you like to use for writing PHP scripts. And then just add some basic html code like this:
<html>
<head>
<title>User Management</title>
</head>
<body style=font-family: Verdana; font-size: 10px;>
<?php
// this is where are we going to place our code
?> </body>
</html>
Now, save this page (name it whatever you like) in the same directory where you have unpacked SQLite (where youre database is). Then we are going to open up a SQLite connection and read data from the database.
<?
1. $db = sqlite_open('mydb', 0666, $sqliteerror);
2. $result = sqlite_query($db, 'SELECT * FROM users');
3. echo '<table border=1><tr><td>id</td><td>username</td><td>password</td></tr>';
4. while($user = sqlite_fetch_object($result))
5. {
6. echo '<tr><td>'.$user->id.'</td><td>'.$user->username.'</td><td>'.$user->password.'</td></tr>';
7. }
8. echo '</table>';
9. //Cleaning up
10. $result = 0;
11. $user = 0;
12. sqlite_close($db);
?>
In the first line of the code we open up a connection to the database and then assign it to the variable $db for further handling.
In the second line of the code we run the query, using the sqlite_query() function and assign the result of the query to the $result variable.
In the third line of code we make a simple table.
In the fourth line we have a while loop with sqlite_fetch_object() function that takes the result of the query to produce an object that we are going to assign to $user variable in order to produce the results to the user.
Then we issue an echo command to write out the values from the database.
And in line 12 we close the sqlite connection.
Now go and try out this code, you should get something like this :
id username password
1 User1 pass1
2 User2 pass2
Summery
I showed you just a part of the abilities of SQLite and PHP 5.0. It can be used for making small guestbook applications or writing structured data into database from the server, in internet surveys for example. The good thing is that its free, and it can be used by anyone. Sure its not very well documented, but it has a lots of nice features. For example you can use it to make your own little databases that you can distribute freely among youre colleagues or friends, it has the ability to publish this information from youre database to the internet it has search abilities and almost everything you need for day-to-day use.
