 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Job with SQLite
Introduction
SQLite is a relational database, searches to which can be carried out by means of language of searches SQL. The database does not support all features SQL and concedes in functionality to anothers advanced SUBD, but quite approaches for storage and extraction of the information.
Difference SQLite from MySQL and similar SUBD
Classical SUBD, such as MySQL (and as MS SQL, Oracle, PostgreeSQL) will consist of the separate server supporting job of a database and listening certain{determined} port, for the reference{manipulation} of clients. As the client can act including expansion PHP, realizing the interface with which help searches to base are carried out. Cursor SQLite and the interface to her are realized in one library that increases speed of performance of searches. Such server often name built - in.
The remark
The built - in server is present and at other databases, for example, at MySQL, but his{its} use demands license deductions, therefore has not received a wide circulation in the world of open initial codes.
SQLite is bestipovoj a database. More precisely, there are only two types - integer "integer" and text "text". And "integer" it is used mainly for a primary key of the table, and for other data will go "text". The length of the line which are written down in a text field, can be any.
Features SQLite
All databases are stored{kept} in files, on one file on base. Quantity{amount} of databases and as tables in them, is limited only an empty seat available on a site. And the greatest possible volume of one database makes 2 Tb.
As all data are stored{kept} in files, problems with carry of a database from one hosting on another do not exist - enough only to copy corresponding files.
Installation SQLite
In PHP5 support SQLite is established and switched on by default.
Installation under Windows: For installation SQLite it is necessary to download and copy in a folder with expansions library " php_sqlite.dll " which can be loaded under the link: http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll. Then it is necessary raskommentirovat` (or to add) a line "extension=php_sqlite.dll" in a file "php.ini". For normal functioning SQLite also it is necessary raskommentirovat` a line "extension=php_pdo.dll".
The remark
If full version PHP in zip-archive, instead of as the installer is used, corresponding libraries of expansion should is in a directory ext. It is possible to esteem in clause{article} Installation Apache, PHP, MySQL more in detail.
The remark
Library " php_pdo.dll " should be loaded before loading "php_sqlite.dll". That is in php.ini line "extension=php_sqlite.dll" should stand after "extension=php_pdo.dll".
Installation under Unix: Download I skin version SQLite from an official site (http://sqlite.org/download.html). Read a file "INSTALL", delivered with initial tests of the module. Or simply use the command of installation PEAR: " pear install sqlite ".
Job with SQLite
Creation of a database: to create a new database it is necessary to use function sqlite_open (). If the base which name is specified in parameter "filename" does not exist, function will create a new database with a name "filename" and will return the identifier of a database.
resource sqlite_open (string filename [, int mode [, string *error_message]])
In a script, prevedjonnom is lower, creation of a new database is shown:
<? php
// We shall create a database
$db = sqlite_open ("my_database.db");
if (! $db) exit (" it was not possible to create a database! ");
?>
In result in a folder with a script at us the file with a name "my_database.db" - our database will appear.
Creation of tables: All searches to a database are carried out with function sqlite_query () which has the following syntax:
resource sqlite_query (resource dbhandle, string query)
The remark
For job with SQLite, as well as any relational database uses language of searches SQL. Therefore to create the table of the data it is possible by means of traditional search CREATE TABLE, to insert recording by means of operator INSERT, to take recording with help SELECT, and to update existing recording by means of search UPDATE.
In the example resulted below the table table1, containing three field is created: an integer field id which represents itself as a primary key, and two text fields field1 and field2.
<? php
// We shall create a new database
$db = sqlite_open ("my_database.db");
if (! $db) exit (" It is impossible to create a database! ");
// We shall create the table "table1" in base
$query_table = sqlite_query ($db, " CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id automatically becomes autoincremental */
field1 TEXT,
field2 TEXT);
");
if (! $query_table) exit (" It is impossible to create the table in a database! ");
// We shall write down something in the table
$query_insert = sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES ('PHP5', 'Apache'); ");
if (! $query_insert) exit (" It is impossible to write down the data in the table! ");
?>
After creation of the table, in it{her} the recording containing lines 'PHP5' and 'Apache', a field id automatically value 1 is added receives.
Conclusion of the data from base: For a conclusion of the data from tables all is used the same function - sqlite_query (). If some recordings get out, the result of sample should be processed by means of a cycle while () and functions sqlite_fetch_array () which has the following syntax:
array sqlite_fetch_array (resource result [, int result_type [, bool decode_binary]])
Below the script showing a conclusion of several recordings from a database is resulted:
<? php
// We shall create a new database
$db = sqlite_open ("my_database.db");
if (! $db) exit (" It is impossible to create a database! ");
// We shall create the table "table1" in base
$query_table = sqlite_query ($db, " CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id automatically becomes autoincremental */
field1 TEXT,
field2 TEXT);
");
if (! $query_table) exit (" It is impossible to create the table in a database! ");
// We shall write down something in the table
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES (' PHP5 + ', 'Apache'); ");
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES (' SQLite - ', 'first-class thing'); ");
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES ('Visit', 'sqlite.org'); ");
// We shall make sample of the data
$res = sqlite_query ($db, " SELECT * FROM table1; ");
// In a cycle we shall deduce{remove} all received data
while ($array = sqlite_fetch_array ($res))
{
echo ($array ['field1.'] $array ['field2.'] " (id recordings: ". $ array ['id']. ") <br/> ");
}
?>
As a result of job of a script we shall receive:
PHP5+Apache (id zapisi:1)
SQLite - a first-class thing (id zapisi:2)
Visit sqlite.org (id zapisi:3)
Redaktrirovanie recordings: For change of a field we shall use function sqlite_query () and we shall pass her search about updating (UPDATE).
<? php
// We shall create a new database
$db = sqlite_open ("my_database.db");
if (! $db) exit (" It is impossible to create a database! ");
// We shall create the table "table1" in base
$query_table = sqlite_query ($db, " CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id automatically becomes autoincremental */
field1 TEXT,
field2 TEXT);
");
if (! $query_table) exit (" It is impossible to create the table in a database! ");
// We shall write down something in the table
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES (' PHP5 + ', 'Apache'); ");
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES (' SQLite - ', 'first-class thing'); ");
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES ('Visit', 'sqlite.org'); ");
// We shall change a field with id=1
sqlite_query ($db, " UPDATE table1 SET field2 ='Apache+Linux ' WHERE id=1; ");
// We shall make sample of the data
$query = sqlite_query ($db, " SELECT * FROM table1; ");
// In a cycle we shall deduce{remove} all received data
while ($array = sqlite_fetch_array ($query))
{
echo ($array ['field1.'] $array ['field2.'] " (id recordings: ". $ array ['id']. ") <br/> ");
}
?>
In result we shall receive:
PHP5+Apache+Linux (id zapisi:1)
SQLite - a first-class thing (id zapisi:2)
Visit sqlite.org (id zapisi:3)
Removal{Distance} of recording from the table: to remove recording from the table, it is necessary to pass functions sqlite_query () search about removal{distance} (DELETE).
<? php
// We shall create a new database
$db = sqlite_open ("my_database.db");
if (! $db) exit (" It is impossible to create a database! ");
// We shall create the table "table1" in base
$query_table = sqlite_query ($db, " CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id automatically becomes autoincremental */
field1 TEXT,
field2 TEXT);
");
if (! $query_table) exit (" It is impossible to create the table in a database! ");
// We shall write down something in the table
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES (' PHP5 + ', 'Apache'); ");
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES (' SQLite - ', 'first-class thing'); ");
sqlite_query ($db, " INSERT INTO table1 (field1, field2) VALUES ('Visit', 'sqlite.org'); ");
// We shall remove a field with id=2
sqlite_query ($db, " DELETE FROM table1 WHERE id=2; ");
// We shall make sample of the data
$query = sqlite_query ($db, " SELECT * FROM table1; ");
// In a cycle we shall deduce{remove} all received data
while ($array = sqlite_fetch_array ($query))
{
echo ($array ['field1.'] $array ['field2.'] " (id recordings: ". $ array ['id']. ") <br/> ");
}
?>
In result we shall receive:
PHP5+Apache (id zapisi:1)
Visit sqlite.org (id zapisi:3)
Closing of a database: For closing a database function sqlite_close () is used. As unique parameter function accepts the identifier of an open database.
void sqlite_close (resource dbhandle)
The circuit of use of the given function is submitted below
<? php
$db = sqlite_open ("my_database.db");
/*
... Here there is a job to a DB...
*/
sqlite_close ($db);
?>
