限制MYSQL数据的数量

Question

I'm using MySQL to show data on my website: http://www.onlinedealsindia.in. You can see the deals (deal boxes) there but I want to limit them. I want only 50 deals (deal boxes) to show up per page. Clicking the more button would show the next 50 deals.

Below is my code to get data from MySQL:

<?php           
$host="localhost";      
$username="username";       
$password="pass";       
$dbname="DB NAme";      
$conn=new mysqli($host, $username, $password, $dbname); 
if($conn->connect_error)        
{               
die("error".$conn->connect_error);      
}           
else { echo "";}    

$sql= "SELECT * FROM table ORDER BY p_id DESC";

$results=$conn->query($sql);    
if($results->num_rows > 0)
{ while($row=$results->fetch_assoc())   
{   $pid=$row["p_id"];  
?>  

Answer 1

The mechanic you are looking for is called skip and take.

In mySQL you can use the LIMIT to accomplish this. With one parameter it returns that many rows. With two parameters, it will skip the first number in rows and return the number of rows for the second number.

---

This SQL would return 20 rows:

$sql= "SELECT * FROM table LIMIT 10 ORDER BY p_id DESC";

This SQL would return the ten rows skipping the first 10

$sql= "SELECT * FROM table LIMIT 20,10 ORDER BY p_id DESC";

Documentation and examples of LIMIT can be found here

To make this work for your website just pass a parameter that tells what page you are on and how many rows per page. You can then calculate the two numbers you need for your select statement.

Answer 2

$mysqli = new mysqli($host, $username, $password, $dbname);
if($mysqli->connect_error)        
{               
die("error".$mysqli->connect_error);      
}           
else { 
echo "";
}   
$datas = $mysqli->prepare('SELECT id FROM table LIMIT 50 ');
if($datas)
{
   $datas->execute();
   $datas->bind_result($id);
   while($datas->fetch)
   {
     $store['id']= $id;
     $results[] = $store;

   }
return $results;

}
foreach($results as $result)
{
  echo $result['id'];
}