php – Incremental search

Question:

I am implementing incremental search in PHP. The problem is that, if I do the query to return all the records, it takes a moment. If, during that time, the user modifies the search parameters (types a few more letters), PHP follows his ball . Even if you cancel the AJAX request, the query is still running.

The database has, more or less, this structure:

CREATE TABLE parts(
  id INTEGER PRIMARY KEY,
  barcode TEXT,
  code TEXT NOT NULL,
  desc TEXT,
  notes TEXT
);

As a database engine, I am using SQLite. For various reasons, another can not be used.

In the PHP documentation, the connection_aborted () function is stated

int connection_aborted ( void )

This function has certain peculiarities : it checks the connection when sending something to the browser. If PHP is waiting for the result of the query , it is useless for my purposes.

Therefore, the only solution I can think of is to perform the query so that it does not return the entire list, but only a certain number of rows.

So if I have this query:

SELECT * FROM parts WHERE desc LIKE '%texto_tecleado_por_usuario%'

How do I modify it so that it returns the results of 100 in 100 rows in successive queries?

  • First consultation: the first 100.
  • Second consultation: the next 100.
  • Third query: the next 100.

Answer:

To paginate the result of a query you can use the LIMIT and OFFSET clauses (optional) in conjunction with ORDER BY in the SELECT query.

LIMIT

The LIMIT clause is used to limit the number of records returned in a SELECT query. There are 2 different syntaxes, and they both return the same result.

Documentation

LIMIT only

Receive one or two arguments.

If we use LIMIT with a single argument , we will be selecting only the first N elements of our table.

// Devuelve el primer registro
SELECT * FROM tabla LIMIT 1

// Devuelve los primeros 100 registros
SELECT * FROM tabla LIMIT 100

By default, the initial record is 0 , that is, 0 equivalent to the first record in the table.

If we use LIMIT with two arguments , the first argument specifies the first record to return and the second the total number of records to return.

// Devuelve 10 registros de la tabla, empezando por el tercero e ignorando los dos primeros
SELECT * FROM tabla LIMIT 2,10

LIMIT + OFFSET

The optional OFFSET clause is used to define the first N records to be omitted in the SELECT query.

// Devuelve 10 registros de la tabla, empezando por el tercero e ignorando los dos primeros
SELECT * FROM tabla LIMIT 10 OFFSET 2

To avoid confusion, it is recommended to use this syntax instead of using the LIMIT clause with the OFFSET separated by a comma ( LIMIT x, y ).

Equivalences

This query

SELECT * FROM tabla LIMIT <devolver> OFFSET <omitir>

It is equivalent to

SELECT * FROM tabla LIMIT <omitir>, <devolver>

the only thing that changes is the syntax.

Original answer in SO

ORDER BY

The ORDER BY clause defines the order in which the records will be returned.

In relational databases the records (rather tuples) do not have a specific order, for this reason it is recommended to use the ORDER BY clause to explicitly guarantee that the records are returned in a particular order.


Solution

So, the first query would be as follows:

// Devuelve los primeros 100 registros ordenados por el id.
SELECT * 
FROM parts 
WHERE desc LIKE '%texto_tecleado_por_usuario%' 
ORDER BY id
LIMIT 100;

The second query:

// Devuelve 100 registros, empezando por el 101 e ignorando los 100 primeros
SELECT * 
FROM parts 
WHERE desc LIKE '%texto_tecleado_por_usuario%' 
ORDER BY id
LIMIT 100 OFFSET 100;
Scroll to Top