SQLite3 query(): Getting result object by executing SQL


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.

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.

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
Read more on different type of JOIN queries here.

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 🔝



SQLite SQLite3 & PHP
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Post your comments , suggestion , error , requirements etc here





    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