YouTipSqlite Php
## Installation
The SQLite3 extension is enabled by default as of PHP 5.3.0. It can be disabled at compile time by using the **--without-sqlite3** option.
Windows users must enable the php_sqlite3.dll extension to use it. This DLL is included in PHP's Windows distributions as of PHP 5.3.0.
For detailed installation instructions, please refer to our PHP tutorial and its official website.
## PHP Interface API
The following are the important PHP programs that can meet your needs for using SQLite databases in PHP programs. If you need more details, please refer to the official PHP documentation.
| No. | API & Description |
| --- | --- |
| 1 | **public void SQLite3::open ( filename, flags, encryption_key )** Opens a SQLite 3 database. If the build includes encryption, it will attempt to use the provided key. If the filename _filename_ is set to **':memory:'**, SQLite3::open() will create an in-memory database in RAM, which will only last for the duration of the session. If the filename is an actual device file name, SQLite3::open() will use this parameter value to attempt to open the database file. If a file with this name does not exist, a new database file with this name will be created. The optional flags are used to determine how the SQLite database is opened. By default, it is opened using SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE. |
| 2 | **public bool SQLite3::exec ( string $query )** This routine provides a shortcut for executing SQL commands, where the SQL command is provided by the sql parameter and can consist of multiple SQL commands. This program is used to execute a query that does not return results on a given database. |
| 3 | **public SQLite3Result SQLite3::query ( string $query )** This routine executes an SQL query and returns an **SQLite3Result** object if the query returns results. |
| 4 | **public int SQLite3::lastErrorCode ( void )** This routine returns the numeric result code of the most recent failed SQLite request. |
| 5 | **public string SQLite3::lastErrorMsg ( void )** This routine returns the English text description of the most recent failed SQLite request. |
| 6 | **public int SQLite3::changes ( void )** This routine returns the number of database rows that were changed, inserted, or deleted by the most recent SQL statement. |
| 7 | **public bool SQLite3::close ( void )** This routine closes the database connection previously opened by SQLite3::open(). |
| 8 | **public string SQLite3::escapeString ( string $value )** This routine returns a string that has been properly escaped for use in SQL statements, for security purposes. |
## Connecting to a Database
The following PHP code shows how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned.
```php
open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfullyn";
}
?>
Now, let's run the above program to create our database **test.db** in the current directory. You can change the path as needed. If the database is successfully created, the following message will be displayed:
Opened database successfully
## Creating a Table
The following PHP code segment will be used to create a table in the previously created database:
```php
open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfullyn";
}
$sql =<<exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfullyn";
}
$db->close();
?>
When the above program is executed, it will create the COMPANY table in **test.db** and display the following message:
Opened database successfully
Table created successfully
## INSERT Operation
The following PHP program shows how to create records in the COMPANY table created above:
```php
open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfullyn";
}
$sql =<<exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Records created successfullyn";
}
$db->close();
?>
When the above program is executed, it will create the given records in the COMPANY table and will display the following two lines:
Opened database successfully
Records created successfully
## SELECT Operation
The following PHP program shows how to fetch and display records from the COMPANY table created above:
```php
open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfullyn";
}
$sql =<<query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
echo "ID = ". $row['ID'] . "n";
echo "NAME = ". $row['NAME'] ."n";
echo "ADDRESS = ". $row['ADDRESS'] ."n";
echo "SALARY = ".$row['SALARY'] ."nn";
}
echo "Operation done successfullyn";
$db->close();
?>
When the above program is executed, it will produce the following result:
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully
## UPDATE Operation
The following PHP code shows how to use the UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table:
```php
open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfullyn";
}
$sql =<<exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record updated successfullyn";
}
$sql =<<query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
echo "ID = ". $row['ID'] . "n";
echo "NAME = ". $row['NAME'] ."n";
echo "ADDRESS = ". $row['ADDRESS'] ."n";
echo "SALARY = ".$row['SALARY'] ."nn";
}
echo "Operation done successfullyn";
$db->close();
?>
When the above program is executed, it will produce the following result:
Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully
## DELETE Operation
The following PHP code shows how to use the DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table:
```php
open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfullyn";
}
$sql =<<exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record deleted successfullyn";
}
$sql =<<query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
echo "ID = ". $row['ID'] . "n";
echo "NAME = ". $row['NAME'] ."n";
echo "ADDRESS = ". $row['ADDRESS'] ."n";
echo "SALARY = ".$row['SALARY'] ."nn";
}
echo "Operation done successfullyn";
$db->close();
?>
When the above program is executed, it will produce the following result:
Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully