PHP-带分页的MySQL查询 [英] PHP - MySQL query with Pagination

问题描述

我将如何为该MySQL& PHP查询.

How would I go about making a pagination script for this MySQL & PHP query.

if (isset($_GET['c'])) {
$c = $_GET['c'];
}

$query = mysql_query("SELECT * FROM Categories WHERE category = '$c' ");

        WHILE($datarows = mysql_fetch_array($query)):

        $id = $datarows['id'];
        $category = $datarows['category'];
        $code = $datarows['code'];

        endwhile;

$query2 = mysql_query("SELECT * FROM Games WHERE category = '$code' ");

WHILE($datarows_cat = mysql_fetch_array($query2)):

        $title = $datarows_cat['title'];
        $description = $datarows_cat['description'];
        $imgurl = $datarows_cat['image_name'];
        $category = $datarows_cat['category'];
        $views = $datarows_cat['view_count'];
        $pagename = $datarows_cat['pagename'];
                $featured = $datarows_cat['featured'];

if ($featured =="1") {$f = "<img src='http://my-site.com/images/star.png' width='13px' title='Featured Game' /> Featured"; } else {$f = "";}
                    if(is_int($views/2)) {
$views = $views / 2;
} else { $views = $views / 2 + .5; }

if (strlen($description) > 95) {
    $desc= substr($description,0,95);
    $desmod = "$desc...<br/><a href=\"http://my-site.com/$pagename#1\" title=\"$description\">- Read More</a>";
    }
else {$desmod = "$description";}

        echo "$f - <a href=\"http://my-site.com/$pagename\">$title - $desmod</a><br/>";

endwhile;

当我访问 http://my-site.com/categories/Action 时，例如，该代码在我的类别表中查找该类别，然后一旦获得该类别的唯一代码，它将运行另一个查询以查找具有该类别代码的另一个表中的所有游戏.但是，目前，我为一个类别加载了200多个游戏，这导致了大量的加载时间.

And when I visit http://my-site.com/categories/Action for instance, The code looks up that category in my category table, then once it gets the unique code for that category, It runs another query to find all games in another table with that category code. Currently, however, I have 200+ games loading for a single category which causes a great amount of loading time.

感谢您的帮助！

推荐答案

首先找出特定类别的游戏数量

First of all find out how many games are there for a specific category

更改行

$query2 = mysql_query("SELECT * FROM Games WHERE category = '$code' ");

到

$sql="SELECT * FROM Games WHERE category = '$code' ";
$query_count=mysql_query($sql);

在其后添加关注

$per_page =30;//define how many games for a page
$count = mysql_num_rows($query_count);
$pages = ceil($count/$per_page);

if($_GET['page']==""){
$page="1";
}else{
$page=$_GET['page'];
}
$start    = ($page - 1) * $per_page;
$sql     = $sql." LIMIT $start,$per_page";
$query2=mysql_query($sql);

然后显示所需的页数

<ul id="pagination">
        <?php
        //Show page links
        for ($i = 1; $i <= $pages; $i++)
          {?>
          <li id="<?php echo $i;?>"><a href="linktoyourfile?c=<?php echo $c;?>&page=<?php echo $i;?>"><?php echo $i;?></a></li>
          <?php           
          }
        ?>
      </ul>

使用CSS进行分页即可达到目的

这篇关于PHP-带分页的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！