PHP SQLite3 query() to get SQLite3Result object by executing SQL

PHP Script using SELECT Query.
Executing multiple SQL statements using query()
Crate Table using query()
Getting single row result
Counting Number of rows using query()
Using ORDER BY with query()
Using Subqueries with query()
Using JOIN with query()
Security using SQL in query()
query() and error handling
Questions

query() executes an SQL query against a SQLite database and returns an `SQLite3Result` object.
Parameter : The SQL query
Returns an SQLite3Result object, or false on failure.

Create a database and add the student table. Details are available here.
OR , you can download the sample SQLite database with student table from here also.
https://www.plus2net.com/python/download/my_db.db

PHP Script using SELECT Query. 🔝

Here our return object has multiple rows of matching data from student table. To iterate over the rows we

<?php
$my_conn = new SQLite3('my_db.db'); // connect to database in same location
$sql="SELECT * FROM student WHERE class='Three'";
$result=$my_conn->query($sql); // Query execution 
while ($row=$result->fetchArray()){
  print $row['name'] . ",". $row['class'] . ",". $row['mark'];
  echo "<br>";
}
?>

Output

Max Ruin,Three,85
Arnold,Three,55
Big Nose,Three,81

By using fetchArray() we are converting the result set $result to an array.
To iterate over the rows we have used PHP while loop.

The query() function returns an `SQLite3Result` object. However, if the query does not yield a result, the returned `SQLite3Result` object is essentially unusable. In such cases, it is more appropriate to use the exec() function instead.

In general, query() function should be used for queries that are expected to return a result set, such as SELECT queries. The exec() function should be used for queries that do not yield a result set, such as INSERT, UPDATE, and DELETE queries.

Executing multiple SQL statements using query() 🔝

SQLite can execute multiple SQL queries separated by semicolons. This allows you to execute a batch of SQL statements that you have loaded from a file or embedded in a script. However, this only works when the result of the function is not used. If the result is used, only the first SQL statement will be executed.

Better to use exec() for executing multiple SQL statements.

Crate Table using query() 🔝

If the SQL execution is not successful, the query() function will return False. Therefore, you can use an if else condition to check the return value of query() and give an appropriate message to the user.

<?php 
$my_conn = new SQLite3('my_db.db'); // Connection to database

$sql="CREATE TABLE
  student(id integer primary key, 
  name text, 
  class text, 
  mark integer, 
  gender text 
  )";
$result=$my_conn->query($sql);
if($result){
	echo "Table created";
}else{
	echo " Query failed ";
}
?>

Getting single row result 🔝

While using this query we can expect single row of data.

$sql="SELECT  * FROM student WHERE id=2 ";

In place of using query() we will use querySingle() here.

<?php 
$my_conn = new SQLite3('my_db.db'); 
$sql="SELECT  name,class,mark,gender FROM student WHERE id=2 ";
$result=$my_conn->querySingle($sql,False); // 
print_r($result); // value of Name column only
echo $result;   // value of Name column only
?>

Above code will return value for name column only as we used $entireRow = False ( default ). If we use True here then the output will change by returning an array with all column data of the matching single row.

<?php 
$my_conn = new SQLite3('my_db.db'); 
$sql="SELECT  name,class,mark FROM student WHERE id=2 ";
$result=$my_conn->querySingle($sql,True); // array with all columns 
//print_r($result); // value of Name column only
echo $result['name'].','.$result['class'].','.$result['mark']; 
?>

Output

Max Ruin,Three,85

Here is single line code to get total number of records from a query.

$nume=$my_conn->querySingle('SELECT count(*) FROM student');

The varialble $nume will hold one integer value (35) saying the number of records as returned by the query.

Counting Number of rows using query() 🔝

Number of students in Class five.

<?php 
$my_conn = new SQLite3('my_db.db'); // database connection 
$sql="SELECT COUNT(*) AS no FROM student WHERE class='Five'";
$result=$my_conn->querySingle($sql); // Return single result of one column
echo "Number of students in class five is : ".$result; // Output is 3 
?>

Output

Number of students in class five is : 3

We can use GROUP BY query to get Number of records in each class.

<?php 
$my_conn = new SQLite3('my_db.db'); // database connection 
$sql="SELECT class, COUNT(*) as no FROM student GROUP BY class";
$result=$my_conn->query($sql); // Return array with two columns 
while($row=$result->fetchArray()){
echo "Number of students in class ".$row['class']. " is : ".$row['no'];
echo "<br>";  // Line break 
}
?>

Output

Number of students in class Eight is : 1
Number of students in class Five is : 3
Number of students in class Four is : 9
Number of students in class Nine is : 2
Number of students in class Seven is : 10
Number of students in class Six is : 7
Number of students in class Three is : 3

We can use aggregate functions to get sum , average , Maximum, Minimum of marks against each class by using GROUP BY query.

<?php 
$my_conn = new SQLite3('my_db.db'); // database connection 
$sql="SELECT class, count(*) no, AVG(mark),MAX(mark),MIN(mark),SUM(mark)
 FROM student GROUP BY class ORDER BY no DESC";
$result=$my_conn->query($sql); // Return array with two columns 
while($row=$result->fetchArray()){
echo $row['class']. ": Total Students: ".$row['no']. " ,Highest Mark : ".$row['MAX(mark)']
." , Minimum Mark : ".$row['MIN(mark)'].", Average : ".round($row['AVG(mark)'],1)
." , Total Mark " . $row['SUM(mark)'];
echo "<br>";  // Line break 
}
?>

Output

Seven: Total Students: 10 ,Highest Mark : 90 , Minimum Mark : 55, Average : 77.6 , Total Mark 776
Four: Total Students: 9 ,Highest Mark : 88 , Minimum Mark : 55, Average : 70.9 , Total Mark 638
Six: Total Students: 7 ,Highest Mark : 96 , Minimum Mark : 54, Average : 82.6 , Total Mark 578
Three: Total Students: 3 ,Highest Mark : 85 , Minimum Mark : 55, Average : 73.7 , Total Mark 221
Five: Total Students: 3 ,Highest Mark : 85 , Minimum Mark : 75, Average : 79.3 , Total Mark 238
Nine: Total Students: 2 ,Highest Mark : 65 , Minimum Mark : 18, Average : 41.5 , Total Mark 83
Eight: Total Students: 1 ,Highest Mark : 79 , Minimum Mark : 79, Average : 79 , Total Mark 79

Using ORDER BY with query() 🔝

We can list rows based on highest mark to lowest mark by using ORDER BY DESC. Similarly we can list from lowest to highest mark by using ASC ( default ) .
We used LIMIT to get only 5 rows in the result object.

<?php 
$my_conn = new SQLite3('my_db.db'); // connect to database in same location
$sql="SELECT * FROM student ORDER BY mark DESC LIMIT 0,5 ";
$result=$my_conn->query($sql); // Query execution 
while ($row=$result->fetchArray()){
  print $row['name'] . ",". $row['class'] . ",". $row['mark'];
  echo "<br>";
} 
?>

Output

$sql="SELECT * FROM student ORDER BY mark LIMIT 0,5 ";

Kenn Rein,Six,96
Recky,Six,94
Binn Rott,Seven,90
Ronald,Six,89
Kty,Seven,88

To change the order use this SQL

$sql="SELECT * FROM student ORDER BY mark ASC LIMIT 0,5 ";

Using Subqueries with query() 🔝

Let us find out the details of the record having highest mark.

<?php 
$my_conn = new SQLite3('my_db.db'); 

$sql="SELECT * FROM `student` WHERE mark=(select max(mark) from student)";

$result=$my_conn->querySingle($sql,True); // Return array with all columns 
echo $result['id'].','.$result['name'].','.$result['class'].','.$result['mark']; 
?>

Output

33,Kenn Rein,Six,96

Using JOIN with query() 🔝

We will create another table student_footbal and store two id of students who has selected in football team.

<?php
$my_conn = new SQLite3('my_db.db'); // create database in same location
$sql="CREATE TABLE IF NOT EXISTS `student_football` (
  `b_id` integer primary key) "; 
$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{
	 $sql="INSERT INTO `student_football` (`b_id`) VALUES(5),(10)";
	 $results = $my_conn->query($sql);
	echo 'Number of rows inserted: ', $my_conn->changes();
}
?>

Output

Number of rows inserted: 2

Now we will use LEFT join to list students who are selected in Football team.

<?php
$my_conn = new SQLite3('my_db.db'); // create database in same location
$sql="SELECT * FROM `student` LEFT JOIN student_football
 ON id=b_id WHERE b_id IS NOT NULL";

$result=$my_conn->query($sql); // Create table 
while ($row=$result->fetchArray()){
 print $row['id'] .",".$row['name'] . ",". $row['class'] . ",". $row['mark'];
 echo "<br>";
}
?>

Output

5,John Mike,Four,100
10,Big John,Four,100

Security using SQL in query() 🔝

Using prepared statements is a good way to prevent SQL injection attacks. SQL injection attacks occur when an attacker is able to insert malicious SQL code into a query, which can allow them to access sensitive data or even take control of the database.

Prepared statements work by separating the SQL query from the data that is being used in the query. The data is then "bound" to the query using a placeholder. This prevents the attacker from being able to insert malicious code into the query.
Here we are not using query()

<?php 
$my_conn = new SQLite3('my_db.db'); 

//$id=$_GET['id']; // id value from query string
$id=2;

$sql='SELECT * FROM student WHERE id=:id';
$stmt = $my_conn->prepare($sql);
$stmt->bindValue(':id', $id, SQLITE3_INTEGER);
$result = $stmt->execute();

//var_dump($result->fetchArray());
$row=$result->fetchArray();
echo $row['name'].','.$row['class'].','.$row['mark'];
?>

query() and error handling 🔝

Use different SQL to check the error message.

<?php 
$my_conn = new SQLite3('my_db.db'); 

//$sql="SELECT * FROM student3 WHERE class='Five'";
$sql="SELECT * FROM student WHERE class6='Five'";
//$sql="SELECT * FROM student WHERE class='Five'";
$result=$my_conn->query($sql); // 

if($result==FALSE){ 
 echo "Error Message : ".$my_conn->lastErrorMsg();
 echo "<BR>Error Code : ".$my_conn->lastErrorCode();
 }else{
 while ($row=$result->fetchArray()) {
 echo $row['name'].",".$row['class'].",".$row['mark'];
 echo "<br>";
}
}
?>

Output

Error Message : no such column: class6
Error Code : 1

Using try catch

<?php 
$my_conn = new SQLite3('my_db.db'); 
$my_conn->enableExceptions(true); 
try {
  $my_conn->query('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

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 🔝

How do I connect to a SQLite database using PHP SQLite3 queries()?
How do I create a table in a SQLite database using PHP SQLite3 queries()?
How do I insert data into a SQLite database using PHP SQLite3 queries()?
How do I select data from a SQLite database using PHP SQLite3 queries()?
How do I update data in a SQLite database using PHP SQLite3 queries()?
How do I delete data from a SQLite database using PHP SQLite3 queries()?
How can I use prepared statements with PHP SQLite3 queries()?
How can I handle errors with PHP SQLite3 queries()?
What are the best practices for writing PHP SQLite3 queries()?
How can I improve the performance of my PHP SQLite3 queries()?
How can I use PHP SQLite3 queries() to create a view?
How can I use PHP SQLite3 queries() to create a trigger?
How can I use PHP SQLite3 queries() to create a stored procedure?
How can I use PHP SQLite3 queries() to import data from a CSV file?
How can I use PHP SQLite3 queries() to export data to a CSV file?
How can I use PHP SQLite3 queries() to create a full-text index?
How can I use PHP SQLite3 queries() to perform a full-text search?
How can I use PHP SQLite3 queries() to create a virtual table?
How can I use PHP SQLite3 queries() to use window functions?
How can I use PHP SQLite3 queries() to create a recursive query?

SQLite SQLite3 & PHP

Subscribe to our YouTube Channel here

SQLite3 query(): Getting result object by executing SQL

PHP Script using SELECT Query. 🔝

Executing multiple SQL statements using query() 🔝

Crate Table using query() 🔝

Getting single row result 🔝

Counting Number of rows using query() 🔝

Using ORDER BY with query() 🔝

Using Subqueries with query() 🔝

Using JOIN with query() 🔝

Security using SQL in query() 🔝

query() and error handling 🔝

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

Questions 🔝

Subscribe