Help with search + pagination script
Posted: Sat Aug 06, 2005 3:15 am
Hi,
I have put to gether a script that paginates records and also allows for searhing ..
I have one minor issue where when you choose an option on the dropdown an add a search term, if the results extend into the pagination page one displays the first few results then I get the following error when moving thru the other pages:
Any idea why?
Also can the script be trimmed down to share one sql query etc keeping the same functions but making for less code?
Here is the script:
Cheers
I have put to gether a script that paginates records and also allows for searhing ..
I have one minor issue where when you choose an option on the dropdown an add a search term, if the results extend into the pagination page one displays the first few results then I get the following error when moving thru the other pages:
Code: Select all
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in Z:\###\htdocs\address_book\index.php on line 45
Total results matching your search: | Displaying Page 2 of 0
Show all records
ADD NEW ADDRESS
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in D:\###\htdocs\address_book\index.php on line 56
No results found
Previous |Any idea why?
Also can the script be trimmed down to share one sql query etc keeping the same functions but making for less code?
Here is the script:
Code: Select all
<form name="form" method="post" action="../address_book/index.php">
<div align="center">Search By:
<select name="options" class="input-box">
<option value="fname">First Name</option>
<option value="lname">Last Name</option>
<option value="city">City</option>
</select>
Term:
<input name="search" type="text" size="18" class="input-box">
<input type="submit" name="Submit" value="Submit" class="submit-button">
</div>
</form>
<?php
include("db.php");
$term = $_POST['options'];
if (!isset($_GET['page']))
{
$page = 1;
} else {
$page = $_GET['page'];
}
$max_results = 3;
$from = (($page * $max_results) - $max_results);
if (isset($_REQUEST['search']) && trim($_REQUEST['search']) !== "") {
$search = $_REQUEST['search'];
// Get the total search results
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM address WHERE ($term LIKE('%$search%'))"),0);
$total_pages = ceil($total_results / $max_results);
echo "<center><font size=\"1\" face=\"tahoma\">Total results matching your search: <b>$total_results</b> | ";
echo "Displaying Page <b>$page</b> of <b>$total_pages</b><br><br>";
echo "<a href=\"../address_book/index.php\">Show all records</a><br><br>";
echo "<a href=\"../address_book/add_address.php\">ADD NEW ADDRESS</a></div></font></center><p>";
$sql = "SELECT * FROM address WHERE ($term LIKE('%$search%')) LIMIT $from, $max_results";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0 ) {
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$fname = $row['fname'];
$lname = $row['lname'];
$phoneno = $row['phoneno'];
$cellno = $row['cellno'];
$address = $row['address'];
$city = $row['city'];
$email = $row['email'];
$comments = $row['comments'];
?>
<table width="350" border="1" align="center" cellpadding="4" cellspacing="1" bordercolor="#000000" bgcolor="#FFFFFF">
<tr bgcolor="#003366">
<td colspan="2"><a href="../address_book/full_address.php" target="name" onClick="window.open('../address_book/full_address.php?fname=<?php echo $row['fname']; ?>&id=<?php echo $row['id']; ?>','name','height=330, width=440,toolbar=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=no'); return false;">VIEW FULL DETAILS</a></td>
</tr>
<tr>
<td width="12%" bgcolor="#999999"><div align="right">Name:</div></td>
<td width="88%" bgcolor="#CCCCCC"><div align="left"><?php echo $row['fname']; ?> <?php echo $row['lname']; ?> </div></td>
</tr>
<tr>
<td nowrap bgcolor="#999999"><div align="right">Phone No:</div></td>
<td bgcolor="#CCCCCC"><div align="left" ><?php echo $row['phoneno']; ?> </div></td>
</tr>
</table><br>
<?php
}
} else {
echo "<center>No results found</center>";
}
} else {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM address"),0);
$total_pages = ceil($total_results / $max_results);
echo "<center>Total results: <b>$total_results</b> | ";
echo "Displaying Page <b>$page</b> of <b>$total_pages</b><br><br>";
echo "<a href=\"../address_book/add_address.php\">ADD NEW ADDRESS</a></div></center>";
$sql = "SELECT * FROM address LIMIT $from, $max_results";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0 ) {
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$fname = $row['fname'];
$lname = $row['lname'];
$phoneno = $row['phoneno'];
$cellno = $row['cellno'];
$address = $row['address'];
$city = $row['city'];
$email = $row['email'];
$comments = $row['comments'];
?>
<table width="350" border="1" align="center" cellpadding="4" cellspacing="1" bordercolor="#000000" bgcolor="#FFFFFF">
<tr bgcolor="#003366">
<td colspan="2"> <a href="../address_book/full_address.php" target="name" onClick="window.open('../address_book/full_address.php?fname=<?php echo $row['fname']; ?>&id=<?php echo $row['id']; ?>','name','height=330, width=440,toolbar=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=no'); return false;">VIEW FULL DETAILS</a></td>
</tr>
<tr>
<td width="12%" bgcolor="#999999"><div align="right">Name:</div></td>
<td width="88%" bgcolor="#CCCCCC"><div align="left"><?php echo $row['fname']; ?> <?php echo $row['lname']; ?> </div></td>
</tr>
<tr>
<td nowrap bgcolor="#999999"><div align="right" >Phone No:</div></td>
<td bgcolor="#CCCCCC"><div align="left"><?php echo $row['phoneno']; ?> </div></td>
</tr>
</table><br>
<?php
}
} else {
echo "<center><font size=\"1\" face=\"tahoma\">No results found</font></center>";
}
}
echo "<center>";
if ($page > 1) {
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?cmd=$item&page=$prev&term=$term&search=$search\">Previous</a> | ";
}
for ($i = 1; $i <= $total_pages; $i++) {
if (($page) == $i) {
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?cmd=$item&page=$i&term=$term&search=$search\">$i</a> ";
}
}
if ($page < $total_pages) {
$next = ($page + 1);
echo " | <a href=\"".$_SERVER['PHP_SELF']."?cmd=$item&term=$term&page=$next&search=$search\">Next</a>";
}
echo "</center>";
?>Cheers