Jacques Posted April 5, 2018 Share Posted April 5, 2018 Hello all, I am busy with a complete user management script and have the following questions: Is it good practice to combine the "view users" and "search users" scripts in the same PHP file? How do I amend the "SELECT COUNT(user_id) FROM users" query for pagination purposes to accommodate the search functionality? In its current form it continues to displays ALL the pagination links after a search query. Is there a more efficient way to formulate the following search query: "SELECT u.user_id, u.username, u.type, c.country, u.email, u.status, DATE_FORMAT($date_created, '%Y-%m-%d %H:%i:%s') AS dc FROM users AS u INNER JOIN countries AS c USING (country_id) WHERE lang_id = {$_SESSION['lid']} AND u.username LIKE '%" . $terms[0] . "%' OR u.type LIKE '%" . $terms[0] . "%' OR c.country LIKE '%" . $terms[0] . "%' OR u.email LIKE '%" . $terms[0] . "%' OR u.status LIKE '%" . $terms[0] . "%' OR date_created LIKE '%" . $terms[0] . "%' ORDER BY $order_by LIMIT $start, $display" I have searched online but could not find any workable solutions. Any help will as always be much appreciated. Link to comment Share on other sites More sharing options...
Larry Posted April 6, 2018 Share Posted April 6, 2018 > Is it good practice to combine the "view users" and "search users" scripts in the same PHP file? Um, I think it's a fine thing to do. But it could be complex, as you're finding. > How do I amend the "SELECT COUNT(user_id) FROM users" query for pagination purposes to accommodate the search functionality? In its current form it continues to displays ALL the pagination links after a search query. You need to change the count query so that it matches the search query, so "SELECT COUNT(user_id) FROM USERS WHERE this AND that AND whatever". > Is there a more efficient way to formulate the following search query: Undoubtedly, yes. I would start by removing the things that no one is likely to search on, like the status or date_created. Probably also type and country. I would envision this as a simple user search, on just the username and email address. Then you could offer a more advanced search that factors in those other things, if need be. Link to comment Share on other sites More sharing options...
Jacques Posted April 23, 2018 Author Share Posted April 23, 2018 Hi Larry, Thank you very much for your unequivocal answers. I have implemented your suggestions successfully! I unfortunately have the following issue with the pagination that I can't seem to resolve. I am using Bootstrap 4. The pagination displays correctly when the page is initially loaded, with page 1 being the active page, but when the sequential page numbers are clicked, they displays in sequence but outside of the Bootstrap 4 <li> tag. I include my code below. Any suggestions to correct the error will be much appreciated. Regards. // Add pagination if necessary. if ($pages > 1) { echo '<nav> <ul class="pagination pagination-sm"> <li class="page-item active"> <a class="page-link" href="#"><span class="sr-only">'; $current_page = ($start/$display) + 1; echo '</span>'; // If it's not the first page, make a Previous link: if ($current_page != 1) { echo '</a></li> <li class="page-item"> <a class="page-link" href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '" tabindex="-1">«</a> </li>'; } // Make all the numbered pages: for ($i = 1; $i <= $pages; $i++) { if ($i != $current_page) { echo '<li class="page-item"><a class="page-link" href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a> </li>'; } else { echo $i . ' '; } } // End of FOR loop. // If it's not the last page, make a Next link: if ($current_page != $pages) { echo '<li class="page-item"><a class="page-link" href="view_users.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">»</a>'; } echo '</li> </ul> </nav>'; // Close pagination. } // End of links section. Link to comment Share on other sites More sharing options...
Larry Posted April 25, 2018 Share Posted April 25, 2018 My guess is the problem is because that last closing LI should be within the if ($current_page != $pages) { block. Link to comment Share on other sites More sharing options...
Jacques Posted April 25, 2018 Author Share Posted April 25, 2018 Hi Larry, Thank you very much for your prompt response. I tried your suggestion without any luck, so I redid the pagination from scratch for the umpteenth time and I finally found my error! I included the active <li class> on line 485 instead of line 505. A really silly error! I include the correct code below. Kind regards. // Add pagination if necessary: if ($pages > 1) { echo '<nav aria-label="page navigation"> <ul class="pagination pagination-sm">'; $current_page = ($start/$display) + 1; // If it's not the first page, make a Previous button: if ($current_page != 1) { echo '<li class="page-item"> <a class="page-link" href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '" aria-label="Previous">«</a></li>'; } // Make all the numbered pages: for ($i = 1; $i <= $pages; $i++) { if ($i != $current_page) { echo '<li class="page-item"><a class="page-link" href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a></li>'; } else { echo '<li class="page-item active"><a class="page-link" href="#">' . $i . '</a></li>'; } } // End of FOR loop. // If it's not the last page, make a Next button: if ($current_page != $pages) { echo '<li class="page-item"><a class="page-link" href="view_users.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '" aria-label="Next">»</a></li>'; } echo '</ul> </nav>'; } // End of pagination section. Link to comment Share on other sites More sharing options...
Larry Posted April 25, 2018 Share Posted April 25, 2018 Ah, that would do it. Kudos for figuring it out and thanks for sharing the solution! Link to comment Share on other sites More sharing options...
Recommended Posts