Работа с MySQL. Создание Web-интерфейса

Мой компьютер Weekly, № 48 (167), 03.12.2001.

http://www.mycomp.com.ua
http://www.mycomp.com.ua/article.php?id=1223

Одежда для скрипта

Весь смысл нашей разработки теряется, если мы ее не облачим в web-оболочку, или иначе говоря, не создадим для нее web-интерфейс. В первую очередь нас интересует список категорий. В предыдущей статье «Работа с MySql. Введение» был рассмотрен пример с SQL-запросом, который выводит список категорий на консоль. Теперь нам надо вывести это все в HTML-страницу.

Значит, установим цель: вывести в две колонки список категорий, слегка облагородив его. Поступим так: изготовим любую HTML-страницу с необходимым нам дизайном и в то место, где нам нужен список категорий, вставим комментарий:

<!-- CATEGORIES_HERE -->

Это для того, чтобы не перегружать текст программы избыточным кодом оформления HTML страницы. Итак, приступим:

• вывести заголовок типа данных;

• выполнить SQL запрос;

• разобрать результат;

• вставить результат в шаблон HTML страницы;

Использование шаблонов.

Как вставлять результат в шаблон? Очень просто:

• открыть файл шаблона;

• перебирать строки файла по порядку и выводить их в print;

• встретив строку <!-- CATEGORIES_HERE -->, начинать выводить результат;

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

• попросту пишем код;

Код

Итак, план действий составлен, можно кодировать.

<span class="rem">#/usr/bin/perl # Используем библиотеку DBI</span><span class="rem"> </span> use DBI; <span class="rem"># Выведем заголовок типа данных</span> print "Content-type:text/html\n\n"; # коннектимся к серверу MySql my $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных;host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; # готовим запрос my $result = $dbh->prepare("SELECT * FROM category"); # и выполняем его $result->execute(); # начинаем вывод в шаблон вместо комментария. open FILE, "cat_template.html" || die ("Не могу открыть файл cat_template.html!\n"); @arr = <FILE>; close FILE; # ищем строку комментария foreach $line(@arr) { if ($line =~ "<!-- CATEGORIES_HERE") { # если нашли, print "<tr>\n"; # начинаем вывод таблицы $bg = 1; #<span class="rem"> признак начатой строки таблицы</span> $col = 1; # номер текущей колонки print "<table width = \"80%\">\n"; # разбор резульата SQL запроса while (my $ln = $result->fetchrow_hashref()) { if ($bg == 1) { # начата строка таблицы if ($col == 1) { #<span class="rem"> если первая колонка</span> print "<td>",$ln->{'name'},"</td>"; $col++; } else { #<span class="rem"> $col == 2 т. е. 2-я колонка</span> print "<td>",$ln->{'name'},"</td></tr>\n"; $col = 1; $bg = 0; } } else { if ($col == 1) { # начинаем строку таблицы print "<tr><td>",$ln->{'name'},"</td>"; $col = 2; $bg = 1; } else { # $col == 2 print "<td>",$ln->{'name'},"</td></tr>\n"; $col = 1; $bg = 0; } } } if ($bg == 1) { #<span class="rem"> если строка начата и больше нет данных в результате</span> print "<td> </td></tr>\n"; #<span class="rem"> надо корректно закрыть начатую строку таблицы</span> } print "</table>\n"; # и закрыть таблицу } else { print $line; <span class="rem"># метка для вывода не найдена</span> } } # метод finish означает, что ссылка на текущий SQL-запрос использоваться больше не будет, и его можно завершить. По идее, его нужно вызывать перед выполнением нового запроса или перед разрывом соединения с базой данных. Вообще-то, когда все данные выбраны из результата, этот метод вызывается автоматически, но возникают ситуации, когда его приходится вызывать принудительно. На моей машине именно так и было. У вас эта ситуация может и не возникнуть. $result->finish(); # Разрыв соединения с сервером. $dbh->disconnect();

В результате вы должны получить таблицу (рамки включены умышленно).

Вставка ссылок

Все хорошо, только толку от нашей таблицы нет никакого. Нужно, чтобы, щелкнув по названию, открывалась страница со ссылками этой категории. Для этого нужно немного дописать код нашей программы. Введем переменную $scr_name, содержащую имя файла со скриптом, который будет выводить страницу со ссылками. После строки use DBI; вставим строку

$scr_name="getlinks.pl"

Далее, внутри цикла while заменим все параметры функции print .... $ln->{'name'} на

<a href=\"$scr_name?page=$ln->{'id'}\">$ln->{'name'}</a>

Должно получиться что-то типа:

print "<td><a href=\"$scr_name?page=$ln->{'id'}\">$ln-{'name'}</a></td></tr>\n";

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

Заполнение таблицы links

Продолжим создание каталогизатора ссылок.

Мы создали две таблицы в базе данных на сервере MySql:

links category

Кроме того, мы внесли в таблицу category данные, импортировав их из текстового файла. Во второй статье мы «одели» результат запроса из списка категорий в web-интерфейс. Теперь нам нужно заполнить таблицу links, написать скрипт getlinks.pl для вывода списка ссылок с соответствующими категориями. Поступим с заполнением таблицы links так же, как и с таблицей categories: импортируем ссылки из текстового файла. Если вами движет академический интерес, то возьмите нижеприведенные данные и сформируйте из них текстовый файл:

"ProgramminG-программирование для всех!";"http://www.programming.dax.ru";1 "Corwell Design Studio";"http://www.cds.ru";1 "МТУ-Информ";"http://www.mtu.ru";1 "Студия Артемия Лебедева";"http://www.design.ru";1 "Каталог детских ресурсов";"http://www.kinder.ru/";1 "Быки и коровы";"http://bk.ru";2 "Мерен";"http://meren.ru/";2 "Все девочки";"http://www.allgirls.ru/";2 "Развлекательный сайт пива Доктор Дизель";"http://www.doctordizel.ru";2 "Анекдотов.net — максимум развлечений";"http://anekdotov.net";2 "Знакомства и Развлечения";"http://www.atis.kz/";2 "Чертовы Кулички";"http://www.kulichki.com/";2 "Драконье логово";"http://balrog.virtualave.net/";2 "Инфосити";"http://www.infocity.kiev.ua/";3 "Открытые системы";"http://www.opennet.ru/";3 "Масса полезной документации";"http://citforum.ru/";3 "Download.ru";http://www.download.ru/;4 "СОФТЛИСТ.РУ";http://www.softlist.ru/;4 "Темы.РУ";http://www.themes.ru/;4 "Altavista.com";"http://www.altavista.com/";5 "Яндекс.ру";"http://www.yandex.ru/";5 "Мета-ukraine";"http://meta-ukraine.com/";5 "Gala chat";"http://www.galachat.com";6 "Свежак для открытых систем";"http://freshmeat.net";7 "Sourceforge";"http://sourceforge.net";7 "Linuxguru";"http://www.linuxguru.com";7 "Для Web-разработчика";"http://www.stars.com";7 "PHP.net";"http://www.php.net";8 "Google groups";"http://groups.google.com";9 "Lenta.ru";"http://lenta.ru";10 "Корреспондент.нет";"http://www.korrespondent.net";10

Если вы хотите создать свою базу, то придется поработать, собирая и систематизируя свои ссылки, или повыдирать их из каталогизаторов в Интернете.

Формат файла прост: первое поле — наименование ссылки, заключенное в кавычки, второе поле — сама ссылка (URL), заключенная в кавычки, третье поле — номер категории, в которой эта ссылка будет хранится. Номер категории, понятно, должен совпадать с существующей категорией в таблице category. Поля разделены символом «;».

Сохраним файл, скажем в c:\MyDocs\links.txt. Теперь нужно эти данные поместить в таблицу links. Мы такую операцию уже делали с категориями, теперь с помощью запроса

LOAD DATA LOCAL INFILE '/MyDocs/links.txt' REPLACE INTO TABLE links FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r'

заполним таблицу ссылок из текстового файла. Обратите внимание, что перед кавычками стоит знак ‘\’. Perl выдаст сообщение об ошибке, если не использовать обратную косую черту перед кавычками, ибо все спецсимволы вводятся в переменные таким образом — сначала обратная косая черта, затем собственно символ.

Запрос можно выполнить как из скрипта, так и из программы администрирования SQL сервера.

Запрос для выбора ссылок

Теперь можно писать скрипт, который будет выводить список ссылок из выбранной категории. В предыдущей статье мы написали скрипт, в котором выводятся категории и ссылки:

$scr_name="getlinks.pl" .... .... print "<td>","<a href=\"$scr_name?page=$ln->{'id'}\">$ln-{'name'}</a>","</td></tr>\n";

Значит, в качестве параметра мы передаем скрипту номер (id) категории под именем page. Чтобы вывести ссылки только из данной категории, нужно выполнить SQL-запрос

SELECT * FROM links WHERE category=id;

Скрипт для вывода ссылок

Скрипт будет выполнять следующие действия:

• разбор параметров, переданных в скрипт;

• выполнение SQL-запроса;

• вывод результата в указанное место в шаблоне.

Подготовим шаблон в виде HTML-документа с необходимым нам оформлением, и в том месте, где должны быть ссылки, вставим комментарий:

<!-- LINKS_HERE -->

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

#!/usr/bin/perl use DBI; print "Content-type:text/html\n\n"; $temp=$ENV{'QUERY_STRING'}; # Разбор строки параметров if ($temp ne '') { @pairs=split(/&/,$temp); foreach $item(@pairs) { # Режем на название ключа и значение. ($key,$content)=split (/=/,$item,2); <span class="rem"></span> <span class="rem"> # Названия параметров и значения помещаем в хэш</span> $data{$key}=$content; } } open FILE,"$template"; @arr = <FILE>; close FILE; foreach $line(@arr) { # проверка шаблона на предмет начала вывода результата запроса if ($line =~"<!-- LINKS_HERE") { #проверяем, есть ли переменная с именем page if ($data{'page'} ne undef) { <span class="rem"># </span><span class="rem"> параметр 'page' не пустой # создаем запрос </span> $qry = "SELECT * FROM links WHERE category=".$data{'page'}; <span class="rem"> </span> my $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных; host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; # готовим запрос my $result = $dbh->prepare($qry); # и выполняем его $result->execute(); # разбираем результат print "<ul>\n"; while (my $ln = $result->fetchrow_hashref()) { <span class="rem"># вывод ссылки</span> print "<li><a href=\"", $ln->{'url'},"\" target=\"_blank\">",$ln->{'name'},"</a></li>\n"; } print "</ul>\n"; $dbh->disconnect(); <span class="rem"># remember to disconnect!</span> print "<a href=javascript:history.back()>Предыдущая страница</a>\n"; } else { <span class="rem"># отсутствует переменная 'page' или пустая</span> print "<h3>Что-то нехорошо, а что не знаю</h3>\n"; print "<a href=javascript:history.back()> Предыдущая страница</a>\n"; } } else { print $line; <span class="rem"># метка начала вывода не найдена , просто печатаем шаблон</span> } }

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

Усовершенствования

Надеюсь, принцип работы данной программы понятен, и каждый волен ее дорабатывать на свой вкус. Я , например, в рабочей версии каталогизатора (http://www.lubny.net.ua/, она, правда, написана на PHP), добавил в таблицу links еще и описание ресурса и счетчик, куда записывается каждый клик по ссылке. Чтобы это сделать, нужно добавить поле counter типа int в таблицу links для подсчета кликов, а заодно и поле comment типа varchar(255) для хранения краткого описания ссылки.

Для того чтобы считать клики, нужно написать небольшой скрипт-редиректор, строку же вывода ссылки переписать следующим образом:

(было):

print "<li><a href=\"", $ln->{'url'},"\" target=\"_blank\">", $ln->{'name'},"</a></li>\n";

(стало):

print "<li><a href=\"redir.pl?url=",$ln->{'url'},"&id=", $ln->{'id'},"\" target=\"_blank\">", $ln->{'name'},"</a></li>\n";

Скрипт redir.pl должен открывать страницу с переданным ему URL и записывать в таблицу links значение счетчика с увеличением на 1.

Изменение данных

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

UPDATE table_name SET field=value,field1=value1 WHERE condition

Мой вариант скрипта:

#/usr/bin/perl use DBI; print "Content-type:text/html\n\n"; $temp=$ENV{'QUERY_STRING'}; # Разбор строки параметров if ($temp ne '') { @pairs=split(/&/,$temp); foreach $item(@pairs) { ($key,$content)=split (/=/,$item,2);<span class="rem"> </span> $data{$key}=$content; } } <span class="rem"># увеличиваем значение счетчика на 1</span> $qry = "UPDATE links SET count = count+1 WHERE id=".$data{'id'}; $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных; host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; $dbh->do($qry); $dbh->disconnect(); <span class="rem"># строка ниже заставит в окне открыть страницу с нужной нам ссылкой.</span> print "<meta http-equiv=\"refresh\" content=\"0;URL=",$data{'url'},"\">\n"; <span class="rem">#============EOF redir.pl===========</span>

Если будете использовать этот скрипт, то скорее всего, вам захочется выводить список ссылок, отсортированный по количеству посещений. Для этого нужно в скрипте getlinks.pl поменять запрос, добавив в него сортировку по полю count по убыванию:

$qry = "SELECT * FROM links WHERE category=".$data{'page'}<span class="rem"># создаем запрос </span>$qry .= "ORDER BY count DESC"; <span class="rem"># сортировка по полю count по убыванию;</span>

Подсчет содержимого полей

Кроме этого, при открытии страницы категорий можно делать подсчет ссылок в категории, подсчет кликов в категории. Подсчет обычно осуществляют агрегатной функцией count(field_name) в инструкции SELECT:

SELECT count(*) as totlinks FROM links WHERE category=$category_id

В данном случае результату работы функции count(*) присваиваем псевдоним totlinks для удобства дальнейшего использования результата.

Подсчет же количества кликов в категории можно подсчитать, используя агрегатную функцию sum(fieldname):

SELECT sum(count) as clicks FROM links WHERE category=$category_id

Так что, если приложить немного фантазии, можно из своего скрипта написать настоящий каталогизатор .

---

Исходная статья находится по адресу: http://alice.stup.ac.ru/~dvn/uproc/books/mysql/mcw48.htm

Статья из электронного представительства Пензенского Государственного Университета КАФЕДРЫ "ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА".