PHP SQLite
PHP
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. We will learn more about using SQLite database in PHP environment using PDO and SQLite library.
What is SQLite3 ?
This is a simple command-line program named sqlite3 or sqlite3.exe on windows. Using this tool we can manage SQLite database by using SQL statements and other commands.
SQLite3 command line tool to manage SQLite database →
SQLite3 and PHP : connection, create table and functions. →
Using pdo_sqlite
One of the several drivers available with PHP PDO is pdo_sqlite . These scripts are developed using pdo_sqlite support. Check your PHP installation for pdo_sqlite support here.
Function Description
support Check SQLite support using phpinfo()
Connection Connection to DB and creating table
Display Display multiple rows using Parameter query
Display Display single row using Parameter query
Update Update and get number of records updated
insert Add records to table and display the record ID
delete delete records and get number of affected by query
drop delete table if exists
Filter records
Function Description
LIKE String Pattern matching using wildcards
LIMIT Strting position and number of records to return
DISTINCT Unique data from the Column
MAX Highest value of the column and record details
MIN Lowest value of the column and record details
Each column in an SQLite 3 database is assigned one of the following type affinities:
TEXT
NUMERIC
INTEGER
REAL
BLOB
$sql="CREATE TABLE student1(
a TEXT,
b NUMERIC,
c INTEGER,
d REAL,
e BLOB
)";
$count=$my_conn->prepare($sql);
Date and time
SQLite does not have a storage class set aside for storing dates and/or times. Use TEXT Datatypes to store date and time.
Managing Date and time in SQLite database →
Boolean Datatype
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>";
?>
Sample script using PHP & SQLite database
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.
⇓ Download sample scripts using SQLite with instructions on how to use.
← PHP MySQL
← PDO Functions
← Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
plus2net.com