PHP/SQL - Limiting amount of results in a certain page

Question

I'm looking to limit the amount of results on a page to 20, but at the bottom of the page would be 1,2,3,4 etc. How would I do this? I've got this so far but don't know where to go from here: Code:

$conn = mysql_connect($dbhost, $dbuser, $dbpass, $database);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

mysql_select_db('evocityi_stocks');

$query = "SELECT * FROM FUMUKU"; //
$result = mysql_query($query);

echo "<table>
<tr>
<th>Stock Name</th>
<th>Stock Price</th>
<th>Time</th>
</tr>";


while($row = mysql_fetch_array($result)){   
echo "<tr><td>" . $row['Stock'] . "</td><td>" . $row['Price'] . "<td>" . $row['TimeD'] . "</td></tr>";
}
echo "</table>"; //Close the table in HTML
mysql_close();
?>

What you are referring to is called 'pagination'. Fortunately, there are many solutions for this you can quickly search for. — KelvZhan, May 01 '16 at 13:49
Welcome to SO. Please read [What topics can I ask about](http://stackoverflow.com/help/on-topic) and [How to ask a good question](http://stackoverflow.com/help/how-to-ask) And [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) SO is **not a free coding or tutorial or library finding service** You have to show that you have made some effort to solve your own problem. — RiggsFolly, May 01 '16 at 13:52

Naresh Kumar · Accepted Answer · 2016-05-01T14:21:02.207

-2

<?php 
if (isset(mysql_real_escape_string($_GET["page"]))) 
{
    $page  = mysql_real_escape_string($_GET["page"]); 
}
else  
{ 
$page=1;
 }
$start_from = ($page-1) * 20; 
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $database);
if(! $conn )
{
 die('Could not connect: ' . mysql_error());
}
mysql_select_db('evocityi_stocks');

$sql = "SELECT * FROM FUMUKU ORDER BY Stock ASC LIMIT $start_from, 20"; 
$result = mysql_query($query);
?> 

echo "<table>
<tr>
<th>Stock Name</th>
<th>Stock Price</th>
<th>Time</th>
</tr>";


while($row = mysql_fetch_array($result)){   
echo "<tr><td>" . $row['Stock'] . "</td><td>" . $row['Price'] . "<td>" . 

$row['TimeD'] . "</td></tr>";
}
echo "</table>"; //Close the table in HTML 
<?php 
$sql = "SELECT COUNT(Stock) FROM FUMUKU"; 
$result = mysql_query($sql); 
$row = mysql_fetch_row($result); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / 20); 

for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href='yourpage.php?page=".$i."'>".$i."</a> "; 
}; 
?>

edited May 01 '16 at 14:21

answered May 01 '16 at 13:55

Naresh Kumar

561
2
15

1

downvoted, because of xss issues (plain writing of values from a database to the HTML). http://www.sitepoint.com/php-security-cross-site-scripting-attacks-xss/ – Johan May 01 '16 at 14:00
1

thanks for the concern, i just helped the user to get his page navigation thing done. It is the users responsibility to maintain such things. He can use PDO statement its upto hum – Naresh Kumar May 01 '16 at 14:05
PDO does not protect against xss. It is the answerer's responsibility not to put harmful code in an answer. That why I downvoted it. – Johan May 01 '16 at 14:07
1

Now corrected my mistake used htmlspecialchars to prevent xss issue. Thank you – Naresh Kumar May 01 '16 at 14:13
Yes, but you've used it in the wrong place. `htmlspecialchars` is used on output, not input. on input you use `mysql_real_escape_string`. Read the linked article above. In this case you need to correct the `echo` lines. – Johan May 01 '16 at 14:19

PHP/SQL - Limiting amount of results in a certain page

1 Answers1