Paging in SQL

Asked

Viewed 96 times

2

I have a doubt here, not even for the sake of code, but for the sake of what logic.

I have a table with 6000 records in Mysql. I have a PHP script that returns 50 records per page, using SQL’s "LIMIT" function.

Now I need to search a single record of this table and know in which of these "pages" of 50 records it will be.

For example, if it’s record 68, I need to know that it’s on the second page. If it’s 120, on the third and so on.

Is that possible with PHP or Mysql? Could someone give me a light?

Thank you.

php mysql

  • When do you need to know this ?

  • @Zoom, is a table with nicknames and player scores, the player will enter the site, inform the nick, based on the nick I will fetch the ID and based on that ID I need to know on page he is. EDIT: The ID part is quiet, I’m just cracking my head with this question of the search there.

  • Got it. But strange you want to do that. You want to show where he is in the scoring ranking ?

  • Exactly @Zoom, I could easily return only his position with the data and etc. But I wanted to do so described above so that he see "around" his position.

  • That SELECT: SELECT @NumRow := @NumRow + 1 As NumeroDaLinha, Nick, ID FROM Users, (SELECT @NumRow := 0) R ORDER BY Pontuacao places a sequential column in your query. This way you have the user position number. Now you have to do the account to know what page this user is on. I’m bad at math.

  • http://answall.com/questions/84265/sistema-de-busca-com-pagina%C3%A7%C3%A3o - Referent?

  • @Zoom, we are two kkkk. But it is precisely this account there that I have no idea how to do.

Show 2 more comments

1 answer

0

Well if you want to find out just the page (assuming you already know what position the user/player is in), you can use a simple math function in PHP itself.

For example, if you are showing 50 results per page, just make the user/player position divided by 50 and in case round the decimal value up. Example:

//posição do usuário
$pos = 68;
// isso ira gerar um decimal 1.36
$pag = $pos/50;

// o "ceil" irá arredondar para cima o decimal, no caso de 1.36 para 2
echo "Página: ".ceil($pag);

To know which page will appear another position just change the value in "$pos". Another example:

//posição do usuário - Outra posição
$pos = 120;
// isso ira gerar um decimal 1.36
$pag = $pos/50;

// o "ceil" irá arredondar para cima o decimal, no caso de 2.4 para 3
echo "Página: ".ceil($pag);

I hope I have helped, I believe that this resolves. But the same can also be done by SQL using the ROUND function.

Att;

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.