user1380936
Reputation:
PHP - MySQL query with Pagination
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!
Upvotes: 4
Views: 24488
Answers (4)
Mahdi Zareei
Reputation: 2028
$page = 1;
$limit = 10;
$offset = ($limit * $page) - $limit;
$query = mysqli_query(
$connect,
"SELECT * FROM Games WHERE category = '$code' limit $limit offset $offset"
);
Upvotes: -1
Backend Developer
Reputation: 1
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";
Upvotes: 0
Darshan Dabasiya
Reputation: 11
//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>
Upvotes: 0
vinu
Reputation: 658
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
Upvotes: 14