Chapter 10: Script 10#5 Making Sortable Displays

[guvna]

Script 10#4 works for pagination, in my case 6 pages.

 

Script 10#5 sort on First Name and Last Name work on the initial page display after selected column heading but not for selecting pages after the sort selection.

 

My url shows view_users_3.php as I save each iteration of the scripts to review.

 

Viewing my page then selecting sort by last name shows:

url = view_users_3.php?sort=ln

The first page of results displays correctly by last name.

 

selecting the page 2:

url = view_users_3.php?s=10&p=6&sortln

The second page of results is showing user_id's 10 to 19 so has not sorted by last_name.

 

The same situation occurs when I use First Name and Date Registered.

 

I rechecked the code and noted lines 68-70 with the href tags (also from forum Sep 2013) not highlighted but cannot find any other difference with my code.

 

My code:

 

<?php # Chapter 10: Script 10.5: view_users_3.php
        //This script retrievs all the records from the users table.
        //This 10.5 version allows results to be sorted in different ways.
        
    $page_title = 'View the Current Users';
    include ('../includes/header.html');
        echo '<h1>Registered Users</h1>'; // Page header
        
        require_once ('../includes/mysqli_connect.php'); // Connect to the db.    
        
        // Number of records to show per page.
        $display = 10;
        
        // Determine how many pages there are ...
        if (isset($_GET['p']) && is_numeric ($_GET['p'])) { // Already been determined.        
            $pages = $_GET['p'];
        } else { // Need to determine.
            // Count the number of records.
                $q = "SELECT COUNT(user_id) FROM users";
                $r = @mysqli_query ($dbc, $q);
                $row = @mysqli_fetch_array ($r, MYSQLI_NUM);
                $records = $row[0];
                // Calcualte the number of pages...
                if ($records > $display) { // More than 1 page.
                    $pages = ceil ($records/$display);
                } else {
                      $pages = 1;
                }
        } // End of p IF.
        
        // Determine where in the database to start returning results...
        if (isset($_GET['s']) && is_numeric ($_GET['s'])) {
              $start = $_GET['s'];
        } else {
              $start = 0;
        }
        
        // Determine the sort...
        // Default is by registration date.
        $sort = (isset($_GET['sort'])) ?
        $_GET['sort'] : 'rd';
        
        // Determine the sorting order.
        switch ($sort) {
            case 'ln':
                $order_by = 'last_name ASC';
                    break;
            case 'fn':
                $order_by = 'first_name ASC';
                    break;
            case 'rd':
                $order_by = 'registration_date ASC';
                    break;
            default:
                $order_by = 'registration_date ASC';
                    $sort = 'rd';
                    break;
        }
        
        // Define the query:
        $q = "SELECT last_name, first_name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY $order_by LIMIT $start, $display";
        $r = @mysqli_query ($dbc, $q); // run the query.
        
        // Table header.
        echo '<table align="center" cellspacing="3" cellpadding="3" width="75%">
                <tr>
                        <td align="left"><b>Edit</b></td>
                        <td align="left"><b>Delete</b></td>
                        <td align="left"><b><a href="view_users_3.php?sort=ln">Last Name</a></b></td>
                        <td align="left"><b><a href="view_users_3.php?sort=fn">First Name</a></b></td>
                        <td align="left"><b><a href="view_users_3.php?sort=rd">Date Registered</a></b></td>
                </tr>    
                ';
              // Fetch and print all the records.
                $bg = '#eeeeee'; // Set the initial background color.
                while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
                  $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
                        echo '<tr bgcolor="' . $bg . '">
                            <td align="left"><a href="edit_user.php?id=' . $row['user_id'] . '">Edit</a></td>
                                <td align="left"><a href="delete_user.php?id=' . $row['user_id'] . '">Delete</a></td>
                                <td align="left">' . $row['last_name'] . '</td>
                                <td align="left">' . $row['first_name'] . '</td>
                                <td align="left">' . $row['dr'] . '</td>
                        </tr>
                        ';
                } // End of WHILE loop.
                
    echo '</table>'; //Close the table.
        mysqli_free_result ($r); // Free up the resources.
        mysqli_close($dbc); // Close the database connection.
        
    // Make the links to other pages, if necessary.
        if ($pages > 1) {
              
                echo '<br /><p>'; // Add some spacing and start a paragraph.
                $current_page = ($start/$display) + 1; // Determine what page the script is on.
                
                // If it's not the first page, make a previous link:
                if ($current_page != 1) {
                      echo '<a href="view_users_3.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '">Previous</a> ';
                }
                
                // Make all the numbered pages.
                for ($i = 1; $i <= $pages; $i++) {
                      if ($i != $current_page) {
                              echo '<a href="view_users_3.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort' . $sort . '">' . $i . '</a> ';
                        } else {
                              echo $i . ' ';
                        }
                } // End of FOR loop.
                
                // If it's not the last page, make a Next button.
                if ($current_page != $pages) {
                      echo '<a href="view_users_3.php?s=' . ($start + $display) . '&p=' . $pages . '&sort' . $sort . '">Next</a>';
                }
                
                echo '</p>'; // Close the paragraph.
                
        } // End of links section.
        
        include ('../includes/footer.html');
?>

[Larry]

You have a typo:

url = view_users_3.php?s=10&p=6&sortln

Needs to be 

url = view_users_3.php?s=10&p=6&sortln

Look at the two instances where you have  

'&sort' . $sort

 without the =.

[guvna]

eureka. I was missing '=' in two instances. Cheers.