复杂的查询+分页脚本 [英] Complicated Query + Pagination Script
问题描述
问题1:我的脚本上的分页不起作用.在20个条目之后，它仅显示最近的20个条目，并且不会将它们分成不同的页面.代码如下:
Problem 1: Pagination on my script is not working. After 20 entries, it only shows the most recent 20 entries and doesn't split them into different pages. Code is below:
问题2:我在其他地方使用了相同的分页脚本，并且在那儿它可以正确分割，但是在下一页上，它显示了与第1页相同的结果.我在使用相同的脚本，除了第二个脚本:
Problem 2: I am using the same pagination script for something else and over there it splits correctly but on the next page it shows the same results from page 1. I am using the same script except for the second script the:
$query = "SELECT COUNT(*) as num FROM table where id =
'$uid' ORDER BY id DESC";
的SQL是:
$sql="SELECT table_one.field_id, table_constant.field_name,
table_one.field_2, table_one.field_3 FROM table_one LEFT
JOIN table_constant ON table_one.common_field
= table_constant.common_field WHERE table_constant.u_id = '$uid'";
代码:
<?php
$tbl_name=""; //not using this since i am doing a union
$adjacents = 3;
$query = "SELECT COUNT(*) as num
from table_one LEFT JOIN table_constant on table_one.c_id
= table_constant.c_id
where table_constant.user_id = '$uid'
UNION
SELECT COUNT(*) as num
from table_two LEFT JOIN table_constant on table_two.c_id
= table_constant.c_id
where table_two.added_by = '$uid'
UNION
SELECT COUNT(*) as num
from table_three LEFT JOIN table_constant ON table_three.c_id
= table_constant.c_id
where table_constant.user_id = '$uid'
UNION
SELECT COUNT(*) as num
from table_four LEFT JOIN table_constant ON table_four.c_id
= table_constant.c_id
where table_constant.user_id = '$uid'
ORDER BY date_time_added DESC";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];
$targetpage = "page.php";
$limit = 20;
$page = $_GET['page'];
if($page)
$start = ($page - 1) * $limit; //first item to display on this page
else
$start = 0; //if no page var is given, set start to 0
$sql = "select table_one.field1, table_constant.field1,
table_one.field2, table_one.field3, table_one.field4,
table_one.field5, table_constant.c_id
from table_one LEFT JOIN table_constant on table_one.field1
= table_constant.c_id
where table_constant.user_id = '$uid'
UNION
select table_two.field1, table_constant.field1, table_two.field2,
table_two.field3, table_two.field4, table_two.field5, table_constant.c_id
from table_two LEFT JOIN table_constant on table_two.c_id
= table_constant.c_id
where table_two.added_by = '$uid'
UNION
select table_three.field1, table_constant.field1, table_three.field2,
table_three.field3, table_three.field4, table_three.field5,
table_constant.c_id
from table_three LEFT JOIN table_constant ON table_three.c_id
= table_constant.c_id
where table_constant.user_id = '$uid'
UNION
select table_four.field1, table_constant.field1, table_four.field2,
table_four.field3, table_four.field4, table_four.field5,
table_constant.c_id
from table_four LEFT JOIN table_constant ON table_four.c_id
= table_constant.c_id
where table_constant.user_id = '$uid'
ORDER BY date DESC LIMIT $start, $limit";
$result = mysql_query($sql);
$query = mysql_query($sql) or die ("Error: ".mysql_error());
$result = mysql_query($sql);
if ($result == "")
{
echo "";
}
echo "";
$rows = mysql_num_rows($result);
if($rows == 0)
{
print("");
}
elseif($rows > 0)
{
while($row = mysql_fetch_array($query))
{
$fields = $row['field']; //Table one Field 1
$fields2 = $row['field']; //Table Constant Field 1
$fields3 = $row['field'];// Table One field 4
$fields4 = $row['field'];//Table Constant Field 2
print("$fields<br>$fields2<br>$fields3<br>$fields4");
}
}
if(mysql_num_rows($result) < 1) {
echo "";
}
/* Setup page vars for display. */
if ($page == 0) $page = 1; //if no page var is given, default to 1.
//next page is page + 1
$lastpage = ceil($total_pages/$limit);
//lastpage is = total pages / items per page,
rounded up.
$lpm1 = $lastpage - 1; //last page minus 1
/*
Now we apply our rules and draw the pagination object.
We're actually saving the code to a variable in case we
want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class=\"pagination\"></div>";
//previous button
if ($page > 1)
$pagination.= "";
else
$pagination.= "";
//pages
if ($lastpage < 7 + ($adjacents * 2))
//not enough pages to bother breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter  </span>";
else
$pagination.= "<a id=\"numberhighlighter\" href=\"$targetpage?page=$counter\">$counter  </a>";
}
}
elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
{
//close to beginning; only hide later pages
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter  </span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter   </a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//close to end; only hide early pages
else
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "     <a href=\"$targetpage?page=2\">2&</a>";
$pagination.= "...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
}
}
//next button
if ($page < $counter - 1)
$pagination.= "";
else
$pagination.= "";
}
?>
<div id="page">
<?php
print("$pagination");
?>
谢谢！
推荐答案
我不确定这对您有多大帮助，但是
I'm not sure how much this will help you, but
1.)您的$ uid在哪里定义?也许在那里，我想念它，因为您有很多代码.如果它是在控制器或其他类型的中间人文件中定义的，那么也许当您更改页面时，uid会被取消设置吗?
1.) Where is your $uid defined at? Perhaps it was there and I missed it because you had ALOT of code. If it was defined in a controller or other type of middleman file, then perhaps when you changed pages the uid got unset?
2.)如果您多次使用相同的分页，则将其创建为函数.
2.) If you are using the same pagination more than once then create it into a function.
我假设使用的查询是正确的结果，唯一的问题是在其他页面上未显示正确的结果.如果是这种情况，请使用我调整过的此功能. (您可能需要自己进行调整，例如您的页面可能不会像我的页面那样读取domain.com/pg=3)
I'm assuming the results you got were correct using the query you were using, the only issue with it was it didn't display the correct results on other pages. If that is the case then use this function I tweaked. (You may have to tweak it yourself, for example your page may not read domain.com/pg=3 like mine does)
function Pagination($list, $limit){
global $pagination;
global $total_pages;
global $pg;
global $offset;
global $page_limit;
$page_limit = $limit;
global $total_results;
$total_results = $list;
global $offset;
// number of rows to show per page
// find out total pages
$total_pages = ceil($list / $limit);
// get the current page or set a default
if ($pagination) {
// cast var as int
$pg = $pagination;
} else {
// default page num
$pg = 1;
} // end if
// if current page is greater than total pages...
if ($pg > $total_pages || $pg == "last") {
// set current page to last page
$pg = $total_pages;
} // end if
// if current page is less than first page...
if ($pg <= 1 || $pg == "first") {
// set current page to first page
$pg = 1;
} // end if
// the offset of the list, based on current page
$offset = ($pg - 1) * $limit;
// get the info from the db
}
function PaginationLinks($url, $tab){
global $pg;
global $total_pages;
global $total_results;
global $page_limit;
global $offset;
$displayed_results = ($total_results - $offset);
if($displayed_results >= $page_limit && $total_results > $page_limit){
$displayed_results = $page_limit;
}
/****** build the pagination links ******/
// range of num links to show
$range = 5;
if($tab){
$tab = "?$tab";
}
// if not on page 1, don't show back links
if ($pg > 1) {
// show << link to go back to page 1
echo "<li class='pagination'><a href='$url/pg=first$tab'><<</a></li>";
// get previous page num
$prevpage = $pg - 1;
// show < link to go back to 1 page
echo "<li class='pagination'><a href='$url/pg=$prevpage$tab'><</a></li>";
} // end if
// loop to show links to range of pages around current page
for ($x = ($pg - $range); $x < (($pg + $range) + 1); $x++) {
// if it's a valid page number...
if (($x > 0) && ($x <= $total_pages)) {
// if we're on current page...
if ($x == $pg) {
// 'highlight' it but don't make a link
echo "<li class='current_page'>$x</li>";
// if not current page...
} else {
// make it a link
echo "<li class='pagination'><a href='$url/pg=$x$tab'>$x</a></li>";
} // end else
} // end if
} // end for
// if not on last page, show forward and last page links
if ($pg != $total_pages) {
// get next page
$nextpage = $pg + 1;
// echo forward link for next page
echo "<li class='pagination'><a href='$url/pg=$nextpage$tab'>></a></li>";
// echo forward link for lastpage
echo "<li class='pagination'><a href='$url/pg=last$tab'>>></a></li>";
} // end if
/****** end build pagination links ******/
echo "<div style='float:right; align: right; margin-top: 10px'>Displaying <font class='f2'>$displayed_results</font> of <font class='f2'>$total_results</font> results</div>";
} // end pagination links function
要使用:(我正在使用基本的用户"表，因为它很简单)
To use: (I'm using the basic "users" table because it is simple)
编写一个查询以获取您要查找的内容的总行数.示例:
Write one query to get the total number of rows for whatever you are looking for. Example:
$getusers = mysql_query("SELECT * FROM users", $conn);
$total_users = mysql_num_rows($getusers)
$display_limit = "20" // display 20 users per page
然后使用第一个功能.
Pagination($total_users, $display_limit);
然后再次运行查询，但这次设置限制，以便每页仅显示20个用户.
then run the query again but set the limits this time so that only 20 users display per page.
$getusers = mysql_query("SELECT * FROM users ORDER BY id DESC LIMIT $offset, $display_limit", $conn);
if($total_users == 0){
echo "There are no users at this time.";
}
else {
// for each user
while ($rowuserss = mysql_fetch_assoc($getusers)) {
// echo data
$user_id = $rowusers['id'];
$username = $rowusers['username'];
// etc etc
}
然后显示链接，使用第二个查询
Then to display the links, use the second query
PaginationLinks($url_to_go_to, $optional_tab_if_you_need_to_select_a_default_tab);
这对任何查询都应该有效，无论您的查询开始时有多复杂.在我看来，您的查询是正确的，只是结果显示在第二页上.无论如何，如果该功能对您不起作用，您仍应在其自身的功能中创建分页功能，因为代码太多，无法在多个位置写出.
This should work for ANY query no matter how complicated as long as your query is correct to begin with. It sounded to me like your query was correct, just the results were displaying on the second page. Any ways, if that function doesn't work for you, you should still create your pagination into it's own function, that's too much code to have written out in more than one location.
这篇关于复杂的查询+分页脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
