sqlite3 exec()

Example using exec()
Update query using exec()
Inserting two rows by exec()
Creating Table by exec()
Executing multiple SQL statements at once
Error handling with exec()
Handling Exceptions
PHP SQLite `exec()` security
exec() and lastInsertRowID()
Questions on exec()

exec(query) : Executes a query based on SQLite3 connection.
Returns True or False based on execution result.

To create the sample student table, use the code given here.

Example using exec() 🔝

Note that we have used the query ( $sql ) as input parameter to the exec()

<?php 
$my_conn = new SQLite3('my_db.db'); // Connect to existing database
$sql="DELETE FROM student WHERE class='Four'";
		
$my_conn->exec($sql); // Executes the query
?>

We can check by reading the return value ( True or False ) and use if condition to give message.
If the query execution is successful ( True ) then we are showing number of rows updated by using changes().

<?php 
$my_conn = new SQLite3('my_db.db'); // Connect to existing database
$sql="DELETE FROM student WHERE class='Four'";
		
$result = $my_conn->exec($sql); // Executes the query
if ($result) {
 echo 'Number of rows deleted : ', $my_conn->changes();
}
?>

Output

Number of rows deleted : 9

Here we are not expecting any return of rows or data from the database so we should use exec(). However if we expect any data returned from query operation then it is better to use query().

exec() is better for INSERT, UPDATE, or DELETE queries.

Update query using exec() 🔝

<?php 
$my_conn = new SQLite3('my_db.db'); 
$sql="UPDATE student SET mark=mark+5 WHERE class='Five'";
		
$result=$my_conn->exec($sql); // connection with Query

if ($result) {
 echo 'Number of rows Updated : ', $my_conn->changes();
}
?>

Output

Number of rows Updated : 3

Inserting two rows 🔝

<?php 
$my_conn = new SQLite3('my_db.db'); 
$sql="INSERT INTO `student` 
(`id`, `name`, `class`, `mark`, `gender`) VALUES
(39, 'Abcd', 'Four', 88, 'Female'),
(40, 'XYZ', 'Five', 38, 'Male')";
		
$result = $my_conn->exec($sql);
if ($result) {
 echo 'Number of rows added : ', $my_conn->changes();
}
?>

Output

Number of rows added : 2

Creating table 🔝

$sql="CREATE TABLE IF NOT EXISTS
	student(id integer primary key, 
    name text, 
    class text, 
    mark integer, 
    gender text 
    )";
$my_conn->exec($sql);

Executing multiple SQL statements at once 🔝

To do this, simply separate the statements with a semicolon (;).

<?php 
$my_conn = new SQLite3('my_db.db'); 
$sql="UPDATE student SET mark=mark+5 WHERE class='Five'; 
      DELETE FROM student WHERE class='Three';
	  DELETE FROM student WHERE class='Six';
	  DELETE FROM student";
		
$result=$my_conn->exec($sql); // connection with Query

if ($result) {
 echo 'Number of rows changed : ', $my_conn->changes();
}
?>

Error handling with exec() 🔝

Here we have id column as primary key ( unique ), so as we try to add a record with id column value = 1 ( which is already available ) we will get error.

<?Php
$my_conn = new SQLite3('my_db.db'); // create database in same location
//$sql="SELECT * FROM student WHERE class3='Five'";
$sql="INSERT INTO `student` 
(`id`, `name`, `class`, `mark`, `gender`) VALUES
(1, 'John Deo', 'Four', 75, 'female')";
$result=$my_conn->exec($sql); // Create table 
if($result==FALSE){
 echo "Error in Message : ".$my_conn->lastErrorMsg();
 echo "<BR>Error Code : ".$my_conn->lastErrorCode();
 }else{
 echo 'Number of rows inserted: ', $my_conn->changes();
}
?>

Output

Error in Message : UNIQUE constraint failed: student.id
Error Code : 19

If you change the query like this ( commented line )

$sql="SELECT * FROM student WHERE class3='Five'";

The error code and message will be like this .

Error in Message : no such column: class3
Error Code : 1

Handling Exceptions 🔝

$my_conn = new SQLite3('my_db.db'); // create database in same location
$my_conn->enableExceptions(true); 
try {
  $my_conn->exec('create table abcd'); // Wrong query 
} catch (Exception $e) {
  echo 'Caught exception: ' . $e->getMessage();
  echo "<BR>Error in Message : ".$my_conn->lastErrorMsg();
}

Output

Caught exception: incomplete input
Error in Message : incomplete input

Exceptions and errors are two different types of problems that can occur in SQLite. Exceptions are typically caused by user errors and can be handled using the `try...catch` statement. Errors are typically caused by system failures or programming errors and cannot be handled using the `try...catch` statement.

PHP SQLite `exec()` security: 🔝

It is better to use prepared statement and execute() when we pass data to SQL to prevent injection attack.

Using query() : As exec() is result-less, it is better to use query() as alternate to exec() when we are expecting return result. ( Example : SELECT * FROM student )

exec() and lastInsertRowID() 🔝

When inserting a record into a SQLite database table, it is not necessary to specify a value for the primary key column. This is because SQLite will automatically assign the next higher unique number as the value for the primary key column.

To get the ID value of the newly inserted record, you can use the lastInsertRowID() method. This method returns the integer ID of the last inserted row.

Here is an example of how to insert a record without specifying a primary key column value:

<?php
$my_conn = new SQLite3('my_db.db'); // create database in same location

$sql="INSERT INTO `student` 
( `name`, `class`, `mark`, `gender`) VALUES
('John Deo', 'Four', 75, 'female')";

$result=$my_conn->exec($sql); // Create table 

echo 'Number of rows inserted: ', $my_conn->changes();
echo '<BR>Last inserted row ID : ', $my_conn->lastInsertRowID() ;
?>

Output is here

Number of rows inserted: 1
Last inserted row ID : 37

We can use exec() with VACUUM ( To clean the database )

$my_conn = new SQLite3('my_db.db'); // create database in same location
$result=$my_conn->exec('VACUUM;'); //

Sample script using PHP , SQLite3 connection and SQLite database : plus2net_sqlite3_v1

These scripts are developed by using PHP SQLite3 connection to learn different queries and how to execute to mange SQLite database.
Download sample scripts using SQLite3 with instructions on how to use.

Questions 🔝

What is the PHP SQLite `exec()` function?
How do you use the `exec()` function to execute a SQL statement in SQLite?
What are the different parameters that can be passed to the `exec()` function?
What is the return value of the `exec()` function?
How can you handle errors that occur when using the `exec()` function?
What are some best practices for using the `exec()` function securely?
How can you improve the performance of SQLite queries executed using the `exec()` function?
How can you use the `exec()` function to create a new SQLite database?
How can you use the `exec()` function to open an existing SQLite database?
How can you use the `exec()` function to close a SQLite database connection?
How can you use the `exec()` function to execute a SQL statement that returns a result set?
How can you use the `exec()` function to get the last inserted row ID after an INSERT statement?
How can you use the `exec()` function to execute a SQL statement that modifies the database schema?
How can you use the `exec()` function to create a SQLite view?
How can you use the `exec()` function to create a SQLite trigger?