PHP SQLite display records using parameters to Query

Inserting and Displaying rows of data from SQLite database using PHP PDO object A3

By using WHERE condition we will display records of class 'Four'.

<?php 
   try {
     
    // 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);
 
    // prepare Query 
    //  $sql="select *  from student";  // All students
	$sql="SELECT * FROM student WHERE class='Four'";
		
    echo "<table>";
	foreach ($my_conn->query($sql) as $row) {
		echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
	}
	echo "</table>";
	}
  catch(PDOException $e) 
  {
    // Print PDOException message
    echo $e->getMessage();
  }    
 $my_conn = null;
?>

Using fetch(), fetchAll() and fetchColumn() with parameter query

PHP PDO fetch() fetchAll() & fetchColumn() with FETCH_ASSOC, FETCH_NUM, and More: A5

fetch(): Fetches one row at a time, useful for looping through results.
fetchAll(): Fetches all rows at once, useful when you need to process all data at once.
fetchColumn(): Fetches a single column from each row, useful when you only need data from one specific column.

In above code we have used foreach loop to get each row of records and displayed the same. We may have to collect the value of class from external source so it is better to use parameter query so we can Binds a parameter to a statement variable.

fetch()

1. PDO::FETCH_ASSOC

Fetches the results as an associative array, where the column names are the keys.

try {
	$my_conn = new PDO('sqlite:my_student.sqlite3');
    // Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
$class='Four'; // Parameter, value  can be changed.
	$sql="SELECT * FROM student WHERE class=:class LIMIT 0,3";
	$stmt = $my_conn->prepare($sql);
	$stmt->bindParam(':class', $class,PDO::PARAM_STR,10);
	$stmt->execute();
    // Fetch one row at a time
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Class: " . $row['class'] . "<br>";
    }
} // end of try 
catch(PDOException $e) 
  {
    // Print PDOException message
    echo $e->getMessage();
  }    
$my_conn = null;

Output is here

ID: 1, Name: John Deo, Class: Four
ID: 4, Name: Krish Star, Class: Four
ID: 5, Name: John Mike, Class: Four

2. PDO::FETCH_NUM

Fetches the results as a numeric array, where the column values are accessed by their index.

$sql = "SELECT * FROM student";
$stmt = $my_conn->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    echo "Name: " . $row[0] . ", Class: " . $row[1] . ", Mark: " . $row[2] . ", Gender: " . $row[3] . "
";
}

3. PDO::FETCH_BOTH

Fetches the results as both an associative and a numeric array. You can access the data by both column name and index.

$sql = "SELECT * FROM student";
$stmt = $my_conn->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_BOTH)) {
    echo "Name: " . $row['name'] . " / " . $row[0] . ", Class: " . $row['class'] . " / " . $row[1] . "
";
}

4. PDO::FETCH_OBJ

Fetches the results as an object, where the column names are the properties of the object.

$sql = "SELECT * FROM student";
$stmt = $my_conn->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "Name: " . $row->name . ", Class: " . $row->class . ", Mark: " . $row->mark . ", Gender: " . $row->gender . "
";
}

5. PDO::FETCH_LAZY

Combines the behavior of PDO::FETCH_BOTH, PDO::FETCH_OBJ, and PDO::FETCH_LAZY. It fetches the data as an object and an array (both associative and numeric), allowing flexible access.

$sql = "SELECT * FROM student";
$stmt = $my_conn->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_LAZY)) {
    echo "Name: " . $row->name . " / " . $row['name'] . " / " . $row[0] . ", Class: " . $row->class . " / " . $row['class'] . " / " . $row[1] . "
";
}

Summary

PDO::FETCH_ASSOC: Returns data as an associative array.
PDO::FETCH_NUM: Returns data as a numeric array.
PDO::FETCH_BOTH: Returns data as both an associative and numeric array.
PDO::FETCH_OBJ: Returns data as an object.
PDO::FETCH_LAZY: Returns data that can be accessed as an object, associative array, or numeric array.

fetchAll()

<?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);
	$class='Four'; // Parameter, value  can be changed.
	$sql="SELECT * FROM student WHERE class=:class";
	$stmt = $my_conn->prepare($sql);
	$stmt->bindParam(':class', $class,PDO::PARAM_STR,10);
	$stmt->execute();
	$stmt = $stmt->fetchAll();
	echo "<table>";
	foreach ($stmt as $row) {
		echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
	}
	echo "</table>";
	

 $my_conn = null;
?>

Output is here

ID: 1, Name: John Deo, Class: Four
ID: 4, Name: Krish Star, Class: Four
ID: 5, Name: John Mike, Class: Four

Other PDO constants with fetchAll()

$stmt=$stmt->fetchAll(PDO::FETCH_ASSOC); // default 
echo "<table>";
foreach ($stmt as $row){
 echo "<tr><td>$row[id]</td><td>$row[name]</td></tr>";   
}
echo "</table>";

PDO::FETCH_ASSOC: Fetches all rows as an array of associative arrays.
PDO::FETCH_NUM: Fetches all rows as an array of numeric arrays.
PDO::FETCH_BOTH: Fetches all rows as an array with both associative and numeric indexes.
PDO::FETCH_OBJ: Fetches all rows as an array of objects.
PDO::FETCH_COLUMN: Fetches a single column from all rows as an indexed array.

fetchColumn()

try {
	$my_conn = new PDO('sqlite:my_student.sqlite3');
    // Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
	$class='Four'; // Parameter, value  can be changed.
	$sql="SELECT name FROM student WHERE class=:class LIMIT 0,3";
	$stmt = $my_conn->prepare($sql);
	$stmt->bindParam(':class', $class,PDO::PARAM_STR,10);
	$stmt->execute();
while ($name = $stmt->fetchColumn()) {
        echo "Student Name: ".$name ."<br>";
    }
} // end of try 
catch(PDOException $e) 
  {
    // Print PDOException message
    echo $e->getMessage();
  }    
$my_conn = null;

Output

Student Name: John Deo
Student Name: Krish Star
Student Name: John Mike

Example : DataList options from Student table

Datalist provides a set of predefined options for user input fields. Here we are collecting the class names from the student table and using the same as options for the DataList.

The SQL uses DISTINCT command to find out unique classes from all the records.

<?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 DISTINCT(class) as class FROM student";
echo "<input name='my_class' list='class' />
	<datalist id='class'>";
	
foreach ($my_conn->query($sql) as $row) {
	echo "<option value=$row[class]>"; // adding options 
}

echo "</datalist>";
?>

DEMO is here, List of classes as Options

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

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

SQLite Connection Update 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: