[SOLVED] Page navigation with seach results - make my page navigation work... please

yandoo · July 1, 2007

Hi,

Was Hoping for a bit of help....Bit of a tricky one but here goes!

I have created a search form that searchs for results from my database matching the criteria to what ever the user inputs. I have done this using 2 php pages the first page points to the second, where the data is sanitised and cleaned accordingly. The results are displayed on the 2nd page.

Heres a copy of the code for the form:


<form name="search" method="get" action="search_laptop_details.php"
		onsubmit="return validate_form(this)">
              <input type="text" name="finda"/> 
              <span class="style10">in</span>
              <Select NAME="field">
		  
             	<Option VALUE="laptopname">Laptop Name</option>
                <Option VALUE="statusID">Status</option>		


              </Select>
              <input type="hidden" name="searching" value="yes" />
              <input type="submit" name="search" value="Search" />
		  </form>

the second page is:

<?php



    $limit          = 10;               
    $query_count    = "SELECT count(*) FROM laptopname";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count); 

    if(empty($page)){
        $page = 1;
    }
        

    $limitvalue = $page * $limit - ($limit); 
    $query  = "SELECT * FROM laptopname WHERE " . $_GET['field'] . " LIKE '%" . $_GET['finda'] . "%' LIMIT $limitvalue, $limit";     
//	echo $query;  
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }

    

      
    



?>

<?php

//check if the user entered data in the form
if (isset($_GET['finda'])) {
//data has been entered so lets search the mofo

echo "<h2>Results</h2><p>";
	// sanitise the data
	$find = strtoupper($_GET['finda']);
	$find = strip_tags($_GET['finda']);
	$find = trim ($_GET['finda']);
	$data = mysql_query($query);




       	//And we display the results
	while($result = mysql_fetch_array( $data ))
	{

   

	echo"<table width=\"200\" border=\"0\" class=\"border_bottom\"> \n";
	echo "<tr> \n";
  
  echo "<td> </td> \n";
  echo "</td> \n";
  echo "</tr> \n";
  echo "<tr> \n";
  
  
    echo "<td>";	echo "<strong>Laptop Name</strong>"; echo "</td>"; 
    echo "<td width=\"75\" style =\"text-align: left\""; ?> <em><a href="search_laptop_details.php?recordID=<?php echo $result['LaptopName']; ?>"><?php echo $result['LaptopName']; ?> </a></em> <?php echo " </td> \n"; 
  echo "</tr> \n";
  echo "<tr> \n";
  
  		echo "<td>";	echo "<strong>Status</strong>"; echo "</td>";
       echo "<td>";	echo "<em>"; 	echo $result['StatusID']; echo "</em>"; echo "</td>";
echo "</tr> \n";
  	echo "<tr> \n";



    
  echo "</tr> \n";

  echo "<tr> \n";
  echo "<td> </td> \n";
  echo "</td> \n";
  echo "</tr> \n";
echo "</table> \n";
echo"<br>";
   
    }
	}

	//This counts the number or results - and if there wasn't any it gives them a little message explaining that
	$anymatches=mysql_num_rows($data);
	if ($anymatches == 0)
	{
	echo "Sorry, but we can not finda an entry to match your query<br><br>";
	}


?>

So far this all works smoothly!!! No problem at all. The problem is when i try to add in the page navigation for it......Here is copy of page navigation bit:

<?php



    $limit          = 2;               
    $query_count    = "SELECT count(*) FROM teachersname";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count); 

    if(empty($page)){
        $page = 1;
    }
        

    $limitvalue = $page * $limit - ($limit); 
    $query  = "SELECT * FROM teachersname WHERE " . $_GET['field'] . " LIKE '%" . $_GET['finda'] . "%' LIMIT $limitvalue, $limit";     
//	echo $query;  
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }

    

      
    



?>

<?php

//check if the user entered data in the form
if (isset($_GET['finda'])) {
//data has been entered so lets search the mofo

echo "<h2>Results</h2><p>";
	// sanitise the data
	$find = strtoupper($_GET['finda']);
	$find = strip_tags($_GET['finda']);
	$find = trim ($_GET['finda']);
	//connect to the db
	//mysql_connect("localhost", "root", "winn3rs") or die(mysql_error());
	//mysql_select_db("laptop_loan_database") or die(mysql_error());
	//Now we search for our search term, in the field the user specified
//	$query = "SELECT * FROM teachersname WHERE " . $_GET['field'] . " LIKE '%$finda%'"  ;
	$data = mysql_query($query);




       	//And we display the results
	while($result = mysql_fetch_array( $data ))
	{

   

	echo"<table width=\"200\" border=\"0\" class=\"border_bottom\"> \n";
	echo "<tr> \n";
  
  echo "<td> </td> \n";
  echo "</td> \n";
  echo "</tr> \n";
  echo "<tr> \n";
    echo "<td>";	echo "<strong>Client Name</strong>"; echo "</td>"; 
    echo "<td width=\"75\" style =\"text-align: left\""; ?> <em><a href="search_client_details.php?recordID=<?php echo $result['Client']; ?>"><?php echo $result['Client']; ?> </a></em> <?php echo " </td> \n"; 
  echo "</tr> \n";
  echo "<tr> \n";
  
  		echo "<td>";	echo "<strong>Department Code</strong>"; echo "</td>";
       echo "<td>";	echo "<em>"; 	echo $result['DepartmentCode']; echo "</em>"; echo "</td>";
    
  echo "</tr> \n";

  echo "<tr> \n";
  echo "<td> </td> \n";
  echo "</td> \n";
  echo "</tr> \n";
echo "</table> \n";
echo"<br>";
   
    }
	}

	//This counts the number or results - and if there wasn't any it gives them a little message explaining that
	$anymatches=mysql_num_rows($data);
	if ($anymatches == 0)
	{
	echo "Sorry, but we can not finda an entry to match your query<br><br>";
	}


?>

<?php

    if($page != 1){ 
        $pageprev = $page--;
         


        echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV" .$limit."</a> "); 

    }else{
        echo("PREV" . $limit." ");
    }

    $numofpages = $totalrows / $limit; 
    

    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");

       }
    }


    if(($totalrows % $limit) != 0){
        if($i == $page){
            echo($i." ");
        }else{
           											echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");

        }
    }

   if(($totalrows - ($limit * $page)) > 0){
        $pagenext = $page++;
         
       												 echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT" .$limit."</a>"); 
    }else{
       												 echo("NEXT" . $limit); 
	$pagenext = ($page++)+1;
	echo("<a href=\"$PHP_SELF?page=$pagenext&field=" . $_GET['field'] . "&finda=" . $_GET['finda'] . "\">NEXT" .$limit."</a>"); 

    } 
?>

Please note the page navigation code page is for a section of the database called clients, other than that it is identical as each section has same search function page.

It seems that when it displays the desired number of records and you can see that there are more records on the following page and the "Next page" is hyperlinked to the next page....That when clicked it doesnt do anything??? It just loads the same page with the same results.

When looking at what the browser is pointing at (hovering mouse pointer over the "Next Page" hyperlink it says that i is looking for whatever records = whatever was entered into the search field at the beginning. NOT page 2???

How can i get this to working properly as the nvigation doesnt seem to work at all. It will not link to 2nd page of results BUT it WILL show that there are more records avaliable on next page as the "Next Page" hyperlinnk is infact hyperlinked, otherwise it would NOT bwe hyperlinked.

Please help me im becomming very desperate now and getting close to my deadline. Ill be so greatful if you can help me out here. If you want to ask anymore questions regarding it or what the code itself, please just let me know.

Many thanks

Tom

sasa · July 1, 2007

again try

<?php
//conect to db
$limit = 2;
$fields = array("laptopname", "statusID");
if (isset($_GET['search']) and $_GET['search'] == 'Search'){
$page = trim(addslashes(strip_tags($_GET['page'])));
if($page == "" OR !is_numeric($page)) $page = 1;
$find = strtoupper($_GET['finda']);
$find = strip_tags($_GET['finda']);
$find = trim ($_GET['finda']);
$find = mysql_real_escape_string($find);
if (in_array($_GET['field'], $fields)) $field = $_GET['field']; else die('wrong field name');
if ($find) $where = ' WHERE '.$field." LIKE '%".$find."%'"; else $where = ' WHERE 1';
$lm = ' LIMIT '.(($page - 1) * $limit).' '.$limit;
$qve_tot_row = "SELECT count(*) FROM teachersname".$where;
$qve_data = "SELECT count(*) FROM teachersname".$where.$lm;
$res = mysql_query($qve_tot_row);
$tot_row = mysql_result($res, 0, 0);
$tot_pages = ceil($tot_row / $limit);
$res =mysql_query($qve_data);
if (mysql_num_rows($res) > 0) {
	while($result = mysql_fetch_array($res)) {
		echo"<table width=\"200\" border=\"0\" class=\"border_bottom\"> \n";
		echo "<tr> \n";
		echo "<td> </td> \n";
		echo "</td> \n";
		echo "</tr> \n";
		echo "<tr> \n";
		echo "<td>";
		echo "<strong>Client Name</strong>"; 
		echo "</td>";
		echo "<td width=\"75\" style =\"text-align: left\""; ?> <em><a href="search_client_details.php?recordID=<?php echo $result['Client']; ?>"><?php 
		echo $result['Client']; ?> </a></em> <?php 
		echo " </td> \n";
		echo "</tr> \n";
		echo "<tr> \n";
		echo "<td>";
		echo "<strong>Department Code</strong>";
		echo "</td>";
		echo "<td>";
		echo "<em>";
		echo $result['DepartmentCode'];
		echo "</em>";
		echo "</td>";
		echo "</tr> \n";
		echo "<tr> \n";
		echo "<td> </td> \n";
		echo "</td> \n";
		echo "</tr> \n";
		echo "</table> \n";
		echo"<br>";
	}
} else echo 'no result';
if ($page > 1) echo '<a href="?search=Search&finda='.$find.'&field='.$field.'&page='.($page - 1).'"> PREV </a>';
else echo ' PREV ';
for ($i =1; $i <= $tot_pages; $i++) {
	if ($i != $page) echo '<a href="?search=Search&finda='.$find.'&field='.$field.'&page='.$i.'"> '.$i.' </a>';
	else echo " $i ";
}
if ($page < $tot_pages) echo '<a href="?search=Search&finda='.$find.'&field='.$field.'&page='.($page + 1).'"> NEXT </a>';
else echo ' NEXT ';
} else die('error');
?>

yandoo · July 2, 2007

Hi,

Firstly thank you for your help here, its greatly appreciated.

I tried the code you suggested....and was slightly perplexed.......The reason being is that the message that appaeared was "Wrong field".... I checked over this code and notice that the array you made were called "laptopname" & "statusID". As i am dealing with the teacher/clients page. I would be using the "teachersname" and "Department code". i made this change accordingly and got the following results:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\local_sites\LaptopLoanDatabase\search_client_details1.php on line 215

no result.

Other than that, the "Next" & "Previous" appear and the even though the same error appears on each page it, the browser changes pages 1, 2, 3 and backwards as it should.

hhhhmmmm what am i doing wrong here????

What do you suggest please?????

Thanks again - a greatful chap

sushant_d84 · July 2, 2007

PEAR has the special class for Paging and for Naviagtion..

Hunt it

It will also help to solve the problem

Illusion · July 2, 2007

Do u think ur query is working properly with the limits values ur passing as php variables with out quotes over there and one more think $limit should be the no of records u want to display per page so if $limit=2 it don't make any sense for pagination.

sasa · July 3, 2007

change line

$qve_data = "SELECT count(*) FROM teachersname".$where.$lm;

to

 echo $qve_data = "SELECT * FROM teachersname".$where.$lm;

is the query look OK

yandoo · July 4, 2007

Hi,

i get this error message:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\local_sites\LaptopLoanDatabase\search_client_details1.php on line 213

that line points to this line of code:

if (mysql_num_rows($res) > 0) {

What does it measn? does it have something to do with what is configured in the php.ini file??

When i echo $qve_data = "SELECT * FROM teachersname".$where.$lm; the guery is just displayed as it is writen here along with the error above!

On a really good note though is that the NEXT and PREVIOUS page links seem to be pointing in the right order. Pages are numbered in order but in the browser it points to the search data that the user would input!!! So that bits quality but i need to get the results of search to display to properly confirm that.

Any more help appreciated :-\

Thank You

sasa · July 5, 2007

is the query Ok

try to execute in phpMYAdmin

yandoo · July 5, 2007

Hi,

I tried running the line of code in phpmyadmin:

$qve_data = "SELECT * FROM teachersname".$where.$lm'

This is the resulting error message i get for it:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$qve_data = "SELECT * FROM teachersname".$where.$lm' at line 1.

Is this what you meant?

Stay with me, we are so close now to cracking it!

Thank You

redarrow · July 5, 2007

$a=$where.$lm;

$a=$_POST['A'];

$qve_data = "SELECT * FROM where teachersname='$a' ";

yandoo · July 5, 2007

Hiya,

I get this error using code within phpmyadmin:

Error

SQL query:

$a = $where.$lm;

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$a=$where.$lm' at line 1

What does this mean?

Thank You

redarrow · July 5, 2007

try this ok

$a=$where.$lm;
$a=$_POST['a'];
$qve_data = "SELECT * FROM where teachersname='$a' ";

you really need to consider using a paginton tutoral ur code sucks sorry.

yandoo · July 5, 2007

Hi,

I have been through a pagnation tutorial but because i need the pagnatation to work from search details put in by a user. Its difficult. This code is from Sasa (which im greatful).

As i mentioned previously Next and Previous links look like they would work properly. Its just this error thats throwing me off and not displaying seearch results.

Please help im breaking down

Thank You

sasa · July 5, 2007

you have line

echo $qve_data = "SELECT * FROM teachersname".$where.$lm;

it echo sql on screen. what is echoed line look like?

try echoed line in phpMyAdmin

if you get echoed output "SELECT * FROM teachersname".$where.$lm; remove single quote in this line

yandoo · July 5, 2007

Hi there,

Thank you for the reply!!

Heres the message genrated in phpmyadmin when i try to run query:

Error

SQL query:

echo $qve_data = "SELECT * FROM teachersname".$where.$lm

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'echo $qve_data = "SELECT * FROM teachersname".$where.$lm' at line 1

What does this mean??? Is this something to do with configuration of sql server in php.ini file?

Thank You

sasa · July 5, 2007

no we don't understand each other

i say that you run script and look the line that scrip outputs just before warning

can you post that line (line that echoed by script)

it must look like SELECT * FROM teachersname WHERE etc.

yandoo · July 5, 2007

Hi,

If i remove the quotes in phpmyadmin i get this message:

Error

SQL query:

echo $qve_data = SELECT *

FROM teachersname.$where.$lm

LIMIT 0 , 30

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'echo $qve_data = SELECT * FROM teachersname.$where.$lm

LIMIT 0, 30' at line 1

What do you think?

Thanks

sasa · July 5, 2007

try

<?php
//conect to db
$limit = 2;
$fields = array("laptopname", "statusID");
if (isset($_GET['search']) and $_GET['search'] == 'Search'){
$page = trim(addslashes(strip_tags($_GET['page'])));
if($page == "" OR !is_numeric($page)) $page = 1;
$find = strtoupper($_GET['finda']);
$find = strip_tags($_GET['finda']);
$find = trim ($_GET['finda']);
$find = mysql_real_escape_string($find);
if (in_array($_GET['field'], $fields)) $field = $_GET['field']; else die('wrong field name');
if ($find) $where = ' WHERE '.$field." LIKE '%".$find."%'"; else $where = ' WHERE 1';
$lm = ' LIMIT '.(($page - 1) * $limit).' '.$limit;
$qve_tot_row = "SELECT count(*) FROM teachersname".$where;
echo $qve_data = "SELECT count(*) FROM teachersname".$where.$lm;
        echo '<br />;
$res = mysql_query($qve_tot_row);
$tot_row = mysql_result($res, 0, 0);
$tot_pages = ceil($tot_row / $limit);
$res =mysql_query($qve_data) or die(mysql_error());
if (mysql_num_rows($res) > 0) {
	while($result = mysql_fetch_array($res)) {
		echo"<table width=\"200\" border=\"0\" class=\"border_bottom\"> \n";
		echo "<tr> \n";
		echo "<td> </td> \n";
		echo "</td> \n";
		echo "</tr> \n";
		echo "<tr> \n";
		echo "<td>";
		echo "<strong>Client Name</strong>"; 
		echo "</td>";
		echo "<td width=\"75\" style =\"text-align: left\""; ?> <em><a href="search_client_details.php?recordID=<?php echo $result['Client']; ?>"><?php 
		echo $result['Client']; ?> </a></em> <?php 
		echo " </td> \n";
		echo "</tr> \n";
		echo "<tr> \n";
		echo "<td>";
		echo "<strong>Department Code</strong>";
		echo "</td>";
		echo "<td>";
		echo "<em>";
		echo $result['DepartmentCode'];
		echo "</em>";
		echo "</td>";
		echo "</tr> \n";
		echo "<tr> \n";
		echo "<td> </td> \n";
		echo "</td> \n";
		echo "</tr> \n";
		echo "</table> \n";
		echo"<br>";
	}
} else echo 'no result';
if ($page > 1) echo '<a href="?search=Search&finda='.$find.'&field='.$field.'&page='.($page - 1).'"> PREV </a>';
else echo ' PREV ';
for ($i =1; $i <= $tot_pages; $i++) {
	if ($i != $page) echo '<a href="?search=Search&finda='.$find.'&field='.$field.'&page='.$i.'"> '.$i.' </a>';
	else echo " $i ";
}
if ($page < $tot_pages) echo '<a href="?search=Search&finda='.$find.'&field='.$field.'&page='.($page + 1).'"> NEXT </a>';
else echo ' NEXT ';
} else die('error');
?>

what is output

yandoo · July 5, 2007

Hi,

Output is:

SELECT count(*) FROM teachersname WHERE DepartmentCode LIKE '%eng%' LIMIT 0 2

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1

This 2 has to be related to the limit?? As for line 1, it makes no sense as line 1 doesnt contain an numbers on the page.

Thanks

sasa · July 6, 2007

ok i see mistakes

1st change line

$lm = ' LIMIT '.(($page - 1) * $limit).' '.$limit;

to

$lm = ' LIMIT '.(($page - 1) * $limit).', '.$limit;

add comma

2nd change line

echo $qve_data = "SELECT count(*) FROM teachersname".$where.$lm;

to

echo $qve_data = "SELECT * FROM teachersname".$where.$lm;

remove count

yandoo · July 6, 2007

Hi there,

I owe you a BIG thank you for all your help and support on this.

Im fantastically please to say that it work...it works....shes live....alive!!!!

Thanks ever so much your a star and really have made my year!!!

Thank You

Tom

p.s. a celebratory cigerrette is now in order!!!

Sign In

[SOLVED] Page navigation with seach results - make my page navigation work... please

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information