Page 1 of 1

Help with search + pagination script

Posted: Sat Aug 06, 2005 3:15 am
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

Re: Help with search + pagination script

Posted: Sat Aug 06, 2005 7:42 am
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)

Posted: Sat Aug 06, 2005 4:07 pm
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

Posted: Sat Aug 06, 2005 4:44 pm
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.

Posted: Sat Aug 06, 2005 5:11 pm
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

Posted: Sat Aug 06, 2005 6:07 pm
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.