Dynamic SQL ORDER BY from PHP

Click the Mark heading to switch between ascending and descending order. The live table below uses a strict two-value whitelist, so the requested direction can only become ASC or DESC.

SQL ORDER BY

SELECT name, mark
FROM student
WHERE class = 'Four'
ORDER BY mark DESC, name ASC;

The second sort column gives stable ordering when several students have the same mark.

Allow a user to choose ASC or DESC safely

SQL keywords and identifiers cannot be bound as PDO value parameters. Validate them against a small allow-list before placing them in the SQL text.

<?php
$direction = (isset($_GET['by']) && strtolower($_GET['by']) === 'desc') ? 'DESC' : 'ASC';

$query = "SELECT name, mark
          FROM student
          WHERE class = :class
          ORDER BY mark $direction, name ASC";

$stmt = $dbo->prepare($query);
$stmt->execute([':class' => 'Four']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

The older page used the removed mysql_query() / mysql_fetch_array() API. The modern example uses PDO and explicitly validates the only dynamic SQL keyword.

See also the PHP paging example.

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: