Jump to content
Larry Ullman's Book Forums
guvna

Chapter 10: Script 10#5 Making Sortable Displays

Recommended Posts

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');
?>

Share this post


Link to post
Share on other sites

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 =.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...