Php Page Number in Huge Array

Question

I'm trying to create a script that shows the content of a table and highlight an element searched by the user.

The output have to highlight the first element that is equal or greater than the searched value.

The searched value is a key of the table (varchar) I need to output just the page that contains the element and not all the data in the table (paged).

This is what I want to achieve:

1. The user makes a search (using a web interface).
2. The php script looks for the first element that contains the string (equal or greater) and get the page number (for example every page has 100 elements)
3. The php script returns a paged result and shows only the data of the correct page.
4. The user should be able to move to next or previous page from the page with the first match.

At the moment my script works as below:

1. Get all elements of the table and put them in a associative array (the table key is the key of the array and is ordered by this attribute and is the searched element).
2. Loop the array looking for the matching value (equal or greater).
3. Get the position of the element in the array
4. Get the page number of the element if every page has 100 elements
5. call a class that is able to output a paged grid starting from the page number found in point 4 (no problems with this point).

I don't think using the recordset/array based method mentioned in point one is a good approach as it has a lot of elements (100.000) and I just need to show one page to the user. However, I'm not able to find the best approach.

Do you think that there is a way to improve this solution?

Below image is an example of what i would like to do:

Language: PHP DBMS: Sql Server

Answer 1

The "better" solution depends on how much load you have on your database and on your sql server.

Usually the sql database is quite faster picking a small range than picking a whole table. So I´d go for selecting the small range in sql each time moving to a next or previous page with SQL LIMIT.