Programming Help

Web Development



What's Here?

  • Members: 132,619
  • Replies: 471,716
  • Topics: 73,136
  • Snippets: 2,541
  • Tutorials: 667
  • Total Online: 999
  • Members: 52
  • Guests: 947

Who's Online?

Welcome to Dream.In.Code
Getting PHP Help is Easy!

Join 132,619 PHP Programmers for FREE! Get instant access to thousands of PHP experts, tutorials, code snippets, and more! There are 999 people online right now. Registration is fast and FREE... Join Now!




Pagination in PHP & MS Access

2 Pages V  1 2 >  
 Reply to this topicStart new topic

Pagination in PHP & MS Access
bexlhoward1
post 1 Jun, 2005 - 09:25 AM
Post #1


New D.I.C Head

*
Joined: 4 Feb, 2005
Posts: 40
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.
User is offlineProfile CardPM

Go to the top of the page

skyhawk133
post 1 Jun, 2005 - 10:34 AM
Post #2


Head DIC Head

Group Icon
Joined: 17 Mar, 2001
Posts: 14,846



Thanked 45 times

Dream Kudos: 1650

Expert In: Web Development

My Contributions
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.
User is offlineProfile CardPM

Go to the top of the page

bexlhoward1
post 1 Jun, 2005 - 11:40 AM
Post #3


New D.I.C Head

*
Joined: 4 Feb, 2005
Posts: 40
Hi, no there are not thousands, only about 300!

What do you suggest?
User is offlineProfile CardPM

Go to the top of the page

skyhawk133
post 1 Jun, 2005 - 12:01 PM
Post #4


Head DIC Head

Group Icon
Joined: 17 Mar, 2001
Posts: 14,846



Thanked 45 times

Dream Kudos: 1650

Expert In: Web Development

My Contributions
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.
User is offlineProfile CardPM

Go to the top of the page

skyhawk133
post 1 Jun, 2005 - 12:04 PM
Post #5


Head DIC Head

Group Icon
Joined: 17 Mar, 2001
Posts: 14,846



Thanked 45 times

Dream Kudos: 1650

Expert In: Web Development

My Contributions
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.
User is offlineProfile CardPM

Go to the top of the page

snoj
post 1 Jun, 2005 - 02:57 PM
Post #6


$Null

Group Icon
Joined: 31 Mar, 2003
Posts: 3,304



Thanked 5 times

Dream Kudos: 700
My Contributions
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.

CODE
$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: 1 Jun, 2005 - 03:01 PM
User is offlineProfile CardPM

Go to the top of the page

skyhawk133
post 1 Jun, 2005 - 03:02 PM
Post #7


Head DIC Head

Group Icon
Joined: 17 Mar, 2001
Posts: 14,846



Thanked 45 times

Dream Kudos: 1650

Expert In: Web Development

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

Go to the top of the page

snoj
post 1 Jun, 2005 - 03:12 PM
Post #8


$Null

Group Icon
Joined: 31 Mar, 2003
Posts: 3,304



Thanked 5 times

Dream Kudos: 700
My Contributions
Ahh, I see. But yeah, it's just an outline, so to speak, of how it's done with SQL commands.

http://docs.codecharge.com/studio/html/ind...ptimizeSQL.html ?
User is offlineProfile CardPM

Go to the top of the page

bexlhoward1
post 2 Jun, 2005 - 01:23 AM
Post #9


New D.I.C Head

*
Joined: 4 Feb, 2005
Posts: 40
Hi, i like the idea of the SQL, I cannot not however get the limiting to work with the offset I have:

CODE

$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.
User is offlineProfile CardPM

Go to the top of the page

bexlhoward1
post 2 Jun, 2005 - 02:33 AM
Post #10


New D.I.C Head

*
Joined: 4 Feb, 2005
Posts: 40
Another problem is that my ids are autonumber and not in order so that rules out the array doesnt it?
User is offlineProfile CardPM

Go to the top of the page

snoj
post 2 Jun, 2005 - 12:39 PM
Post #11


$Null

Group Icon
Joined: 31 Mar, 2003
Posts: 3,304



Thanked 5 times

Dream Kudos: 700
My Contributions
Not necessarily. Your array index can be seperate from your database index.
User is offlineProfile CardPM

Go to the top of the page

bexlhoward1
post 2 Jun, 2005 - 03:09 PM
Post #12


New D.I.C Head

*
Joined: 4 Feb, 2005
Posts: 40
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.
User is offlineProfile CardPM

Go to the top of the page

2 Pages V  1 2 >
Fast ReplyReply to this topicStart new topic
Time is now: 11/23/08 03:16AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live PHP Help!

PHP Tutorials

Reference Sheets

PHP Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month