PHP - MySQL query with Pagination

Question

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;

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.

Thanks for your help!

vinu · Accepted Answer · 2012-09-08 04:20:32Z

14

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

change the line

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

to

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

Add following after it

$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);

Then display the numbers of pages where you want

<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>

Use CSS for pagination this will do the trick

edited Sep 8, 2012 at 4:20

answered Sep 7, 2012 at 10:21

vinu

65810 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1380936 Over a year ago

When I do this, I get the error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mcabinet/public_html/cat.php on line 34

user1380936 Over a year ago

And that line is: WHILE($datarows_cat = mysql_fetch_array($query2)):

vinu Over a year ago

Oh sorry friend. I have edited the answer. now its okay. check it out

user1380936 Over a year ago

Awesome! Thank You so much for your help! I've accepted your answer and +1.

Climb Tree Over a year ago

Hey there! Is there any way out with which we can arrange all the rows in vice versa order. meaning the last page be the first page.

Darshan Dabasiya · Accepted Answer · 2017-11-24 07:07:21Z

//database connation
<?php
    $conn = new mysqli("localhost", "root", "","database_name");
?>
<!DOCTYPE html>
<html>enter code here
<head>
    <title>View Student Details</title>
    <h1 align="center"> Student Details </h1>

    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="bootstrap/bootstrap.min.js"></script>
    <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">

</head>
<body>

    <div class="row">
        <div class="col-sm-2"></div>
        <div class="col-sm-8">

            <form>
                <table class="table table-striped">
                    <tr>
                        <th>Sr.No.</th>
                        <th>Student ID</th>
                        <th>Student Name</th>
                        <th>Class</th>
                        <th>Gender</th>
                        <th>Birth of Date</th>
                        <th>Contact No.</th>
                        <th>Action</th>
                    </tr>

                    <?php 
                        $count=0;

                        if(isset($_GET['page_count']))
                        {
                            $count=1;
                            $page_count=$_GET['page_count']-1;
                            $count=$page_count*10;
                        }

                        $q="SELECT * from student_detail LIMIT $count,10";
                        $result=$conn->query($q);
                        $j=0;
                        while($data=$result->fetch_array())
                        { $j=$j+1;
                    ?>

                    <tr>
                        <td><?php echo $j ?></td>
                        <td><?php echo $data['std_id'] ?></td>
                        <td><?php echo $data['std_name'] ?></td>
                        <td><?php echo $data['std_class'] ?></td>
                        <td><?php echo $data['gender'] ?></td>
                        <td><?php echo $data['bod'] ?></td>
                        <td><?php echo $data['contact'] ?></td>
                        <td>
                            <div class="row">
                                <div class="col-sm-12">
                                    <a href="delete_master.php?std_id=<?php echo $data['std_id']; ?>" class="btn btn-danger">Delete</a>
                                    <a href="update.php?std_id=<?php echo $data['std_id']; ?>" class="btn btn-danger">Update</a>
                                </div>
                            </div>
                        </td>
                    </tr>
                    <?php } ?>
                </table>

                <ul class="pagination">
                    <?php 

                        $q="SELECT count(std_id) from student_detail";
                        $result=$conn->query($q);
                        $data=$result->fetch_array();
                        $total=$data[0];
                        $total_page=ceil($total/10);

                        if($total_page>1)
                        {
                            for($i=1;$i<=$total_page;$i++)
                            {
                    ?>
                                <li class="active"><a href="view.php?page_count=<?php echo $i;?>" name="page_count" id="page_count"><?php echo $i; ?></a></li>
                    <?php
                            }
                        }   
                    ?>
                </ul>
            </form>
            <div class="col-sm-2"></div>
        </div>
    </div>
</body>
</html>

Backend Developer · Accepted Answer · 2018-06-29 11:07:23Z

    Pagiantion, it is working simple and easy 
       <?php  
        $sql = "SELECT COUNT(id) FROM contact_info";

        $rs_result  = $conn->query($sql); 
        $row = mysqli_fetch_row($rs_result);  
        $total_records = $row[0]; 
        echo $total_records;
        $previous   = 1;
        $total_pages = ceil($total_records / $limit); 
        $next  = $_GET["page"] + 1;
        $previous  = $_GET["page"] - 1;
        $pagLink = "<span>"; 
        if($previous ==0)
        {
        $prev  = "<a href='javascript:void(0);' >Previous</a>";
        }
        else
        {
          $prev  = "<a href='http://homeacresfinefurniture.com/all-queries.php?page=".$previous."' style='color:black;'>Previous</a>";   
        };
        echo $prev;

        "</span><div class='pagination'>";
        for ($i=1; $i<=$total_pages; $i++) 
        {  
        $pagLink .= "<a href='http://homeacresfinefurniture.com/all-queries.php?page=".$i."'>".$i."</a>";  
        };  
        echo $pagLink;

        $nex  = "<span><a href='http://homeacresfinefurniture.com/all-queries.php?page=".$next."' style='color:black;'>Next</a></span>";
        echo $nex;

        ";
        </div>";
      ?>

Get all data from database.

            $limit = 1;  
            if (isset($_GET["page"])) 
            {
                $page  = $_GET["page"];
            }
            else 
            {
             $page=1; 
            }  
            $start_from = ($page-1) * $limit;     
            $sql = "SELECT * FROM contact_info ORDER BY id desc LIMIT $start_from , $limit";

Code only answers arent encouraged as they dont provide much information for future readers please provide some explanation to what you have written

Mahdi Zareei · Accepted Answer · 2022-06-21 17:46:53Z

-1

$page = 1;
$limit = 10;
$offset = ($limit * $page) - $limit;

$query = mysqli_query(
    $connect,
    "SELECT * FROM Games WHERE category = '$code' limit $limit offset $offset"
);

answered Jun 21, 2022 at 17:46

Mahdi Zareei

2,11622 silver badges26 bronze badges

Collectives™ on Stack Overflow

PHP - MySQL query with Pagination

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related