PHP SQLite limit query to get records from different start position with number of records using pdo

SQLite

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.

Read more on limit query here.

USING limit query

LIMIT query takes two inputs, one is the starting position of the record and another is number of records. We will add these two values along with SELECT query using LIMIT.

<?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);
////////////////////	
echo "<br>LIMIT 0, 10 <br><br>";
$sql="SELECT * FROM student  LIMIT 0,10";

$step = $my_conn->prepare($sql);
	
$step->execute();
$step = $step->fetchAll();
echo "<table>";
foreach ($step as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td></tr>";
}
echo "</table>";
?>

Output

1	John Deo	Four
2	Max Ruin	Three
3	Arnold	Three
4	Krish Star	Four
5	John Mike	Four
6	Alex John	Four
7	My John Rob	Five
8	Asruid	Five
9	Tes Qry	Six
10	Big John	Four

Note that the first record is at 0^th position and 10 records are returned staring from 0^th position record.

Using Parameters

Always use parameterized query when the data is coming from unknown sources. This is required to prevent injection attack.

$val1=5;
$val2=10;
echo "<br>LIMIT 5, 10 <br><br>";
$sql="SELECT * FROM student  LIMIT :val1,:val2";

$step = $my_conn->prepare($sql);
	$step->bindParam(':val1', $val1,PDO::PARAM_INT,10);
	$step->bindParam(':val2', $val2,PDO::PARAM_INT,10);
	$step->execute();
	$step = $step->fetchAll();
	echo "<table>";
foreach ($step as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td></tr>";
}
echo "</table>";

Output is here

Output

LIMIT 5, 10

6	Alex John	Four
7	My John Rob	Five
8	Asruid	Five
9	Tes Qry	Six
10	Big John	Four
11	Ronald	Six
12	Recky	Six
13	Kty	Seven
14	Bigy	Seven
15	Tade Row	Four

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 LIMIT Query

USING limit query

Using Parameters

Subscribe