php - 分页结果不显示在第一页之外

Question

目标是对我的搜索结果进行分页，但由于某种原因，当我搜索并且结果超过一页时，我单击第二页却没有显示任何内容。我认为原因是因为 POST 但我真的不确定。我哪里出错了？

<?php
include "functions.php";
error_reporting(E_ALL);
ini_set('display_errors', '1');
$search_output = "";
$nr = "";
if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){

    $searchquery = preg_replace('#[^a-zA-Z 0-9?!]#i', '', $_POST['searchquery']);

    $sqlCommand2 = "
    (SELECT id AS id, username AS name, firstname AS admin 
    FROM myMembers 
    WHERE firstname LIKE '%$searchquery%' OR lastname LIKE '%$searchquery%' OR username LIKE '%$searchquery%') 
    UNION (SELECT id AS id, team_name AS name, churchname AS admin 
    FROM myTeams 
    WHERE team_name LIKE '%$searchquery%' OR churchname LIKE '%$searchquery%' OR admin_name LIKE '%$searchquery%') ORDER BY id";

    $query = mysql_query($sqlCommand2) or die(mysql_error());
    $nr = mysql_num_rows($query);
    if($nr > 0){
        $nr = mysql_num_rows($query); // Get total of Num rows from the database query
        if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
            $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
        } else { // If the pn URL variable is not present force it to be value of page number 1
            $pn = 1;
        }
        //This is where we set how many database items to show on each page 
        $itemsPerPage = 10; 
        // Get the value of the last page in the pagination result set
        $lastPage = ceil($nr / $itemsPerPage);
        // Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
        if ($pn < 1) { // If it is less than 1
            $pn = 1; // force if to be 1
        } else if ($pn > $lastPage) { // if it is greater than $lastpage
            $pn = $lastPage; // force it to be $lastpage's value
        } 
        // This creates the numbers to click in between the next and back buttons
        $centerPages = ""; // Initialize this variable
        $sub1 = $pn - 1;
        $sub2 = $pn - 2;
        $add1 = $pn + 1;
        $add2 = $pn + 2;
        if ($pn == 1) {
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '" style="color:black">' . $add1 . '</a> &nbsp;';
        } else if ($pn == $lastPage) {
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '" style="color:black">' . $sub1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
        } else if ($pn > 2 && $pn < ($lastPage - 1)) {
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '" style="color:black">' . $sub2 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '" style="color:black">' . $sub1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '" style="color:black">' . $add1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '" style="color:black">' . $add2 . '</a> &nbsp;';
        } else if ($pn > 1 && $pn < $lastPage) {
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '" style="color:black">' . $sub1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '" style="color:black">' . $add1 . '</a> &nbsp;';
        }
        // This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
        $limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
        // Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
        // $sql2 is what we will use to fuel our while loop statement below
        $sql2 = mysql_query("
            (SELECT id AS id, username AS name, firstname AS admin 
            FROM myMembers 
            WHERE firstname LIKE '%$searchquery%' OR lastname LIKE '%$searchquery%' OR username LIKE '%$searchquery%') 
            UNION (SELECT id AS id, team_name AS name, churchname AS admin 
            FROM myTeams 
            WHERE team_name LIKE '%$searchquery%' OR churchname LIKE '%$searchquery%' OR admin_name LIKE '%$searchquery%') ORDER BY id $limit"); 

        $paginationDisplay = ""; // Initialize the pagination output variable
        // This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
        if ($lastPage != "1"){
            // This shows the user what page they are on, and the total number of pages
            $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '<img src="images/clearImage.gif" width="48" height="1" alt="Spacer" />';
            // If we are not on page 1 we can place the Back button
            if ($pn != 1) {
                $previous = $pn - 1;
                $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '" style="color:black"> Back</a> ';
            } 
            // Lay in the clickable numbers display here between the Back and Next links
            $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
            // If we are not on the very last page we can place the Next button
            if ($pn != $lastPage) {
                $nextPage = $pn + 1;
                $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '" style="color:black"> Next</a> ';
            } 
        }

////////////Display output/////////////////////////////////////////////////////////
    while($row = mysql_fetch_array($sql2)){
        $id = $row["id"];
        $name = $row['name'];
        $real = $row['admin'];
            $sql_if1 = mysql_query("SELECT id, username, firstname FROM myMembers");
            while($row2 = mysql_fetch_array($sql_if1)){
                $if1id = $row2['id'];
                $if1user = $row2['username'];
                $if1first = $row2['firstname'];
                $userpic1 = '<div style="width:50px; height:50px; overflow:hidden; float:left; margin-right:20px;">' . check_pic("members", $if1id, "50", "profile") . '</div>';
                if($id == $if1id && $name == $if1user){
                    $search_output .= '<div style="height:60px; padding:7px 5px 0px 5px;">' . $userpic1 . 'Username - <a href="profile.php?id='.$if1id.'" style="color:black;">' . $if1user . '<br />First Name - '.$if1first.'</a></div><hr />';
                }
            }
            $sql_if2 = mysql_query("SELECT id, team_name, admin_name, churchname FROM myTeams");
            while($row3 = mysql_fetch_array($sql_if2)){
                $if2id = $row3['id'];
                $if2user = $row3['team_name'];
                $if2first = $row3['admin_name'];
                $if2church = $row3['churchname'];
                $userpic2 = '<div style="width:50px; height:50px; overflow:hidden; float:left; margin-right:20px;">' . check_pic("teams", $if2id, "50", "team") . '</div>';
                if($id == $if2id && $name == $if2user){
                    $search_output .= '<div style="height:60px; padding:7px 5px 0px 5px;">' . $userpic2 . '<a href="team.php?id='.$if2id.'" style="color:black;">Team Name - ' . $if2user . '<br />Team Admin - '.$if2first.'</a></div><hr />';
                }
            }
    } // close while
    } else {
    $search_output = "<hr />0 results for <strong>$searchquery</strong><hr />$sqlCommand2";
    }
}
?>

Answer 1

先做小事。

if(isset($_POST['searchquery']) && $_POST['searchquery'] != "") 可以替换为 if(!empty($_POST[ '搜索查询']))

您不需要 $nr = mysql_num_rows($query); 在 'if($nr > 0)' 内;

您是否知道 $sql_if1 和 $sql_if2 没有 where 子句？ protip，如果在主获取循环之外，有一个内部方法来重新协调行，那很好，但那是另一个故事了......

现在，在更概念的层面上，您可能最好查询数据库一次且仅查询一次。一个简单的选择是将结果存储在 $_SESSION 中。

if (empty($_GET['pn'])) {
  ... //db query stuff
  $query = mysql_query($sqlCommand2) or die(mysql_error());
  $results = $query->to_array; // to sure if it exists, use pdo anyway ^^  
  $_SESSION['usersChurchResults'] = $results;
}
else {
  $results = $_SESSION['usersChurchResults']  
}

$nr = count($results);
...

然后就很容易对数组进行分页了。

最后，是的，这是由于 $_POST。当您单击“第 2 页”时，您将获得 $_GET 参数(页码)，但不会获得 $_POST，因为表单尚未提交。所以基本上，你必须在执行 sql2 之前结束 if (!empty($_POST)) block ，并在其外部声明 if (isset($_GET['pn'])) block ，为了能够在“限制”构造函数中访问 $pn 。

想要完整的代码，还是这样就可以了？

编辑:我在那里，所以这里是:

<?php
include "functions.php";
error_reporting(E_ALL);
ini_set('display_errors', '1');
$search_output = "";
$nr = "";

if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
}

if( ! empty($_POST['searchquery']) ) {

    $searchquery = preg_replace('#[^a-zA-Z 0-9?!]#i', '', $_POST['searchquery']);

    // TODO: use pdo, plizzz
    $_SESSION['membersTeamsSearchquery'] = $searchquery;
    $sqlCommand2 = "
    (SELECT id AS id, username AS name, firstname AS admin 
    FROM myMembers 
    WHERE firstname LIKE '%$searchquery%' OR lastname LIKE '%$searchquery%' OR username LIKE '%$searchquery%') 
    UNION (SELECT id AS id, team_name AS name, churchname AS admin 
    FROM myTeams 
    WHERE team_name LIKE '%$searchquery%' OR churchname LIKE '%$searchquery%' OR admin_name LIKE '%$searchquery%') ORDER BY id";

    $query = mysql_query($sqlCommand2) or die(mysql_error());
    $nr = mysql_num_rows($query);
    if($nr > 0){
        $nr = mysql_num_rows($query); // Get total of Num rows from the database query

        //This is where we set how many database items to show on each page 
        $itemsPerPage = 10; 
        // Get the value of the last page in the pagination result set
        $lastPage = ceil($nr / $itemsPerPage);
        // Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
        if ($pn < 1) { // If it is less than 1
            $pn = 1; // force if to be 1
        } else if ($pn > $lastPage) { // if it is greater than $lastpage
            $pn = $lastPage; // force it to be $lastpage's value
        } 
        // This creates the numbers to click in between the next and back buttons
        $centerPages = ""; // Initialize this variable
        $sub1 = $pn - 1;
        $sub2 = $pn - 2;
        $add1 = $pn + 1;
        $add2 = $pn + 2;
        if ($pn == 1) {
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '" style="color:black">' . $add1 . '</a> &nbsp;';
        } else if ($pn == $lastPage) {
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '" style="color:black">' . $sub1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
        } else if ($pn > 2 && $pn < ($lastPage - 1)) {
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '" style="color:black">' . $sub2 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '" style="color:black">' . $sub1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '" style="color:black">' . $add1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '" style="color:black">' . $add2 . '</a> &nbsp;';
        } else if ($pn > 1 && $pn < $lastPage) {
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '" style="color:black">' . $sub1 . '</a> &nbsp;';
            $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
            $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '" style="color:black">' . $add1 . '</a> &nbsp;';
        }
    }
}  // end POST
// TODO: output/echo pagination display and centerPages, maybe??????????????????????????????????????????????
if($nr > 0){ /* output pagination */ } 

//// get output data //// and several more /

if($nr > 0 || empty($_POST['searchquery'])){ //  "|| $np > 1" won't work if you click on page 2, and then page 1 again
    // This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
    $limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
    // Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
    // $sql2 is what we will use to fuel our while loop statement below
    $searchquery = $_SESSION['membersTeamsSearchquery'];
    $sql2 = mysql_query("
        (SELECT 'member' AS type, id AS id, username AS name, firstname AS admin 
        FROM myMembers 
        WHERE firstname LIKE '%$searchquery%' OR lastname LIKE '%$searchquery%' OR username LIKE '%$searchquery%') 
        UNION (SELECT 'team' AS type, id AS id, team_name AS name, churchname AS admin 
        FROM myTeams 
        WHERE team_name LIKE '%$searchquery%' OR churchname LIKE '%$searchquery%' OR admin_name LIKE '%$searchquery%') ORDER BY id $limit"); 

    $paginationDisplay = ""; // Initialize the pagination output variable
    // This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
    if ($lastPage != "1"){
        // This shows the user what page they are on, and the total number of pages
        $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '<img src="images/clearImage.gif" width="48" height="1" alt="Spacer" />';
        // If we are not on page 1 we can place the Back button
        if ($pn != 1) {
            $previous = $pn - 1;
            $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '" style="color:black"> Back</a> ';
        } 
        // Lay in the clickable numbers display here between the Back and Next links
        $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
        // If we are not on the very last page we can place the Next button
        if ($pn != $lastPage) {
            $nextPage = $pn + 1;
            $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '" style="color:black"> Next</a> ';
        } 
    }

    ////////////Display output/////////////////////////////////////////////////////////
    // TODO: don't use row2 rowX, or play saint row, use $row_myMembers...
    // TODO: get the firstname, userpic, etc in in "sql2" query.
    // TODO: will only work if IDs in members and teams are different, AFTER the addition of my where clause.
    while($row = mysql_fetch_assoc($sql2)){
        $id = $row["id"];
        $name = $row['name'];
            //$sql_if1 = mysql_query("SELECT id, username, firstname FROM myMembers");
        $real = $row['admin'];
            if ( $row['type'] == 'member' ) {
                $sql_if1 = mysql_query("SELECT id, username, firstname FROM myMembers WHERE id=".$id); // if ID is an integer
                while($row2 = mysql_fetch_array($sql_if1)){
                    $if1id = $row2['id'];
                    $if1user = $row2['username'];
                    $if1first = $row2['firstname'];
                    $userpic1 = '<div style="width:50px; height:50px; overflow:hidden; float:left; margin-right:20px;">' . check_pic("members", $if1id, "50", "profile") . '</div>';
                    if($id == $if1id && $name == $if1user){
                        $search_output .= '<div style="height:60px; padding:7px 5px 0px 5px;">' . $userpic1 . 'Username - <a href="profile.php?id='.$if1id.'" style="color:black;">' . $if1user . '<br />First Name - '.$if1first.'</a></div><hr />';
                    }
                }
            }
            else // if team
            {
                $sql_if2 = mysql_query("SELECT id, team_name, admin_name, churchname FROM myTeams WHERE id='".$id."'");  // if ID is a string
                while($row3 = mysql_fetch_array($sql_if2)){
                    $if2id = $row3['id'];
                    $if2user = $row3['team_name'];
                    $if2first = $row3['admin_name'];
                    $if2church = $row3['churchname'];
                    $userpic2 = '<div style="width:50px; height:50px; overflow:hidden; float:left; margin-right:20px;">' . check_pic("teams", $if2id, "50", "team") . '</div>';
                    if($id == $if2id && $name == $if2user){
                        $search_output .= '<div style="height:60px; padding:7px 5px 0px 5px;">' . $userpic2 . '<a href="team.php?id='.$if2id.'" style="color:black;">Team Name - ' . $if2user . '<br />Team Admin - '.$if2first.'</a></div><hr />';
                    }
                }
            }
    } // close while
} else {
    $search_output = "<hr />0 results for <strong>$searchquery</strong><hr />$sqlCommand2";
} // OPEN BEER \o/
?>

还有很多事情要做，但它会起作用。