Jump to content

How do I paginate results from sql database

bruceholt

By bruceholt
in PHP Coding Help

 Share

Recommended Posts

bruceholt Newbie

bruceholt

Posted
Posted

Hoping someone can help me. I am trying to limit the number of results from a sql database and I assume that I should use pagination. How do I do this with the following code? Thanks in advance.

 

<?php 
if ($_GET[search] == 1) $qstring = "WHERE TITLE LIKE '%$_POST[search]%'";
if ($_GET[search] == 2) { 
        $qstring = "WHERE CATEGORY LIKE '%-$_POST[search_cat]-%'";
        $SQL3 = "SELECT * from CATEGORIES WHERE ID = '%-$_POST[search_cat]-%'"; $result3 = @mysql_query( $SQL3 ); $row3 = @mysql_fetch_array( $result3 );
}
if ($_GET[search] == 3) { 
        $qstring = "WHERE PACKAGE = '$_POST[search_package]'";
}
$setpage = 'add_listings';
$return = 'manage_listings';
$table = "LISTINGS";
if (empty($qstring)) $qstring = 'ORDER BY TITLE ASC';
$qstring = "$qstring";
?> 
<?php if ($rowxxx[HELPBOX] == $nil || $rowxxx[HELPBOX] == Show) { ?>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td3" colspan="2"><b> Tool Tips</b></td>
</tr>
<tr> 
<td class="td4" valign="top"> 
<p><img src="../images/Help.png" width="64" height="64"></p>
</td>
<td class="td4" width="100%" valign="top"> 
<table width="100%" align="center" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td> 
<div align="center"><img src="../images/edit_listing.jpg" border="0" alt="Edit this listing" width="22" height="22" /></div>
</td>
<td class="font1" width="100%"> Edit this listing.</td>
</tr>
<tr> 
<td> 
<div align="center"><img src="../images/delete-page-red.gif" alt="Delete this listing" border="0" width="14" height="14" /></div>
</td>
<td class="font1" width="100%"> Delete this listing.</td>
</tr>
<tr> 
<td> 
<div align="center"><img src="../images/user-group3.gif" width="14" height="14"></div>
</td>
<td class="font1" width="100%"> Duplicate this listing and edit its clone.</td>
</tr>
<tr> 
<td><a href="?page=add_photos&id=<?php echo $row[iD]; ?>"><img src="../images/Iphoto.png" width="22" height="22" border="0"></a></td>
<td class="font1" width="100%"> Add photos.</td>
</tr>
<tr> 
<td> </td>
<td class="font1" width="100%"> 
<?php $sql_active_accounts = "SELECT count(*) FROM LISTINGS";
$res_active_accounts = @mysql_query($sql_active_accounts);
$row_active_accounts = @mysql_fetch_row($res_active_accounts); ?>
<b> ( 
<?php echo " $row_active_accounts[0] "; if ($row_active_accounts == $nil) echo " 0 "; ?>
) </b>Listings in your database.</td>
</tr>
</table>
</td>
</tr>
</table>
<br>
<?PHP } ?>
<form action="?page=<?php echo $_GET
; ?>&search=2" method="post" name="form1" id="form1">
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td3"> <b>Find Listings by Category</b></td>
</tr>
</table>
<table  width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td4" nowrap width="100%"> Select Category</td>
<td class="td4"> 
<select name="search_cat">
<option selected> 
<?PHP ECHO $row3[TITLE]; ?>
</option>
<?PHP
$SQLcaT = "SELECT * from CATEGORIES ORDER BY TITLE ASC";
$resultcaT = @mysql_query( $SQLcaT ); while( $rowcaT = @mysql_fetch_array( $resultcaT ) ) {

$sql2 = "SELECT count(*) FROM LISTINGS WHERE CATEGORY LIKE '%-$rowcaT[iD]-%'";
$res2 = @mysql_query($sql2); $row2 = @mysql_fetch_row($res2);
?>
<option value = "<?php echo $rowcaT[iD]; ?>"> 
<?php echo "$rowcaT[TITLE]"; if (empty($row2[0])) echo ' [ 0 ]'; else echo " [ $row2[0] ]"; ?>
</option>
<?PHP } ?>
</select>
</td>
<td class="td4"> 
<input type="submit" name="Submit" value="Search" />
</td>
</tr>
</table>
</form>
<br>
<form action="?page=<?php echo $_GET
; ?>&search=3" method="post" name="form1" id="form1">
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td3"> <b>Find Listings by Package</b></td>
</tr>
</table>
<table  width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td4" nowrap width="100%"> Select Package</td>
<td class="td4"> 
<select name="search_package" id="search_package">
<option selected value ="<?php echo $row[DEFAULTPACKAGE]; ?>"> 
<?PHP $SQLlu = "SELECT * from PACKAGES WHERE ID = '$row[DEFAULTPACKAGE]'"; $resultlu = @mysql_query( $SQLlu ); $rowlu = @mysql_fetch_array( $resultlu ); ?>
<?PHP echo $rowlu[NAME]; ?>
</option>
<?PHP
$SQL3            = "SELECT * from PACKAGES ORDER BY NAME ASC";
$result3         = mysql_query( $SQL3 );
while( $row3     = mysql_fetch_array( $result3 ) ) {

?>
<option value="<?php echo $row3[iD]; ?>"> 
<?php echo $row3[NAME]; ?>
</option>
<?PHP } ?>
</select>
</td>
<td class="td4"> 
<input type="submit" name="Submit" value="Search" />
</td>
</tr>
</table>
</form>
<br>
<form action="?page=<?php echo $_GET
; ?>&search=1" method="post" name="form1" id="form1">
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td3"> <b>Find Listing by Name</b></td>
</tr>
</table>
<table  width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td4" nowrap width="100%"> Listing Title</td>
<td class="td4"> 
<input type="text" name="search" value="<?php echo $_POST[search]; ?>" size="40" />
</td>
<td class="td4"> 
<input type="submit" name="Submit" value="Search" />
</td>
</tr>
</table>
</form>
<br>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td3"> <b>Last Listing Added</b></td>
</tr>
</table>
<?php
$SQL = "SELECT * from $table ORDER BY ID DESC LIMIT 1";
$result = @mysql_query( $SQL ); while( $row = @mysql_fetch_array( $result ) ) {
$check = 1;
?>
<table  width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<?PHP
$filename = "$svr_rootscript/product_images/thumb/$row[iMAGENAME].jpg"; 
if (file_exists($filename)) {
?>
<td nowrap class="td4" align="center" valign="top"> <a href="?page=add_photos&id=<?php echo $row[iD]; ?>"><img src="../product_images/thumb/<?php echo "$row[iMAGENAME]"; ?>.jpg" border="1" /></a> </td>
<?PHP } ?>
<td class="td4"  colspan="2" width="100%"><b> 
<?php echo "<a href = \"?page=$setpage&id=$row[iD]&qt=update\">$row[TITLE]</a>"; ?>
</b><br>
<span class="font3">Category(s): 
<?PHP
$pieces = explode("-", $row[CATEGORY]);
foreach ($pieces as $cats => $value) {
if ($value != $nil && $value != '-' && $value != '--') {

	$SQLCat = "SELECT * from CATEGORIES WHERE ID = '$value'"; 
	$resultCat = @mysql_query( $SQLCat ); $rowCat = @mysql_fetch_array( $resultCat );

	echo "$rowCat[TITLE], ";
}
}
?>
</span></td>
<td nowrap class="td4"><a href="?page=add_photos&id=<?php echo $row[iD]; ?>"><img src="../images/Iphoto.png" width="22" height="22" border="0"></a></td>
<td class="td4"><a href="<?php echo "?page=$setpage&id=$row[iD]&qt=insert&clone=1"; ?>"> <img src="../images/user-group3.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td>
<td class="td4"><a href="<?php echo "?page=$setpage&id=$row[iD]&qt=update"; ?>"> <img src="../images/edit-comment-orange.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td>
<td class="td4"><a href="../library/delete.php?id=<?php echo $row[iD]; ?>&return=<?php echo $return; ?>&type=<?php echo "$table"; ?>"> <img src="../images/delete-page-red.gif" alt="Delete this listing" border="0" width="14" height="14" /></a> 
</td>
</tr>
</table>
<?php } ?>
<br>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td class="td3"> <b>Your Listings</b></td>
</tr>
</table>
<?php
$SQL = "SELECT * from $table $qstring";
$result = @mysql_query( $SQL ); while( $row = @mysql_fetch_array( $result ) ) {
$check = 1;
?>
<table  width="100%" cellpadding="0" cellspacing="0" border="0">
<tr>
<?PHP
$filename = "$svr_rootscript/product_images/thumb/$row[iMAGENAME].jpg"; 
if (file_exists($filename)) {
?>
<td nowrap class="td4" align="center" valign="top"> <a href="?page=add_photos&id=<?php echo $row[iD]; ?>"><img src="../product_images/thumb/<?php echo "$row[iMAGENAME]"; ?>.jpg" border="1" /></a> </td>
<?PHP } ?>
<td class="td4"  colspan="2" width="100%"><b> 
<?php echo "<a href = \"?page=$setpage&id=$row[iD]&qt=update\">$row[TITLE]</a>"; ?>
</b><br>
<span class="font3">Category(s): 
<?PHP
$pieces = explode("-", $row[CATEGORY]);
foreach ($pieces as $cats => $value) {
if ($value != $nil && $value != '-' && $value != '--') {

	$SQLCat = "SELECT * from CATEGORIES WHERE ID = '$value'"; 
	$resultCat = @mysql_query( $SQLCat ); $rowCat = @mysql_fetch_array( $resultCat );

	echo "$rowCat[TITLE], ";
}
}
?>
</span> </td>
<td nowrap class="td4"><a href="?page=add_photos&id=<?php echo $row[iD]; ?>"><img src="../images/Iphoto.png" width="22" height="22" border="0"></a></td>
<td class="td4"><a href="<?php echo "?page=$setpage&id=$row[iD]&qt=insert&clone=1"; ?>"> <img src="../images/user-group3.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td>
<td class="td4"><a href="<?php echo "?page=$setpage&id=$row[iD]&qt=update"; ?>"> <img src="../images/edit-comment-orange.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td>
<td class="td4"><a href="../library/delete.php?id=<?php echo $row[iD]; ?>&return=<?php echo $return; ?>&type=<?php echo "$table"; ?>"> <img src="../images/delete-page-red.gif" alt="Delete this listing" border="0" width="14" height="14" /></a> 
</td>
</tr>
</table>
<?php } ?>
<?PHP if (empty($check)) { ?>
<table  width="100%" cellpadding="0" cellspacing="0" border="0">
<tr> 
<td nowrap class="td4">No listings found.</td>
</tr>
</table>
<?PHP } ?>

QuickOldCar Regular Member

QuickOldCar

Posted
Posted

There are some pagination classes out there if look for them.

Many of them would query and fetch all the results each time, then just display x amount of them. Thats not a very efficient way to do it, imagine having 2 million records being fetched for every page when only showing around 10 results.

 

If you just get the count of your specific query, and then split those up by x amount, and fetch just those is much faster and efficient.

 

You are better off following the tutorial, but i did make my own pagination, and 2 different types of navigation, if need help understanding how it works.

 

The simple one and can set to any amount per page.

http://get.blogdns.com/paginate

 

More advanced with jumping x amount ahead or back, hard set to 10 results per page

http://get.blogdns.com/dynaindex/paginate

 

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

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

  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.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.