[bexlhoward1]

Hi, I need to limit a set to search results to be 10 per page. The problem is I am using MS Access for the database (clients request!).

Does anyone know how I can do this as all the tutorials I have found have been for PHP with mySQL.

Thanks

Rebecca.

[skyhawk133]

How many records total in the database? I'm thinking if there aren't thousands you could programatically do the pagination rather than trying to accomplish it with SQL commands.

[bexlhoward1]

Hi, no there are not thousands, only about 300!

What do you suggest?

[skyhawk133]

I was thinking of just looping through the query result (an array) and starting at position x, ending at y... depending on your query, you should be able to use the TOP function in the SQL to limit the number of records you pull back, but that only selects the first however many records.

Also, depending on how your primary/unique key is setup, you can often times do a WHERE id >= x AND id <= y where x and y are your ranges. Problem with that is, they must be in order and if you skip ID's you'll end up with less than the number of records you want.

[skyhawk133]

Here is an example.


Say you have your page numbers and you're doing 10 records per page, you would have something like this:

<- Previous | 1 | 2 | 3 | Next ->

Each link would have ?start=x&end=y with 1,10;11,20;21,30 respectively. Then in your loop in the PHP you would loop from $start to $end.

[snoj]

Behold, pagation with SQL Commands (with a little help from PHP).

Not sure about MS Access...but...here is a SQL example. (Not sure if it's standard SQL though. It may have MySQL elements.) I use a similar system for my blog archives at josherickson.org.

$page = (int) (isset($_GET['page'])) ? $_GET['page'] : 1;
$limit = 10;
$offset = $limit*($page-1);

$query = "SELECT * FROM table WHERE 1 ORDER BY `id` ASC LIMIT {$limit} OFFSET {$offset};";

So if $page equals 1, then we will get back rows 0-9 (e.g. entries 1-10).

This post has been edited by hotsnoj: 01 June 2005 - 04:01 PM

[skyhawk133]

Yeh, there is no LIMIT command in MS SQL if I remember correctly, we have the same damn problems at work all the time.

[snoj]

Ahh, I see. But yeah, it's just an outline, so to speak, of how it's done with SQL commands.

http://docs.codechar...ptimizeSQL.html ?

[bexlhoward1]

Hi, i like the idea of the SQL, I cannot not however get the limiting to work with the offset I have:

$page = (int) (isset($_GET['page'])) ? $_GET['page'] : 1;
$limit = 10;
$offset = $limit*($page-1);

$strSQL = "SELECT TOP $limit,$offset * FROM property.......

If I take out the $offset in the SQL statement then my results are limited to 10 but if I leave it in I get all the records with no limits!

Thanks

Rebecca.

[bexlhoward1]

Another problem is that my ids are autonumber and not in order so that rules out the array doesnt it?

[snoj]

Not necessarily. Your array index can be seperate from your database index.

[bexlhoward1]

I think maybe using an array would be the way. How do I pass the contents of the record set to an array?

Also how would I go about counting the number of rows? I tried using COUNT in the SQL statement but cannot get the result to print in a variable.

[Amadeus]

COUNT is definitely the way to go, although if you are using MS ACCESS, you could also use a recordset object to store the results, and simply access the number through the recrodset.count property.

What code are you using and not getting the count?

[bexlhoward1]

I was probably doing it completly wrong as I have never used it before:

$strSQL = "SELECT * FROM property WHERE propertytype = '".$propertyrequired."' AND area='".$area."' AND bedrooms = '".$bedrooms."' AND price <= '".$budget."' ORDER BY '".$address3."'";

$resultcount = "SELECT COUNT(propertyID) FROM property WHERE propertytype = '".$propertyrequired."' AND area='".$area."' AND bedrooms = '".$bedrooms."' AND price <= '".$budget."' ORDER BY '".$address3."'";

So what I have tried to do is run the first SQL statement to get the results then run the second based on the first to count the results. When I run the second the code is:

$rs2 = $conn->execute($resultcount);

When I try to print the value of $rs2 I just get 'Object' rather than the value.

I am sure I am just approaching this the wrong way!

Thanks

Bex.

[skyhawk133]

Rather than running a second query, you can actually use a built in function in PHP that will return the number of rows in a result set:

http://us2.php.net/m...ql-num-rows.php

You'll probably wanna use the mysql_fetch_array() function to bring back everything in to an array, then loop through to do your pagination.