Pagination in PHP & MS Access

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 9384 Views - Last Post: 06 June 2005 - 04:19 PM Rate Topic: -----

#1 bexlhoward1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 04-February 05

Pagination in PHP & MS Access

Posted 01 June 2005 - 10:25 AM

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.
Is This A Good Question/Topic? 0
  • +

Replies To: Pagination in PHP & MS Access

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1876
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 11:34 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 bexlhoward1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 04-February 05

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 12:40 PM

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

What do you suggest?
Was This Post Helpful? 0
  • +
  • -

#4 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1876
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 01:01 PM

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.
Was This Post Helpful? 0
  • +
  • -

#5 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1876
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 01:04 PM

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.
Was This Post Helpful? 0
  • +
  • -

#6 snoj  Icon User is offline

  • Married Life
  • member icon

Reputation: 84
  • View blog
  • Posts: 3,564
  • Joined: 31-March 03

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 03:57 PM

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

Was This Post Helpful? 0
  • +
  • -

#7 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1876
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 04:02 PM

Yeh, there is no LIMIT command in MS SQL if I remember correctly, we have the same damn problems at work all the time.
Was This Post Helpful? 0
  • +
  • -

#8 snoj  Icon User is offline

  • Married Life
  • member icon

Reputation: 84
  • View blog
  • Posts: 3,564
  • Joined: 31-March 03

Re: Pagination in PHP & MS Access

Posted 01 June 2005 - 04:12 PM

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 ?
Was This Post Helpful? 0
  • +
  • -

#9 bexlhoward1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 04-February 05

Re: Pagination in PHP & MS Access

Posted 02 June 2005 - 02:23 AM

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.
Was This Post Helpful? 0
  • +
  • -

#10 bexlhoward1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 04-February 05

Re: Pagination in PHP & MS Access

Posted 02 June 2005 - 03:33 AM

Another problem is that my ids are autonumber and not in order so that rules out the array doesnt it?
Was This Post Helpful? 0
  • +
  • -

#11 snoj  Icon User is offline

  • Married Life
  • member icon

Reputation: 84
  • View blog
  • Posts: 3,564
  • Joined: 31-March 03

Re: Pagination in PHP & MS Access

Posted 02 June 2005 - 01:39 PM

Not necessarily. Your array index can be seperate from your database index.
Was This Post Helpful? 0
  • +
  • -

#12 bexlhoward1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 04-February 05

Re: Pagination in PHP & MS Access

Posted 02 June 2005 - 04:09 PM

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.
Was This Post Helpful? 0
  • +
  • -

#13 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Pagination in PHP & MS Access

Posted 04 June 2005 - 05:52 AM

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?
Was This Post Helpful? 0
  • +
  • -

#14 bexlhoward1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 04-February 05

Re: Pagination in PHP & MS Access

Posted 05 June 2005 - 08:44 AM

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.
Was This Post Helpful? 0
  • +
  • -

#15 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1876
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Pagination in PHP & MS Access

Posted 05 June 2005 - 08:53 AM

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.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2