Php Page Number in Huge Array

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

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.

It is a very bad idea to load everything into a PHP array and do the job of the RDBMS on PHP. Remember, that way you load a LOT of data into memory on the RDBMS, which sends a response of that large chunk of data to the application server which will do a job which is unapropriate at that level, losing indexes and stuff. Instead of that, you can use a script like this:

declare @pageSize int;
declare @value varchar;
declare @elementIndex int;
declare @currentPage int;

set @pageSize = 100;
set @value = 'foobar';
set @elementIndex = (select count(*)
                     from MyTable
                     where MyTable.MyColumn < @value);
set @currentPage = (@elementIndex + 1) / @pageSize;

WITH Rows AS
(
    SELECT
              ROW_NUMBER() OVER (ORDER BY [dbo].[MyColumn]) [Row]
            , *
        FROM
              [dbo].[MyTable]
)
SELECT TOP 10
          *
     FROM
         Rows
    WHERE Row > (@pageSize * (@currentPage - 1));

You can generate this from PHP, or write a stored function and call that as you like, just make sure you protect yourself against SQL Injection.