-1 1 Vote

MySQL: Display search results on multiple pages

Tutorial by Stefan Trost | 30/04/2012 at 19:57

In this little tutorial, we want to look at how you can display the results of a MySQL query on multiple pages as well as the search engine results are displayed on lots of other websites around the world.

First, we consider our normal MySQL query:

SELECT col FROM tab WHERE col LIKE '%abc%'

As an example, we want to select all records in which the column "col" contains the string "abc". With this query, under circumstances, we may get about 1000 hits, which then must be shown on our results page. Since most users would anyway only be interested in the first hit, we have unnecessarily strained the database and provoked an unnecessarily long time to load.

So, we would like to display only the first 10 hits per page. For this, first, we have to work in a LIMIT-clause:

SELECT col FROM tab WHERE col LIKE '%abc' LIMIT 10

This query will bring us only the first 10 hits from our database and therefore, it will reduce our loading times. Of course, this would only work for the first page. But for the second page, we need the hits 11 to 20.

For this, we have to rewrite the query in the following way:

SELECT col FROM tab WHERE col LIKE '%abc%' LIMIT 0,10;
SELECT col FROM tab WHERE col LIKE '%abc%' LIMIT 10,10;
SELECT col FROM tab WHERE col LIKE '%abc%' LIMIT 20,10;

These are the queries for our first three pages of search results. The first number after LIMIT indicates at which record we would like to start, the second number indicates how many records we would like to select.

Therefore, LIMIT 20,10 means, that we skip the first 20 records and then finally output 10 records beginning from the 20th record of the search result. By the way, this process is way much better than initially select the first 30 records and then using PHP to display only the last 10 records.

For our script for displaying the records, of course, we would not write an individual query for each page, but we are using variables:

$start = $page * 10;
$query = "SELECT col FROM tab LIMIT $start,10"

In the variable $page, the recent search page is stored. This page number can, for example, be transferred using GET or POST (beware of code injections at this point). To define the starting point of the search, we simply take 10 times the page number (for our 10 results per page) and add $start to the search query.

ReplyPositiveNegative

About the Author

Avatar AuthorYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile | Message

 

Related Topics

MySQL: Sort by Relevance
Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.