Help with search + pagination script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Help with search + pagination script

Post by Jim_Bo »

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:

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']; ?>&nbsp;</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']; ?>&nbsp;</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']; ?>&nbsp;</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']; ?>&nbsp;</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>&nbsp|&nbsp"; 
}

for ($i = 1; $i <= $total_pages; $i++) {
    
	if (($page) == $i) {
		
		echo "$i&nbsp;"; 
} else { 
		
		echo "<a href=\"".$_SERVER['PHP_SELF']."?cmd=$item&page=$i&term=$term&search=$search\">$i</a>&nbsp"; 
 }
}

if ($page < $total_pages) {
	
	$next = ($page + 1);
	echo "&nbsp;|&nbsp;<a href=\"".$_SERVER['PHP_SELF']."?cmd=$item&term=$term&page=$next&search=$search\">Next</a>"; 
}

echo "</center>";

?>

Cheers
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Help with search + pagination script

Post by timvw »

Jim_Bo wrote:

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[/quote]

This means you have probably an error in query. So, echo it out before you perform it and/or use mysql_error where the error exactly is.


[quote="Jim_Bo"]
Also can the script be trimmed down to share one sql query etc keeping the same functions but making for less code?[/quote]

After a first glance, you do twice more or less the same operations to generate a sql query. You could consider using a class to generate it.

Here is an [url=http://timvw.madoka.be/programming/php/activerecord.php.txt]example[/url] of you could do it (it uses [url=http://adodb.sf.net]adodb[/url]) (If you want, unittests are available too)
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Just to illiminate my first issue, when is use:

Code: Select all

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM address WHERE (lname LIKE('%$search%'))"),0);
It parses to the next page fine, but when I use a dropdown menu to choose table field to search it wont parse onto the other pages:

Code: Select all

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM address WHERE ($term LIKE('%$search%'))"),0);
Why would it work one way and not ther other even tho its the same variable either way?

Cheers
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

If you're using a form select box, $term won't be set until you submit the form data, then you could use it in your SQL

$term would be empty if you don't first submit that form, and then run the query.

Echo the query containing $term to the browser, and I think most likely it will show that it is empty.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

I try to explain it ..

Say I have the pagination set to 3 records, I submit a search and there are 5 results, the first 3 results show fine, when you click on page 2 there is an error in the sql query as stated above in the first original post.

But when using lname instead of $term your able to surf thru the pagination fine ..

Cheers
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

What happens when you echo the query containing $term to the browser?

I would do that, and then use a code like

Code: Select all

if($_GET['page'] == 2)
{
   $sql = "SELECT * FROM address WHERE ($term LIKE('%$search%')) LIMIT $from, $max_results"; 
   echo $sql;
}
That would print out your second page SQL instructions to see if the LIMIT settings are correct. If the first page works fine, then my guess would be that the LIMIT isn't correct.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply