PHP SQLite like query to collect matching records using pdo

We will create the database and connection object.

$my_conn = new PDO('sqlite:my_student.sqlite3');

We will use $my_conn to execute our query.

By using LIKE with WHERE we will collect matching records .

All records where name column ending with John.

<?php 
  // Create (connect to) SQLite database in file
  $my_conn = new PDO('sqlite:my_student.sqlite3');
  // Set errormode to exceptions
  $my_conn->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
	$sql="SELECT * FROM student WHERE name like '%John'";
  
  echo "<table>";
   foreach ($my_conn->query($sql) as $row) {
	echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
   }
  echo "</table>";
	
 $my_conn = null;
?>

Output ( name column ending with John )

6	Alex John
10	Big John
21	Babby John

name column starting with Bi.

$sql="SELECT * FROM student WHERE name like 'Bi%'";
  
  echo "<table>";
   foreach ($my_conn->query($sql) as $row) {
	echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
   }
  echo "</table>";

Output

10	Big John
14	Bigy
27	Big Nose
32	Binn Rott

name column having 'Ro' at any position

  $sql="SELECT * FROM student WHERE name like '%Ro%'";
  
  echo "<table>";
   foreach ($my_conn->query($sql) as $row) {
	echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
   }
  echo "</table>";

Output

7	My John Rob
11	Ronald
15	Tade Row
23	Herod
28	Rojj Base
32	Binn Rott
35	Rows Noump

Using Prameters in query.

While using parameters from external sources in our query , to prevent any injection we must use parameterized query .

$val1='%ig%';
$val2='%Rot%';
$sql="SELECT * FROM student WHERE name like :val1 or name like :val2";
 $step = $my_conn->prepare($sql);
	$step->bindParam(':val1', $val1,PDO::PARAM_STR,10);
	$step->bindParam(':val2', $val2,PDO::PARAM_STR,10);
	$step->execute();
	$step = $step->fetchAll();
	echo "<table>";
	foreach ($step as $row) {
		echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
	}
	echo "</table>";

Output

10	Big John
14	Bigy
27	Big Nose
32	Binn Rott

Here is a summary of string matching using Like with %

`'%John'`	Matches string ending with John
`'John%'`	Matches string starting with John
`'%John%'`	Matches string anywhere with John
`'j%n'`	Matches string starting with j and ending with n

We can change the query part now to get data by filtering using NOT, AND , OR

$val1='%Jo%';
$val2='%Ro%';
$sql="SELECT * FROM student WHERE name like :val1 and name like :val2";

Output

7	My John Rob

Using NOT

$sql="SELECT * FROM student WHERE name like :val1 and name NOT like :val2";

Output


1	John Deo
5	John Mike
6	Alex John
10	Big John
21	Babby John

Using underscore to match single char with LIKE

We can match any char at one position by using underscore _ . Multiple underscores can be used to match more chars.

Under the sex column we have data as male or female. We can get all records where sex column first two chars are fe. This query will return all records having data as female. ( All male data is excluded as they don't have fe as first two chars )

$val1='fe____';
$sql="SELECT * FROM student WHERE sex like :val1";
$step = $my_conn->prepare($sql);
$step->bindParam(':val1', $val1,PDO::PARAM_STR,10);
$step->execute();
$step = $step->fetchAll();
echo "<table>";
foreach ($step as $row) {
echo"<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[sex]</td></tr>";
}
echo "</table>";

Using mark column
We can get records having marks in eighties. In mark column we can get records where first digit is 9, here we are excluding mark equal to or above 90 and marks less than 80.

$val1='8_';
$sql="SELECT * FROM student WHERE mark like :val1";
 $step = $my_conn->prepare($sql);
$step->bindParam(':val1', $val1,PDO::PARAM_STR,10);
$step->execute();
$step = $step->fetchAll();
echo "<table>";
foreach ($step as $row) {
echo"<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[mark]</td></tr>";
}
echo "</table>";

Example : we want to collect all the account numbers ending with 074 in a five digit account number field. Here is the query for this.

SELECT * FROM account_master WHERE acc_no LIKE '__074'

Case sensitive matching

By default the value for PRAGMA case_sensitive_like is OFF , so our LIKE operator ignore case for ASCII characters. We can change this value and make it case sensitive.

We can remove the line $my_conn->query('PRAGMA case_sensitive_like = ON'); or set the value to OFF to make the search case insensitive.

$val1='%Ro%';
$sql="SELECT * FROM student WHERE name like :val1";
// Case sensitive search by setting next line to ON
$my_conn->query('PRAGMA case_sensitive_like = ON');
 $step = $my_conn->prepare($sql);
	$step->bindParam(':val1', $val1,PDO::PARAM_STR,10);
	$step->execute();
	$step = $step->fetchAll();
	echo "<table>";
	foreach ($step as $row) {
		echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
	}
	echo "</table>";

Download sample script for SQLite with instructions on how to use.

Check PHP pdo support for SQLite using phpinfo()

PHP SQLite PDO Functions

Subscribe to our YouTube Channel here

PHP SQLite LIKE matching

Using Prameters in query.

Using underscore to match single char with LIKE

Case sensitive matching

Subscribe