Pagination

[danjoe_15]

I have a query which is built like this:

 

$query  = "SELECT * FROM customers WHERE 1=1 ";

if ($_POST['Customer_id'] != "")
{
	$query .= " AND customer_id = '%" . mysql_real_escape_string($_POST['Customer_id']) . "%' ";
}
if ($_POST['FirstName'] != "")
{
	$query .= " AND first_name LIKE '%" . mysql_real_escape_string($_POST['FirstName']) . "%' ";
}
if ($_POST['LastName'] != "")
{
	$query .= " AND last_name LIKE '%" . mysql_real_escape_string($_POST['LastName']) . "%' ";
}
if($_POST['StatusID']!=0)
{
	$query .= " AND status_id= '" . mysql_real_escape_string($_POST['StatusID']) . "' ";
}
if ($_POST['Care_of'] != "")
{
	$query .= " AND care_of LIKE '%" . mysql_real_escape_string($_POST['Care_of']) . "%' ";
}	
if($_POST['Group']!=0)
{
	$query .= " AND group_id= '" . mysql_real_escape_string($_POST['Group']) . "' ";
}
if($_POST['Filter']!="")
{
	$query .= " AND sort_id= '" . mysql_real_escape_string($_POST['Filter']) . "' ";
}
if ($_POST['UPS_Address'] != "")
{
	$query .= " AND ups_address LIKE '%" . mysql_real_escape_string($_POST['UPS_Address']) . "%' ";
}	
if ($_POST['Address2'] != "")
{
	$query .= " AND address LIKE '%" . mysql_real_escape_string($_POST['Address2']) . "%' ";
}	
if($_POST['Support']!=0)
{
	$query .= " AND support_id= '" . mysql_real_escape_string($_POST['Support']) . "' ";
}
if ($_POST['Zip'] != "")
{
	$query .= " AND zip LIKE '%" . mysql_real_escape_string($_POST['Zip']) . "%' ";
}
if ($_POST['City'] != "")
{
	$query .= " AND city LIKE '%" . mysql_real_escape_string($_POST['City']) . "%' ";
}		
if ($_POST['State'] != "")
{
	$query .= " AND states LIKE '%" . mysql_real_escape_string($_POST['State']) . "%' ";
}	
if ($_POST['First_Contact'] != "")
{
	$query .= " AND first_contact LIKE '%" . mysql_real_escape_string($_POST['First_Contact']) . "%' ";
}		
if ($_POST['Phone'] != "")
{
	$query .= " AND phone_number LIKE '%" . mysql_real_escape_string($_POST['Phone']) . "%' ";
}	
if ($_POST['Fax'] != "")
{
	$query .= " AND fax_number LIKE '%" . mysql_real_escape_string($_POST['Fax']) . "%' ";
}	
if ($_POST['Tax_Number'] != "")
{
	$query .= " AND tax_id LIKE '%" . mysql_real_escape_string($_POST['Tax_Number']) . "%' ";
}	
if ($_POST['email'] != "")
{
	$query .= " AND email_address LIKE '%" . mysql_real_escape_string($_POST['email']) . "%' ";
}

$result = mysql_query($query);
$customer = mysql_fetch_assoc($result);

 

I am unsure on how to use such a query to go through the results one by one with buttons such as First, Previous, Next and Last.  I have done such buttons before with other queries, however they were not built in this way.

[ngreenwood6]

It would be the same as any other pagination. I am not sure that I understand what you are asking here. You will simply have to limit the results in the query and then make the pagination links.

[danjoe_15]

i suppose I am unsure on how to limit those results.  I have tried things such as this:

 

$query .= "LIMIT $_GET['recordno'], 1";

 

after all of the other parts of the guery

[ngreenwood6]

I am still unsure about what you are trying to do but if you just wanted to display one record per page you would do:

 

$query .= "LIMIT 1";

[kittrellbj]

From the MySQL manual:

 

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

 

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

 

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

 

So, basically, your offset is the first number (the place to start counting), and the second number is the maximum amount of rows to return.  For pagination, this is important in determining which page you are currently on.  $_GET['page'] could be used in the following fashion:

 

SELECT * FROM tbl LIMIT " . $_GET['startrecord'] . ",10;  // Retrieve 10 rows starting with [i]n[/i] starting record.

 

Now, a little bit of pre-plannning could give you something a little easier to deal with in the beginning.  For instance, consider this code:

 

$page = $_GET['page'];
$searchedrecords = mysql_query($yourquery);
$recordsperpage = 10;
$totalrecords = mysql_num_rows($searchedrecords);
// Above is where the mysql query code goes, establishing a count on the rows that have been returned
// according to your search parameters established.
$pagecount = ceil($totalrecords / $recordsperpage);
if ($pagecount < 1) { $pagecount = 1; }
// This gives you the maximum number of pages (total divided by records per page, with any remainder rounded up)
// if pages is less than one or is zero, set it to one (so the user will get some records)
if ($page < 1 || $page > $total_pages) {
$page = 1; }
// If the user is on the first page (which won't have a page=# (it is interpreted as 0), set it to one.
// If the user is trying to goto a page that doesn't exist (number is too large), set it to 1
// to keep the script from doing strange things.
$offset = ($page - 1) * $recordsperpage;
// Creates an offset for the database
$displayresults = mysql_query($yourquery . " LIMIT $offset, $recordsperpage");
// Brings up only the records needed for display of results
while($row = mysql_fetch_assoc($displayresults))
{
// Display the results to the user in a loop
}

// Now, make a list of pages and make
// them into links so it can be used by
// the user to navigate.
for($n = 1; $n <= $totalpages; $n++) {
echo "<a href="?page=" . $n . ">$n</a> ";
}

 

I don't think I left anything out.  If I did, it was probably something simple that shall be spotted.

 

Edit:  You would replace $yourquery with the long query stated in your OP.  It was not added to the code I wrote to increase readability (and you already know your code anyway).

[danjoe_15]

It appears to be getting closer.  With this as my query:

 

<?php
include 'common.php';

dbConnect();

$query  = "SELECT * FROM customers WHERE 1=1 ";

if ($_POST['Customer_id'] != "")
{
	$query .= " AND customer_id = '%" . mysql_real_escape_string($_POST['Customer_id']) . "%' ";
}
if ($_POST['FirstName'] != "")
{
	$query .= " AND first_name LIKE '%" . mysql_real_escape_string($_POST['FirstName']) . "%' ";
}
if ($_POST['LastName'] != "")
{
	$query .= " AND last_name LIKE '%" . mysql_real_escape_string($_POST['LastName']) . "%' ";
}
if($_POST['StatusID']!=0)
{
	$query .= " AND status_id= '" . mysql_real_escape_string($_POST['StatusID']) . "' ";
}
if ($_POST['Care_of'] != "")
{
	$query .= " AND care_of LIKE '%" . mysql_real_escape_string($_POST['Care_of']) . "%' ";
}	
if($_POST['Group']!=0)
{
	$query .= " AND group_id= '" . mysql_real_escape_string($_POST['Group']) . "' ";
}
if($_POST['Filter']!="")
{
	$query .= " AND sort_id= '" . mysql_real_escape_string($_POST['Filter']) . "' ";
}
if ($_POST['UPS_Address'] != "")
{
	$query .= " AND ups_address LIKE '%" . mysql_real_escape_string($_POST['UPS_Address']) . "%' ";
}	
if ($_POST['Address2'] != "")
{
	$query .= " AND address LIKE '%" . mysql_real_escape_string($_POST['Address2']) . "%' ";
}	
if($_POST['Support']!=0)
{
	$query .= " AND support_id= '" . mysql_real_escape_string($_POST['Support']) . "' ";
}
if ($_POST['Zip'] != "")
{
	$query .= " AND zip LIKE '%" . mysql_real_escape_string($_POST['Zip']) . "%' ";
}
if ($_POST['City'] != "")
{
	$query .= " AND city LIKE '%" . mysql_real_escape_string($_POST['City']) . "%' ";
}		
if ($_POST['State'] != "")
{
	$query .= " AND states LIKE '%" . mysql_real_escape_string($_POST['State']) . "%' ";
}	
if ($_POST['First_Contact'] != "")
{
	$query .= " AND first_contact LIKE '%" . mysql_real_escape_string($_POST['First_Contact']) . "%' ";
}		
if ($_POST['Phone'] != "")
{
	$query .= " AND phone_number LIKE '%" . mysql_real_escape_string($_POST['Phone']) . "%' ";
}	
if ($_POST['Fax'] != "")
{
	$query .= " AND fax_number LIKE '%" . mysql_real_escape_string($_POST['Fax']) . "%' ";
}	
if ($_POST['Tax_Number'] != "")
{
	$query .= " AND tax_id LIKE '%" . mysql_real_escape_string($_POST['Tax_Number']) . "%' ";
}	
if ($_POST['email'] != "")
{
	$query .= " AND email_address LIKE '%" . mysql_real_escape_string($_POST['email']) . "%' ";
}
	$query .= "LIMIT". $_GET['recordno'] .", 1";
$result = mysql_query($query);
$customer = mysql_fetch_assoc($result);

 

and this as my pagination buttons:

 

<td><form action="Inventory.php" method="GET">
						<input type="hidden" name="customer_id" value="<? echo ($customer['customer_id']); ?>"/>
						<input type="submit"style="width:150px" name="Reg. & Inventory" value="Reg. & Inventory"/></form></td>
			<td><form action="Remove.php" method="GET">
						<input type="hidden" name="customer_id" value="<? echo ($customer['customer_id']); ?>"/>
						<input type="submit"style="width:150px" name="Delete Customer" value="Delete Customer"/></form></td>						
		</tr>
	</table>
	<table>
			<tr>

				<td><form action="FilteredP.php" method="GET">
						<input type="hidden" name="recordno" value="0"/>
						<input type="submit" <? if ($_GET['recordno']==0) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="First" value="First"/></form></td>
				<td><form action="FilteredP.php" method="GET">
						<input type="hidden" name="recordno" value="<? echo ($_GET['recordno']-1); ?>"/>
						<input type="submit" <? if ($_GET['recordno']==0) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="Previous" value="Previous"/></form></td>

<?
$query  = "SELECT count(*) FROM customers WHERE 1=1 ";

if ($_POST['Customer_id'] != "")
{
	$query .= " AND customer_id = '%" . mysql_real_escape_string($_POST['Customer_id']) . "%' ";
}
if ($_POST['FirstName'] != "")
{
	$query .= " AND first_name LIKE '%" . mysql_real_escape_string($_POST['FirstName']) . "%' ";
}
if ($_POST['LastName'] != "")
{
	$query .= " AND last_name LIKE '%" . mysql_real_escape_string($_POST['LastName']) . "%' ";
}
if($_POST['StatusID']!=0)
{
	$query .= " AND status_id= '" . mysql_real_escape_string($_POST['StatusID']) . "' ";
}
if ($_POST['Care_of'] != "")
{
	$query .= " AND care_of LIKE '%" . mysql_real_escape_string($_POST['Care_of']) . "%' ";
}	
if($_POST['Group']!=0)
{
	$query .= " AND group_id= '" . mysql_real_escape_string($_POST['Group']) . "' ";
}
if($_POST['Filter']!="")
{
	$query .= " AND sort_id= '" . mysql_real_escape_string($_POST['Filter']) . "' ";
}
if ($_POST['UPS_Address'] != "")
{
	$query .= " AND ups_address LIKE '%" . mysql_real_escape_string($_POST['UPS_Address']) . "%' ";
}	
if ($_POST['Address2'] != "")
{
	$query .= " AND address LIKE '%" . mysql_real_escape_string($_POST['Address2']) . "%' ";
}	
if($_POST['Support']!=0)
{
	$query .= " AND support_id= '" . mysql_real_escape_string($_POST['Support']) . "' ";
}
if ($_POST['Zip'] != "")
{
	$query .= " AND zip LIKE '%" . mysql_real_escape_string($_POST['Zip']) . "%' ";
}
if ($_POST['City'] != "")
{
	$query .= " AND city LIKE '%" . mysql_real_escape_string($_POST['City']) . "%' ";
}		
if ($_POST['State'] != "")
{
	$query .= " AND states LIKE '%" . mysql_real_escape_string($_POST['State']) . "%' ";
}	
if ($_POST['First_Contact'] != "")
{
	$query .= " AND first_contact LIKE '%" . mysql_real_escape_string($_POST['First_Contact']) . "%' ";
}		
if ($_POST['Phone'] != "")
{
	$query .= " AND phone_number LIKE '%" . mysql_real_escape_string($_POST['Phone']) . "%' ";
}	
if ($_POST['Fax'] != "")
{
	$query .= " AND fax_number LIKE '%" . mysql_real_escape_string($_POST['Fax']) . "%' ";
}	
if ($_POST['Tax_Number'] != "")
{
	$query .= " AND tax_id LIKE '%" . mysql_real_escape_string($_POST['Tax_Number']) . "%' ";
}	
if ($_POST['email'] != "")
{
	$query .= " AND email_address LIKE '%" . mysql_real_escape_string($_POST['email']) . "%' ";
}

					$result = dbquery($query);
					if($subrow=dbNext($result))
					{
?>
						<td><form action="FilteredP.php" method="GET">
							<input type="hidden" name="recordno" value="<? echo ($_GET['recordno']+1); ?>"/>
							<input type="submit" <? if ($_GET['recordno']==($subrow['count(*)']-1)) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="Next" value="Next"/></form></td>
						<td><form action="FilteredP.php" method="GET">
							<input type="hidden" name="recordno" value="<? echo ($subrow['count(*)'] - 1); ?>"/>
							<input type="submit" <? if ($_GET['recordno']==($subrow['count(*)']-1)) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="Last" value="Last"/></form></td>

 

it appears to be paging through.  However I am getting no data in my fields.