我在哪里将ORDER BY放在这段代码中？

Question

This is a simple problem but I can't seem to figure it out out. So I have this code that parses a search and paginates the results. Works well.

<?php
require_once("models/config.php");
if (!securePage($_SERVER['PHP_SELF'])){die();}
require_once("models/header.php");
define("NUMBER_PER_PAGE", 5); //number of records per page of the search results

function pagination($current_page_number, $total_records_found, $query_string = null)
{
    $page = 1;

    echo "Page: ";

    for ($total_pages = ($total_records_found/NUMBER_PER_PAGE); $total_pages > 0;     $total_pages--)
{
    if ($page != $current_page_number)
        echo "<a href=\"?page=$page" . (($query_string) ? "&$query_string" : "") .     "\">";

    echo "$page ";

    if ($page != $current_page_number)
        echo "</a>";

    $page++;
}
}

$page = ($_GET['page']) ? $_GET['page'] : 1;
$start = ($page-1) * NUMBER_PER_PAGE;


$personid = ($_POST['personid']) ? $_POST['personid'] : $_GET['personid'];
$firstname = ($_POST['firstname']) ? $_POST['firstname'] : $_GET['firstname'];
$surname = ($_POST['surname']) ? $_POST['surname'] : $_GET['surname'];

$sql = "SELECT * FROM persons WHERE 1=1";

if ($personid)
$sql .= " AND personid='" . mysqli_real_escape_string($mysqli,$personid) . "'";

if ($firstname)
$sql .= " AND firstname='" . mysqli_real_escape_string($mysqli,$firstname) . "'";

if ($surname)
$sql .= " AND surname='" . mysqli_real_escape_string($mysqli,$surname) . "'";


$total_records = mysqli_num_rows(mysqli_query($mysqli,$sql));


$sql .= " LIMIT $start, " . NUMBER_PER_PAGE;

pagination($page, $total_records,    "personid=$personid&firstname=$firstname&surname=$surname");



 $loop = mysqli_query($mysqli,$sql)
 or die ('cannot run the query because: ' . mysqli_error($mysqli,i));

 while ($record = mysqli_fetch_assoc($loop))
 echo "<br/>{$record['personid']}) " . stripslashes($record['firstname']) . " - {$record['surname']}";


 echo "<center>" . number_format($total_records) . " search results found</center>";

pagination($page, $total_records,     "personid=$personid&firstname=$firstname&surname=$surname");

?>

However, I want my search results to be sorted by surname. So I amend one line of code to say this:

$sql = "SELECT * FROM persons WHERE 1=1 ORDER BY surname";

Then my when I do a search for a name I get all the records, sorted perfectly by surname. I've tried putting ORDER BY elsewhere but then the search does not work.

WHere does the ORDER BY condition have to go here in order to get properly filtered results sorted by surname?

Thanks for your help in advance.

Answer 1

Are you looking for something like this?

$sql .= " ORDER BY surname";
$sql .= " LIMIT $start, " . NUMBER_PER_PAGE;

Is the problem just that you need to append the "ORDER BY" statement at the end of your SQL query you're building?