Search +Paginate results

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

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

Search +Paginate results

Post by Jim_Bo »

Hi,

Im trying to search a table and bring back paginated results .. Here is the code I have been playing with .. but have had no luck in gettin it to work ..

Code: Select all

<form name="form" method="post" action="search.php">
  <input type="text" name="textfield">
  <input type="submit" name="Submit" value="Search">
</form>

<?php

// Database Connection 

require 'db.php';

// If current page number, use it 
// if not, set one! 

if(!isset($_GET&#1111;'page']))&#123; 
    $page = 1; 
&#125; else &#123; 
    $page = $_GET&#1111;'page']; 
&#125; 

// Define the number of results per page 
$max_results = 1; 

// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results 

$sql = mysql_query("SELECT * FROM users WHERE (user_name LIKE('$search%') OR first_name LIKE('%$search%')) ORDER BY first_name LIMIT $from, $max_results"); 

while($row = mysql_fetch_array($sql))&#123; 
    
    $user_name = $row&#1111;"user_name"]; 
	$first_name = $row&#1111;"first_name"]; 

echo "<table width="100%" height="" border="1" align="center" cellpadding="5" cellspacing="1" bordercolor="#000000">
  <tr> 
    <td width="164" rowspan="4"> <div align="center"><font size="1" face="verdana">$picture</font></div></td>
   
  </tr>
  <tr> 
    <td width="189" height="20" div align="center">
<div align="left"><font size="1" face="verdana">$user_name</font></div></td>
    <td height="20" align="center" div>
<div align="left"><font color="#FF0000" size="1" face="verdana">$first_name $last_name </font></div></td>
  </tr>
</table>
<br>";

&#125; 

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM users"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

/* Build Page Number Hyperlinks 
Build Previous Link */ 

if($page > 1)&#123; 
    $prev = ($page - 1); 
    echo "<a href="".$_SERVER&#1111;'PHP_SELF']."?page=$prev"><<Previous</a>&nbsp;"; 
&#125; 

for($i = 1; $i <= $total_pages; $i++)&#123; 
    if(($page) == $i)&#123; 
        echo "$i&nbsp;"; 
        &#125; else &#123; 
            echo "<a href="".$_SERVER&#1111;'PHP_SELF']."?page=$i">$i</a>&nbsp;"; 
    &#125; 
&#125; 

// Build Next Link 
if($page < $total_pages)&#123; 
    $next = ($page + 1); 
    echo "<a href="".$_SERVER&#1111;'PHP_SELF']."?page=$next">Next>></a>"; 
&#125; 
echo "</center>"; 

?>
What am I missing out?

Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

output? errors?

whats the problem?
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

No errors .. When the page is viewed .. all the users in the table show .. the pagination works fine .. But when you search .. nothing seems to exicute .. the page reloads with all users still being displayed ..

I trying to have it display all records .. then after a search is exicuted have it only display the search results .. and paginate if needed ..

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$search isn't set anywhere.. nor could it come in from the form you have on the page for "searching"
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Anymore tips? ...

Not sure where the variables need to be etc etc .. Never worked with search querys before .. Not really to sure what im doing here ..

Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

As feyd said, you never define $search.... unless register globals is on which is off by default.

Also, your input field name should be changed to search.

Back to your query,

Code: Select all

$sql = mysql_query("SELECT * FROM users WHERE (user_name LIKE('%$search%') OR first_name LIKE('%$search%')) ORDER BY first_name LIMIT $from, $max_results");
the % was missing before $seach, and also before the query, do something like

Code: Select all

$search = (empty($_POST&#1111;'search']) ? $_POST&#1111;'search'] : '');
edited to fix parse error
Last edited by John Cartwright on Mon Feb 28, 2005 4:23 pm, edited 1 time in total.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Made the changes ..

Code: Select all

$search = (empty($_POST&#1111;'search'] ? $_POST&#1111;'search'] : '');
Gave me error .. but the following worked ..

Code: Select all

$search = $_POST&#1111;'search'];
\

It seems when a search is performed .. the matching record comes to the top of the list .. but all the other records show .. how do you have it so only the search results show ..

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

Post by Jim_Bo »

Hi,

If I use:

Code: Select all

$search = (empty($_POST&#1111;'search']) ? $_POST&#1111;'search'] : '');
The search doesnt work .. It shows all the records ..

If I use:

Code: Select all

$search = $_POST&#1111;'search'];
The search works to a certain extent .. If I have the max records set to 2 .. and I perfom a search .. and there is 1 match .. it displays the 1 match .. but also has links to page 1,2,3 .. etc ..

If there are 2 records that match .. it shows both but also has the page links ..

What needs to change so if there is 1 match .. have it display the 1 match with page 1 showing .. but not links to page 2,3,4 etc .. but if there is 3 matches .. then have it show 2 on page one then a link to page 2 with the 3rd match ..

Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

wow really sorry,

Code: Select all

$search = (!empty($_POST&#1111;'search']) ? $_POST&#1111;'search'] : '');
Basically, as long as the form is submitted with the search field filled in, it will perform the search, other than that should display all results until form is submitted
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Thanks .. that works now .. But after a search it stills paginates all the records .. I want it so after you submit a search .. it only paginates the records that match the search query .. and doesnt display the rest ..


Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the search information is not passed from page to page. The number of results isn't restricted to searched data either.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Is this correct?

Code: Select all

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM users WHERE (user_name LIKE('%$search%') OR first_name LIKE('%$search%'))"),0);
?

Im not sure how to parse the search variable thru the pages ?

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you can... pass it through the url, or via sessions, or a cookie.. there's many ways..
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Yer still not quite clear on exactly how ..

Is it something like:

Code: Select all

if($page < $total_pages)&#123; 
    $next = ($page + 1); 
    echo "<a href="".$_SERVER&#1111;'PHP_SELF']."?page=$search&$next">Next</a> >>";
?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm not being clear because there's nothing to be clear about here. You can do it in whatever fashion you wish. It's up to you, not me.
Post Reply