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.

Share this post


Link to post
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. 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

My guess is the problem is because that last closing LI should be within the if ($current_page != $pages) { block.

Share this post


Link to post
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.

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