SQLite is a full featured file based database where security ( if required ) can be added to the file based on the environment.
There is no userid and password to connect to SQLite database. SQLite is not like our other database (Example : Oracle or MySQL ) which runs on a client – server concept.
No need to install or setup to use SQLite. The initial procedure of setup, configuration and run of database server is not required in case of SQLite.
Portable Database
The database with table created in Python can be copied and used in PHP environment.
Database connection and Managing
We will learn more about using SQLite database in PHP environment using PDO and SQLite library. We will learn two ways to connect and manage SQLite database from PHP.
PDO
By using pdo_sqlite driver
SQLite3 By using sqlite3
Older versions of PHP provided a set of SQLite functions (e.g., sqlite_open(), sqlite_query()) to interact with SQLite databases. However, these functions are deprecated and should be avoided in favor of SQLite3 or PDO.
Which one to use PDO or SQLite3 ?
Choose PDO for its ability to work seamlessly with multiple types of databases while providing a consistent and unified API, allowing you to leverage advanced features like named placeholders and better error handling across various database systems.
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
PHP Script using SQLite database
<?php
// Create (connect to) SQLite database in file
//$my_conn = new PDO('sqlite:D:\\sqlite-data\\my_student.db');// different path
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/my_student.db'); // same location
// Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$sql="SELECT * FROM student LIMIT 0,5";
echo "<table>";
foreach ($my_conn->query($sql) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
}
echo "</table>";
?>
Database Connection
PHP PDO offers a standard way to connect to SQLite databases. This connection ensures flexibility across different database systems without changing much code. Example:
$pdo = new PDO('sqlite:database.db');
Prepared Statements
Prepared statements help protect your application from SQL injection by separating SQL code from user input.
These scripts are developed by using PHP PDO database connection, so it can be used with MySQL database or SQLite database by changing the connection object.