Jump to content

bruceholt

New Members
 View Profile  See their activity

  • Posts

    3

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by bruceholt

    How do I paginate results from sql database

    in PHP Coding Help

    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 } ?>

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