php - 使用 PHP 进行 MySQL 查询分页

Question

如何向这个简单的项目显示添加分页系统？我如何为过滤器的结果添加分页？我只是迷失了那部分，我无法弄清楚!我也想在菜单上应用 CSS!

代码如下:

<?php
                include('db.php');
                if(isset($_POST['filter']))
                {
                    $filter = $_POST['filter'];
                    $result = mysql_query("SELECT * FROM products where Product like '%$filter%' or Description like '%$filter%' or Category like '%$filter%'");
                }
                else
                {
                    $result = mysql_query("SELECT * FROM products");
                }
                while($row=mysql_fetch_assoc($result))
                {
                    echo '<li class="portfolio-item2" data-id="id-0" data-type="cat-item-4">';
                    echo '<div>
                    <span class="image-block">
                    <a class="example-image-link" href="reservation/img/products/'.$row['imgUrl'].'" data-lightbox="example-set" title="'.$row['Product'].'""><img width="225" height="140" src="reservation/img/products/'.$row['imgUrl'].'" alt="'.$row['Product'].'" title="'.$row['Product'].'" />                    
                    </a>
                    </span>
                    <div class="home-portfolio-text">
                    <h2 class="post-title-portfolio"><font color="#666666">'.$row['Product'].'</font></h2>
                    <p class="post-subtitle-portfolio"><font color="#666666">Descrição: '.$row['Description'].'
                    <p class="post-subtitle-portfolio"><font color="#666666">Categoria: '.$row['Category'].'
                    <p class="post-subtitle-portfolio">Código: '.$row['Price'].'</p><br/></font></p>
                    </div>
                    </div>';
                    echo '</li>';
                }
                ?>

编辑:

<?php
                include('db.php');
                if(isset($_POST['filter']))
                {
                    $filter = $_POST['filter'];
                    $result = mysql_query("SELECT * FROM products where Product like '%$filter%' or Description like '%$filter%' or Category like '%$filter%'");
                }
                else
                {
$start=0;
$limit=6;

if(isset($_GET['id']))
{
    $id = $_GET['id'];
    $start = ($id-1)*$limit;
}

$result = mysql_query("SELECT * FROM products LIMIT $start, $limit");
                }
while($row = mysql_fetch_array($result))
{
    echo '<li class="portfolio-item2" data-id="id-0" data-type="cat-item-4">';
                    echo '<div>
                    <span class="image-block">
                    <a class="example-image-link" href="reservation/img/products/'.$row['imgUrl'].'" data-lightbox="example-set" title="'.$row['Product'].'""><img width="225" height="140" src="reservation/img/products/'.$row['imgUrl'].'" alt="'.$row['Product'].'" title="'.$row['Product'].'" />                    
                    </a>
                    </span>
                    <div class="home-portfolio-text">
                    <h2 class="post-title-portfolio"><font color="#666666">'.$row['Product'].'</font></h2>
                    <p class="post-subtitle-portfolio"><font color="#666666">Descrição: '.$row['Description'].'
                    <p class="post-subtitle-portfolio"><font color="#666666">Categoria: '.$row['Category'].'
                    <p class="post-subtitle-portfolio">Código: '.$row['Price'].'</p><br/></font></p>
                    </div>
                    </div>';
                    echo '</li>';
}
echo "</ul>";


$rows = mysql_num_rows(mysql_query("SELECT * FROM products"));
$total = ceil($rows/$limit);

if($id>1)
{
    echo "<center><a href='?id=".($id-1)."' class='button'>Anterior</a></center>";
}
if($id!=$total)
{
    echo "<center><a href='?id=".($id+1)."' class='button'>Próximo</a></center>";
}

?>

Answer 1

你应该有这样的东西:

// declare a base query
$q = "SELECT * FROM products";
if(isset($_POST['filter']))
{
    $filter = $_POST['filter'];
    // append filter to query
    $q += "where Product like '%$filter%' or Description like '%$filter%' or Category like '%$filter%'");
}
// check for "page" URL parameter, if not available, go to first page
$page = isset($_GET['page']) ? $_GET['page'] : 1;
// check for "pageSize" URL parameter, if not available, fall back to 20
$pageSize = isset($_GET['pageSize']) ? $_GET['pageSize'] : 20;
// append the pagination to your query
$q += sprintf("LIMIT %d,%d;", ($page-1)*$pageSize, $pageSize);
// execute the constructed query
$result = mysql_query($q);

请注意，该代码是“伪代码”，未经测试，但应该能为您提供基本思路。

此外，您还可以检查 this SO post关于 MySQL 的分页。

更新

在 PHP 中，如果您使用未初始化的变量，那么它将有一个上下文相关的默认值。请参阅有关此的文档 here .这是一个摘录:

It is not necessary to initialize variables in PHP however it is a very good practice. Uninitialized variables have a default value of their type depending on the context in which they are used - booleans default to FALSE, integers and floats default to zero, strings (e.g. used in echo) are set as an empty string and arrays become to an empty array.