PHP SQLite limit query to get records from different start position with number of 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.

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

SQLite Paging

Paging of records in SQLite
Paging in SQLite allows us to efficiently display large datasets by splitting the result set into smaller chunks, typically using a LIMIT and OFFSET clause in our query. This technique reduces memory usage and speeds up performance by fetching only a subset of records at a time, which is ideal for applications like data tables or reports.
For example, we can retrieve 10 records starting from a specific offset, enabling seamless navigation between different "pages" of results in our application.
SQlite Paging of records

Podcast on SQLite database management using PHP PDO

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

DISTINCT : Getting unique records

PHP SQLite PDO Functions

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

PHP SQLite LIMIT Query

USING limit query

Using Parameters

SQLite Paging

Subhendu Mohapatra