Запросы в mysql из php mysqli query. Отправка запросов к базе данных в PHP. Группируем данные с помощью GROUP BY и PIVOT

Mixed CDatabase::Query( string sql , bool ignore_errors =false, string error_position ="", array Options=array() )

Метод выполняет запрос к базе данных и если не произошло ошибки возвращает результат. В случае успешного выполнения метод возвращает объект класса CDBResult .
ignore_errors равен "true", то метод вернет "false".
Если произошла ошибка и параметр ignore_errors равен "false", то метод прерывает выполнение страницы, выполняя перед этим следующие действия:

1. Вызов функции AddMessage2Log .
2. Если текущий пользователь является администратором сайта, либо в файле /bitrix/php_interface/dbconn.php была инициализирована переменная $DBDebug=true; , то на экран будет выведен полный текст ошибки, в противном случае будет вызвана функция SendError .
3. Будет подключен файл /bitrix/php_interface/dbquery_error.php , если он не существует, то будет подключен файл /bitrix/modules/main/include/dbquery_error.php

Примечания для Oracle версии :
1. При возникновении ошибки, если была открыта транзакция, то выполняется CDataBase::Rollback .
2. Для вставки текстовых полей типа BLOB, CLOB, LONG и т.п. (длинною больше 4000 символов), воспользуйтесь методом CDatabase::QueryBind .
3. Если при выполнении SQL-запроса типа "SELECT" требуется связывание переменных, то воспользуйтесь методом CDatabase::QueryBindSelect .

Нестатический метод.

Аналог метода в новом ядре D7 - .

Параметры

См. также

Примеры использования

Function: GetByID
Line: "; global $DB; $where = ($GET_BY_SID=="N") ? " F.ID = "".intval($ID)."" " : " F.VARNAME="".$DB->ForSql($ID,50)."" "; $strSql = " SELECT F.*, F.FIRST_SITE_ID, F.FIRST_SITE_ID LID, F.VARNAME, F.VARNAME SID, ".$DB->DateToCharFunction("F.TIMESTAMP_X")." TIMESTAMP_X, count(distinct D1.ID) C_FIELDS, count(distinct D2.ID) QUESTIONS, count(distinct S.ID) STATUSES FROM b_form F LEFT JOIN b_form_status S ON (S.FORM_ID = F.ID) LEFT JOIN b_form_field D1 ON (D1.FORM_ID = F.ID and D1.ADDITIONAL="Y") LEFT JOIN b_form_field D2 ON (D2.FORM_ID = F.ID and D2.ADDITIONAL<>"Y") WHERE $where GROUP BY F.ID "; $res = $DB->Query ($strSql, false, $err_mess.__LINE__); return $res; } ?>

elar 17.09.2009 09:17:22

Данный пример говоря программистским языком не совсем рабочий. Класс "CForm" не найден, может для этого надо как-то дополнительно объявлять объект. Запрос выдает ошибку т.к. в базе нет определенных полей (что может возникнуть у многих пользователей). Поэтому данный код к сожалению является не лучшим примером, особенно для тех кто имеет не особо сильный опыт в программировании под битрикс. Проще использовать вот такой вариант: Преимущество этого кода, в том, что он будет выполнятся абсолютно на любой странице сайта и он очень простой.

«Битрикс», 2001-2019, «1С-Битрикс», 2019

Спасибо Евгению Намоконову за помощь в подготовке материала.

Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.

Синтаксис функции:

=QUERY(данные; запрос; [заголовки])

• данные - это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
• запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
• заголовки - количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.

Итак, правила формирования запросов:

1. Запрос указывается в кавычках.
2. В запросе используются ключевые слова:
   1. SELECT - определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
   2. WHERE - ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
   3. GROUP BY - группирует значения по заданным полям.
   4. PIVOT - позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
   5. ORDER BY - задает сортировку. Например: «ORDER BY C DESC» - сортировка по столбцу C по убыванию.
   6. LIMIT - ограничивает количество возвращаемых строк. Например: «LIMIT 50».
   7. OFFSET - пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
   8. FORMAT - определяет формат определенных столбцов по заданному шаблону.
   9. LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году"». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».

Справка от Google по языку запросов API находится по ссылке: https://developers.google.com/chart/interactive/docs/querylanguage

Рассмотрим несколько примеров применения QUERY на практике.

Простой пример: выбираем книги определенной тематики из таблицы

Из простой исходной таблицы будем формировать список книг по тематике:

При этом тематику будем выбирать из выпадающего списка на отдельном листе:

Функция QUERY для решения этой задачи будет выглядеть следующим образом:

=QUERY(Книги ‘!A1:C ; «SELECT A, C WHERE B = ‘» & A1 & «‘ ORDER BY C DESC» ; 1 )

Мы извлекаем данные из столбцов A и C в диапазоне ‘Книги’!A1:C. Фильтруем данные по столбцу B (тематике) этого диапазона по выбранному критерию из выпадающего списка в ячейке A1. Сортируем по убыванию по столбцу C исходного диапазона и добавляем к нашей выборке заголовки (последний аргумент функции QUERY = 1).

Группируем данные с помощью GROUP BY и PIVOT

Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.

Таблица, с которой мы будем работать:

Задачей будет вывести сумму продаж по каждой тематике, то есть сгруппировать данные по столбцу B.

Начнем с GROUP BY, текст функции будет таким:

=QUERY(‘Книги ‘!A1:C6;»select B, sum(C) group by B»)

Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().

Результат нашей формулы:

С помощью GROUP BY возможна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:

Группировка с помощью PIVOT.

Обратите внимание, что здесь в SELECT не нужно писать столбец B, по которому данные будут сгруппированы.

Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.

Добавим еще один столбец для группировки.

Видите — два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.

Строим сводную таблицу со средними/максимальными значениями по тематикам

В этом примере мы построим небольшую сводную таблицу, где будут отображены средние значения по тематикам за два года:

QUERY(‘ Книги ‘!A1:D ; «SELECT avg(C), avg(D) pivot B» ; 1 )

Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).

Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):

Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):

Или отобразить и среднее, и максимум, но только по столбцу D:

SELECT avg(D), max(D)

Кейс «Считаем средний чек, выбирая данные с определенной даты»

На скриншоте массив данных, с которым мы будем работать:

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

Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:

Правильно использовать дату в формуле QUERY так:

• QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
• перед датой и перед апострофом нужно написать date;
• можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:

• дата с двух сторон обрамляется одиночными кавычками (‘).

Вернемся к тому, что у нас получилось. Наша формула выдала вот такой массив данных:

Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (SUM):

Чтобы получить средний чек, получившееся число нужно разделить на общую сумму клиентов в отобранных строках. Чтобы закрепить использование QUERY, опять воспользуемся этой формулой.

Берем предыдущую формулу, меняем B*C на sum(B) и получаем такую конструкцию:

Наконец, совмещаем формулы:

Все работает, ура! 53 (этот результат видно на всплывающей подсказке в верхнем левом углу) - средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.

Кейс «QUERY и выпадающий список»

Возьмем табличку с продажами книг. На ее основе будем делать отчет с выпадающим списком, в котором будут все тематики, и формулой QUERY, выводящей книги выбранной тематики и сортирующей их по продажам.

Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных .

В Правилах выбираем Значение из списка , перечисляем все наши тематики через запятую и нажимаем Сохранить :

Список получился вот таким:

В соседнюю ячейку А2 впишем следующую формулу:

=QUERY(‘ Книги ‘!A1:C13 , «SELECT A, C WHERE B = ‘» & A1 & «‘ ORDER BY C DESC» )

И разберем ее по частям:

• ‘Книги’!A1:C13 - исходный диапазон, таблица с продажами, книгами и тематиками.
• SELECT A, C - в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
• WHERE B = ‘»&A1&»‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1. Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
• ORDER BY C DESC - сортируем данные по столбцу B (продажам) по убыванию.

Результат:

Изменив тематику в ячейке А1 на философию, мы получим книги только по философии, отсортированные по продажам. Удобно.

Если бы в нашей исходной таблице была дата, мы могли бы добавить ее в условие QUERY и выводить данные за выбранный день, месяц, неделю - таким образом можно получить готовый отчет по продажам, который не нужно каждый раз заново собирать.

Query по нескольким диапазонам данных

В качестве первого аргумента функции QUERY можно указать массив, состоящий из нескольких диапазонов данных. Главное, чтобы эти диапазоны были таблицами с одинаковой структурой.

Диапазоны указываются через точку с запятой в фигурных скобках:

=QUERY({Диапазон 1; Диапазон 2; Диапазон 3; Диапазон 4};…

Важно отметить: в таком случае столбцы внутри запроса обозначаются не буквами, как в других случаях (A, B, AH, CZ и так далее), а в виде ColN, где N — номер столбца.

Канал «Google Таблицы — это просто» в Телеграме

Спасибо Евгению Намоконову за помощь в подготовке кейсов для этой статьи. Мы с Евгением ведем канал в Телеграме по Google Таблицам.

Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач.

Я считаю Query одной из наиболее полезных функций Google Таблиц. Но в справке Google она описывается очень поверхностно, и вся мощь данной функции не раскрыта. При более детальном знакомстве становится ясно, что она способна заменить большую часть существующих функций.

Для работы с QUERY вам понадобятся базовые знания SQL. Для тех, кто не в курсе: пугаться не надо, функция QUERY на самом деле поддерживает самые простые возможности SQL.

Синтаксис QUERY

QUERY(данные; запрос; [заголовки])

• данные — это диапазон ячеек, который будет служить базой данных для SQL запроса;
• запрос — текст SQL запроса;
• заголовки — необязательный аргумент, в котором вы можете указать, сколько первых строк массива содержат заголовки.

Для максимального восприятия дальнейшей информации предлагаю открыть и скопировать себе следующую Google Таблицу

В доксе, копию которого вы только что создали, существует несколько листов. Лист DB - это база данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.

План SQL запроса в функции Query

Любой SQL запрос состоит из отдельных блоков, которые часто называют кляузами. В SQL для функции Query заложен синтаксис языка запросов API визуализации Google, который поддерживает следующие кляузы:

• select — перечисление полей, которые будут возвращены запросом;
• where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;
• group by — содержит перечень полей, по которым вы хотите группировать результат;
• pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;
• order by — отвечает за сортировку результатов;
• limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;
• offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обрабатывать запросом;
• label — данная кляуза отвечает за название полей, возвращаемых запросом;
• format — отвечает за формат выводимых данных;
• options — дает возможность задавать дополнительные параметры вывода данных.

Hello World для функции Query (Select)

Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.

Query(DB!A1:L1143;"select * limit 100")

Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100 » содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100 » мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.

Используем фильтры и сортировку (Where, Order by)

Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order . Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select .

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

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

• поле Date — столбец A;
• поле Campaign — столбец B;
• поле Sessions — столбец G.

Соответственно, часть запроса, отвечающая за перечень выводимых в результате данных, будет выглядеть так:

Select A, B, G

Далее в запросе идет кляуза Where . При написании запроса кляузы обязательно должны располагаться в таком порядке, в котором были описаны в первом разделе этой статьи. После объявления Where нам необходимо перечислить условия фильтрации. В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date. Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

WHERE (A >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2")

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

Query(DB!A1:L1143;" Select A, B, G WHERE (A >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2")")

Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат:

Помимо обычных логических операторов (=, <, >) блок WHERE поддерживает дополнительные операторы фильтрации:

• contains — проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver;
• starts with — фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english;
• ends with — фильтрует значения по окончанию строки. Например, строка ‘cowboy’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»;
• matches — соответствует регулярному выражению. Например: where matches ‘.*ia’ вернёт значения India и Nigeria.
• like — упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre’, ‘fred’, и ‘freddy’.

Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by . По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:

Order by G desc

Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:

Query(DB!A1:L1143;" SELECT A, B, G WHERE (A >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2") ORDER BY G DESC")

Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.

mysql_db_query — Переключается к указанной базе данных и посылает запрос

Описание

Resource mysql_db_query (string $database , string $query [, resource $link_identifier ])

Возвращает указатель на результат запроса к MySQL или FALSE при ошибке. Функция также возвращает TRUE / FALSE для INSERT /UPDATE /DELETE запросов для индикации успеха/провала.

mysql_db_query() выбирает указанную базу данных и выполняет указанный запрос. Если опциональный параметр указателя на соединение не указан, функция будет использовать последнее открытое соединение. Если нет ни одного открытого соединения, функция попытается создать новое, аналогично функции mysql_connect() , вызванной без параметров.

Учтите, что эта функция НЕ переключает соединение обратно к предыдущей базе данных. Другими словами, вы не можете использовать эту функцию, чтобы временно переключиться на другую базу данных и выполнить запрос. Переключиться обратно вам придётся вручную. Крайне рекомендуется использовать синтаксис database.table в SQL-запросах, вместо использования этой функции.

См. также mysql_connect() и

resource mysql_query (string query [, resource link_identifier])

mysql_query() посылает запрос активной базе данных сервера, на который ссылается переданный указатель. Если параметр link_identifier опущен, используется последнее открытое соединение. Если открытые соединения отсутствуют, функция пытается соединиться с СУБД, аналогично функции mysql_connect() без параметров. Результат запроса буфферизируется.

Замечание: Строка запроса НЕ должна заканчиваться точкой с запятой.

Только для запросов SELECT, SHOW, EXPLAIN, DESCRIBE, mysql_query() возвращает указатель на результат запроса, или FALSE если запрос не был выполнен.
В остальных случаях (INSERT, UPDATE, DELETE, DROP, и т.п.), mysql_query() возвращает TRUE в случае успешного запроса и FALSE в случае ошибки. Значение не равное FALSE говорит о том, что запрос был выполнен успешно. Он не говорит о количестве затронутых или возвращённых рядов. Вполне возможна ситуация, когда успешный запрос не затронет ни одного ряда.

Следующий запрос составлен неправильно и mysql_query() вернёт FALSE :

mysql_query() также считается ошибочным и вернёт FALSE , если у вас не хватает прав на работу с указанной в запросе таблицей.

Работая с результатами запросов, вы можете использовать функцию mysql_num_rows() , чтобы найти число, возвращённых запросом SELECT, рядов, или mysql_affected_rows() , чтобы найти число рядов, обработанных запросами DELETE, INSERT, REPLACE, или UPDATE.

Только для запросов SELECT, SHOW, DESCRIBE, EXPLAIN, функция mysql_query() возвращает указатель на результат, который можно использовать в функции mysql_fetch_array() и других функциях, работающих с результатами запросов. Когда работа с результатом окончена, вы можете освободить ресурсы, используемые для его хранения, с помощью функции