mysql постраничный вывод php mysql
Универсальный постраничный вывод на PHP
Наверное, каждый разработчик сталкивался с необходимостью постраничного вывода какой-либо информации на экран. Практически в каждом проекте всегда есть «что-то», что не помещается на одну страницу и его нужно вывести по частям, классическим примером этому может служить, вывод информации в yandex или google. Все видели панель навигации, в низу страницы, между страницами, как сделать подобную панель навигации, и посвящена эта статья.
Так будет выглядеть панель навигации, которая описана в этой статье. Итак, приступим. Все комментарии я буду выдавать по ходу работы.
Но сейчас немного теории и принципа работы данного скрипта
Приблизительно так выглядит панель навигации в yandex, когда вы находитесь на 8 странице.
Как видно в примере 3, количество ссылок изменилось. Немного лучше выглядит панель навигации, в которой количество ссылок постоянно, но в таком скрипте больше кода.
Ниже приведен скрипт простейшего постраничного вывода, а также скрипты обеих навигационных панелей, т.е. с постоянным количеством ссылок и с отсеченными по «краям».
Скрипт 1. Простейший постраничный вывод
Скрипт 2. С постоянным количеством ссылок
Скрипт 3. С отсеченными по «краям» ссылками
Вот и все! Универсальный постраничный вывод готов ;).
Дополнение применительно к MySQL
раньше это выглядело так:
всё же на самом деле в несколько раз проще и понятнее если немного поглубже ознакомится с MySQL того же самого можно добиться след. запросом:
Постраничный вывод (PHP и MySQL)
Довольно часто на сайте возникает необходимость в отображении большого объема однотипной информации, при этом, для удобства восприятия, её следует разбивать на части, т.е. реализовать постраничный просмотр этой информации. Данное решение используется поисковыми системами при отображении результатов поиска, форумами, досками объявлений и т.д. Данная статья описывает то, как используя MySQL и PHP реализовать постраничный вывод.
Для начала отмечу, что статья не учит работе с базой данных и PHP, а даёт объяснение реализации, и приводит готовый к использованию PHP класс для постраничной разбивки (постраничной навигации).
Начнем! Допустим имеется база данных (MySQL), например, с объявлениями. Нам нужно реализовать их отображение на сайте, порциями по 20 штук на странице. Для перехода же между порциями в нижней части каждой страницы необходимо сформировать ссылки с номерами «порций» (ярлыки страниц):
Выборка данных порциями
Для выборки ВСЕХ объявлений из базы требуется запрос вида:
Для того чтобы этот запрос делал выборки порциями, необходимо к нему добавить оператор LIMIT :
Синтаксис оператора LIMIT: LIMIT [offset,] row_count
Необязательный параметр offset сообщает сколько рядов от начала выборки нужно пропустить, а row_count указывает сколько рядов нужно выбрать, т.е. LIMIT 0, 20 (или просто LIMIT 20 опустив нулевой offset) выбирает первые 20 рядов (с 0 по 19 ряд), а LIMIT 40, 20 указывает пропустить 40 (с 0 по 39 ряд) и выбрать следующие 20 (т.е. будут выбраны ряды с номера 40 по 59).
Обращаю ваше внимание, что ряды в выборке нумеруются с нуля, а не с единицы.
Таким образом запросы для нашего примера с объявлениями будут следующими:
Вторая часть с которой нам нужно разобраться это строка с ярлыками страниц.
Ярлыки страниц
Ярлыки — это ссылки для «перелистывания» страниц, т.е. ссылки нажимая на которые пользователь получает очередную порцию данных. Эти ссылки содержат в качестве параметра номер нужной порции (страницы).
Например, для выборки третьей двадцатки объявлений ярлык может иметь следующий вид:
При клике по этой ссылке запускается скрипт obyavleniya.php, которому доступен параметр page_number, сообщающий, что запрашивается 3 двадцатка объявлений — 3 страница. Скрипт пропускает первые 40 объявлений, и выбирает следующие 20.
Для вывода этой строки ярлыков требуется знать общее число страниц (чтобы знать сколько ярлыков «рисовать»). Его мы можем получить, разделив общее число объявлений на количество объявлений на странице, округлив результат до большего целого. Т.е., если в нашем примере, допустим, всего 107 объявлений, а выводим мы их на каждой странице по 20 штук, то число страниц будет: 107 / 20 = 5.35, т.е. 5 полных страниц (по 20 объявлений) + одна неполная (7 объявлений), итого, округлив получаем 6 страниц (соответственно будет 6 ярлыков).
Рассмотрим второй вариант подсчета общего числа рядов:
Второй вариант однозначно выглядит более элегантным, а также может обеспечить некоторый выигрыш по скорости.
Собираем все вместе
Теперь вы знаете все что нужно, и я могу привести алгоритм описывающий логику скрипта obyavleniya.php для постраничной разбивки, который запускается при заходе пользователя на страницу объявлений.
Вот собственно и всё. Теперь, я надеюсь, вы сможете написать свой скрипт, или воспользоваться моим, понимая суть того как он работает.
PHP класс Paging для постраничной разбивки
Теперь я приведу пример как организуется постраничная навигация с использованием PHP-класса Paging.
Постскриптум
P.S.: Этот постскриптум я привожу скорее для полноты изложения, нежели как реально актуальную информацию для большинства читателей.
Постраничная навигация с MySQL при большом количестве записей
Рано или поздно многие крупные проекты сталкиваются с проблемами производительности при постраничной навигации по записям. Некоторые из них решают эту проблему ограничением количества доступных для просмотра записей (скажем, не больше 1000). Вполне приемлемое решение. Но в этом случаем могут возникнуть проблемы с индексированием сайта сторонними поисковиками, которые и представляют наибольшую угрозу. В этой статье я хотел бы отказаться от привычной для всех панели навигации вида «1..2..3..4..» в пользу простой «вперед… назад» (будет проще объяснить), но это не проблема реализовать подобное и с первым вариантом.
Более точно определить тему, назвав, какое количество записей считать достаточно большим для появления тормозов, не получится, так как эта цифра для всех разная и сильно зависит от того, насколько быстрые у Вас жесткие диски, сколько памяти, и какая часть Ваших данных уже закеширована в ней и тд. Но если Вы и Ваши сервера ощущают, что n-ная страница при выводе даётся тяжелее первой, и при этом не знаете, что с этим делать – статья для Вас. Но для начала, я хотел бы на пальцах объяснить, почему ОНО работает медленно.
Кстати, тест происходит на виртуальной машинке, работаю я с СУБД под рутом, версия MySQL – 5.0.32.
1 Начнем с данных
Для тестирования создадим небольшую табличку и наполним ее чем-нибудь.
CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
height INT UNSIGNED NOT NULL DEFAULT 0,
width INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(10,2) NOT NULL DEFAULT 0.0,
title VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
Заносим это все в нашу БД. И можно начинать…
2 Обычный метод постраничного вывода
Все, кто уже знает, чем плохи COUNT(*) и LIMIT… OFFSET, могут пропустить эту часть.
Прежде, чем рисовать навигатор, мы делаем SELECT COUNT(*) … WHERE (условия_выборки). Многие, почему-то уверены, что даже если у нас миллионы записей, но условия_выборки позволяют использовать индекс, то такой запрос отработает очень быстро. Проведем эксперимент. Выберем количество записей, у которых height больше 100. Для начала посмотрим, что будет, если индекса по полю height нет.
SELECT SQL_NO_CACHE COUNT(*) FROM items WHERE height>100;
+———-+
| count(*) |
+———-+
| 16405 |
+———-+
1 row in set (0.09 sec)
SHOW STATUS LIKE ‘handler%’;
Последняя команда покажет, сколько же, и каких действий пришлось сделать СУБД, чтобы выполнить наш запрос. Так как индекса у нас нет, MySQL пришлось читать данные прямо из таблицы, поэтому нас интересует строка:
…
| Handler_read_rnd_next | 100001 |
…
То есть, MySQL пришлось сделать 100001 операцию перехода к следующей записи, чтобы найти все, соответствующие запросу.
Везде ниже перед каждым SELECT подразумевается выполнение FLUSH STATUS, а после: SHOW STATUS LIKE ‘handler%’.
Чем нам может помочь индекс.
ALTER TABLE items ADD INDEX height_idx (height);
SELECT SQL_NO_CACHE COUNT(*) FROM items FORCE INDEX(height_idx) WHERE height>100;
+———-+
| count(*) |
+———-+
| 16405 |
+———-+
1 row in set (0.04 sec)
В данном случае использовался индекс, поэтому Handler_read_rnd_next будет равен 0, а вот
…
| Handler_read_next | 16405 |
…
То есть индекс позволяет изначально посчитать только те записи, которые нужны, НО ему все равно требуется пробежаться по ним всем. Нет никакой магии, MySQL нигде не хранит количество записей, соответствующих запросу. Поэтому, если у вас миллионы записей, соответствующих условиям запроса, COUNT будет работать очень медленно.
Второй момент. LIMIT … OFFSET. Тот же эксперимент. Попросим нам 5 записей.
SELECT SQL_NO_CACHE * FROM items FORCE INDEX(height_idx) WHERE height>100 LIMIT 5;
…
5 rows in set (0.00 sec)
…
| Handler_read_next | 4 |
…
Вроде все логично. А теперь попросим вернуть другие 5 записей, начиная с 16401.
SELECT SQL_NO_CACHE * FROM items FORCE INDEX(height_idx) WHERE height>100 LIMIT 16400, 5;
…
5 rows in set (0.13 sec)
Видим, что время выборки значительно возросло. Смотрим статус:
…
| Handler_read_next | 16404 |
…
То есть, MySQL, прочитал все 16405, а только потом просто откинул все ненужные.
Как быть?
3 Как быть
Итак. От нас требуется вывести 10 записей, а так же нарисовать меню навигации. Мы поняли, что, чтобы дойти до записи, с которой необходимо начать отдавать нам результаты, MySQL тратит много лишних действий. Единственное, как это избежать – сразу перейти к нужной, изменив условия выборки.
Рассмотрим все на простом примере: пусть записи выдаются отсортированные по id. В этом случае нам нужно вместе со ссылкой передать id записи, на которой мы остановились. А остановимся мы на записи с То есть, в параметрах ссылки на следующую страницу нам нужно передать 10. Соответственно, для второй страницы запрос будет выглядеть так:
SELECT SQL_NO_CACHE id FROM items WHERE id>10 ORDER BY id LIMIT 10;
Кстати, в обоих случаях Handler_read_next будет равен 9. То есть, прыгнули на первую соответствующую запросу запись (благодаря индексу) и сделали 9 переходов на следующую. Самое главное, что какое бы число вместо 10 в условие мы не подставили – мы всегда в результате SHOW STATUS увидим одно и то же, и время выполнения такого запроса уже не будет зависеть от того, где мы находимся, а будет зависеть только от того, сколько и чего мы выбираем.
Надеюсь, смысл Вам понятен. Давайте тогда решим, что делать с меню навигации, а потом перейдем к более сложной ситуации. Пусть в url мы используем ключевые слова next, previous и last. В каких случаях показывать ссылки «вперед», «назад» и «последняя»?
Каждый раз, когда нам приходит next (запрос следующей страницы), мы выбираем не 10 записей, а 11, начиная с id, переданного в параметрах запроса. Если нам вернулось 11 записей, то следует показать ссылку вперед с id 10-й записи, а 11-ю откинуть. Если вернулось меньше 11 записей, то ссылку вперед показывать не надо. При этом мы всегда (всегда, когда пришло next) показываем ссылку назад (previous) с id первой записи из выборки. Ссылки «в начало» и «последняя» всегда показываются вместе с «назад» и «вперед» соответственно. То есть, если мы решили показывать «назад», то должны показать и «в начало».
Каждый раз, когда нам приходит previous (запрос на предыдущую страницу), мы выбираем 11 записей, у которых id меньше указанного в запросе, отсортированные в обратном порядке. То же самое: если вернулось 11 записей, то ссылку «назад» показываем. Ссылку вперед показываем всегда.
Надеюсь, понятно написал…
Что если нам пришел запрос «last»? Просто показать 10 записей, начиная с самой последней. То есть:
SELECT id FROM items ORDER BY id DESC LIMIT 10;
Думаете, у кого-нибудь из пользователей хватит сил промотать несколько сотен, а то и тысяч страниц, для того, чтобы обвинить Вас во лжи, обнаружив в итоге на первой странице не 10 записей? Даже если и хватит, то Вы можете ответить, что он слишком долго мотал…
Предыдущий пример был прост тем, что id – уникален. А что если требуется сортировка по полю, значения которого могут повторяться? Например, height в нашем случае. Простым запросом было выяснено, что в таблице каждое значения height встречается примерно 800 раз. Просто передать последний выведенный height в параметрах запроса уже мало. Поможет нам всё тот же id. От нас просят отсортировать записи по высоте, но это ведь не мешает нам отсортировать их потом еще и по id?
Для этого нам понадобится новый индекс:
ALTER TABLE items ADD KEY height_id_idx (height, id);
Запрос для первой страницы будет такой:
SELECT SQL_NO_CACHE id, height FROM items ORDER BY height, id LIMIT 10;
В моих результатах у последней записи height=0, Так и надо передать следующей странице. Например, next_0_1174 или next/0/1074 – как Вам удобнее.
Теперь нам нужно выбрать записи, у которых либо height больше 0, либо height=0, а id>1174 (именно для этого мы и сделали дополнительную сортировку).
То есть:
SELECT SQL_NO_CACHE * FROM items WHERE (height>0) OR (height=0 AND id>1174) ORDER BY height, id LIMIT 10;
Надеюсь, пояснять, почему так, не нужно. Статус по-прежнему показывает всего 9 шагов вперед.
Таким образом, мы можем добавлять и другие сортировки. Например, если мы хотим вывести все записи, отсортированные по цене и высоте, запрос будет таким:
SELECT SQL_NO_CACHE * FROM items WHERE (price>5) OR (price=5 AND height>0) AND (price=5 AND height=0 AND id>1174) ORDER BY price, height, id LIMIT 10;
Остается только передавать все необходимые данные, правильно их обрабатывать и подставлять в запрос. И про индекс не забудьте.
4 Что делать с количеством результатов
Как быть, если мы хотим показать пользователям, сколько же результатов найдено? Так как речь идет о больших числах, то вряд ли нас кто-то будет проверять. Тот же гугл может выдать, что нашел 1000000 страниц, соответствующих запросу, но больше 1000 вы не увидите. Мы тоже можем выдать количество результатов лишь примерно. Где его взять и как оценить? Помните, мы выполняли запрос:
SELECT SQL_NO_CACHE COUNT(*) FROM items FORCE INDEX(height_idx) WHERE height>100;
А давайте сделаем так:
EXPLAIN SELECT SQL_NO_CACHE * FROM items FORCE INDEX(height_idx) WHERE height>100;
Столбец rows как раз показывает оценочное число записей, которые нужно просмотреть. 22616 и 16405 – разница совсем не велика. Можно округлить до
20000, да и ладно. Сойдёт. Только помните, что если используете, например, подзапросы и/или объединения, то EXPLAIN вернет несколько строк. Их все надо прочитать и перемножить значения rows.
Блог web-мастера
PHP, MySQL, JavaScript, AJAX, HTML и CSS
Постраничный вывод из MySQL
Этот запрос вернет записи с первой по 10, поскольку нумерация начинается с 0. Cоответственно, запросы для второй и третьей страницы будут выглядеть
Как видите, нам надо лишь передать в скрипт число, которое потом подставить в запрос.
Для построения постраничной навигации нам еще понадобится общее число записей в таблице. Это можно сделать с помощью запроса
Далее определим, сколько всего получится страниц:
Здесь используется константа ITEMS_PER_PAGE, в которой хранится количество выводимых на странице записей.
$dblocation = «localhost» ; // Имя сервера
$dbuser = «root» ; // Имя пользователя
$dbpswrd = «» ; // Пароль
$dbname = «catalog» ; // Имя базы данных
// Выводим «шапку» таблицы
echo ‘
