Jump to content
Larry Ullman's Book Forums

Recommended Posts

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

> 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

  • 3 weeks later...

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

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

 Share

×
×
  • Create New...