PHP pagination with single sql query

Question

i am trying to archive pagination with single php sql query as i feel its too much running a select statement twice just because i want to paginate my data i have tried the query function (*)count

i have tried just setting a limit and if the number_of_rows is equal to my limit i automatically paginate this works for two pages but i can't determine up to third page so its not a good approach.

another way i am thinking of is getting all the data then use java script to paginate but i have no idea how to do that so i am trying to do it with php but will like to learn how to do it with java script if possible.

i just want to run only one query and paginate thanks for your help

here is my code:

   <?php
    //count rows in table
    $query= "SELECT * FROM $database.$table WHERE `id` <> '$id'";
    $result= mysqli_query($conn, ($query));

    $results_per_page =30;

    //calculate total pages
    if($result)
    {
        $num_row = mysqli_num_rows($result);
        mysqli_free_result($result);
        $total_pages = ceil($num_row / $results_per_page);
    }   

    //to get page value
    if(isset($_GET["page"])){ 
        $page =$_GET["page"]; 
    } else{ 
        $page=1; 
    };
    $start_from = ($page-1) * $results_per_page;
    $start_from_here = mysqli_real_escape_string($conn, stripslashes($start_from));
    $start_from_here = filter_var($start_from_here, FILTER_SANITIZE_NUMBER_INT );

    //get data to display
    $query1= " SELECT * FROM $database.$table 
                WHERE `id` <> '$id' 
                ORDER BY type ASC 
                LIMIT $results_per_page OFFSET $start_from_here  ";
    $result1= mysqli_query($conn, ($query1));

and my html pagination code is like so

<?php
    if(isset($total_pages) && ($total_pages>1)) {   
        $url="http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
        $url=strtok($url, '?');

        for ($i=1; $i<=$total_pages; $i++) {   
        //Print links for all pages
            echo "<a class='pagination' href='".$url."?page=".$i."'>".$i."</a>";
        }
    }
    ?>

thank you for all your help

@Sayra, look. Unless you are caching the results you're getting from the database (which I am not seeing happening), the same query will be executed every time for each page that would be seen. Given that fact, if you still want to use the pagination based on the full results, I will write an example. Is this what you want to perform? (This might affect drastically the performance of it, considering the number of rows you might have) — Rafael, Aug 18 '18 at 20:07
yes i just want the best way to get the number of rows and paginate with out having to query the table twice — Sayra, Aug 18 '18 at 20:12
I was between doing or finding one solution, turns out this one is great, check it out, please: https://stackoverflow.com/questions/3705318/simple-php-pagination-script — Rafael, Aug 18 '18 at 20:21
okay thank you i think i had a wrong conception that `SELECT COUNT(*) FROM $database.....` grabs the data also but it seems thats not the case in all the example two queries were performed but the `count(*)` wont take time as it only just counts the rows and not get data? am i on the right path? — Sayra, Aug 18 '18 at 20:34
@Rafael how do i go about grabbing the data once and cache it then paginate from it do you mean getting all the data then storing it in users session or cookie or something client side? i really want to learn please help me understand — Sayra, Aug 18 '18 at 20:50
@Sayra, that depends, users session is more common, but plugins for datatables very often cache all the results on javascript and use as they want. But then again, that's a lot of extra work that might be completely unnecessary for you. The referred link is quite broad and commented as to how it can be done and how it works. I recommend it. — Rafael, Aug 18 '18 at 21:14
Maybe what you're looking for is the `SQL_CALC_FOUND_ROWS` option? If you put this in a query that uses `LIMIT`, you can do a second query [`SELECT FOUND_ROWS()`](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows) to find out the total number of records that would have been returned if you didn't use `LIMIT`. — Barmar, Aug 18 '18 at 21:17
Don't you just need to take the page number that is selected, work out how many items per page you list, then select from the DB data where it's between the two - ie from the number based on the page number and total per page, *up to* the total you need which you display per page? This way works for any page selected, and you can allow them to change the total per page as it'll calculate based on a dynamic number. — James, Aug 18 '18 at 22:12
Oh, and I strongly suggest putting it in a function, calling that function and pass in the page number and total_per_page, then you can reuse it in many places of the site without having to copy paste it everywhere — James, Aug 18 '18 at 22:14
thank you every one for your help i learned a whole lot today following all the links and suggestions even learned what premature-optimization was :) but ultimately i still couldn't find a way to perform a pagination without running a `mysqli query twice` — Sayra, Aug 19 '18 at 04:22
@Barmar thank you i read all about `SQL_CALC_FOUND_ROWS` didn't even know of such a function found myself in a never ending debate between `SQL_CALC_FOUND_ROWS` and `COUNT(*)` for example [see](https://stackoverflow.com/questions/3453809/how-to-use-mysql-found-rows-in-php#3455149) but in the end no matter which one i choose and how i adjust my logic i will still need to query the database twice. — Sayra, Aug 19 '18 at 04:26
Yes, you query twice, but the query for `FOUND_ROWS()` is very simple, it doesn't need to access the table data. — Barmar, Aug 19 '18 at 21:42
I guess there are still some limitations we can't yet over come :( — Sayra, Aug 20 '18 at 15:01

RalfFriedl · Accepted Answer · 2018-08-18T20:12:16.703

1

As your first query is only looking for the number of rows, you can optimize this by selecting the number of rows.

$query= "SELECT COUNT(*) FROM $database.$table WHERE `id` <> '$id'";

This way the SQL server will just return the number of rows, not the matching rows.

Edit

You can also load the whole data and store it in a session variable. That way you load the data only once and can display the relevant part without another query.

edited Aug 18 '18 at 20:12

answered Aug 18 '18 at 19:58

RalfFriedl

1,134
3
11
12

yes i have tried this but want to be able to also paginate the data also else i will end up displaying all the data once – Sayra Aug 18 '18 at 20:02
if i do it like this i can't paginate i still have to use the second query to limit and offset the data or should i just get all the data and number of rows and then paginate in my loop that echos out the data? – Sayra Aug 18 '18 at 20:08
but that will defeat the purpose of not grabbing the whole data once like what if the table has like 1000 rows? – Sayra Aug 18 '18 at 20:09
Though the count is a really good improvement, this doesn't really answer the question that was asked. – Rafael Aug 18 '18 at 20:27
@Rafael But the "read everything once" part does answer the "single sql query" question. – RalfFriedl Aug 18 '18 at 20:32
yes i had a wrong idea that count(*) used to grab data as well or may be it was used along with a query that grabbed data – Sayra Aug 18 '18 at 20:38
@Ralf, the question is stated here: "i just want to run only one query and paginate thanks for your help" – Rafael Aug 18 '18 at 20:49
@RalfFriedl it seems like you right that no matter what i do i will still have to query the data base twice in order to archive a pagination thank you for your answer if i cant find a better one i will accept – Sayra Aug 19 '18 at 04:29

score 0 · Answer 2 · answered Aug 18 '18 at 21:40

Doing both in a single query is not possible. You can optimise the aggregation by doing a

SELECT COUNT(1) AS row_count
FROM database.table_name
WHERE `id` <> YOUR_ID_HERE

You can then use the row_count variable to do all the calculations about total pages and limit.

PHP pagination with single sql query

2 Answers2