[SOLVED] With Pagination You Have to Run the Same Query Twice?

[limitphp]

I was reading the tutorial on here, and I just want to make sure...before you start the main query that will be limited to how many rows to give us and which row to start at, you have to run the exact same query before to get a count on how many rows are affected, correct?

 

So, you have to run the query, do a count on how many rows are affected, then run the query again and limit it?

 

 

Thanks...

 

[jonsjava]

here's an example on how I do pagination. You can feel free to ask me any questions about my code (I ripped it from one of my older sites, so it prolly won't make much sense)

<?php
//This part figures out what page you are on
if (isset($_GET['page']) && is_numeric($_GET['page'])){
$page_number = $_GET['page'];
}
else{
$page_number = "1";
}
//gets the Database variables
include("db.inc.php");
//this is just some stuff for the date.
$day = date("d");
$month = date("M");
//how many articles do you want on each page?
$max_per_page = 4;
//the SQL (beginning) to get the count (and the actual data) of the articles
$sql1 = "SELECT * FROM `my_site` WHERE `category` = '$page' ORDER BY `pk` DESC";
//setting what page we are currently on
$cur_page = $page_number;
//if it's not a static page.....
if($page != "contact" && $page != "login"){
//ok, this query is done. I do it this way, because I reuse the query above later
$sql =$sql1.";";
$result = mysql_query($sql);
$pages_first = mysql_num_rows($result);
//do the math to calculate how many pages we are working with
$pages = $pages_first / $max_per_page;
//round it off
$pages = ceil($pages);
//start outputting the page numbers, with links.
$count = 1;
while ($pages >= $count){
	if($count == $curr_page){
		print "| $count\n";
		$count++;
	}
	else{
		print "| <a href='?page=$count'>$count</a>\n";
		$count++;
	}
}
}
//ok, this one just takes the sql we already had, and reuses it
$sql2 = $sql1;
//grab *only* the articles we want, starting at the right number article for
// this page.
$first_number = $cur_page - 1;
$first_number = $first_number * $max_per_page;
$sql2 .= " LIMIT $first_number, $max_per_page;";
$result2 = mysql_query($sql2);
//ok, we got the data, now it's time to manipulate/output it
while ($row = mysql_fetch_assoc($result2)){
$title = "$row[title]";
$body = "$row[message]";
$day = "$row[date]";
		?>
		                        <!--Your HTML HERE --!>
		                <?php
}

[GingerRobot]

So to actually answer the question: yes, you do.

 

Edit: Well, not quite the same query. The first should simply select a count of the rows. The second should select the fields you wish to display.

[limitphp]

here's an example on how I do pagination. You can feel free to ask me any questions about my code (I ripped it from one of my older sites, so it prolly won't make much sense)

<?php
//This part figures out what page you are on
if (isset($_GET['page']) && is_numeric($_GET['page'])){
$page_number = $_GET['page'];
}
else{
$page_number = "1";
}
//gets the Database variables
include("db.inc.php");
//this is just some stuff for the date.
$day = date("d");
$month = date("M");
//how many articles do you want on each page?
$max_per_page = 4;
//the SQL (beginning) to get the count (and the actual data) of the articles
$sql1 = "SELECT * FROM `my_site` WHERE `category` = '$page' ORDER BY `pk` DESC";
//setting what page we are currently on
$cur_page = $page_number;
//if it's not a static page.....
if($page != "contact" && $page != "login"){
//ok, this query is done. I do it this way, because I reuse the query above later
$sql =$sql1.";";
$result = mysql_query($sql);
$pages_first = mysql_num_rows($result);
//do the math to calculate how many pages we are working with
$pages = $pages_first / $max_per_page;
//round it off
$pages = ceil($pages);
//start outputting the page numbers, with links.
$count = 1;
while ($pages >= $count){
	if($count == $curr_page){
		print "| $count\n";
		$count++;
	}
	else{
		print "| <a href='?page=$count'>$count</a>\n";
		$count++;
	}
}
}
//ok, this one just takes the sql we already had, and reuses it
$sql2 = $sql1;
//grab *only* the articles we want, starting at the right number article for
// this page.
$first_number = $cur_page - 1;
$first_number = $first_number * $max_per_page;
$sql2 .= " LIMIT $first_number, $max_per_page;";
$result2 = mysql_query($sql2);
//ok, we got the data, now it's time to manipulate/output it
while ($row = mysql_fetch_assoc($result2)){
$title = "$row[title]";
$body = "$row[message]";
$day = "$row[date]";
		?>
		                        <!--Your HTML HERE --!>
		                <?php
}

 

It might take me a little while to fully grasp this, but what I might be realizing is that....you can just run the query once, put the limits on it regardless....and then for the displaying of page links, then you'll need the number of rows affected?

[jonsjava]

It might take me a little while to fully grasp this, but what I might be realizing is that....you can just run the query once, put the limits on it regardless....and then for the displaying of page links, then you'll need the number of rows affected?

it's run query (for counting number of articles/posts), calculate number of pages, put up the page links, then run query again(calculating the proper article to start from based on the current page that is being viewed) and output rows.

[jonsjava]

a slight mod on the script to make it lighter on the system

<?php
$table_to_work_with = "your_table";
//This part figures out what page you are on
if (isset($_GET['page']) && is_numeric($_GET['page'])){
$page_number = $_GET['page'];
}
else{
$page_number = "1";
}
//gets the Database variables
include("db.inc.php");
//this is just some stuff for the date.
$day = date("d");
$month = date("M");
//how many articles do you want on each page?
$max_per_page = 4;
//the SQL (beginning) to get the count (and the actual data) of the articles
$sql1 = "SELECT COUNT(*) AS `total` FROM `$table_to_work_with` WHERE `category` = '$page';";
$sql2 = "SELECT * FROM `$table_to_work_with` WHERE `category` = '$page' ORDER BY `pk` DESC";
//setting what page we are currently on
$cur_page = $page_number;
//if it's not a static page.....
if($page != "contact" && $page != "login"){
//ok, this query is done. I do it this way, because I reuse the query above later
$sql =$sql1;
$result = mysql_query($sql);
$pages_first = mysql_fetch-assert($result);
$pages_first = $pages_first['total'];
//do the math to calculate how many pages we are working with
$pages = $pages_first / $max_per_page;
//round it off
$pages = ceil($pages);
//start outputting the page numbers, with links.
$count = 1;
while ($pages >= $count){
	if($count == $curr_page){
		print "| $count\n";
		$count++;
	}
	else{
		print "| <a href='?page=$count'>$count</a>\n";
		$count++;
	}
}
}
//ok, this one just takes the sql we already had, and reuses it
//grab *only* the articles we want, starting at the right number article for
// this page.
$first_number = $cur_page - 1;
$first_number = $first_number * $max_per_page;
$sql2 .= " LIMIT $first_number, $max_per_page;";
$result2 = mysql_query($sql2);
//ok, we got the data, now it's time to manipulate/output it
while ($row = mysql_fetch_assoc($result2)){
$title = "$row[title]";
$body = "$row[message]";
$day = "$row[date]";
		?>
		                        <!--Your HTML HERE --!>
		                <?php
}

*EDIT* Forgot to modify a portion to take into account the new sql1

[xangelo]

While the code works, if your database grows to be too large, you'll end up trying to do too much. As jonsjava said, first all you need is a count of the results.

 

$q = mysql_query('select count(*) from table_name');

 

Do all the grunt work in PHP (IE, figure out what page your on, which results you'll have to display, create the sql statement with limits. Figure out how many pages you'll need, which ones you want to display etc). Then just run the query that you create from all that grunt work. That way you'll have only the results that pertain to that particular page.

[limitphp]

sorry....i don't know what I was thinking....of course you have to run the query twice....

if you just run it with limits then you won't get the right number of affected rows....

 

I wasn't thinking.....thanks guys...