limit results from database php

Question

I have this code that will display all of the info on my database. The problem is I want to show only about 10 of the last results. But be able to request more somehow by pressing button. Thank you for taking interest in my question.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";


$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

     while($row = $result->fetch_assoc()) {
         echo "<div id='message'> <br> ". $row["firstname"]. " " . $row["lastname"] . "<br> </div>";
     }
} else {
     echo "0 results";
}

$conn->close();
?>

You can add pagination like this http://stackoverflow.com/questions/3705318/simple-php-pagination http://stackoverflow.com/a/3707457/5807975 — nix, Jan 20 '16 at 03:34
then limit using `$sql = "SELECT id, firstname, lastname FROM MyGuests LIMIT , 10";` — FrozenFire, Jan 20 '16 at 03:35

score 2 · Answer 1 · edited May 23 '17 at 12:31

2

There are two ways you could solve this

Option 1

The problem is I want to show only about 10 of the last results

One way to do it is to use the SQL Limit specifier

$sql="Select * from ORDERS LIMIT 5"

this will fetch the 5 items from your database. You can use ordering mechanisms for sorting,etc

But be able to request more somehow by pressing button.

You can use the offset specifier for this.

$sql="Select * from ORDERS LIMIT 5 OFFSET 15"

This fetches 10 records from the 16th record.You can add to the offset on each button press.

Here is some more information on this link.

Option 2

Pagination.This link should help.

edited May 23 '17 at 12:31

Community

1
1

answered Jan 20 '16 at 03:35

Satej S

2,113
1
16
22

would that show the last entered results or going from the top of the database. – Jan 20 '16 at 03:40
To fetch the last entered results, you could do `ORDER BY` the `id` in `DESC`. If you have a `date` colum, then replace the `id` by data.Any parameter that is taking your entries in a sequential manner. – Satej S Jan 20 '16 at 03:45

blurfx · Accepted Answer · 2016-01-20T03:51:44.030

1

use LIMIT and OFFSET clause for limit selection. link

if you want to select last 10 data, write query like this. SELECT id, firstname, lastname FROM MyGuests ORDER BY id DESC LIMIT 10

and you should use ajax for "show more" function.

edited Jan 20 '16 at 03:51

answered Jan 20 '16 at 03:42

blurfx

1,270
11
22

Mystika, w3schools.com is not an authenticated resource for reference. You can use php.net or MySQL's official site. – Pupil Jan 20 '16 at 03:47
thanks. how would i make the results shown go back to front. for example "A" is at the top and "J" is at the bottom. i want to make "J" at the top and "A" at the bottom. – Jan 20 '16 at 03:48
@Pupil agree, I replaced link to MySQL's official document. – blurfx Jan 20 '16 at 03:52
@jack there is ORDER BY clause for that! check out link on my answer :D – blurfx Jan 20 '16 at 03:56
@Mystika it makes it show my first entered content into the server. the desc is correct but i just want that page content that loaded up to be back to front. – Jan 20 '16 at 04:02
@jack it seems javascript problem. check this fiddle https://jsfiddle.net/6qq91n2b/ – blurfx Jan 20 '16 at 04:22
thanks @Mystika but i can't seam to figure out how i can incorporate it into my code. – Jan 20 '16 at 13:07

limit results from database php

2 Answers2