CODEWITHSUNDEEP
phpmysqlmysqli
JLA
JLA

Reputation: 100

where do I put ORDER BY in this code?

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.

Upvotes: 0

Views: 94

Answers (4)

Farnabaz
Farnabaz

Reputation: 4066

add order by before this $sql .= " LIMIT $start, " . NUMBER_PER_PAGE; 

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

Upvotes: 1

Peter Tirrell
Peter Tirrell

Reputation: 3003

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?

Upvotes: 2

Andrea_86
Andrea_86

Reputation: 527

What's the matter with your code? it return records result unordered or it generate error? the position of ORDER BY is correct so maybe there an error on the sql query

A little suggestion, the tag <center> is deprecated in html5, use css

Upvotes: 0

AlexanderFT
AlexanderFT

Reputation: 73

Try:

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

or:

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

Upvotes: -1

Related Questions