Splitting a Search into Pages

[Alec]

After trial, error, 2 days reading about PHP and some fine Single Malt,
have finally created a search from a database with multiple criteria as
below.

NOTE comments on tidying the code up are welcomed.

BUT.... if I want to split the search into groups of 10 and then (like
Google) prompt for page 2 of 10, 3 of 10 etc.

Anyone know of any tutorials on this?
Many thanks

Alec

<?php
$result = @mysql_query ("SELECT name, townname, town, townid, photo
FROM site01_towns, site01_details WHERE townname='$townsearch' AND
town=townid AND code='sleep'");
while ($row = mysql_fetch_array($result))
{
$photoname = $row['photo'];
$company = $row['name'];
?>

<div align="center">
<center>
<table border="0" cellpadding="0" cellspacing="0" width="400"
height="95">
<tr>
<td valign="top"><?php echo '<p>' . $row['name'] . '</p>'; ?></td>
<td valign="middle" width="140"><img src="images/<?php print
$photoname; ?>" border="0"/></td>
</tr>
</table>
</center>
</div>

<?php
}
?>

[frizzle]

What you need is 'LIMIT' in your query.
Limit CAN exist of (afaik) 2 variables:

"LIMIT 20, 10" would mean the query
skips the first 19 results, and then returns
the following 10.

So search for info about "LIMIT".

Greetings Frizzle.

[comp.lang.php]

That would only produce the "first 10 rows", but how would he then tell
the person "next 10 of 100" or however many rows he ultimately
produced?

Best solution would be for him to write a Pagination code routine. I
did just that, basically stuffing the entire resultset into a $_SESSION
variable and parsing it using integer values to only display "first 10
rows", "rows 20 - 29", etc.

Phil

[chadedge]

1. look at PEAR. There's a good pagination class for doing just that. I
found it through "Object Oriented PHP Solutions - Volume 1"
(sitepoint).

2. You could do 2 queries: query1 = COUNT (gets you the number of total
rows selected); query2 = SELECT with LIMIT (select the rowset you need;
use the count++10;).

[Alec]

So the key is 'pagination'.

Have absolutely no idea what that is (sorry am totally new to this
stuff), but have found the link

http://bfl.rctek.com/source/?file=in...pagination.php

Have not tried this as yet, but is this what you mean?

Many thanks

Alec

[chadedge]

That link won't help you too much. That include requires explicit page
numbering (you can get that by doing a COUNT query ("SELECT COUNT(*)
FROM [tablename] WHERE [?]"). This will give you a result as an integer
you can then test against.

Let's say the result of the count query is 22 (22 rows). You want to
display 5 rows/page. You then take your COUNT query and pass its result
along to your second query (the RESULT query) to get the current
appropriate rows.

I still think you should check out PEAR: Pager
(http://pear.php.net/package/Pager).

The learning curve will hurt, but not very much. There's good support
for PEAR packages, and your host may already have them installed.

[basement_addict]

> BUT.... if I want to split the search into groups of 10 and then (like

Google) prompt for page 2 of 10, 3 of 10 etc.

Here is the way I go about it. It might work for you.

//constant number of results per page.
$pagesize=25

//start at 0 then pass in $page=[some number] in the url.
$currentpage=3

//calculate number to skip by multiplying what page you are on
//by the page size.
$skip = $currentpage * $pagesize

//use calc_found_rows in mysql select and mysql will calculate
//the result count regardless of the limit in the sql

select calc_found_rows * from table where limit $skip, $pagesize

//use "select found_rows()" after running the above query
//and assign to $totalcount

$totalcount=150 // assigned from "select found_rows()"

Take $totalcount (150 in this case) divided by $pagesize (25) to get
the max number of pages to display on the page (6 in this case).

Create links on the webpage that pass in the page number.

1 2 3 4 5 6

example url

<a href="/webpage.php? ... &page=5">5</a>
where ... is the query string of what the user was searching on

This is not complete but should be enough to get you going somewhere.

[Alec]

Sorry guys but have now come a little unstuck with this after trying
some of the methods.

Its only day 6 of learning php......

So back to basics. The page below allows a user to type in a name of a
town, and search for companies from this town with certain criteria
(using two tables to cross check the town name).

What exactly do I need to split this search into groups of say 5
companies.

Sorry guys, but this is early days for me.... Thanks in advance

Alec

<title>New Page 1</title>
</head>

<body>

<?php
$connection = mysql_connect('localhost', 'uks49179', 'FWE4872xd');
$db = mysql_select_db("test", $connection);
?>

<?php $townsearch = $_GET['town']; ?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<p align="center">
<input type="text" name="town" size="20" value="<?php echo
$townsearch; ?>" />&nbsp;
<input type="submit" value="GO" />
</form>

<?php
$result = @mysql_query ("SELECT name, townname, town, townid FROM
site01_towns, site01_details WHERE townname='$townsearch' AND
town=townid AND code='kids'");
while ($row = mysql_fetch_array($result))
{
$company = $row['name'];
echo $company;
}
?>

</body>

</html>