|
|
|
|
|
|
| |
Introduction to using PHP 5.0 and SQLite
1.Intorduction
2.SQLite
3.Start
4.PHP
5.Summery
Introduction
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 you’re 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 you’re database, after which you will get: |
|
sqlite version 2.8.6
Enter “.help” for instructions
sqlite>
|
|
|
To create a table within our database type: |
|
sqlite>CREATE TABLE users(id INTEGER PRIMARY KEY, username VARCHAR(12), password VARCHAR(12));
|
|
|
|
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:
|
| |
|
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 we’re 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, that’s 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 that’s 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 you’re 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 you’re 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 it’s 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 you’re colleagues or friends, it has the ability to publish this information from you’re database to the internet it has search abilities and almost everything you need for day-to-day use. |
|
|
|
| PHP 101 Part 9 of 15 : SQLite My Fire!
Categories : PHP, Beginner Guides, Databases, SQLite | | | Simple Connection to PostgreSQL with PHP
Categories : PHP, PostgreSQL, Databases | | | Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation | | | Descriptions of Common Data Types
Categories : MySQL, Databases, PHP, PHP options/info, General | | | Apache, PHP, and PostgreSQL on RedHat Linux
Categories : Apache, PHP, Databases, PostgreSQL, Linux | | | Simple Connection to Informix with PHP
Categories : PHP, Informix, Databases | | | Simple Connection to Sybase with PHP
Categories : PHP, Sybase, Databases | | | Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Categories : PHP, MySQL, Databases, DHTML | | | Simple ODBC Connections with PHP
Categories : PHP, ODBC, Databases | | | Building A Persistent Shopping Cart With PHP and MySQL
Categories : PHP, MySQL, Databases, Ecommerce | | | Case Study: Handling MySQL Growth With a PHP Class
Categories : Databases, MySQL, PHP | | | PHP, MySQL and Authentication 101
Categories : PHP, Databases, MySQL, Authentication | | | Practical Date and Time examples with PHP and MySQL
Categories : Databases, MySQL, PHP, Date/time | | | Abstracting Oracle Connectivity with PHP and OCI8
Categories : PHP, Databases, OCI8, Oracle | | | Miles To Go Before I Sleep...
Categories : PHP, Calendar, Databases, MySQL | |
| |
|
|
