PHP SQLite Aggregate function MAX to get highest data of a column

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 MAX query we will display highest mark.

Highest mark across all classes from student table

<?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 max(mark) as m_mark FROM student ";

$result=$my_conn->query($sql);
$row = $result->fetch(PDO::FETCH_OBJ);
echo " Highest Mark : ".$row->m_mark;
?>

Output is here

Highest Mark : 96

We can get the details of the student who got maximum mark. Here we will use sub query to get the matching record.

$sql="SELECT * FROM `student` WHERE mark=(select max(mark) from student)";
$result=$my_conn->query($sql);
$row = $result->fetch(PDO::FETCH_OBJ);
echo " Highest Mark : ".$row->mark;
echo " <br>ID  : ".$row->id;
echo " <br>Name  : ".$row->name;
echo " <br>Class  : ".$row->class;
echo " <br>Sex  : ".$row->sex;
?>

Output is here

Highest Mark : 96
ID : 33
Name : Kenn Rein
Class : Six
Sex : female

We can get the highest mark of all classes by using group by query.

$sql="SELECT class,max(mark) as m_mark FROM student group by class";

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

Output

Eight	79
Five	85
Four	88
Nine	65
Seven	90
Six	96
Three	85

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

MIN()

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 MAX query

Subhendu Mohapatra