Jump to content
Larry Ullman's Book Forums

Paginated And Sortable Search Results


Recommended Posts

Hi,
I've modified script 10.5 by adding a search form at the top.  The search correctly returns a paginated table of results with page numbers and a next link.  However, there are some problems that is beyond my knowledge of php.
 
The first is that I don't know how to prevent the undefined index error caused by the blank form fields when the next link or a page number link is clicked.  In other words, trying to view the paged results creates an undefined index error.   The second thing is that clicking the sort link returns all the database records, not just the search results. 
 
The code is posted.  Any help would be appreciated.

<?php
$page_title = 'Search';
include ('includes/header.html');
require("mysqli_connect_skn.php");

?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="GET">
<h1>Search the Database</h1>
<h3>Return (hopefully) Paginated search results with sortable columns</h3> 
<p>Form Field One: <input name="one" type="text" size="6" maxlength="6" value="<?php if(isset($_POST['one'])) echo $_POST['one']; ?>" /> </p>
<p>Form Field Two: <input name="five" type="text" size="5" maxlength="5" value="<?php if(isset($_POST['five'])) echo $_POST['five']; ?>" /> </p>
<p>Form Field Three: <input name="freetext" type="text" size="40" maxlength="40" value="<?php if(isset($_POST['freetext'])) echo $_POST['freetext']; ?>" /> </p>
<p>
<input type="submit" value="Search">
</p>
</form>


<?php #Sript 10.5 - view_users.php

//Add number of records to show per page as a variable.
	$one = mysqli_real_escape_string($dbc, trim($_GET['one']));
	$five = mysqli_real_escape_string($dbc, trim($_GET['five']));
	$freetext = mysqli_real_escape_string($dbc, trim($_GET['freetext']));
	
	$display = 10;

//Determine the total number pages.
if (isset($_GET['p']) && is_numeric($_GET['p'])) {
		// if the number of records is already determined.
	$pages = $_GET['p'];
	}
	else {
		// Count the number of records
		$q = "SELECT COUNT(acr_id) FROM acr WHERE one LIKE \"%$one%\" AND five LIKE \"$five%\" AND (six LIKE \"%$freetext%\" OR two LIKE \"%$freetext%\" OR four LIKE \"%$freetext%\" OR seven LIKE \"%$freetext%\" OR nine LIKE \"%$freetext%\" OR eight LIKE \"%$freetext%\")";
		
		$r = mysqli_query($dbc, $q);
		$row = mysqli_fetch_array($r, MYSQLI_NUM);
		$records = $row[0];
		//Calculate the number of pages
		if ($records > $display){
		$pages = ceil($records/$display);  //ceil — Round fractions up
		}
		else {
			$pages = 1;
			}
		}  //End of p IF.
		
		// Determine where to start returning results.
		if (isset($_GET['s']) && is_numeric($_GET['s'])) {
			$start = $_GET['s'];
			}
				else{
				$start = 0;
				}
		//Determine the default sort.
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'three';
		
	
		//Determine the sorting order.
		$one_sort = 'one';
		$seq_sort = 'two';
		$date_sort = 'three';
		$four_sort = 'four';
		$five_sort = 'five';
		$comp_sort = 'six';
		$seven_sort = 'seven';
		$eight_sort = 'eight';
		
		
		
		switch ($sort) {
			case 'one':
				$order_by = 'one ASC';
				$one_sort = 'rone';
				break;
			case 'rone':
				$order_by = 'one DESC';
				break;
				
			case 'two':
				$order_by = 'two ASC';
				$seq_sort = 'rtwo';
				break;
			case 'rtwo':
				$order_by = 'two DESC';
				break;
				
			case 'three':
				$order_by = 'three ASC';
				$date_sort = 'rthree';
				break;
			case 'rthree':
				$order_by = 'three DESC';
				break;	
				
			case 'four':
				$order_by = 'four ASC';
				$four_sort = 'rfour';
				break;
			case 'rfour':
				$order_by = 'four DESC';
				break;
				
			case 'five':
				$order_by = 'five ASC';
				$five_sort = 'rfive';
				break;
			case 'rfive':
				$order_by = 'five DESC';
				break;	
				
			case 'six':
				$order_by = 'six ASC';
				$comp_sort = 'rsix';
				break;
			case 'rsix':
				$order_by = 'six DESC';
				break;		
			
			case 'seven':
				$order_by = 'seven ASC';
				$seven_sort = 'rseven';
				break;
			case 'rseven':
				$order_by = 'seven DESC';
				break;
			
			case 'eight':
				$order_by = 'eight ASC';
				$eight_sort = 'reight';
				break;
			case 'reight':
				$order_by = 'eight DESC';
				break;
				
			}

			 
		
		//Define the query:
		$q = "SELECT * FROM acr WHERE one LIKE \"%$one%\" AND five LIKE \"$five%\" AND (six LIKE \"%$freetext%\" OR two LIKE \"%$freetext%\" OR four LIKE \"%$freetext%\" OR seven LIKE \"%$freetext%\" OR nine LIKE \"%$freetext%\"  OR eight LIKE \"%$freetext%\") ORDER BY $order_by LIMIT $start, $display";
		//Run the query
		$r = @mysqli_query($dbc, $q)
		or die("Error: ".mysqli_error($dbc));
		
		//Start the table header
		echo '<table align="center" cellspacing="15" cellpadding="2" width="100%">
		<tr>"There were  ' . $records . ' hits for your search '.$q . '. "
		<tr>
			<td align="left"><b>Details</b></td>
			<td align="left"><b><a href="data-search.php?sort='. $one_sort. '">One</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $two_sort .'">Two</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $three_sort.'">Three</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $four_sort .'">Four</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $five_sort .'">Five</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $six_sort .'">Six</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $seven_sort .'">Seven</a></b></td>
			<td align="left"><b><a href="data-search.php?sort='. $eight_sort .'">Eight</a></b></td>
						
			</tr>
		';

		
		//Fetch and print all the records.
		$bg = '#eeeeee';
		while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
		$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
		echo '
		<tr bgcolor="' .$bg . '">
		<td valign ="top" align="left"><a href="edit.php?id=' . $row['acr_id'] . '">Details</a></td>
		<td valign ="top" align="left">'.$row['one'] .'</td>
		<td valign ="top" align="left">'.$row['two'] .'</td>
		<td valign ="top" align="left">'.$row['three'] .'</td>
		<td valign ="top" align="left">'.$row['four'] .'</td>
		<td valign ="top" align="left">'.$row['five'] .'</td>
		<td valign ="top" align="left">'.$row['six'] .'</td>
		<td valign ="top" align="left">'.$row['seven'] .'</td>
		<td valign ="top" align="left">'.$row['eight'] .'</td>
		</tr>
		';
						 
		} //End of WHILE loop.
		echo '</table>';
		mysqli_free_result ($r);
		mysqli_close($dbc);
		//Make the links to other pages, if necessary.
		if ($pages > 1) {
		echo '<br /><p>';
		$current_page = ($start/$display) + 1;
		
		// If it's not the first page, make a Previous buttion:
		if ($current_page != 1) {
		echo '<a href="'.$_SERVER['PHP_SELF'].'?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="'.$_SERVER['PHP_SELF'].'?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 buttion:
		if ($current_page != $pages) {
		echo '<a href="'.$_SERVER['PHP_SELF'].'?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">Next</a>';
		}
		echo '</p>'; // Close the paragraph
		} // End the page links section

		include ('includes/footer.html');
		?>

 

Link to comment
Share on other sites

Thanks for the kick in the right direction.  That worked! 

Here is the code, in case it may help someone else:

 

//Define the query:  NOTE: acr is the table name. One, five, and freetext are form input names.
		$q = "SELECT * FROM acr WHERE one LIKE \"%$one%\" AND five LIKE \"$five%\" AND (six LIKE \"%$freetext%\" OR two LIKE \"%$freetext%\" OR four LIKE \"%$freetext%\" OR seven LIKE \"%$freetext%\" OR nine LIKE \"%$freetext%\"  OR eight LIKE \"%$freetext%\") ORDER BY $order_by LIMIT $start, $display";
		//Run the query
		$r = @mysqli_query($dbc, $q)
		or die("Error: ".mysqli_error($dbc));
		
		//Start the table header
		echo '<table align="center" cellspacing="15" cellpadding="2" width="100%">
		<tr>"There were  ' . $records . ' hits for your search '.$q . '. "
		<tr>
			<td align="left"><b>Details</b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $one_sort. '">One</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $two_sort .'">Two</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $three_sort.'">Three</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $four_sort .'">Four</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $five_sort .'">Five</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $six_sort .'">Six</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $seven_sort .'">Seven</a></b></td>
			<td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $eight_sort .'">Eight</a></b></td>
			</tr>';

		
		//Fetch and print all the records.
		$bg = '#eeeeee';
		while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
		$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
		echo '
		<tr bgcolor="' .$bg . '">
		<td valign ="top" align="left"><a href="edit.php?id=' . $row['acr_id'] . '">Details</a></td>
		<td valign ="top" align="left">'.$row['one'] .'</td>
		<td valign ="top" align="left">'.$row['two'] .'</td>
		<td valign ="top" align="left">'.$row['three'] .'</td>
		<td valign ="top" align="left">'.$row['four'] .'</td>
		<td valign ="top" align="left">'.$row['five'] .'</td>
		<td valign ="top" align="left">'.$row['six'] .'</td>
		<td valign ="top" align="left">'.$row['seven'] .'</td>
		<td valign ="top" align="left">'.$row['eight'] .'</td>
		</tr>
		';
						 
		} //End of WHILE loop.
		echo '</table>';
Link to comment
Share on other sites

 Share

×
×
  • Create New...