Поиск с помощью индекса FULLTEXT

Задача
Вы хотите выполнить поиск в тексте большого объема.

Решение
Используйте индекс FULLTEXT.

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

SELECT * from имя_таблицы
WHERE столбец1 LIKE 'шаблон' OR столбец2 LIKE 'шаблон' OR столбец3 LIKE 'шаблон' ...


Полезной альтернативой (доступной начиная с версии MySQL 3.23.23) является использование FULLTEXTпоиска, предназначенного для просмотра больших объемов текста c одновременным просмотром нескольких столбцов. Добавьте в таблицу индекс FULLTEXT, затем используйте оператор MATCH для поиска строк индексированного столбца или столбцов. Индексирование FULL TEXT может применяться в таблицах MyISAM для столбцов типа CHAR, VARCHAR или TEXT.

FULLTEXTпоиск лучше всего продемонстрировать на тексте подходящего размера. Если у вас нет тестового набора данных, можно воспользоваться одним из свободно доступных хранилищ электронных текстов, имеющихся в Интернете. В примерах данного раздела использован текст Библии в версии King James Version (KJV) – достаточно большой и очень хорошо структурированный текст: книга, глава, стих. Изза своего объема этот набор данных не включен в дистрибутив recipes, но для него на вебсайте книги «MySQL
Сookbook» создан собственный дистрибутив mcb kjv1

Дистрибутив включает файл kjv.txt, который содержит записи стихов. Записи выглядят так:

O Genesis 1 1 1 In the beginning God created the heaven and the earth.
O Exodus 2 20 13 Thou shalt not kill.
N Luke 42 17 32 Remember Lot's wife.

Каждая запись содержит поля:
• Раздел книги. Это или O, или N, что означает Ветхий (Old) и Новый (New) Завет.
• Название книги и соответствующий номер, от 1 до 66.
• Номер главы и стиха.
• Текст стиха.

Чтобы импортировать записи в MySQL, создайте такую таблицу kjv:
CREATE TABLE kjv
(
    bsect   ENUM('O','N') NOT NULL,         # раздел книги (Завет)
    bname   VARCHAR(20) NOT NULL,           # название книги
    bnum    TINYINT UNSIGNED NOT NULL,      # номер книги
    cnum    TINYINT UNSIGNED NOT NULL,      # номер главы
    vnum    TINYINT UNSIGNED NOT NULL,      # номер стиха
    vtext   TEXT NOT NULL                   # текст стиха
) TYPE = MyISAM;


Затем загрузите файл kjv.txt в таблицу, выполнив такое предложение:

LOAD DATA LOCAL INFILE 'kjv.txt' INTO TABLE kjv;


Таблица kjv содержит столбцы как для названий книг (Genesis – Книга Бытия, Exodus – Исход, ...), так и для их номеров (1, 2, ...). Названия и номера книг четко соответствуют друг другу, и одни могут быть однозначно получены из других. Налицо избыточность данных, то есть таблица не приведена к нормальной форме. Чтобы избавиться от такой избыточности, можно хранить только номера книг (они занимают меньше места, чем названия) и при необходимости выводить названия книг как результаты запроса, используя
соединение (join) с простой вспомогательной таблицей, сопоставляющей номерам книг их названия. Но пока я хочу избежать соединения. Поэтому таблица будет содержать названия книг для удобства восприятия результатов поиска и номера книг для удобства сортировки результатов по книгам.

После заполнения таблицы данными подготовим ее к полнотекстовому поиску, добавив индекс FULLTEXT. Для этого выполним предложение ALTER TABLE:1

ALTER TABLE kjv ADD FULLTEXT (vtext);


Чтобы выполнить поиск по индексу, используем MATCH() для указания индексированного столбца и AGAINST() для определения того, какой текст следует искать. Например, чтобы ответить на вопрос «Как часто встречается имя Mizraim» (ведь вас это всегда интересовало, не так ли?), будем просматривать столбец vtext при помощи такого запроса:

SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Mizraim');


+------------ +
| COUNT(*) |
+ ------------+
| 4 |
+ ------------+

Чтобы найти соответствующие стихи, выберем столбцы, которые вы хотели бы видеть (для того чтобы результат поместился на странице, в примере используется \G):

SELECT bname, cnum, vnum, vtext
    > FROM kjv WHERE MATCH(vtext) AGAINST('Mizraim')\G

*************************** 1. row ***************************
bname: Genesis
cnum: 10
vnum: 6
vtext: And the sons of Ham; Cush, and Mizraim, and Phut, and Canaan.
*************************** 2. row ***************************
bname: Genesis
cnum: 10
vnum: 13
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
*************************** 3. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 8
vtext: The sons of Ham; Cush, and Mizraim, Put, and Canaan.
*************************** 4. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 11
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,

В данном конкретном случае результаты выводятся по порядку номеров книг, глав и стихов, но это простая случайность. По умолчанию FULLTEXTпоиск вычисляет величину релевантности и сортирует результаты начиная с наиболее релевантных. Чтобы обеспечить сортировку результата в необходимом вам порядке, добавьте явную инструкцию ORDER BY:

SELECT bname, cnum, vnum, vtext
FROM kjv WHERE MATCH(vtext) AGAINST('строка_поиска')
ORDER BY bnum, cnum, vnum;


Для сужения области поиска можно задать дополнительные условия. В следующем фрагменте выполняются постепенно уточняющиеся запросы для нахождения частоты упоминания имени Abraham во всем тексте KJV, в Новом Завете (New Testament), в книге «К евреям» (Hebrews) и в главе 11этой книги:

SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Abraham');


+------------ +
| COUNT(*) |
+ ------------+
| 216 |
+ ------------+

SELECT COUNT(*) from kjv
    > WHERE MATCH(vtext) AGAINST('Abraham')
    > AND bsect = 'N';


+------------ +
| COUNT(*) |
+------------ +
| 66 |
+ ------------+

SELECT COUNT(*) from kjv
    > WHERE MATCH(vtext) AGAINST('Abraham')
    > AND bname = 'Hebrews';


+------------ +
| COUNT(*) |
+ ------------+
| 10 |
+ ------------+

SELECT COUNT(*) from kjv
    > WHERE MATCH(vtext) AGAINST('Abraham')
    > AND bname = 'Hebrews' AND cnum = 11;


+------------ +
| COUNT(*) |
+ ------------+
| 2 |
+ ------------+

Если вы планируете использовать условия поиска, относящиеся к другим не FULLTEXT столбцам, то для повышения производительности таких запросов можно добавить обычные индексы для этих столбцов. Например, можно проиндексировать столбцы номеров книги, главы и стиха:

ALTER TABLE kjv ADD INDEX (bnum), ADD INDEX (cnum), ADD INDEX (vnum);


Строка поиска в запросах FULLTEXT может представлять собой не только отдельное слово. Казалось бы, указание дополнительных слов в строке поиска должно делать поиск более точным. Но на самом деле поиск только расширяется, так как при полнотекстовом поиске возвращаются записи, содержащие любое из указанных слов (фактически выполняется поиск с логическим ИЛИ для всех указанных слов). Рассмотрим запросы, возвращающие все
большее количество стихов по мере добавления новых слов поиска:

SELECT COUNT(*) from kjv
    > WHERE MATCH(vtext) AGAINST('Abraham');


+------------ +
| COUNT(*) |
+ ------------+
| 216 |
+ ------------+

SELECT COUNT(*) from kjv
    > WHERE MATCH(vtext) AGAINST('Abraham Sarah');


+------------ +
| COUNT(*) |
+ ------------+
| 230 |
+ ------------+

SELECT COUNT(*) from kjv
    > WHERE MATCH(vtext) AGAINST('Abraham Sarah Ishmael Isaac');


+ ------------ +
| COUNT(*) |
+ ------------+
| 317 |
+ ------------+

Если вы хотите использовать FULLTEXTпоиск для параллельного просмотра нескольких толбцов, укажите их имена при создании индекса:

ALTER TABLE имя_таблицы ADD FULLTEXT (столбец1, столбец2, столбец3);

Чтобы создать запрос, использующий такой индекс, укажите имена тех же самых столбцов в списке MATCH():

SELECT ... FROM имя_таблицы
WHERE MATCH(столбец1, столбец2, столбец3) AGAINST('строка_поиска');

Добавлено: 12 Июля 2018 08:22:14 Добавил: Андрей Ковальчук

Управление чувствительностью к регистру при поиске по образцу

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

Решение
Измените чувствительность строк к регистру.

Обсуждение
По умолчанию операция LIKE не чувствительна к регистру:

SELECT name, name LIKE '%i%', name LIKE '%I%' FROM metal;


+ ----------+--------------------- + ---------------------+
| name | name LIKE '%i%' | name LIKE '%I%' |
+ ----------+ --------------------+--------------------- +
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |
| silver | 1 | 1 |
| tin | 1 | 1 |
+---------- + ---------------------+-------------------- +

В настоящий момент не чувствительна к регистру и операция REGEXP.

SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal;


+----------- +-------------------- + --------------------+
| name | name REGEXP 'i' | name REGEXP 'I' |
+ -----------+ --------------------+ --------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |
| silver | 1 | 1 |
| tin | 1 | 1 |
+ ----------+ ---------------------+------------------- +

Однако до версии MySQL 3.23.4 операции REGEXP были чувствительны к регистру:

SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal;


+---------- +-------------------- +-------------------- +
| name | name REGEXP 'i' | name REGEXP 'I' |
+---------- +-------------------- +-------------------- +
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 0 |
| silver | 1 | 0 |
| tin | 1 | 0 |
+ -----------+------------------ + ---------------------+

Обратите внимание на то, что текущее поведение REGEXP (нечувствительность к регистру) может привести к некоторым интуитивно непонятным результатам:

SELECT 'a' REGEXP '[[:lower:]]', 'a' REGEXP '[[:upper:]]';


+--------------------------- + ----------------------------+
| 'a' REGEXP '[[:lower:]]' | 'a' REGEXP '[[:upper:]]' |
+--------------------------- +---------------------------- +
| 1 | 1 |
+ ---------------------------+-----------------------------+

Оба выражения истинны, так как в случае нечувствительности к регистру [:lower:] и [:upper:] эквиваленты. Изменить нежелательное для вас поведение операции поиска по образцу в отношении чувствительности к регистру можно посредством тех же приемов,
что и для операции сравнения строк:

• Чтобы сделать поиск по образцу чувствительным к регистру, используйте двоичную строку для любого из операндов (например, при помощи ключевого слова BINARY). Следующий запрос показывает, что обычно недвоичный столбец name не чувствителен к регистру:

SELECT name, name LIKE '%i%%', name REGEXP 'i' FROM metal;


+ ----------+ ------------------------+ -------------------+
| name | name LIKE '%i%%' | name REGEXP 'i' |
+ ----------+ ------------------------+ -------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |
| silver | 1 | 1 |
| tin | 1 | 1 |
+---------- + ------------------------+------------------- +

Используем ключевое слово BINARY, чтобы заставить значения name стать чувствительными к регистру:

SELECT name, BINARY name LIKE '%I%', BINARY name REGEXP 'I' FROM metal;


+----------- +------------------------------- +------------------------------ +
| name | BINARY name LIKE '%I%' | BINARY name REGEXP 'I' |
+----------- + -------------------------------+ ------------------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+ -----------+------------------------------ +------------------------------ +

Использование BINARY заставляет [:lower:] и [:upper:] работать в регулярных выражениях так, как вам хотелось бы. Второе выражение следующего запроса выдает результат, который на самом деле является истинным только для букв верхнего регистра:

SELECT 'a' REGEXP '[[:upper:]]', BINARY 'a' REGEXP '[[:upper:]]';


+ ----------------------------+-------------------------------------- +
| 'a' REGEXP '[[:upper:]]' | BINARY 'a' REGEXP '[[:upper:]]' |
+ ----------------------------+ -------------------------------------+
| 1 | 0 |
+---------------------------- + -------------------------------------+

• Поиск по образцу для двоичного столбца чувствителен к регистру. Чтобы сделать его нечувствительным, преобразуйте оба операнда в один регистр. Давайте изменим таблицу metal, добавив в нее столбец binname, аналогичный столбцу name, но имеющий тип VARCHAR BINARY, а не VARCHAR:

mysql> ALTER TABLE metal ADD binname VARCHAR(20) BINARY;
mysql> UPDATE metal SET binname = name;

Первый из представленных ниже запросов показывает, что двоичный столбец binname чувствителен к регистру при поиске по образцу, а второй запрос показывает, как заставить столбец изменить свое поведение с помощью UPPER():

SELECT binname, binname LIKE '%I%', binname REGEXP 'I'
    > FROM metal;


+ -----------+ -------------------------+ -----------------------+
| binname | binname LIKE '%I%' | binname REGEXP 'I' |
+ -----------+------------------------- +----------------------- +
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+----------- +------------------------- + ----------------------+

SELECT binname, UPPER(binname) LIKE '%I%', UPPER(binname) REGEXP 'I'
    > FROM metal;


+ ------------+---------------------------------- +----------------------------------+
| binname | UPPER(binname) LIKE '%I%' | UPPER(binname) REGEXP 'I' |
+------------ +----------------------------------+ ----------------------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |
| silver | 1 | 1 |
| tin | 1 | 1 |
+----------- + -----------------------------------+--------------------------------- +

Добавлено: 12 Июля 2018 08:11:52 Добавил: Андрей Ковальчук

Управление чувствительностью к регистру при сравнении строк

Sql

Задача
Сравнение строк чувствительно к регистру тогда, когда это нежелательно, или наоборот.

Решение
Измените чувствительность строк к регистру.

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

По умолчанию сравнение строк в MySQL не чувствительно к регистру:

SELECT name, name = 'lead', name = 'LEAD' FROM metal;

+---------- + ----------------+------------------ +
| name | name = 'lead' | name = 'LEAD' |
+ ----------+---------------- +------------------ +
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 1 | 1 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+ ----------+---------------- + ------------------+

Нечувствительность к регистру затрагивает и сравнения на взаимный порядок:
SELECT name, name < 'lead', name < 'LEAD' FROM metal;

+---------- +---------------- +------------------ +
| name | name < 'lead' | name < 'LEAD' |
+---------- +---------------- +------------------ +
| copper | 1 | 1 |
| gold | 1 | 1 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+ ----------+----------------- +------------------ +

Если вы знакомы со схемой сортировки ASCII, то знаете, что коды ASCII для букв нижнего регистра больше, чем коды букв верхнего регистра, так что результаты второго столбца должны были бы вас удивить. Такие результаты показывают, что по умолчанию упорядочение строк производится без учета регистра букв, так что и A, и a считаются лексически меньшими, чем B. Сравнения строк чувствительны к регистру, только если хотя бы один из операндов является двоичной строкой. Существуют следующие методы
контроля чувствительности к регистру в операциях сравнения строк:

• Чтобы сделать чувствительным к регистру сравнение, которое само по себе таким не было, приведите один из операндов в двоичную форму, используя ключевое слово BINARY. Не имеет значения, какую именно из строк вы сделаете двоичной, – как только одна из них станет двоичной, сравнение станет чувствительным к регистру:
SELECT name, name = BINARY 'lead', BINARY name = 'LEAD' FROM metal;

+---------- +-------------------------- +---------------------------- +
| name | name = BINARY 'lead' | BINARY name = 'LEAD' |
+---------- +-------------------------- +---------------------------- +
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 1 | 0 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+---------- +-------------------------- +---------------------------- +

Начиная с MySQL 3.23 в качестве оператора приведения типа используется BINARY.
• Чтобы сделать нечувствительной к регистру операцию сравнения, которая должна была бы учитывать регистр, преобразуйте обе строки к одному регистру, используя функцию UPPER() или LOWER():
SELECT UPPER('A'), UPPER('b'), UPPER('A') < UPPER('b');

+------------- +------------- +----------------------------- +
| UPPER('A') | UPPER('b') | UPPER('A') < UPPER('b') |
+------------- + -------------+ -----------------------------+
| A | B | 1 |
+------------- +------------- +----------------------------- +
SELECT LOWER('A'), LOWER('b'), LOWER('A') < LOWER('b');

+-------------- +-------------- +------------------------------- +
| LOWER('A') | LOWER('b') | LOWER('A') < LOWER('b') |
+-------------- +-------------- +------------------------------- +
| a | b | 1 |
+-------------- +-------------- +------------------------------- +

Эти же приемы можно применять и к функциям сравнения строк. Например, функция STRCMP() принимает два строковых аргумента и возвращает –1, 0 или 1 в зависимости от того, лексически меньше, равна или больше первая строка по отношению ко второй. До версии MySQL 4.0.0 включительно функция STRCMP() была чувствительна к регистру; она всегда воспринимала свои аргументы как двоичные строки независимо от их реального типа:
SELECT STRCMP('Abc','abc'), STRCMP('abc','abc'), STRCMP('abc','Abc');

+------------------------ +------------------------ +------------------------ +
| STRCMP('Abc','abc') | STRCMP('abc','abc') | STRCMP('abc','Abc') |
+------------------------ +------------------------ +------------------------ +
| 1 | 0 | 1 |
+------------------------ +------------------------ +------------------------ +

Однако начиная с MySQL 4.0.1 функция STRCMP() больше не чувствительна к регистру:
SELECT STRCMP('Abc','abc'), STRCMP('abc','abc'), STRCMP('abc','Abc');

+------------------------ +------------------------ +------------------------ +
| STRCMP('Abc','abc') | STRCMP('abc','abc') | STRCMP('abc','Abc') |
+------------------------ +------------------------ +------------------------ +
| 0 | 0 | 0 |
+------------------------ +------------------------ +------------------------ +

Чтобы сохранить поведение функции в версиях до 4.0.1, сделайте один из ее аргументов двоичной строкой:
SELECT STRCMP(BINARY 'Abc','abc'), STRCMP(BINARY 'abc','Abc');

+---------------------------------- + ----------------------------------+
| STRCMP(BINARY 'Abc','abc') | STRCMP(BINARY 'abc','Abc') |
+---------------------------------- +---------------------------------- +
| 1 | 1 |
+ ----------------------------------+---------------------------------- +

Кстати, заметьте, что нулевое и ненулевое значения, возвращаемые функцией STRCMP(), означают равенство и неравенство соответственно. В этом отличие функции от оператора сравнения =, который возвращает нулевое и не нулевое значения для неравенства и равенства соответственно. Чтобы избежать проблем, запомните основные правила, определяющие, является ли строка двоичной:

• Любую буквенную строку, строковое выражение или строковый столбец можно сделать двоичными, предварив их ключевым словом BINARY. Если же ключевого слова нет, действуют следующие правила.
• Строковое выражение является двоичным, если хотя бы одна из составляющих его строк двоичная, иначе оно не является двоичным. Например,результат, возвращенный выражением CONCAT(), является двоичным, так как второй аргумент – двоичный:
CONCAT('This is a ',BINARY 'binary',' string')

• Чувствительность строкового столбца к регистру определяется его типом. Типы CHAR и VARCHAR по умолчанию не чувствительны к регистру, но их можно объявить как BINARY, тогда они станут чувствительными к регистру. Столбцы типов ENUM, SET и TEXT не чувствительны к регистру, а столбцы типа BLOB – чувствительны.

Итак, операции сравнения чувствительны к регистру, если в них участвует двоичная буквенная строка, или строковое выражение, или столбец типа CHAR BINARY, VARCHAR BINARY или BLOB. Сравнения же, в которых участвуют только недвоичные буквенные строки, или строковые выражения, или столбцы типа CHAR, VARCHAR, ENUM, SET или TEXT, не чувствительны к регистру. Столбцы ENUM и SET не чувствительны к регистру. Более того, поскольку они внутренне хранятся в числовом виде, их нельзя объявлять как чувствительные к регистру в определении таблицы (добавляя ключевое слово BINARY). Но
вы можете поставить ключевое слово BINARY в сравнении перед значениями ENUM и SET, чтобы сделать операцию чувствительной к регистру.

Чувствительность к регистру и скорость выполнения сравнений
Обычно чувствительные к регистру сравнения, содержащие двоичные строки, работают немного быстрее, чем нечувствительные к регистру, так как MySQL не приходится во время операции приводить буквы кодному регистру.

Если оказалось, что вы объявили столбец, используя тип, несовместимый с теми операциями сравнения, которые предполагается для него применять, вы можете изменить тип столбца при помощи предложения ALTER TABLE. Предположим, что у вас есть таблица для хранения новостных статей:
CREATE TABLE news
(
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    article BLOB NOT NULL,
    PRIMARY KEY (id)
);

Столбец article объявлен как BLOB, то есть тип, чувствительный к регистру. Если вы захотите преобразовать столбец так, чтобы он не был чувствителен к регистру, то можете изменить его тип на TEXT, используя одно из предложений ALTER TABLE:
ALTER TABLE news MODIFY article TEXT NOT NULL;
ALTER TABLE news CHANGE article article TEXT NOT NULL;

До версии MySQL 3.22.16 предложение ALTER TABLE ... MODIFY недоступно, и если вы работаете с более ранней версией, то можете использовать только ALTER TABLE ... CHANGE.

Добавлено: 05 Июля 2018 08:31:20 Добавил: Андрей Ковальчук

Регламентное обслуживание всех таблиц БД MySQL

Выдаем команду CHECK по всем таблицам базы последовательно, если в результате этого запроса будет содержаться негативный результат, то запускается операция восстановления базы (REPAIR). После того как выполнена проверка таблицы, проводится ее оптимизация.

<?php
$database="test"; 
mysql_connect("localhost", "root", "$database"); 
$res=mysql_query("SHOW TABLES IN ".$database.";"); 
while ( $o=mysql_fetch_row($res) ) 
{ 
 $res_ch=mysql_query("CHECK TABLE $database.$o[0] EXTENDED;"); 
 $o_ch=mysql_fetch_row(); 
 $o_rp[3]="No"; 
 if ( $o_ch[3]!="OK" ) 
 { 
 $res_rp=mysql_query("REPAIR TABLE $database.$o[0] EXTENDED;"); 
 $o_rp=mysql_fetch_row(); 
 } 
 $res_op=mysql_query("OPTIMIZE TABLE $database.$o[0] EXTENDED;"); 
 $o_op=mysql_fetch_row(); 
 echo "$o[0] CHECK:$o_ch[3] REPAIR: $o_rp[3], OPTIMIZE: $o_op[3]\n"; 
} 

print "База данных ".$database." проверенна и оптимизированна.<br>\n";
?>

Добавлено: 29 Мая 2018 19:29:37 Добавил: Андрей Ковальчук

Подсчёт суммы в MySql

Очень часто бывает необходимость подсчитать сумму в таблице mysql. Если быть точнее-сумму значений в определённых ячейках таблицы.
Это делается спомощью оператора SUM. Этому оператору передается имя поля в качестве аргумента.
Например имеем таблицу users пользователей, с указанием id, name, dateregister и money.

id name dateregister money
1 Вася 1312700694 125
2 Саша 1314555798 47
3 Коля 1316071029 36
4 Пётр 1318137334 8
5 Агафон 1320819968 15

И нам нужно подсчитать общее количество денег у всех пользователей.Это делается так:
select sum(money) as `total_money` from `users`
В итоге мы получем одну строку состоящую из одной ячейки с именем total_money,которое мы указали в качестве псевдонима(указать можете любое).

Тот же самый запрос на DataBase API Drupal 7 будет выглядеть так:
$q->db_select('users','u');
$q->addExpression('SUM(u.money)','total_money');
$q->execute();

Эта форма записи более громоздка,но на деле гораздо удобнее.
Почитать об этом API и посмотреть более сложные примеры можно тут

Если в запросе нужно поставить условие-используем оператор where. Например посчитать общую сумму денег, только у пользователей, чей баланс более 20.
select sum(money) as `total_money` from `users` where `money`>20

И соответственно на Drupal
$q->db_select('users','u');
$q->addExpression('SUM(u.money)','total_money');
$q->condition('u.money',20,'>');
$q->execute();

Вот впринципе и всё. Если возникли вопросы-пишите в комментариях, дополню статью.

Добавлено: 29 Мая 2018 09:01:21 Добавил: Андрей Ковальчук

Оптимизация всех таблиц БД MySQL

Чтобы объединить фрагментированные записи и избавиться от потерь пространства, происходящих из-за удаления и обновления записей необходимо воспользоваться командой MySQL OPTIMIZE TABLE. Здесь представлена универсальная процедура оптимизации всех таблиц базы данных.

<?php
$db_host = '127.0.0.1'; 
$db_user = 'mysql'; 
$db_pass = 'mysql_password';
$db_name = 'myBase'
    
set_time_limit(0);

mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error() . "\n\n");
mysql_select_db($db_name) or die(mysql_error() . "\n\n");

$r = mysql_query("SHOW TABLES");

$q = "LOCK TABLES";

while($row = mysql_fetch_row($r))
{
  $table[] = $row[0];
  $q .= " " . $row[0]." WRITE,";
}
$q = substr($q,0,strlen($q)-1);
mysql_query($q);

print "База данных заблокированна для чтения/записи.<br>\n";

foreach($table as $value)
{
  $q = "OPTIMIZE TABLE ".$value;
  print $q; flush();
  mysql_query($q) or die("QUERY: \"$q\" " . mysql_error() . "\n\n");
}
mysql_query("UNLOCK TABLES");
print "База данных оптимизированна и разблокированна.<br>\n";
?>

Добавлено: 26 Мая 2018 21:42:00 Добавил: Андрей Ковальчук

Наконец-то заработал поиск

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

MATCH() … AGAINST() первое, что приходит в голову, когда говорят о поиске с релевантностью, забудьте об этом бреде!!! Во-первых, это накладывает очень два ограничений: первое и главное — надо делать полнотекстовые индексы, без них никак, второе — индексы можно сделать только на таблицах MyISAM. Во вторых это просто глючно, потому что при поиске небольших фраз по большой базе их релевантность близка к абсолютному нулю, или же если слово присутствует в более 50% строк, то оно не учитывается.

SELECT 
MATCH('Content') AGAINST ('keyword1 keyword2') as Relevance 
FROM table 
WHERE MATCH ('Content') AGAINST('+keyword1 +keyword2' IN BOOLEAN MODE) 
HAVING Relevance > 0.2 
ORDER BY Relevance DESC

Это так сказать классический способ поиска, взятый с сайта самого мускула, но ИМХО это большая ошибка, тут первый MATCH выдает релевантность, а второй который IN BOOLEAN MODE используется для поиска обоих слов сразу в одной строке, если есть только одно то он выдаст 0. Если слов больше двух то это начинает давать сильную погрешность, выдавая только те ряды, где есть все слова фразы. Более того, куча MATCH сильно тормозит запрос.

Поэтому лучше ввести весовые коэффициенты в запрос. Коэффициенты вы раздаете сами, поэтому их общее число всегда может быть в пределах 100 и к нему можно смело добавлять знак %. Итак у нас есть три колонки title, description и text. Если в каждом из трех полей есть искомое словосочетание то релевантность 100%, при этом каждое поле имеет свой вес: заголовок — самый большой(40%), потом описание(20%) а потом уже текст(10%). Я считаю, что полное словосочетание это 70% релевантности и если есть все слова из него, то это еще 30%. немного запутал, сейчас объясню. Есть словосочетание «мама мыла раму», если оно дословно есть во всех полях то это 70%, а если в каждом из них есть «раму мыла мама», то это только 30%. В результате мы получаем либо 100% (70% все вместе и 30% каждое по отдельности) совпадение со строкой либо только 30% (каждое по отдельности) совпадение, ну это лично мое мнение.

Итак алгоритм будет выгладить следующим образом:
$text = "мама мыла раму";
$query = "SELECT *, 
IF (title like '%".$text."%', 40, 0) + IF (title LIKE '%".str_replace(" ", "%', 5.71, 0) + IF (title LIKE '%", $text)."%', 5.71, 0) + 
IF (description like '%".$text."%', 20, 0) + IF (description LIKE '%".str_replace(" ", "%', 2.86, 0) + IF (description LIKE '%", $text)."%', 2.86, 0) +
IF (text like '%".$text."%', 10, 0) + IF (text LIKE '%".str_replace(" ", "%', 1.43, 0) + IF (text LIKE '%", $text)."%', 1.43, 0) AS rel
FROM pages 
WHERE 
(title LIKE '%".str_replace(" ", "%' OR title LIKE '%", $text)."%') OR
(description LIKE '%".str_replace(" ", "%' OR description LIKE '%", $text)."%') OR
(text LIKE '%".str_replace(" ", "%' OR text LIKE '%", $text)."%') 
ORDER BY rel DESC";

Если подставить значения будет выглядеть менее запутано но все равно непонятно
SELECT *, 
IF (title LIKE '%мама мыла раму%', 40, 0) + IF (title LIKE '%мама%', 5.71, 0) + IF (title LIKE '%мыла%', 5.71, 0) + IF (title LIKE '%раму%', 5.71, 0)  + 
IF (description LIKE '%мама мыла раму%', 20, 0) + IF (description LIKE '%мама%', 2.86, 0) + IF (description LIKE '%мыла%', 2.86, 0) + IF (description LIKE '%раму%', 2.86, 0)  + 
IF (text LIKE '%мама мыла раму%', 10, 0) + IF (text LIKE '%мама%', 1.43, 0) + IF (text LIKE '%мыла%', 1.43, 0) + IF (text LIKE '%раму%', 1.43, 0) AS rel
FROM pages 
WHERE 
(title LIKE '%мама%' OR title LIKE '%мыла%' OR title LIKE '%раму%') OR
(description LIKE '%мама%' OR description LIKE '%мыла%' OR description LIKE '%раму%') OR
(text LIKE '%мама%' OR text LIKE '%мыла%' OR text LIKE '%раму%')
ORDER BY rel DESC

Вот такой немаленький запросик получился))) Теперь попробуем разобрать что значат все эти непонятные коэффициенты 5.71, 2.86 и 1.43. Для этого я нарисую маленькую табличку.
\	Все	мама	мыла	раму
title	40	5.71	5.71	5.71
description	20	2.86	2.86	2.86
text	10	1.43	1.43	1.43

Сумма первого столбика получается как раз 70%, сумма всех остальных ячеек 30% в результате искомые 100% релевантности. Сумма каждого следующего столбика 10%. А сумма каждой ячейки высчитывается по формуле (вес поля) * (общее число процентов 30) / (общее число коэффициентов 7) / (количество слов), если подставить поле title то выйдет 4*30/7/3 = 5.71. В общем, я думаю, поняли, кто не понял, возьмите калькулятор!

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

Кстати в моем поиске еще наложена логика отключения полей, если отключено одно поле то коэффициенты становятся 3 и 4 , а если отключено два поля то оставшееся имеет коэффициент 7. И еще не забудьте удалить пробелы в начале и конце строки, а также двойные пробелы в середине. Все!

Добавлено: 25 Мая 2018 10:00:51 Добавил: Андрей Ковальчук

Категориальное упорядочение результатов запроса

Практические вопросы, которые возникают при работе с базами данных, часто требуют решений нестандартного характера, заставляя отвлечься на миг от запальцеванного монитора и, взяв карандаш, поимпровизировать на бумаге, концентрируясь в первую очередь на алгоритме решения... и только после -- на реализации. Имея (я надеюсь неплохую) привычку браузить форумы, посвященные СУБД, наталкиваюсь на разнообразные вопросы посвященные не только проблемам установки компонент, синтаксиса SQL, доступа к БД... но и задачи алгоритмического характера, представляющие практический интерес. Одному из таких вопросов и посвящена настоящяя статья.

Итак, формулировка, в которой я встречал эту задачу в последний раз перед написанием статьи была приблизительно таковой (оригинал, говоря откровенно, был весьма расплывчатым, поэтому попробую в некоей мере перефразировать, опуская излишние детали): имеется таблица Images (ImageID INT, CategoryID INT,

 FLOAT), содержащая номера рисунков (ImageID), каждому рисунку сопоставляется некоторая категория (CategoryID), к которой он принадлежит, и релевантность ([Relevance]). Дело в том, что в задачу разработчика входила реализация некоего "поискового движка", который по определенным критериям возвращал бы соответствующее множество рисунков. Вобщим, как было ясно из поста, движок он построил согласно всем требованиям заказчика... кроме одного. Результат запроса, возвращаемого движком, нужно было упорядочить таким образом, чтобы получились "кортежи" (просьба не путать с понятием "кортеж" в теории реляционных баз данных) следующего вида. В первый кортеж должны входить наиболее релевантные рисунки по одному из каждой категории (упорядоченные по CategoryID), во второй -- следующие ниже по релевантности (то-есть, не более релевантные, чем первые, но наиболее релевантные из оставшихся) также по одному из каждой категории и т. д. Иными словами, результат запроса к движку должен быть отчет, состоящий из колонок, в каждой из которых находятся рисунки, принадлежащие фиксированной категории, и упорядоченные внутри колонки согласно релеванитности.

Хочу отметить, что в оригинале релевантность не содержалась в таблице, а определенным образом вычислялась на основе значений некоторых величин, извлекаемых из других таблиц. Но это не ограничивает ни в коей мере общности рассуждений... мы просто скрываем лишние детали. Кроме того, категорий рисунков имеется около дюжины (иными словами, небольшое число, но не фиксированное), в свою очередь разработчик приводил примерное число рисунков -- около 200 000. Рисунки приблизительно равномерно распределены по категориям, но "приблизительно" значит, что последние кортежи будут неполными, то-есть в общем случае в них будут присутствовать рисунки не из всех категорий. Ну и, естественно, читатель уже подметил, что для формулировки задачи абсолютно неважно, какие объекты мы рассматриваем (будь-то рисунки, как в нашем случае, или имена подружек... упорядоченные по релевантности). Главное -- это наличие двух признаков: групирующего (CategoryID) и упорядывающего ([Relevance]).

Ну что ж. Переходим к решению. Так как разаработчик использовал MS SQL Server 2000, то и мы будем использовать этот же SQL-сервер. Именно нам и понадобится Transact-SQL. Но сначала -- алгоритм. Итак, создаем временную таблицу, которая кроме столбцов таблицы Images будет содержать еще два: Ind и Ind2 целого типа. В эту таблицу (назовем ее @Tab) поместим все записи из Images, упорядоченные по CategoryID (по возростанию) в качестве первичного признака и по [Relevance] (по убыванию) в качестве вторичного, проставив им соответсвующие значения поля Ind от 1 до числа, равного количеству записей в Images. А теперь -- самый важный шаг. Точнее, последовательность шагов. Подсчитываем, сколько рисунков (записей) во всех категориях, кроме последней (согласно упорядочению по возрастанию). Пускай это число @x. Тогда в каждой записи последней категории полю Ind2 присваиваем значение Ind - @x. На следующем шаге подсчитываем, сколько записей во всех категориях, кроме двух последних и помещаем значение в @x. В каждой записи в предпоследней категории полю Ind2 присваиваем значение Ind - @x и т. д., пока не заполним значения Ind2 во всех записях. Таким образом мы добиваемся того, что Ind2 в рамках каждой категории будет изменяться от еденицы до числа, равного количеству рисунков в категории (естественно, в рамках категории упорядочение, задаваемое Ind2, будет совпадать с упорядочением по релевантности). И, наконец, упорядочиваем теперь @Tab по Ind2 (по возрастанию) в качестве первичного признака и по релевантности (по убыванию) в качестве вторичного. Следующий код иллюстрирует сказанное.
[SQL]DECLARE @Tab TABLE (ImageID INT, CategoryID INT, [Relevance] FLOAT, Ind INT IDENTITY(1, 1), Ind2 INT)
INSERT INTO @Tab (ImageID, CategoryID, [Relevance])
SELECT * FROM Images
ORDER BY CategoryID ASC, [Relevance] DESC
DECLARE @Cat TABLE (CategoryID INT)
INSERT INTO @Cat
SELECT DISTINCT CategoryID FROM Images
DECLARE @Curr_Cat_Quant INT 
SELECT @Curr_Cat_Quant = COUNT(*) FROM @Cat
DECLARE @Var1 INT 
DECLARE @Var2 INT 
WHILE @Curr_Cat_Quant > 0
BEGIN 
        SELECT @Var1 = MAX(CategoryID) FROM @Cat
        SELECT @Var2 = COUNT(*) FROM @Tab A INNER JOIN @Cat B ON A.CategoryID = B.CategoryID
        WHERE A.CategoryID != @Var1
        UPDATE @Tab 
        SET Ind2 = Ind - @Var2 
        WHERE CategoryID = @Var1
        DELETE FROM @Cat
        WHERE CategoryID = @Var1
        SELECT @Curr_Cat_Quant = COUNT(*) FROM @Cat
END

У читателя уже возник, наверное, вопрос: почему именно так, почему не создать для запроса из @Tab курсор и не пробежаться по записям, присваивая Ind2 сразу необходимое значение... и вобще, зачем нужны два поля Ind и Ind2. Но тут как раз все дело в производительности. Такой код ощутимо тормозил бы работу системы, так как рисунков в каждой группе у нас много, а FETCH -- "дорогостоящая" операция. Вместо этого мы предлагаем SQL-серверу самому заполнить Ind (посредством автоинкремента) и потом за несколько шагов (так как категорий у нас немного) обработать Ind2 с помощью более быстрой конструкции INSERT... SELECT.

Остается произвести заключительный запрос, который и дает нам необходимый результат:
SELECT * FROM @Tab
ORDER BY Ind2 ASC, [Relevance] DESC

Я буду весьма признателен за комментарии по поводу моей статьи.

Добавлено: 25 Мая 2018 07:07:40 Добавил: Андрей Ковальчук

Работаем с SQLite

Создание базы данных и таблицы SQLite
Рассмотрим на примере создание базы данных и таблицы SQLite в PHP 5:

<?php
      //  Создание базы/таблицы данных
     error_reporting(0); // Отключение вывода стандартных сообщений об ошибках
     try {
          if (!file_exists("sample.db")) {
              // Базы нет, создаем ее
              $db = sqlite_open("sample.db");
              if (!$db) throw new Exception("невозможно создать базу!");
              $sql = "CREATE TABLE test
                    (
                            id INTEGER PRIMARY KEY, -- автоинкремент 
                            textfield TEXT, -- текстовое поле неограниченной длины
                            intfield INTEGER(2) -- числовое поле
                    )";
               sqlite_query($db, $sql);
               if (sqlite_last_error($db)) throw new Exception("невозможно создать таблицу в базе!");
               echo "База и таблица созданы";
               sqlite_close($db);
           } 
      } catch (Exception $exception) { 
          echo "Произошла ошибка в строке", $exception->getLine()-1, ": ", 
                   $exception->getMessage();                       
 } 
?>

Поскольку удобнее всего для обработки ошибок применять в PHP 5 новую конструкцию try/catch/throw, которая будет также выводить информацию о возникшей ошибке, то в самом начале PHP сценария будет уместно функцией error_reporting() отключить вывод стандартных сообщений об ошибках.

Далее идет блок try, в котором проверяется наличие файла базы данных. Если такового не существует – переходим к его созданию функцией sqlite_open(), которая не только открывает файлы баз данных, но и создает его в случае отсутствия, чем мы и воспользуемся в нашем сценарии. В случай невозможности создания файла через throw new Exception() генерируется исключение и управление передается в блок catch для вывода сообщения об ошибке и завершения сценария. Если же создание файла базы произошло успешно – формируется SQL-запрос на создание тестовой таблицы.

Каким же должен быть SQL-запрос для создания таблицы, учитывая бестиповый характер таблиц SQLite? Все очень и очень просто - при описании полей таблицы принимаются в расчет только имена полей. Указание типов полей и их максимальной длины является необязательным и SQLite’ом не учитывается! Исключение составляет только первичный ключ (PRIMARY KEY), объявленный как INTEGER – такое поле автоматически становится автоинкрементным.

Таким образом, аналогичную таблицу можно было бы создать и таким SQL-оператором:
CREATE TABLE test(id INTEGER PRIMARY KEY, textfield, intfield)

Выполнение запроса к базе производится функцией sqlite_query(), а проверка успешности выполнения запрос функцией sqlite_last_error().

Необязательно закрытие соединения с базой SQLite выполняется функцией sqlte_close().

В случае генерации любого исключения в блоке catch методом getLine() объекта $exception осуществляется вывод номера строки, где ошибка произошла, и методом getMessage() информационного сообщения об ошибке.

Сохранение данных в таблицу SQLite
Добавим в нашу тестовую таблицу SQLite некоторые значения.
<?php
       // Ввод данных в базу/таблицу
      error_reporting(0); // Отключение вывода стандартных сообщений об ошибках
      try {
           if (file_exists("sample.db")) {
               // Открытие файла базы
               $db = sqlite_open("sample.db");
               if (!$db) throw new Exception("невозможно открыть базу!");
               $sql = "INSERT INTO test(textfield, intfield) VALUES(''PHP version'', 5)";
               sqlite_query($db, $sql);
               if (sqlite_last_error($db)) throw new Exception("невозможно добавить данные в таблицу!");
               echo "Данные в таблицу добавлены успешно";
               sqlite_close($db);
           } 
      } catch (Exception $exception) { 
          echo "Произошла ошибка в строке ", $exception->getLine()-1, ": ", 
          $exception->getMessage();                       
      } 
?>

По сравнению с предыдущим сценарием создания таблицы с точки зрения PHP в представленном коде нет ничего нового. Тем не менее, стоит обратить внимание на SQL-оператор INSERT добавления данных в таблицу. В его записи нет неожиданностей. Однако, учитывая бестиповый характер таблиц SQLite возможен и следующий вариант, который не будет являться ошибочным:
INSERT INTO test(textfield, intfield) VALUES(5,''PHP version'')

В этом примере числовое значение записывается в текстовое поле, а символьное значение в целочисленное! Поскольку при создании таблицы типы полей, указанные в операторе CREATE TABLE игнорируются, SQLite позволяет вставлять данные любого типа в любые поля! Единственно, когда типы в SQLite имеют значение – это процесс сортировки или выборки данных по условиям, при этом приведение типов SQLite выполняет автоматически.

Вывод данных из таблицы SQLite
Теперь посмотрим, каким образом PHP 5 может произвести вывод данных из созданной нами таблицы.
<?php
       // Вывод данных из базы/таблицы
       error_reporting(0); // Отключение вывода стандартных сообщений об ошибках
       try {
            if (file_exists("sample.db")) {
                // Открытие файла базы
                $db = sqlite_open("sample.db");
                if (!$db) throw new Exception("невозможно открыть базу!");
                $sql = "SELECT * FROM test";
                $result = sqlite_query($db, $sql);    
                if (sqlite_last_error($db)) throw new Exception("невозможно выбрать данные из таблицы!");
                while ($row = sqlite_fetch_object($result)) {
                     echo "Поле 1: " . $row->textfield . "\n";
                     echo "Поле 2: " . $row->intfield  . "\n";
                }
                sqlite_close($db);
           } 
      } catch (Exception $exception) { 
           echo "Произошла ошибка в строке ", $exception->getLine()-1, ": ", 
                   $exception->getMessage();                       
     } 
?>

Выполняя запрос SELECT необходимо результат сохранять в переменную, которая будет иметь ресурсный тип ($result). Вывод данных из такой переменной возможен множеством способов, однако в любом случае потребуется перебор всех записей, как это делалось и при работе с MySQL.

В данном примере используется функция sqlite_fetch_object(), возвращающая данные одной записи в виде объекта. Получить же значение одного конкретного поля из такого объекта можно, обратившись к его свойству, имя которого совпадает с именем поля.

Как это принято в PHP, функция sqlite_fetch_object() возвратит FALSE, в случае прохода по всем записям вернувшихся значений, вот почему ее использование удачно сочетается с оператором цикла while.

Добавлено: 24 Мая 2018 19:54:47 Добавил: Андрей Ковальчук

Рейтинг как на YouTube

Я получил email вопрос от моих читателей, они спросили меня, как осуществить рейтинг как на YouTube с jQuery. Это приятный бар с результатами, так что я разработан простой сценарий с помощью PHP, jQuery и Ajax. Уверен, что вам понравится.
Рейтинг как на YouTube

Создаем базу данных
Выполняем sql запрос через phpmyadmin

CREATE TABLE messages(
id INT PRIMARY KEY AUTO_INCREMENT,
message TEXT,
up INT,
down INT);


index.php
<script type="text/javascript" src="http://ajax.googleapis.com/
ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$(".like").click(function()
{
var id=$(this).attr("id");
var name=$(this).attr("name");
var dataString = 'id='+ id + '&name='+ name;
$("#votebox").slideDown("slow");

$("#flash").fadeIn("slow");

$.ajax
({
type: "POST",
url: "rating.php",
data: dataString,
cache: false,
success: function(html)
{
$("#flash").fadeOut("slow");
$("#content").html(html);
} 
});
});

// Close button action
$(".close").click(function()
{
$("#votebox").slideUp("slow");
});

});
</script>
//HTML Code
<a href="#" class="like" id="1" name="up">Like</a>
-- 
<a href="#" class="like" id="1" name="down">Dislike</a>

<div id="votebox">
<span id='close'><a href="#" class="close">X</a></span>
<div id="flash">Loading........</div>
<div id="content">
</div>
</div>


rating.php
<?php
include("db.php"); // подключаем настройки к БД
if($_POST['id'])
{
$id=mysql_escape_String($_POST['id']);
$name=mysql_escape_String($_POST['name']);
// Vote update  
mysql_query("update messages set $name=$name+1 where id='$id'");
// Getting latest vote results
$result=mysql_query("select up,down from messages where id='$id'");
$row=mysql_fetch_array($result);
$up_value=$row['up'];
$down_value=$row['down'];
$total=$up_value+$down_value; // Total votes 
$up_per=($up_value*100)/$total; // Up percentage 
$down_per=($down_value*100)/$total; // Down percentage
//HTML output
echo '<b>Ratings for this blog</b> ( '.$total.' total)
Like :'.$up_value.'
<div id="greebar" style="width:'.$up_per.'%"></div>
Dislike:'.$down_value.'
<div id="redbar" style="width:'.$down_per.'%"></div>';
}
?>

Украшаем css style
#votebox
{
border:solid 1px #dedede; padding:3px;
display:none;
padding:15px;
width:700px;
-moz-border-radius: 6px;
-webkit-border-radius: 6px;
}
#greebar
{
float:left;
background-color:#aada37;
border:solid 1px #698a14;
width:0px;
height:12px;
}
#redbar
{
float:left;
background-color:#cf362f;
border:solid 1px #881811;
width:0px;
height:12px;
}
#close
{
float:right; font-weight:bold; 
padding:3px 5px 3px 5px; 
border:solid 1px #333;
-moz-border-radius: 6px;
-webkit-border-radius: 6px;
}

Добавлено: 20 Мая 2018 18:10:30 Добавил: Андрей Ковальчук

Таблицы ключ значение и как их использовать в PHP/MYSQL

Эта статья для продвинутых программистов, но и новички смогут всё понять, если будут внимательны. Применение принципа ключ значение в таблицах бывает полезно тогда, когда нам в одной таблице необходимо хранить информацию о другой таблице. Предположим, что у нас есть таблица пользователей. И всё было бы хорошо, но однажды босс захотел хранить ещё несколько телефонов, пол, дату рождения…

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

Тут-то нам и пригодятся таблицы ключ значение. Создадим таблицу для пользователей:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `pass` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM ;

В ней будут хранится самые примитивные данные. Ну а теперь создадим дополнительную таблицу, в которой будем хранить всё остальное.
CREATE TABLE IF NOT EXISTS`user_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

На самом деле поля key и value будут хранить информацию о пользователе. Поскольку в такой таблице информация будет расти вертикально, то мы можем в любой момент добавить новые атрибуты пользователя.

Довольно лирики. Теперь для того, чтобы вставлять данные в эту таблицу, нам необходимо использовать запросы типа:
UPDATE user_data SET value = 'What?' WHERE key = 'date_of_birth'

Таким образом у нас есть ключ, по которому мы будем искать необходимое нам значение. Однако в этом способе есть и свои минусы. Допустим нам надо найти всех пользователей из Чикаго. Имея одну таблицу, мы бы это сделали так:
SELECT username FROM users WHERE city = 'Chicago'

Всё просто. С таблицами ключ значение будет посложнее:
SELECT username FROM users a
INNER JOIN user_data b
    ON a.id = b.user_id
WHERE b.key = 'city'
AND b.value = 'Chicago'

Довольно-таки громоздко для такой простой задачи. Но это ещё не самое страшное. Проблемы начинаются там, где нам надо произвести поиск по нескольким критериям. В случае с одной таблицей эта задача решается так:
SELECT username FROM users WHERE city = 'Chicago' AND state = 'Illinois'

Если вы используете таблицы ключ значение, то можете попасться в капкан следующего выражения:
SELECT username FROM users a
    INNER JOIN user_data b
    ON a.id = b.user_id
WHERE
    (b.key = 'city' AND b.value = 'Chicago')
AND
    (b.key = 'state' AND b.value = 'Illinois')

Но это выражение всегда вернут 0. Это происходит потому, что в базе никогда не будет одной и той же строки, которая содержит 2 значения одновременно.

Но решение есть – похожее на монстра:
SELECT username FROM users a
INNER JOIN user_data b
ON a.id = b.user_id
    INNER JOIN user_data c
    ON b.user_id = c.user_id
WHERE
    (b.key = 'city' AND b.value = 'Chicago')
AND
    (c.key = 'state' AND c.value = 'Illinois')

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

Добавлено: 16 Мая 2018 08:48:50 Добавил: Андрей Ковальчук

Функции даты

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

ADDDATE()
CURDATE()

SELECT ADDDATE('2010-05-01', 7) as nextweek, CURDATE() as today;
SELECT ADDDATE(CURDATE(), 7) as nextweek;
 
-- OUTPUT: 2010-05-08, 2010-05-01
-- OUTPUT: 2010-05-08

В примере функции используются вместе. Второй пример отображает рекомендуемый метод использования.

Добавлено: 10 Мая 2018 21:14:22 Добавил: Андрей Ковальчук

Функции криптографии

Для многих станет открытием, что MySQL поддерживает базовые криптографические функции для алгоритмов AES и DES. Такие функции обеспечивают высокий уровень безопасности, особенно алгоритм AES, и их рекомендуется использовать для улучшения устойчивости приложений ко взлому.

AES_ENCRYPT(string, key) и AES_DECRYPT(string, key);
DES_ENCRYPT(string, key) и DES_DECRYPT(string, key);
ENCODE(string, key) и DECODE(string, key);

SELECT AES_DECRYPT(
                   AES_ENCRYPT('текст для шифрования', 'ключ'),
                  'ключ') as encryption;
-- OUTPUT: текст для шифрования

Описание: Запрос выводит ту же самую строку, которую мы отправляли на шифрование, потому что как только произошло шифрование строки, тут же мы производим дешифрование в оригинальный текст.

AES_ENCRYPT() и AES_DECRYPT() представляют собой наиболее устойчивый криптографический алгоритм, который доступен в MySQL.

Добавлено: 10 Мая 2018 21:13:40 Добавил: Андрей Ковальчук

Хэш функции

MySQL поддерживает следующие хэш функции:

MD5(string);
SHA(string);

SELECT MD5('area72') as md5,
       SHA('area72') as sha
 
-- OUTPUT: MD5 e82b6069c7e968485f0de6d756d622dd
-- OUTPUT: SHA 4feb5546a60f18df755395d1ff39e29a2cab7234

Добавлено: 10 Мая 2018 21:12:17 Добавил: Андрей Ковальчук

Определяем, насколько велика таблица

SELECT
  table_schema AS 'Db Name',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ;

Добавлено: 10 Мая 2018 18:33:03 Добавил: Андрей Ковальчук