PHP SQLite distinct query to get unique data from a column and using as drop down options using pdo

Return unique values with DISTINCT

DISTINCT removes duplicate result rows for the selected columns.

<?php
$sql = 'SELECT DISTINCT class FROM student ORDER BY class';
foreach ($db->query($sql) as $row) {
    echo htmlspecialchars($row['class'], ENT_QUOTES, 'UTF-8') . '
';
}
?>

Current example first; detailed examples below are retained for additional learning and comparison.

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 DISTINCT we can get unique classes from our student table .

<?php 
   $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 "<table>";
   foreach ($my_conn->query($sql) as $row) {
	echo "<tr ><td>$row[class]</td></tr>";
   }
  echo "</table>";
?>

Output

Four
Three
Five
Six
Seven
Nine
Eight

Using DISTINCT query we can collect unique classes and populate a drop down list box as options. User can select one of the option from the pull down list box.

$sql="SELECT DISTINCT(class) as class FROM student ";
  
  echo "<SELECT name=class>";
   foreach ($my_conn->query($sql) as $row) {
	echo "<option value='$row[class]'>$row[class]</option>";
   }
  echo "</SELECT>";

Output is here

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

LIMIT : Limiting number of records from starting position

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

Get DISTINCT Values from SQLite

Return unique values with DISTINCT

Subhendu Mohapatra