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
– Rafael, Commented Aug 18, 2018 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
– Sayra, Commented Aug 18, 2018 at 20:12
I was between doing or finding one solution, turns out this one is great, check it out, please: stackoverflow.com/questions/3705318/… — Rafael
– Rafael, Commented Aug 18, 2018 at 20:21
Yes, you query twice, but the query for FOUND_ROWS() is very simple, it doesn't need to access the table data. — Barmar
– Barmar, Commented Aug 19, 2018 at 21:42

RalfFriedl · Accepted Answer · 2018-08-18 20:12:16Z

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, 2018 at 20:12

answered Aug 18, 2018 at 19:58

RalfFriedl

1,1303 gold badges11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sayra Over a year ago

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 Over a year ago

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 Over a year ago

but that will defeat the purpose of not grabbing the whole data once like what if the table has like 1000 rows?

Rafael Over a year ago

Though the count is a really good improvement, this doesn't really answer the question that was asked.

RalfFriedl Over a year ago

@Rafael But the "read everything once" part does answer the "single sql query" question.

|

Sheikh Azad · Accepted Answer · 2018-08-18 21:40:53Z

0

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.

answered Aug 18, 2018 at 21:40

Sheikh Azad

3432 silver badges11 bronze badges

Collectives™ on Stack Overflow

PHP pagination with single sql query

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related