PHP/MySQL - Pagination with variety of ORDER BY clauses

I have the following queries: the first as a count for pagination, the second to fill the resulting table with data. They both worked very well, but I want to add Ordering functionality to the table also, and have made some ham-fisted efforts in that direction.

Essentially, I have broken up the MySQL query into portions stored in variables, which may or may not be assigned based on the wishes of the user as expressed by the submitting and GETting of forms. I'm only at a beginner level, and this is what made logical sense to me. It is probably not the correct method!

The data is ordered by default as it is entered in the database. The table is filled 15 rows at a time. Ideally, the user could then choose to order the animal's data in another way - by age, or colour, for example. The choice is made via submitting a form to the page itself, the query is re-submitted, and the result is outputted in 15-row pages again.

So far, here is my code:

PAGINATION:

$genderid = $_GET['groupid'];
$sortby = $_GET['sortby'];

##################

if (isset($_GET['pageno'])) { $pageno = $_GET['pageno']; }
    else { $pageno = 1; }

    $initialcount = "SELECT count(*) 
                FROM profiles
                WHERE ProfileName != 'Unknown'";

        if ($genderid > 0) {
            $genderquery = " AND ProfileGenderID = $genderid";
            }                 
        if ($sortby == 'age') {
            $orderby = " ORDER BY ProfileYearOfBirth ASC";
            } 
        elseif ($sortby == 'colour') {
            $orderby = " ORDER BY ProfileAdultColourID ASC";
            }

    $finalcount = ($initialcount . ' ' . $genderquery . ' ' . $orderby);

    $result = mysql_query($finalcount) or trigger_error();
    $query_data = mysql_fetch_row($result);
    $numrows = $query_data[0];

    $rows_per_page = 15;
    $lastpage = ceil($numrows/$rows_per_page);

    $pageno = (int)$pageno;
    if ($pageno > $lastpage) {
        $pageno = $lastpage;
    }
    if ($pageno < 1) {
        $pageno = 1;
    }

    $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;


    if ($pageno == 1) 
        {
            echo '<p class="pagination">';
            echo '<span class="first"><< First</span><span class="previous">< Previous</span>';
        } 
    else    
        {
            echo '<p class="pagination">';
            echo "<span class='first'><a href='{$_SERVER['PHP_SELF']}?pageno=1'><< First</a></span>";
            $prevpage = $pageno-1;
            echo "<span id='class'><a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>< Previous</a></span>";
        }

    echo '<span class="pagination-nav">' . "Page $pageno of $lastpage" . '</span>';

    if ($pageno == $lastpage) 
        {
            echo '<span class="next">Next ></span><span class="last">Last >></span>';
            echo '</p>';
        } 
    else 
        {
            $nextpage = $pageno+1;
            echo "<span class='next'><a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>Next ></a></span>";
            echo "<span class='last'><a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>Last >></a></span>";
            echo '</p>';
        } 

OUTPUT:

     <table class="admin-display">
    <thead>
        <tr>
            <th>Name:</th>
            <th>Age:
                <form class="sorting-form" method="GET" action="">
                <input type="hidden" name="sortby" value="age" />
                <input type="hidden" name="groupid" value="<?php echo $genderid; ?>" />
                <input type="submit" value="&#8657;" class="sort-submit" />
                </form>
            </th>
            <th&开发者_JAVA技巧gt;Colour:
                <form class="sorting-form" method="GET" action="">
                <input type="hidden" name="sortby" value="colour" />
                <input type="hidden" name="groupid" value="<?php echo $genderid; ?>" />
                <input type="submit" value="&#8657;" class="sort-submit" />
                </form>
            </th>
            <th>Gender:</th>
            <th>Owner:</th>
            <th>Breeder:</th>
        </tr>
    </thead>    

<?php

    $initialquery = "SELECT ProfileID, ProfileOwnerID, ProfileBreederID, 
            ProfileGenderID, ProfileAdultColourID, ProfileColourModifierID, ProfileYearOfBirth, 
            ProfileYearOfDeath, ProfileLocalRegNumber, ProfileName,
            owner.ContactFirstName AS owner_fname, owner.ContactLastName AS owner_lname,
            breeder.ContactFirstName AS breeder_fname, breeder.ContactLastName AS breeder_lname,
            BreedGender, BreedColour, BreedColourModifier
            FROM profiles
                        LEFT JOIN contacts AS owner
                            ON ProfileOwnerID = owner.ContactID
                        LEFT JOIN contacts AS breeder
                            ON ProfileBreederID = breeder.ContactID
                LEFT JOIN prm_breedgender
                            ON ProfileGenderID = prm_breedgender.BreedGenderID
                        LEFT JOIN prm_breedcolour
                            ON ProfileAdultColourID = prm_breedcolour.BreedColourID
                        LEFT JOIN prm_breedcolourmodifier
                            ON ProfileColourModifierID = prm_breedcolourmodifier.BreedColourModifierID
                      WHERE ProfileName != 'Unknown'";

$finalquery = ($initialquery . ' ' . $genderquery . ' ' . $orderby . ' ' . $limit);

$result = mysql_query($finalquery) or trigger_error("SQL", E_USER_ERROR);
//process results

The data still outputs correctly (initially), and the user can re-submit the query successfully also. The problem arises when - after ordering by anything other than default - I click to go forward a page. Once the page changes, the ordering returns to default. I don't know how to maintain that ORDER BY clause beyond the initial re-submission.

This is as far as I've gotten before I start breaking the code and things begin to get hairy. I need someone to point out the glaring error! Much thanks in advance :)

This sounds like it should be handled in an AJAX call. Theoretically, you can persist your initial and incremented state throughout the session (using the session variable, a hidden form field, or even a cookie) but I think you'll be happier with asynchronously grabbing X records at a time and keeping the arguments on the client side until you want to get the next page.

Per poster's inquiry, storing the necessary values in the session would look something like this:

session_start();

$startat = intval($_REQUEST["startat"]);
$numrows= intval($_REQUEST["numrows"]);

if (isset($_SESSION['base_query'])){
    $query = $_SESSION['base_query'];
} else {
    $query = <query construction minus limit clause>;
    $_SESSION['base_query'] = $query;
}


$query .= " LIMIT $startat, $numrows";

<query db/ send results>

Does that give you the idea?

maybe you can get around this problem by using http://www.datatables.net/ ? it's a client side ajax solution that orders results for you. And you avoid reloading the whole page each time.

Ok, I worked on what I had for another few hours, and came up with a solution. It doesn't use AJAX, or SESSIONS, and is undoubtably horrible to behold, but it suits the skill level I'm at.

First off, I changed the pagination script to give a distinct value to the offset parameter:

$title = intval($_GET['groupid']);
$genderid = intval($_GET['groupid']);
$sortby = $_GET['sortby'];

##################################

    $initialcount = "SELECT count(*) FROM profiles WHERE ProfileName != 'Unknown'";

    if($genderid > 0) {
        $where = "AND ProfileGenderID = $genderid";
        $finalcount = ($initialcount . ' ' . $where);
        }
    else { $finalcount = $initialcount;  }

    $result = mysql_query($finalcount) or trigger_error("SQL", E_USER_ERROR);
    $r = mysql_fetch_row($result);
    $numrows = $r[0];

$rowsperpage = 15;
$totalpages = ceil($numrows / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   $currentpage = (int) $_GET['currentpage'];
} 
    else { $currentpage = 1; }

if ($currentpage > $totalpages) {
   $currentpage = $totalpages;
} 

    if ($currentpage < 1) 
       {  $currentpage = 1; } 

$offset = ($currentpage - 1) * $rowsperpage;

I also changed the pagination links to a) hold the value of the current gender (overall) sort, and the subsequent sort (age, colour); and b) to display numbered page links á la Google:

// range of num links to show
$range = 3;

##############

        $range = 3;

        echo '<p class="pagination">';
        if ($currentpage == 1) 
        {
            echo '<span class="first"><< First</span><span class="previous">< Previous</span>';
        } 
        if ($currentpage > 1) {
            echo "<span class='first'><a href='{$_SERVER['PHP_SELF']}?sortby=$sortby&groupid=$genderid&currentpage=1'><< First</a></span>";
            $prevpage = $currentpage - 1;
            echo "<span class='previous'><a href='{$_SERVER['PHP_SELF']}?sortby=$sortby&groupid=$genderid&currentpage=$prevpage'>< Previous</a></span>";
        }

        echo '<span class="pagination-nav">';

        for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
            if (($x > 0) && ($x <= $totalpages)) {
                    if ($x == $currentpage) {
                        echo " [<b>$x</b>] ";
                    } 
                    else {
                        echo " <a href='{$_SERVER['PHP_SELF']}?sortby=$sortby&groupid=$genderid&currentpage=$x'>$x</a> ";
                    }
            }
        }

        echo '</span>';

        if ($currentpage != $totalpages) {
               $nextpage = $currentpage + 1;
               echo "<span class='next'><a href='{$_SERVER['PHP_SELF']}?sortby=$sortby&groupid=$genderid&currentpage=$nextpage'>Next ></a></span>";
               echo "<span class='last'><a href='{$_SERVER['PHP_SELF']}?sortby=$sortby&groupid=$genderid&currentpage=$totalpages'>Last >></a></span>";
        }
        if ($currentpage == $totalpages) 
            {
                echo '<span class="next">Next ></span><span class="last">Last >></span>';
            } 

        echo '</p>';

And finally constructed the query:

$baseQuery = "SELECT ProfileID, ProfileOwnerID, ProfileBreederID, 
                ProfileGenderID, ProfileAdultColourID, ProfileColourModifierID, ProfileYearOfBirth, 
                ProfileYearOfDeath, ProfileLocalRegNumber, ProfileName,
                owner.ContactFirstName AS owner_fname, owner.ContactLastName AS owner_lname,
                breeder.ContactFirstName AS breeder_fname, breeder.ContactLastName AS breeder_lname,
                BreedGender, BreedColour, BreedColourModifier
                FROM profiles
                            LEFT JOIN contacts AS owner
                                ON ProfileOwnerID = owner.ContactID
                            LEFT JOIN contacts AS breeder
                                ON ProfileBreederID = breeder.ContactID
                    LEFT JOIN prm_breedgender
                                ON ProfileGenderID = prm_breedgender.BreedGenderID
                            LEFT JOIN prm_breedcolour
                                ON ProfileAdultColourID = prm_breedcolour.BreedColourID
                            LEFT JOIN prm_breedcolourmodifier
                                ON ProfileColourModifierID = prm_breedcolourmodifier.BreedColourModifierID
                          WHERE ProfileName != 'Unknown'";


    if($sortby == 'age') {
        $orderby = "ORDER BY ProfileYearOfBirth ASC";
        }
    elseif ($sortby == 'colour') {
        $orderby = "ORDER BY ProfileAdultColourID ASC";
        }

    $limitClause = "LIMIT $offset, $rowsperpage";

    $finalQuery = ($baseQuery . ' ' . $where . ' ' . $orderby . ' ' . $limitClause);

$result = mysql_query($finalQuery) or trigger_error("SQL", E_USER_ERROR);

Effective as a hammer hitting a cheesecake :) Much thanks to the contributors above, particularly for introducing me to INTVAL (and by progression, CASTING).