Multiple Search Result Pages

[Lauj]

I have created a PHP search page; however, the result was too long and a user has to scroll down forever. I wanted to list those results in pages down on the bottom of the page instead of scrolling down for the rest of the results. If the result generated from the search page meets a certain length then generate the number of pages or the "Next" to jump to the next page instead of scrolling.

If anyone has any idea, please help. Lots of thanks in advance!

Chong

[theeper]

Use a LIMIT statement in your SQL search query. Then build the query based off that limit. Your first query would look something like this ...

SELECT * FROM mytable WHERE something='%searchword%' LIMIT 0, 9

Then the next one would look like this ...

SELECT * FROM mytable WHERE something='%searchword%' LIMIT 10, 19

This would give you 10 results per page.

[Lauj]

Thank you very much,Theeper. I was able to put a limit on it, but my other problem is how do I generate the additional pages automatically and list the link on the bottom of the page? For example, if a user click on Next or Page 2, how will the php script generate that page automatically?

Again, thank for your help.

Chong

[theeper]

Basically, you need to create a loop to figure out the two values in the LIMIT clause.

If your select query looks like this ...

SELECT * FROM mytable WHERE something='something' LIMIT $start, $end

You will specify an offset value which sets how many results per page will display. Then your loop will print the links for each page. Add the offset value to the start and end variables to determine which results are printed on each page.

If your query returns 25 matches and you want to display 10 per page, you will need three pages of results.

Divide your total results by the offset value and round up for this. Do something like this ...

$pages = ceil($results/$offset);

Then run your loop '$pages' times and create a new link for each of your results pages.

[Lauj]

Again thanks, Theeper. Here's my situation. My SQL query is to select records from a range of years.

Here's the for loop:

for($i = $yrFrom; $i <= $yrTo; $i++){
$query = "select distinct * from emailadd where Fyear = $i";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
$counter += $num_results;
}

For example, I want to find people in the database from 1936 to 1950 but some years in between this two years have no records. For instance the year 1937, 1938, 1939, and 1945 are not even recorded in the database if there is no record pertain to that year. So I have two "for" loops. One, to check if it is a valid date that exist in the database. The second loop is to print the record if the year exist in the database. So if I put a "limit" say, 0, 10, on my select query, the result will be ten records per year and not per page, and imagine there are 15 records or so in each of the year that found.

Now, I was able to find the total records found for all the years that exist and devided by the #offset to get the total pages. However, I'm not quite understand about how to run the "for" loop again to generate each of those individual pages. For example, I click on page 3, what page or link is page 3 points to to generate the records that are suppose to be in page 3? Another problem I have is how do I keep track of those records or the years that have those records if for instance a user click on page 1 again. The user should be able to come back to the previous page and expect all the records that suppose to be in the previous page to be there.

Sorry for taking too much of your time, but I'm quite new to MYSQL and PHP.

Thanks a lot!

Chong

[Lauj]

I just discovered that the parameter I pass by:

echo "a href=\"$self?offset=$offset\">Next 5</a>";

don't get pass to the select query. In fact nothing changes when I clicked the Next 5 link on the page. Will someone help me why I can't get these parameter to pass to the variable in the script or select query?

Many thanks in advance!

Lauj

[theeper]

Change this line ...

echo "a href=\"$self?offset=$offset\">Next 5</a>";

to this ...

echo "a href=\"$PHP_SELF?offset=$offset\">Next 5</a>";

also, make sure you are getting your value like this

$_POST["offset"]

if you have register_globals = off

[Lauj]

Thanks Theeper. The problem I have right now it seemed that whatever I passed from the line:

echo "<a href=\"$self?s=$year&q=$var\">Next 5</a>"

never replaces this part at the top:

$var = @$POST['q'] ;
$trimmed = trim($var);
$self = $SERVER['PHP_SELF'];

The variable "q" will come back with a value of "0" when I click the link "Next 5" on the page to load the next five records. I couldn't make the variable from the link to pass through or carry through the value when I click the link to load the next five records.

Many thanks for the help.

Lauj

[theeper]

oops, sorry, you are doing a GET request, not a POST.

Access your variables like this instead ...

$var = $_GET['q'] ;

[Lauj]

So even if in the input.html file form method I use POST, I can still use $_GET["q"] in the search.php file to retrieve the variable?

I have tried changing the $POST["q'] to $GET["q"] and I get no result because the value "q" is zero.

Lauj

[theeper]

There won't be a value set for $q if it is an HTML page.

It needs to be a PHP script.

Also, you're doing a get request not a post when you click a link.

[Lauj]

I have changed the "input" file to .shtml and the "search" file is a .php and contain $var = $_GET['q'] but still no result when click the link.

Here is my input.shtml file:

<body>
<form name="form" action="search.php" method="GET">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>
</body>
</html>

And here is my search.php file:

<?php

// Get the search variable from URL
$var = $GET['q'];
$trimmed = trim($var);
$self = $SERVER['PHP_SELF'];

// rows to return
$limit=5;

// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}

// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}

mysql_connect("localhost", "", "");

//specify database EDIT REQUIRED HERE
mysql_select_db("test") or die("Unable to select database"); //select which database we're using

// Build SQL Query

$query = "select * from yrtest where Year like \"%$trimmed%\"

order by Year"; // EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

// begin to show results set
//echo "Results";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$crntYear = $row["Year"];
$state = $row["Condition"];

echo "Year is: ".$crntYear."&nbsp;&nbsp;Condtion is: ".$state;
$count++ ;
}
$currPage = (($s/$limit) + 1);

//break before paging
echo "<br />";

// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
echo "Previous is: ".$prevs."<br>";
echo "<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;Prev 5</a>";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$year=$s+$limit;
echo "<br>";
echo "<a href=\"$self?s=$year&q=$var\">Next 5 &gt;&gt;</a><br>";
}
?>

Thank you so much for your help.

Lauj

[Lauj]

I found out that the problem why my code is not working correctly has to do with some settings in my PHP. Somehow the GET is not able to pass the parameter from the URL Next 5 >> link back into the page. The PHP version I use is 4.2.3 and I'm not sure where to correct this problem. If anyone who has an idea where this problem lies, please tell me the steps to fix this problem.

Many thanks in advance!

Lauj

[Lauj]

The problem I have right now is that the parameter is not passing through a URL query such as when clicking on a link to load the next 5 records from the database. The page will not pull the next 5 records. My guess is that I might have some settings incorrect. The problem is that I don't know where. If anyone can help me, here is the link to my phpinfo page.

http://www.uwstout.edu/temp/foundation/alumni/phpinfo.html

Thank you very very much!

Lauj

[Lauj]

I found out that it is a setting in the PHP that I have it incorrect. I gave up on trying to find out what the problem is. The campus PHP engine is working and I'm using that; however, I am back to my initial question again. That is how do I write a script that will search the database for records from a range of years. Each year will contain some records and some year may not. If I use the following select statment:

$limit=10;
for($i = $yrFrom; $i <= $yrTo; $i++){
$query = "select distinct * from email where Fyear = $i order by Fname LIMIT $page, $limit";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
}

Then for example, if year 1940 has 15 records, 1946 has 20 records, 1950 has 22 records and any year between 1940 and 1950 that does not have any records will not show anything. The problem is, the "LIMIT" show 10 records per year and not per page. So therefore, there will be a total of 30 records in one page.

Here's the url to this page I'm testing.

http://www.uwstout.edu/temp/foundation/alumni/emlSearch_test.shtml

Valid years are from 1936 to 1970.

If anyone has an idea, please help me out.

Thanks!

Lauj