- PHP & MySQL — Select data in a database Tutorial
- How to select data in a table
- How to refine selection
- Ограничение количество выводимых записей MySQL
- Условие PHP MySQL LIMIT
- Примеры ограничения результатов выбора
- Пример
- PHP MySQL Select with Limit: A Comprehensive Guide
- Syntax of PHP MySQL Select with Limit
- Example of PHP MySQL Select with Limit
- PHP MySQL LIMIT Clause
- Limiting Result Sets
- Example
PHP & MySQL — Select data in a database Tutorial
In this tutorial we learn how to find and select data elements in a database table with PHP and an SQL query. We also cover how to refine our selections with extra SQL clauses.
For this tutorial, please start the MySQL module in your XAMPP control panel.
How to select data in a table
To return data from our database, we need to select it from the table first. We do this with the SELECT FROM query.
We can select multiple columns from a table by separating each column name with a comma.
We can select all the columns in a table with the asterisk * symbol.
In the example above, we select data from all three of the columns in our Users table. Then, we execute the query and store the data into the $result variable.
To be able to perform operations on our data, we fetch it into and associative array with the mysqli_fetch_assoc() function.
The mysqli_fetch_assoc() function returns $row as an associative array where each key of the array represents the column name of the table.
It will return NULL if there are no more rows, so we can use a while loop to iterate over the array.
In the while loop is where we perform whatever operations we need to. In this case, we simply print out the data to the page.
How to refine selection
It may be that we only want to select specific records from the table based on a condition. We can add this condition to our query with the WHERE clause.
If the value is a string type, it must be wrapped in quotes.
Ограничение количество выводимых записей MySQL
В этом уроке вы узнаете, как получить ограниченное количество записей из таблицы базы данных MySQL с помощью условия LIMIT.
Условие PHP MySQL LIMIT
Условие LIMIT используется для ограничения количества строк, возвращаемых оператором SELECT . Эта функция очень полезна для оптимизации времени загрузки страницы, а также для повышения читабельности веб-сайта. Например, вы можете разделить большое количество записей на несколько страниц, используя разбиение на страницы, где ограниченное количество записей будет загружено на каждую страницу из базы данных, когда пользователь запросит эту страницу, щелкнув ссылку разбиения на страницы.
Базовый синтаксис условия LIMIT может быть задан следующим образом:
Условие LIMIT принимает один или два параметра, которые должны быть неотрицательным целым числом:
- Если указаны два параметра, первый параметр указывает смещение первой возвращаемой строки, то есть начальную точку, тогда как второй параметр указывает количество возвращаемых строк. Смещение первой строки равно 0 (а не 1).
- В то время, когда указан только один параметр, он определяет максимальное количество строк, возвращаемых с начала набора результатов.
Например, чтобы получить первые 4 строки, вы можете использовать следующий запрос:
Чтобы получить строки от 16 до 25 (включительно), вы можете использовать оператор OFFSET :
В приведенном ниже SQL-запросе говорится: вернуть только 10 записей, начать с записи 16 (OFFSET 15):
Вы также можете использовать более короткий синтаксис для достижения того же результата:
Примеры ограничения результатов выбора
Давайте сделаем SQL-запрос, используя условие LIMIT в операторе SELECT , после чего мы выполним этот запрос, передав его функции PHP mysqli_query() , чтобы получить ограниченное количество записей. Рассмотрим следующую таблицу persons в БД demo:
Код PHP в следующем примере будет отображаться только две строки из таблицы persons:
Пример
// Попытка выполнения запроса select $sql = "SELECT * FROM persons LIMIT 2"; if($result = mysqli_query($conn, $sql)) < if(mysqli_num_rows($result) >0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = mysqli_fetch_array($result))< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Закрыть набор результатов mysqli_free_result($result); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> else < echo "ОШИБКА: не удалось выполнить $sql. " . mysqli_error($conn); >// Закрыть соединение mysqli_close($conn); ?> // Попытка выполнения запроса select $sql = "SELECT * FROM persons LIMIT 2"; if($result = $mysqli->query($sql))< if($result->num_rows > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch_array())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set $result->free(); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> else< echo "ОШИБКА: не удалось выполнить $sql. " . $mysqli->error; > // Закрыть соединение $mysqli->close(); ?>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ОШИБКА: не удалось подключиться. " . $e->getMessage()); > // Попытка выполнения запроса select try< $sql = "SELECT * FROM persons LIMIT 2"; $result = $pdo->query($sql); if($result->rowCount() > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Доступный набор результатов unset($result); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> catch(PDOException $e)< die("ОШИБКА: не удалось выполнить $sql. " . $e->getMessage()); > // Закрыть соединение unset($pdo); ?>После выполнения сценария результат вывода будет выглядеть так:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | +----+------------+-----------+----------------------+
PHP MySQL Select with Limit: A Comprehensive Guide
In a database, you may want to retrieve only a specific number of records, rather than all the records in a table. The LIMIT clause in the PHP MySQL SELECT statement is used to set a limit on the number of records returned. This is useful when working with large databases and you only need a subset of data. In this article, we’ll explore the PHP MySQL SELECT statement with the LIMIT clause in detail, including its syntax, examples, and best practices.
Syntax of PHP MySQL Select with Limit
The basic syntax for the PHP MySQL SELECT statement with the LIMIT clause is as follows:
SELECT column1, column2, . FROM table_name LIMIT number_of_records;Here’s what each part of the statement means:
- SELECT : This keyword is used to indicate that we’re performing a select operation.
- column1, column2, . : The names of the columns that we want to retrieve from the table.
- FROM : This keyword is used to specify the name of the table from which we want to retrieve data.
- LIMIT : This keyword is used to set a limit on the number of records returned.
- number_of_records : The maximum number of records that we want to retrieve from the table.
Example of PHP MySQL Select with Limit
Let’s take a look at a concrete example of how to use the PHP MySQL SELECT statement with the LIMIT clause. Consider a MySQL database table named students with the following structure:
+----+---------+--------+-------+ | id | name | class | marks | +----+---------+--------+-------+ | 1 | John | 10 | 90 | | 2 | Michael | 9 | 85 | | 3 | Jessica | 8 | 80 | +----+---------+--------+-------+
Suppose we want to retrieve only the first two records from the students table. We can use the following PHP code to accomplish this:
$conn = mysqli_connect("localhost", "username", "password", "database"); $query = "SELECT * FROM students LIMIT 2"; $result = mysqli_query($conn, $query); while($row = mysqli_fetch_array($result)) < echo "ID: " . $row["id"]. " Name: " . $row["name"]. " Class: " . $row["class"]. " Marks: " . $row["marks"]. "
"; > mysqli_close($conn); ?>The output of the above code will be:
ID: 1 Name: John Class: 10 Marks: 90 ID: 2 Name: Michael Class: 9 Marks: 85
As you can see, only the first two records from the students table are retrieved and displayed.
PHP MySQL LIMIT Clause
In this tutorial you will learn how to fetch limited number of records from a MySQL database table using PHP.
Limiting Result Sets
The LIMIT clause is used to constrain the number of rows returned by the SELECT statement. This feature is very helpful for optimizing the page loading time as well as to enhance the readability of a website. For example you can divide the large number of records in multiple pages using pagination, where limited number of records will be loaded on every page from the database when a user request for that page by clicking on pagination link.
The basic syntax of the LIMIT clause can be given with:
The LIMIT clause accepts one or two parameters which must be a nonnegative integer:
- When two parameters are specified, the first parameter specifies the offset of the first row to return i.e. the starting point, whereas the second parameter specifies the number of rows to return. The offset of the first row is 0 (not 1).
- Whereas, when only one parameter is given, it specifies the maximum number of rows to return from the beginning of the result set.
For example, to retrieve the first three rows, you can use the following query:
To retrieve the rows 2-4 (inclusive) of a result set, you can use the following query:
Let’s make a SQL query using the LIMIT clause in SELECT statement, after that we will execute this query through passing it to the PHP mysqli_query() function to get the limited number of records. Consider the following persons table inside the demo database:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 4 | John | Carter | johncarter@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+----------------------+
The PHP code in the following example will display just three rows from the persons table.
Example
// Attempt select query execution $sql = "SELECT * FROM persons LIMIT 3"; if($result = mysqli_query($link, $sql)) < if(mysqli_num_rows($result) >0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = mysqli_fetch_array($result))< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Close result set mysqli_free_result($result); > else < echo "No records matching your query were found."; >> else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>connect_error); > // Attempt select query execution $sql = "SELECT * FROM persons LIMIT 3"; if($result = $mysqli->query($sql))< if($result->num_rows > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch_array())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set $result->free(); > else < echo "No records matching your query were found."; >> else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt select query execution try< $sql = "SELECT * FROM persons LIMIT 3"; $result = $pdo->query($sql); if($result->rowCount() > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set unset($result); > else < echo "No records matching your query were found."; >> catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>After limiting the result set the output will look something like this:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | +----+------------+-----------+----------------------+