PHP SQLite display records using parameters to Query

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 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;
?>

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.

<?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";
	$step = $my_conn->prepare($sql);
	$step->bindParam(':class', $class,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>";
	

 $my_conn = null;
?>

Output is here

1	John Deo
4	Krish Star
5	John Mike
6	Alex John
10	Big John
15	Tade Row
16	Gimmy
21	Babby John
31	Marry Toeey

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

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 displaying records

DataList options from Student table

Subscribe