0

I will do a project for my database class (new in databases). I will use php. The tables are created in mysql. I have to display a webpage with some information:

attr1 | atrr2 | atrr3 | atrr4 
      |       |       |
      |       |       |

But I want to display the last inserted 20 rows. Then, if the user wants to see the other rows inserted, the webpage will have a next page button showing the following 20 rows (not the same ones).

I know that I can use something like:

select (attributes) from tbl ORDER BY 'ID' DESC LIMIT 20

1- But then, How I can retrieve (with php programming) the other 20 rows? If I use the same query it will display the same information.

2- I was thinking to just query all the data and somehow store it, but I don't know if it is a good choice to store all the data even if the user does not want to see it. I want to do it as my first question because I have to retrieve other information from other tables in the database.

What do you suggest? And how I can retrieve the data as I want in my first question?

Edwardo
  • 643
  • 3
  • 9
  • 23
  • The `LIMIT,OFFSET` section of https://dev.mysql.com/doc/refman/5.0/en/select.html is what you need. Note that your order by isn't currently doing what you expect. `ID` should _not_ be single quoted as `'ID'`. That is interpreted as a string literal, and though syntactically valid does not produce the result you expect. – Michael Berkowski Apr 17 '14 at 20:09
  • so it will be select (attributes) from tbl ORDER BY ID DESC LIMIT 20? – Edwardo Apr 17 '14 at 20:23

2 Answers2

1

This concept is called pagination. In this -

1st) LIMIT 20,0    (from 1 to 20)
2nd) LIMT 20,20    (from 21 to 40)
3rd) LIMIT 20,40   (from 41 to 60) 
4th) LIMIT 20,60   (from 61 to 80)

It is a combination of limit and offset. 1st, 2nd, 3rd... are the page and the (LIMIT 20,0) are the addons to the query, and offset parameter you'll get from $_GET (from URL)

Parag Tyagi
  • 8,780
  • 3
  • 42
  • 47
  • thanks, but I think that the syntax is wrong. It should be LIMIT 20 OFFSET 0 right? I am using your syntax and mysql does not reply the correct rows... I mean in mysql syntax. – Edwardo Apr 20 '14 at 03:17
1

I made one of these recently in a project. I used the Bootstrap pagination class to style it:

<div class="pagination" style="text-align:center">
<ul>

<?php

        if($firstPage)
        {
            $relative = "./";
        }
        else
            $relative = "../";

        if($page != 1)
        {
            echo "<li><a href=\"".$relative."page/$page\">&laquo;</a></li>";
        }
        for($i = 1; $i <= ceil($numRows / $numItems); $i++)
        {
            if($i == $page)
                echo "\t<li><a href=\"".$relative."page/$i"."\" style=\"background-color:#e5e5e5;color:#AAAAAA;\"><b>".$i."</b></a></li>";
            else
                echo "\t<li><a href=\"".$relative."page/$i\">".$i."</a></li>";
        }
        if(($page+1) <= ceil($numRows / $numItems))
            echo "\t<li><a href=\"".$relative."page/".($page+1)."\">&raquo;</a></li>";
?>
</ul>
</div>

Then in the controller, I form the query:

$numItems = 20;  // Number of results to show per page
$q = "SELECT * FROM --- WHERE ---- = $-----";
$q .= " LIMIT ".(($page_number-1)*$numItems).','.($numItems*($page_number));
Alex W
  • 37,233
  • 13
  • 109
  • 109
  • Hi Alex, Thanks for sharing this. I have some questions: What do you mean by the controller? Can you explain me a little bit your code? The for loop? – Edwardo Apr 17 '14 at 21:11
  • @Edwardo It's the function that gets called when the web browser requests a URL with /page/xxx where the x's are numbers – Alex W Apr 18 '14 at 00:46
  • Sorry for bothering you again Alex, but how I can know which page I am? How I can do it with $_GET? For example, I have a page called main.php, How I, in the same page, can get the number of the page(say 2) when the user click (choose) the page number 2? Help! – Edwardo Apr 20 '14 at 05:09
  • @Edwardo In your URL for your links, put `main.php?page=2` then in your PHP you can use it via `$_GET['page']` – Alex W Apr 22 '14 at 20:30
  • I managed to do it with a tutorial of basic pagination: http://www.phpfreaks.com/tutorial/basic-pagination I used the _SERVER['PHP_SELF'] variable. But many thanks for your help! – Edwardo Apr 23 '14 at 16:48