jqGrid — поиск данных

Владимир | | Ajax, HTML, JavaScript, PHP, Web разработка.

Приветствую всех!

Я довольно давно обещал рассказать о функции поиска в плагине jqGrid и сегодня выполняю обещание.

Для тех, кто сразу хочет посмотреть, как это работает, я сделал демонстрационную страничку

Demo

и, конечно, можно скачать архив с примером

Source

Напомню, что jqGrid — это плагин для библиотеки jQuery, который значительно упрощает работу с таблицами данных.

Сразу хочу отметить, что эта статья — продолжение предыдущих двух частей (jQuery Grid Plugin — «продвинутое» решение для создания таблиц и jqGrid: редактирование табличных данных с помощью inline редакторов). Поэтому если вы впервые слышите о jqGrid — очень советую почитать хотя бы первую часть.

Кстати, недавно появилось новая версия этого плагина (3.5.2). Поэтому прежде чем переходить к поиску, хочу отметить несколько изменений, которые в ней появились.
Принцип работы, конечно, остался тот же.
Но плагин теперь совместим с темами jQuery UI.
Это означает, что, во-первых, вы можете легко изменить оформление таблицы, а во-вторых, тему нужно загружать отдельно.

На странице загрузки плагина вы выбираете и скачиваете необходимые компоненты jqGrid, а после этого отдельно качаете тему jQuery UI.

Кроме того, появилась локализация, и русская версия входит в дистрибутив.

Теперь переходим к поиску.

Всего поддерживается 4 режима поиска:
— a toolbar searching
— a custom searching
— a single field searching
— a more complex approach involving many fields and conditions — advanced searching

Мы рассмотрим последний вариант (advanced searching), т.к. он самый сложный и позволяет искать сразу по нескольким полям.

Для использования этого варианта поиска нужно загрузить плагины Common, Form Edit, Search Plugin. И, конечно, понравившуюся тему jQuery UI.

Структура проекта будет такой.

index.html (страница с таблицей)
getdata.php (скрипт получения данных, используется как для отображения всех данных, так и для поиска
js/ (папка с jqGrid и библиотекой jQuery)
js/i18n/ (папка с файлами переводов)
css/ (папка со стилями)
css/smoothness/ (папка с темой jQuery UI)

Теперь создаем таблицу и добавляем поддержку поиска (index.html).

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  4. <head>
  5.   <title>jqGrid поиск</title>
  6.  
  7.   <meta http-equiv="content-type" content="text/html;charset=utf-8" />
  8.   <meta http-equiv="Content-Style-Type" content="text/css" />
  9.   
  10.   <meta name="description" content="jqGrid поиск" />
  11.   <meta name="keywords" content="jqGrid, javascript, jquery" />
  12.   
  13.   <link rel="stylesheet" type="text/css" media="screen" href="css/smoothness/jquery-ui-1.7.2.custom.css" />
  14.   <link rel="stylesheet" type="text/css" media="screen" href="css/ui.jqgrid.css" />
  15. </head>
  16. <body>
  17.   <table id="list"></table>
  18.   <div id="pager"></div>
  19.  
  20.   <p>Для поиска нажмите кнопку с изображением лупы (в левом нижнем углу)</p>
  21.   <p>Вы можете добавлять и удалять условия поиска с помощью кнопок "плюс" и "минус", которые расположены справа от условия.</p>
  22.   <p>Esc - закрывает форму поиска</p>
  23.   <p>Описание этого примера вы найдете в статье: <a href="https://www.simplecoding.org/jqgrid-poisk-dannyx.html">jqGrid - поиск данных</a>.</p>
  24.   
  25.   <script type="text/javascript" src="js/jquery-1.3.2.min.js"></script>
  26.   <script type="text/javascript" src="js/i18n/grid.locale-ru.js"></script>
  27.   <script type="text/javascript" src="js/jquery.jqGrid.min.js"></script>
  28.   <script type="text/javascript">
  29.   jQuery(document).ready(function(){
  30.     var lastSel;
  31.     jQuery("#list").jqGrid({
  32.       url:'getdata.php',
  33.       datatype: 'json',
  34.       mtype: 'POST',
  35.       colNames:['#', 'Фамилия', 'Имя', 'Отчество'],
  36.       colModel :[
  37.         {name:'id', index:'id', width:50, align:'right', search:false}
  38.         ,{name:'surname', index:'surname', width:150, align:'right', editable:true, edittype:"text", searchoptions:{sopt:['eq','ne','bw','cn']}}
  39.         ,{name:'fname', index:'fname', width:150, align:'right', editable:true, edittype:"text", searchoptions:{sopt:['eq','ne','bw','cn']}}
  40.         ,{name:'lname', index:'lname', width:150, align:'right', editable:true, edittype:"text", searchoptions:{sopt:['eq','ne','bw','cn']}}
  41.         ],
  42.       pager: jQuery('#pager'),
  43.       rowNum:5,
  44.       rowList:[5,10,30],
  45.       sortname: 'id',
  46.       sortorder: "asc",
  47.       viewrecords: true,
  48.       caption: 'Данные пользователей',
  49.       ondblClickRow: function(id) {
  50.         if (id && id != lastSel) {
  51.           jQuery("#list").restoreRow(lastSel);
  52.           jQuery("#list").editRow(id, true);
  53.           lastSel = id;
  54.         }
  55.       },
  56.       editurl: 'saverow.php'
  57.     }).navGrid('#pager',{view:false, del:false, add:false, edit:false},
  58.       {}, // default settings for edit
  59.       {}, // default settings for add
  60.       {}, // delete instead that del:false we need this
  61.       {closeOnEscape:true, multipleSearch:true, closeAfterSearch:true}, // search options
  62.       {} /* view parameters*/
  63.     );
  64.   });
  65.   </script>
  66. </body>
  67. </html>

* This source code was highlighted with Source Code Highlighter.

На создании самой таблицы я останавливаться не буду, все подробно описано в первой части.
Но те параметры, которые относятся к поиску, рассмотрим подробно.

Прежде всего, обратите внимание на строки 37-40, в которых указываются настройки для полей (столбцов) таблицы.

Для поля id мы добавили параметр search:false, т.е. запретили поиск по этому полю.

Для остальных полей установлен параметр searchoptions
searchoptions:{sopt:['eq','ne','bw','cn']}

С его помощью мы задали доступные условия сравнения. Полный перечень этих настроек можно (и нужно 😉 ) посмотреть в документации к плагину, в данном случае мы использовали:

eq – равно;
ne – не равно;
bw – начинается с;
cn – содержит.

Т.к. в этом примере все поля текстовые, использовать сравнения вроде «больше» и «меньше» особого смысла не имеет.

Теперь обратите внимание на функцию navGrid (строки 57-63). В ней мы отключаем не нужные в данном случае кнопки (строка 57) и указываем настройки поиска (строка 61). Рассмотрим их подробнее:

multipleSearch:true – разрешает поиск сразу по нескольким полям;
closeOnEscape:true – окно поиска закроется при нажатии на клавишу «Esc»;
closeAfterSearch:true – окно поиска закроется после завершения поиска.

Естественно, это далеко не полный список параметров.

На данном этапе мы включили и настроили поиск на стороне клиента. И наша таблица должна выглядеть примерно так.

jqgrid_initial

В левом нижнем углу появилась кнопка «Найти записи» при нажатии на которую открывается окно поиска.

Все отлично, за исключением того, что плагин ничего не ищет 🙂

Дело в том, что при нажатии на кнопку «Найти» плагин только передает условия поиска серверному (php) скрипту, который и должен выполнить поиск.

В данном случае используется тот же скрипт, что и для первоначальной загрузки данных (getdata.php). И мы должны добавить в него поддержку поиска.

Принцип работы будет следующим.

1) Если получен обычный запрос (не поиск) – просто получаем нужно количество записей из БД и отправляем браузеру.

2) Если запрос на поиск. Добавляем условия поиска в запрос и возвращаем результат браузеру.

Теперь рассмотрим, какие параметры передаются браузером серверу при поисковом запросе.

_search true
filters {"groupOp":"AND","rules":[{"field":"surname","op":"eq","data":"Иванов"}]}
nd 1251656518553
page 1
rows 5
sidx id
sord asc

Первый параметр указывает поисковый это запрос или нет. Последние 4 параметра мы рассматривали в первой части и на них останавливаться не будем. Все-таки, напомню, они используются для пагинации (разбивки данных на страницы).

Наибольший интерес представляет второй параметр (filters). Эта строка содержит все условия поиска и записана она в формате JSON.

На практике это означает, что вы можете преобразовать её в объект с помощью функции json_decode.

Для этого примера вы получите такой объект

jqgrid_search_data

Как видите он содержит свойство groupOp значение которого равно AND. И свойство rules, которое содержит массив со всеми введенными условиями поиска.

В каждое условие входит три параметра:
field – имя поля;
op – тип операции сравнения;
data – данные для сравнения.

Используя эти данные, мы можем сконструировать WHERE часть запроса и найти нужные записи.

Обратите внимание. Нужно очень внимательно проверять полученные данные. Иначе ваш скрипт станет уязвимым.

Теперь посмотрите на код скрипта (getdata.php).

  1. <?php
  2. require_once('dbdata.php');
  3.  
  4. try {
  5.   //читаем параметры
  6.   $curPage = $_POST['page'];
  7.   $rowsPerPage = $_POST['rows'];
  8.   $sortingField = $_POST['sidx'];
  9.   $sortingOrder = $_POST['sord'];
  10.   
  11.   //подключаемся к базе
  12.   $dbh = new PDO('mysql:host='.$dbHost.';dbname='.$dbName, $dbUser, $dbPass);
  13.   //указываем, мы хотим использовать utf8
  14.   $dbh->exec('SET CHARACTER SET utf8');
  15.  
  16.   $qWhere = '';
  17.   //определяем команду (поиск или просто запрос на вывод данных)
  18.   //если поиск, конструируем WHERE часть запроса
  19.   if (isset($_POST['_search']) && $_POST['_search'] == 'true') {
  20.     $allowedFields = array('surname', 'fname', 'lname');
  21.     $allowedOperations = array('AND', 'OR');
  22.     
  23.     $searchData = json_decode($_POST['filters']);
  24.  
  25.     //ограничение на количество условий
  26.     if (count($searchData->rules) > 10) {
  27.       throw new Exception('Cool hacker is here!!! :)');
  28.     }
  29.  
  30.     $qWhere = ' WHERE ';
  31.     $firstElem = true;
  32.  
  33.     //объединяем все полученные условия
  34.     foreach ($searchData->rules as $rule) {
  35.       if (!$firstElem) {
  36.         //объединяем условия (с помощью AND или OR)
  37.         if (in_array($searchData->groupOp, $allowedOperations)) {
  38.           $qWhere .= ' '.$searchData->groupOp.' ';
  39.         }
  40.         else {
  41.           //если получили не существующее условие - возвращаем описание ошибки
  42.           throw new Exception('Cool hacker is here!!! :)');
  43.         }
  44.       }
  45.       else {
  46.         $firstElem = false;
  47.       }
  48.       
  49.       //вставляем условия
  50.       if (in_array($rule->field, $allowedFields)) {
  51.         switch ($rule->op) {
  52.           case 'eq': $qWhere .= $rule->field.' = '.$dbh->quote($rule->data); break;
  53.           case 'ne': $qWhere .= $rule->field.' <> '.$dbh->quote($rule->data); break;
  54.           case 'bw': $qWhere .= $rule->field.' LIKE '.$dbh->quote($rule->data.'%'); break;
  55.           case 'cn': $qWhere .= $rule->field.' LIKE '.$dbh->quote('%'.$rule->data.'%'); break;
  56.           default: throw new Exception('Cool hacker is here!!! :)');
  57.         }
  58.       }
  59.       else {
  60.         //если получили не существующее условие - возвращаем описание ошибки
  61.         throw new Exception('Cool hacker is here!!! :)');
  62.       }
  63.     }
  64.   }
  65.   
  66.   //определяем количество записей в таблице
  67.   $rows = $dbh->query('SELECT COUNT(id) AS count FROM users'.$qWhere);
  68.   $totalRows = $rows->fetch(PDO::FETCH_ASSOC);
  69.  
  70.   $firstRowIndex = $curPage * $rowsPerPage - $rowsPerPage;
  71.   //получаем список пользователей из базы
  72.   $res = $dbh->query('SELECT * FROM users '.$qWhere.' ORDER BY '.$sortingField.' '.$sortingOrder.' LIMIT '.$firstRowIndex.', '.$rowsPerPage);
  73.   
  74.   //сохраняем номер текущей страницы, общее количество страниц и общее количество записей
  75.   $response->page = $curPage;
  76.   $response->total = ceil($totalRows['count'] / $rowsPerPage);
  77.   $response->records = $totalRows['count'];
  78.  
  79.   $i=0;
  80.   while($row = $res->fetch(PDO::FETCH_ASSOC)) {
  81.     $response->rows[$i]['id']=$row['id'];
  82.     $response->rows[$i]['cell']=array($row['id'], $row['surname'], $row['fname'], $row['lname']);
  83.     $i++;
  84.   }
  85.   echo json_encode($response);
  86. }
  87. catch (Exception $e) {
  88.   echo json_encode(array('errMess'=>'Error: '.$e->getMessage()));
  89. }
  90.  
  91. // end of getdata.php

* This source code was highlighted with Source Code Highlighter.

Тут тоже мы рассмотрим только ту часть, которая касается поиска.

Прежде всего, мы проверяем параметр $_POST['_search'] (строка 19). Если он равен true, начинаем конструировать запрос.

В строках 20 и 21 объявлены два массива с перечнями полей, по которым разрешен поиск ($allowedFields) и допустимых логических операций ($allowedOperations).

Если параметры, отправленные браузером, не будут входить в эти массивы, то мы расцениваем такую ситуацию как попытку атаки (SQLInjection).

После этого, мы декодируем параметр $_POST['filters'] (строка 23). И сразу проверяем общее количество условий поиска (строки 26-28).

Дело в том, что существует уязвимость, связанная с выполнением длинных запросов.

Поэтому мы ограничим количество условий десятью (цифра взята «с потолка»).

После этого начинаем формировать строку WHERE с условиями (строки 30-63).

Мы перебираем массив $searchData->rules и для каждого элемента конструируем соответствующее условие.

При этом мы выполняем следующие проверки.

1) С помощью функции in_array мы проверяем, входит ли значение $searchData->groupOp в массив $allowedOperations (строка 37). Если да, то добавляем оператор, если нет – считаем, что кто-то пытается провести SQLInjection.

2) Такой же принцип используем и при обработке имен полей таблицы, по которым выполняется поиск (строка 50). Кстати этот метод защиты можно использовать для ограничения доступа к части полей таблицы. Например, если вы не показываете часть полей, которые есть в БД, то логично запретить и поиск по ним.

3) При добавлении условия используем оператор switch со всеми допустимыми сравнениями (строки 51-57).

4) С помощью $dbh->quote(…) экранируем спецсимволы во всех значения, введенных пользователем (строки 52-55).

После этого просто добавляем строку $qWhere к запросам (строки 67 и 72).

Дальнейшее описание работы скрипта вы найдете в первой части.

В общем, разница между обычным отображением данных из таблицы и поиском сводится к выполнению более сложного запроса (с условиями).

Для формы, изображенной на следующем рисунке

jqgrid_search_form

мы получим такой запрос

SELECT * FROM users WHERE surname = 'Иванов' AND fname LIKE '%ван%' AND lname LIKE 'И%' ORDER BY id asc LIMIT 0, 5

Надеюсь, принцип работы вы поняли. Если есть вопросы – спрашивайте, постараюсь ответить.

Интересно почитать

Памятники водопроводчикам и сантехникам