Hi All

I am developing a web site that interacts with a MySQL database. When I run
a query and display the records in a web page, this can become a problem
because there might be too many records to be displayed in one single web
page so I am trying to divide the total number of records and display them
in multiple pages. Let's say, if the query returns 100 records then I would
like to display the first 10 records and then put a navigation bar where I
can go to the next 10 or return to the previous 10 records

Is this done at a SQL or PHP level ? any experience on doing this ?

Thanks a bunch
Eduardo

On 5/27/07, Eduardo Vizcarra <EMAIL REMOVED> wrote:
> Hi All
>
> I am developing a web site that interacts with a MySQL database. When I run
> a query and display the records in a web page, this can become a problem
> because there might be too many records to be displayed in one single web
> page so I am trying to divide the total number of records and display them
> in multiple pages. Let's say, if the query returns 100 records then I would
> like to display the first 10 records and then put a navigation bar where I
> can go to the next 10 or return to the previous 10 records
>
> Is this done at a SQL or PHP level ? any experience on doing this ?
>
> Thanks a bunch
> Eduardo

Ik could be done with both, you could use the LIMIT option in your SQL
code, or just fetch all and only output 10 (using php).
I would prefer the first one, as it won't send all records, but only
the ones you need. This save some time when interacting with the
database. Of course you won't note the difference with 100-200 pages,
but when it becomes more and more (think about atleast 10000+) then
you probably will note the speed difference.

Tijnema

Eduardo,
The best way to do it is on both a php and sql level. Use sql's limit
keyword in your query to return only the amount of records you want. For
instance, page 1 will have LIMIT 0, 9 in the query. Page 2 will have LIMIT
10, 19. Then you can simply use *_fetch_array and a foreach(maybe) to
iterate through all the values. If you want to have the the amount of
records dynamically generate then either have a asdf.php?min=0&max=9 or just
do min = (page * 10) - 10 and max = (page * 10) - 1. Many different ways to
do this. However I find this one to be the most efficient.

Hope this helped.

Brian Seymour
AeroCoreProductions
http://www.aerocore.net/

-----Original Message-----
From: Eduardo Vizcarra [private.php?do=newpm&u=]
Sent: Saturday, May 26, 2007 7:18 PM
To: php-EMAIL REMOVED
Subject: [PHP] Too many records to display in one web page

Hi All

I am developing a web site that interacts with a MySQL database. When I run
a query and display the records in a web page, this can become a problem
because there might be too many records to be displayed in one single web
page so I am trying to divide the total number of records and display them
in multiple pages. Let's say, if the query returns 100 records then I would
like to display the first 10 records and then put a navigation bar where I
can go to the next 10 or return to the previous 10 records

Is this done at a SQL or PHP level ? any experience on doing this ?

Thanks a bunch
Eduardo

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

At 6:17 PM -0500 5/26/07, Eduardo Vizcarra wrote:
>Hi All
>
>I am developing a web site that interacts with a MySQL database. When I run
>a query and display the records in a web page, this can become a problem
>because there might be too many records to be displayed in one single web
>page so I am trying to divide the total number of records and display them
>in multiple pages. Let's say, if the query returns 100 records then I would
>like to display the first 10 records and then put a navigation bar where I
>can go to the next 10 or return to the previous 10 records
>
>Is this done at a SQL or PHP level ? any experience on doing this ?
>
>Thanks a bunch
>Eduardo

Eduardo:

Yes, look into "LIMIT" in MySQL.

Such as:

$dbQuery="SELECT $id FROM $dbtable ORDER BY title LIMIT $offset, $rowsPerPage";

Cheers,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

On May 27, 5:17 am, evizca...@iteso.mx ("Eduardo Vizcarra") wrote:
> Hi All
>
> I am developing a web site that interacts with a MySQL database. When I run
> a query and display the records in a web page, this can become a problem
> because there might be too many records to be displayed in one single web
> page so I am trying to divide the total number of records and display them
> in multiple pages. Let's say, if the query returns 100 records then I would
> like to display the first 10 records and then put a navigation bar where I
> can go to the next 10 or return to the previous 10 records
>
> Is this done at a SQL or PHP level ? any experience on doing this ?
>
> Thanks a bunch
> Eduardo

Eduardo,

Here you go, read this article.

http://www.phpfreaks.com/tutorials/43/0.php

Brian Seymour wrote:
> Eduardo,
> The best way to do it is on both a php and sql level. Use sql's limit
> keyword in your query to return only the amount of records you want. For
> instance, page 1 will have LIMIT 0, 9 in the query. Page 2 will have LIMIT
> 10, 19. Then you can simply use *_fetch_array and a foreach(maybe) to
> iterate through all the values. If you want to have the the amount of
> records dynamically generate then either have a asdf.php?min=0&max=9 or just
> do min = (page * 10) - 10 and max = (page * 10) - 1. Many different ways to
> do this. However I find this one to be the most efficient.

problem with your example.

Limit is not used that way

Check out this page for further explanation

http://dev.mysql.com/doc/refman/5.0/...html#id3064319


Here is something I whipped together for this as an example


<?php

$page = 1;
$rowsPerPage = 10;
$start = 0;

if (
isset($_GET['page']) &&
is_numeric($_GET['page']) &&
$_GET['page'] >= $page
) {
$start = ($rowsPerPage * floor($_GET['page']) );
}

$SQL = "select * from table LIMIT {$start}, {$rowsPerPage}"

display results...

?>

Let me know if I completely confused you. I am often confusing...

Jim Lucas

>
> Hope this helped.
>
> Brian Seymour
> AeroCoreProductions
> http://www.aerocore.net/
>
> -----Original Message-----
> From: Eduardo Vizcarra [private.php?do=newpm&u=]
> Sent: Saturday, May 26, 2007 7:18 PM
> To: php-EMAIL REMOVED
> Subject: [PHP] Too many records to display in one web page
>
> Hi All
>
> I am developing a web site that interacts with a MySQL database. When I run
> a query and display the records in a web page, this can become a problem
> because there might be too many records to be displayed in one single web
> page so I am trying to divide the total number of records and display them
> in multiple pages. Let's say, if the query returns 100 records then I would
> like to display the first 10 records and then put a navigation bar where I
> can go to the next 10 or return to the previous 10 records
>
> Is this done at a SQL or PHP level ? any experience on doing this ?
>
> Thanks a bunch
> Eduardo
>

Jim,
Your totally right, must have slipped my mind.

Brian Seymour
AeroCoreProductions
http://www.aerocore.net/

-----Original Message-----
From: Jim Lucas [private.php?do=newpm&u=]
Sent: Sunday, May 27, 2007 1:07 AM
To: Brian Seymour
Cc: php-EMAIL REMOVED
Subject: Re: [PHP] Too many records to display in one web page

Brian Seymour wrote:
> Eduardo,
> The best way to do it is on both a php and sql level. Use sql's limit
> keyword in your query to return only the amount of records you want. For
> instance, page 1 will have LIMIT 0, 9 in the query. Page 2 will have LIMIT
> 10, 19. Then you can simply use *_fetch_array and a foreach(maybe) to
> iterate through all the values. If you want to have the the amount of
> records dynamically generate then either have a asdf.php?min=0&max=9 or
just
> do min = (page * 10) - 10 and max = (page * 10) - 1. Many different ways
to
> do this. However I find this one to be the most efficient.

problem with your example.

Limit is not used that way

Check out this page for further explanation

http://dev.mysql.com/doc/refman/5.0/en/select.html#id3064319


Here is something I whipped together for this as an example


<?php

$page = 1;
$rowsPerPage = 10;
$start = 0;

if (
isset($_GET['page']) &&
is_numeric($_GET['page']) &&
$_GET['page'] >= $page
) {
$start = ($rowsPerPage * floor($_GET['page']) );
}

$SQL = "select * from table LIMIT {$start}, {$rowsPerPage}"

display results...

?>

Let me know if I completely confused you. I am often confusing...

Jim Lucas

>
> Hope this helped.
>
> Brian Seymour
> AeroCoreProductions
> http://www.aerocore.net/
>
> -----Original Message-----
> From: Eduardo Vizcarra [private.php?do=newpm&u=]
> Sent: Saturday, May 26, 2007 7:18 PM
> To: php-EMAIL REMOVED
> Subject: [PHP] Too many records to display in one web page
>
> Hi All
>
> I am developing a web site that interacts with a MySQL database. When I
run
> a query and display the records in a web page, this can become a problem
> because there might be too many records to be displayed in one single web
> page so I am trying to divide the total number of records and display them

> in multiple pages. Let's say, if the query returns 100 records then I
would
> like to display the first 10 records and then put a navigation bar where I

> can go to the next 10 or return to the previous 10 records
>
> Is this done at a SQL or PHP level ? any experience on doing this ?
>
> Thanks a bunch
> Eduardo
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

The way I handle it:

Step 1-
Add SELECT SQL_CALC_FOUND_ROWS to the front of your initial query and add
the pagination limit to the end (LIMIT 200, 400 or whatever range you want
to display.)

Step 2-
Do a 2nd query: SELECT FOUND_ROWS() immediately after.

What does this do?

It will return your record set within the limit range specified. However
SQL_CALC_FOUND_ROWS also allows you to calculate how many total rows you
would return if you had no limit. By running SELECT FOUND_ROWS() it will
give you the total number of rows in the query. So for instance in the
pseudo query above, mysql_num_rows would only return 200 on the query but
SELECT FOUND_ROWS() might return 2000 if that's how many rows the query
would return.

Now you can use math to determine how many total pages you will have, what
page you are on, etc so you can tweak the pagination to whatever suits your
needs.

""Eduardo Vizcarra"" <EMAIL REMOVED> wrote in message
news:EMAIL REMOVED...
> Hi All
>
> I am developing a web site that interacts with a MySQL database. When I
> run a query and display the records in a web page, this can become a
> problem because there might be too many records to be displayed in one
> single web page so I am trying to divide the total number of records and
> display them in multiple pages. Let's say, if the query returns 100
> records then I would like to display the first 10 records and then put a
> navigation bar where I can go to the next 10 or return to the previous 10
> records
>
> Is this done at a SQL or PHP level ? any experience on doing this ?
>
> Thanks a bunch
> Eduardo

Steve wrote:
> The way I handle it:
>
> Step 1-
> Add SELECT SQL_CALC_FOUND_ROWS to the front of your initial query and add
> the pagination limit to the end (LIMIT 200, 400 or whatever range you want
> to display.)
>
> Step 2-
> Do a 2nd query: SELECT FOUND_ROWS() immediately after.

With a *HUGE* clause that this only works with mysql.

There are other databases out there :P

--
Postgresql & php tutorials
http://www.designmagick.com/

Here is some script I created recently to paginate results from a
mysql query. It includes the

"previous 1 2 3 . . . next"

coding as well.

I hope I got all the code. I copied and pasted it out of a larger
script. I think you should be able to at least figure out how it
works from the following:

<?

$display = 25;// Number of records to show per page

// Determine where in the database to start returning results.
// See end of script to see where "s" is set.

if (isset($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Determine how many pages there are.

if (isset($_GET['np'])) { // Already been determined.
$num_pages = $_GET['np'];
} else { // Need to determine.

// Count the number of records
$query = "SELECT COUNT(*) FROM tbl_person $_SESSION[where] ORDER BY
registration_date ASC";

$result = @mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];

$_SESSION[count] = $num_records; //Preserve record count for future
use.

// Calculate the number of pages.
if ($num_records > $display) { // More than 1 page.
$num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}

} // End of np if.

//:::::::::: Main query to retrieve lookup data to be displayed on
this page. :::::::::://

// "$_SESSION[where]" provides any search criteria enterred on a
lookup page that links to this page. It
// has nothing to do with pagination.

$query = "SELECT * FROM tbl_person $_SESSION[where] ORDER BY
$order_by LIMIT $start, $display";

$result = @mysql_query ($query); // Run the query.
if (!$result) { echo "You ****ed up!"; } // 'scuse my Latin!

// Count table rows
$count=mysql_num_rows($result);
$_SESSION[count] = $count;

//body of page goes here. i.e. display results from the database
query
//There is missing html display code and php code you will have to
fill in

// After data is displayed the code that displays the pagination
controls follows
// Make the links to other pages, if necessary.

if ($num_pages > 1) {

echo '<tr>';
echo '<td colspan="5"><span cl***="paging_text" >'; //Cell 1
// Determine what page the script is on.
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page != 1) {
echo ' <a href="$_SESSION['php_self']?s=' . ($start - $display) .
'&np=' . $num_pages . '&sort=' . $sort . '&alph=' . $alph .
'">Previous </a> ';
} else { echo "Previous "; }

// Make all the numbered pages.

for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
echo '&nbsp;<a href="$_SESSION['php_self']?s=' . (($display * ($i -
1))) . '&np=' . $num_pages . '&sort=' . $sort .'&alph=' . $alph .
'">' . $i . '</a>&nbsp;';
} else {
echo '&nbsp;' . $i . '&nbsp;';
}
}

// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
echo '&nbsp;<a href="$_SESSION['php_self']?s=' . ($start +
$display) . '&np=' . $num_pages . '&sort=' . $sort .'&alph=' . $alph .
'">Next</a>';
} else { echo "&nbsp;Next"; }

} // End of links section.

?>

On May 26, 4:17 pm, evizca...@iteso.mx ("Eduardo Vizcarra") wrote:
> Hi All
>
> I am developing a web site that interacts with a MySQL database. When I run
> a query and display the records in a web page, this can become a problem
> because there might be too many records to be displayed in one single web
> page so I am trying to divide the total number of records and display them
> in multiple pages. Let's say, if the query returns 100 records then I would
> like to display the first 10 records and then put a navigation bar where I
> can go to the next 10 or return to the previous 10 records
>
> Is this done at a SQL or PHP level ? any experience on doing this ?
>
> Thanks a bunch
> Eduardo