Splitting a DB into 2 web pages.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Splitting a DB into 2 web pages.

Post by impulse() »

If I have a DB that over 300 entries and I want a page that prints them all out, but the page is starting to take a while to load, what steps can I take to split this DB into 2 web pages?

I imagine something along the lines of counting all the rows in the DB, using a IF statement to check if the amount of rows are above 100 (for example) and if they are above 100 to overwrite the current MySQL query to something like

Code:
SELECT * FROM somewhere WHERE id < '100'


And build on it all from there?

If you can think of a more efficiant way to do it, then please share.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

first page: SELECT * FROM somewhere LIMIT 0,100
second page: SELECT * FROM somewhere LIMIT 100
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

One way to do this would be to use a parameter in the URL to store the page number you are at:

eg: http://www.mydomain.com/mypage.php?page=4

Then, use this $_GET parameter in your page to change the LIMIT value of the query:

Code: Select all

if(!isset($_GET['page'])) {
    $page_num = 1;
} else {
   $page_num = $_GET['page']; //don't forget to clean/sanitise this input!!!
}

define("RECORDS_PER_PAGE", 100); // can be done elsewhere

$starting_offset = ($page-1) * RECORDS_PER_PAGE;

$query = "SELECT * FROM my_table LIMIT ".$starting_offset.", ".RECORDS_PER_PAGE;

//execute the query, display the results etc.
You can put forward and backwards links on the page that reload the page, incrementing and decrementing the page number.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

GM wrote:One way to do this would be to use a parameter in the URL to store the page number you are at:

eg: http://www.mydomain.com/mypage.php?page=4

Then, use this $_GET parameter in your page to change the LIMIT value of the query:

Code: Select all

if(!isset($_GET['page'])) {
    $page_num = 1;
} else {
   $page_num = $_GET['page']; //don't forget to clean/sanitise this input!!!
}

define("RECORDS_PER_PAGE", 100); // can be done elsewhere

$starting_offset = ($page-1) * RECORDS_PER_PAGE;

$query = "SELECT * FROM my_table LIMIT ".$starting_offset.", ".RECORDS_PER_PAGE;

//execute the query, display the results etc.
You can put forward and backwards links on the page that reload the page, incrementing and decrementing the page number.
Looking at this part ($starting_offset = ($page-1) * RECORDS_PER_PAGE; ):

Where is the value defined for "$page-1" ?

I like the idea of how this works though, it's interesting.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

sorry - you are right, should have been "$page_num-1"

I wrote it off the top of my head, so it's not tested or anything.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

If it's off the top of your head I'll question a little further as there's something else I don't understand. :)
$query = "SELECT * FROM my_table LIMIT ".$starting_offset.", ".RECORDS_PER_PAGE;
If RECORDS_PER_PAGE is an unchangable 100, when it creates a link on page 2 to goto page 3, wont the query look like:

Code: Select all

SELECT * FROM my_table LIMIT 200, 100
To the server, causing it to display records between 200-100?

Regards,
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

LIMIT x,y means start at record x, and display y records.

LIMIT 100 is the same as LIMIT 0,100

LIMIT 200,100 means "start at record 200, and show me the next 100 records".

'tis all in the Manual.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

You'll have to excuse my presumptions.

Thank you for the help.

Stephen,
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

No probs :)
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

That has worked an absolute treat!

I'll be using that in the future.
Post Reply