PHP SQLite Database Management

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.

Which one to use PDO or SQLite3 ?

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.

CRUD : Create Read Update Delete

Function Description
supportCheck SQLite support using phpinfo()
ConnectionConnection to DB and creating table
DisplayDisplay multiple rows using Parameter query
DisplayDisplay single row using Parameter query
UpdateUpdate and get number of records updated
insertAdd records to table and display the record ID
deletedelete records and get number of affected by query
dropdelete table if exists
ACIDAtomicity, Consistency, Isolation, Durability
BlobBinary Large Object to store images audio files, videos etc.
FormCollect user inputs and store in SQLite database table.
LoginSimple Login system using SQLite & PHP PDO .
Filter records
Function Description
LIKEString Pattern matching using wildcards
LIMITStrting position and number of records to return
DISTINCTUnique data from the Column
MAXHighest value of the column and record details
MINLowest 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>";
?>

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.

Fetching Data

PDO provides methods like fetch() and fetchAll() for retrieving query results.

Error Handling

PDO offers built-in error handling methods to capture exceptions when a query or connection fails.

Transactions

Use PDO transactions for safely executing multiple related queries, ensuring database integrity.

Parameterized Queries

With PDO, queries can be parameterized to dynamically bind values during execution, ensuring security and efficiency.

CRUD Operations

PDO simplifies performing Create, Read, Update, and Delete (CRUD) operations securely and efficiently.

SQLite Data Types

SQLite supports dynamic typing, allowing flexibility in handling various data types.

Last Insert ID

PDO’s lastInsertId() method retrieves the ID of the last inserted row, helpful for tracking new records.

SQLite In-Memory Database

SQLite supports in-memory databases, enabling faster, temporary database operations ideal for testing.

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.

Podcast on SQLite database management using PHP PDO
Download sample scripts using SQLite with instructions on how to use.

PHP SQLite support
PHP MySQL PDO Functions
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com











    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer