MySQL and the LIMIT part

Sometimes you only need a limited number of rows from a table, for instance because you want pagination, where data are divided into pages with a specific number of rows on each page. In MySQL, you can limit the amount of rows returned with the LIMIT keyword. Here's an example:

SELECT name, country FROM test_users LIMIT 3

This query will return the first 3 rows in the table, but in most cases, you would want to order the data, to get the first rows based on a specific sort order. Fortunately, we learned about the ORDER BY keyword in the previous chapter, so here's an example where we combine them:

SELECT name, country FROM test_users ORDER BY country LIMIT 3

This will give us the first 3 rows, but based on the country name instead of the default sorting order in the table, which is usually the primary key. The LIMIT keyword is followed by one or two parameters. If there's only one, it will be used to control the amount of rows returned, but if you specify two parameters, the first will be used to control the starting row, while the second parameter becomes the amount of rows returned. Here's an example:

SELECT name, country FROM test_users ORDER BY country LIMIT 1, 3

This query will get 3 rows, starting with the secondary row, ordered by country name. You might think that the first number should be 2 instead of 1, to start from the secondary row, but the LIMIT keyword is zero-index based, which means that counting starts from 0 instead of 1. So to start from the first row, you would specify 0 (which is of course the default value), 1 to start from the second row, 2 to start from the third row and so on.

To test the examples above, you can use this test code we wrote in a previous chapter. Simply put one of the queries inside the mysql_query() function and run the code:

mysql_connect("localhost", "username", "password");
mysql_select_db("my_database");

$query = mysql_query("Insert your SQL query here");
while($row = mysql_fetch_array($query))
    echo $row["name"] . " is from " . $row["country"] . "<br />";