Робота з MySQL

1. Архітектура Web-баз даних

Основна операція WEB сервера показана на рисунку 10.1.

Ця система складається з 2 об’єктів: WEB браузера та WEB сервер. Між ними має існувати канал зв’язку. WEB браузер посилає запит на сервер, сервер відсилає відповідь [1].

Рис.1.Схема роботи сервера

Архітектура сайту, який включає базу даних складніше. Розглянемо один з можливих варіантів реалізації. Трансакція складається з наступних етапів

1. WEB браузер відправляє HTTP запит, що визначає WEB сторінку, використовуючи HTML форму.

2. WEB сервер приймає файл та передає його механізму PHP на обробку.

3. Механізм PHP починає синтаксичний аналіз сценарію. В сценарії присутні команди підключення до бази даних і виконання запиту до неї. PHP відкриває з єднання з сервером MySQL та відправляє необхідний запит.

4. Сервер приймає запит в базу даних, опрацьовує його, а потім відправляє результати в PHP.

5. Механізм PHP завершує виконання сценарію, формуючи результати у вигляді HTML, після чого відправляє результати в HTML форматі WEB серверу.

6. WEB сервер пересилає HTML в браузер. За допомогою якого користувач переглядає результати.

Використання мови PHP та MySQL дозволяє робити сайти динамічними, які містять інформацію в реальному часі. MySQL СУБД швидка та надійна. Крім MySQL можна використовувати Postgre SQL, Oracle, dbm, Hyperware, Informix, InterBase, SyBase.

2. Мова SQL. Загальні відомості

Запити – це основний інструмент вибірки й обробки даних у СУБД. Для створення і реалізації запитів розроблена спеціальна мова SQL (Structured Query Language – мова структурованих запитів). Перший прототип мови SQL з'явився наприкінці 70-х років і одержав через якийсь час широке поширення. Він став застосовуватися у всіх комерційних СУБД і поступово став стандартом де-факто для мов маніпулювання даними в реляційних БД. Перша версія стандарту називалася SQL-86 і була прийнята ANSI (Американським національним інститутом стандартів) і ISO (Міжнародним інститутом стандартів). Наприкінці 1992 р. був прийнятий новий міжнародний стандарт мови – це SQL-92. Цей стандарт підтримується всіма сучасними СУБД, у тому числі і MS Access.

Останній стандарт з'явився в 1999 р., у якому були введені нові типи даних, і ряд інших нововведень, важливих для розроблювачів нових версій сучасних СУБД. Ми коротко ознайомимося з деякими найбільш важливими елементами мови, що відповідають стандарту SQL-92.

Всі оператори мови можна розділити на такі три категорії:

1. Оператори контролю даних – використовуються для перевірки повноважень користувача при звертанні до БД. Це оператори GRANT і REVOKE.

2. Оператори визначення даних – використовуються для створення об'єктів БД і визначення їхньої структури. До них відносяться оператори CREATE SCHEMA, CREATE TABLE, CREATE VIEW, CREATE DOMAIN.

3. Оператори керування даними – використовуються для пошуку, видалення, зміни і збереження даних. Це оператори SELECT, UPDATE, INSERT, DELETE.

Найважливішим оператором мови SQL є оператор SELECT, призначений для вибірки даних з таблиць БД відповідно до заданого критерію і перетворення отриманих результатів до потрібного виду.

Оператор SELECT. Добір записів з однієї таблиці

Загальний формат оператора SELECT наступний:

SELECT [DISTINCT] {* | <Список полів>}

FROM <Список таблиць >

[WHERE <Умова добору записів >]

[GROUP BY <Список полів для групування>]

[HAVING <Умови добору для груп>]

[ORDER BY <Список полів для сортування>]

Результатом виконання оператора SELECT є набір даних, який складається з записів, що відповідають заданим умовам добору. В операторі обов'язково повинні бути присутніми інструкції SELECT і FROM. Інші інструкції (вказані у квадратних дужках) можуть бути відсутніми.

Інструкція SELECT повідомляє СУБД, що це команда – запит. В інструкції SELECT указується список полів, які будуть включатися в записи, що відбираються. У списку полів повинне бути задане хоча б одне поле. Якщо в список полів потрібно включити всі поля з таблиці (таблиць), то замість перерахування полів можна вказати символ *. Якщо в список полів включаються поля з різних таблиць, то для вказівки належності поля до тієї чи іншої таблиці використовують складене ім'я, що складається з імені таблиці й імені поля, розділених крапкою. Необов'язкова інструкція DISTINCT забороняє включення в результуючий набір даних повторюваних записів. В інструкції FROM перелічуються імена таблиць, з яких відбираються записи. Список повинний містити хоча б одну таблицю.

В інструкції WHERE задається умова (критерій) добору записів, представлена логічним виразом. Логічний вираз складається з операндів, операцій порівняння і логічних операцій. У якості операндів можуть використовуватися імена полів і константи.

У вираженнях умов добору можуть використовуватися такі операції порівняння і логічні оператори і операції:

= , < , > , <> , <= , >= - операції порівняння;

Between – предикат, що перевіряє приналежність значення поля заданому діапазону значень;

In – предикат, що перевіряє приналежність значення поля заданій множині;

Like – предикат, що перевіряє відповідність значення поля заданому шаблону;

And, Or, Not – логічні операції.

Інструкція GROUP BY призначена для вказівки полів, по яких визначаються групи записів. В одну групу включаються записи з однаковими значеннями в полях, перерахованих в інструкції GROUP BY. Для груп записів можна застосовувати групові операції (їх ще називають агрегатними функціями). У мові SQL визначені такі групові операції:

• Max( ) – вибирає максимальне значення поля;

• Min( ) – вибирає мінімальне значення поля;

• Count( ) – визначає число значень у групі;

• Avg( ) – обчислює середнє значення;

• Sum( ) – обчислює суму значень полів у групі.

Інструкція HAVING застосовується разом з інструкцією GROUP BY і використовується для завдання умов добору для згрупованих даних. Правила запису умов добору аналогічні правилам завдання умов в інструкції WHERE.

В інструкції ORDER BY указується список полів, по яких потрібно сортування записів у результуючому наборі даних. За замовчуванням сортування по кожному полю виконується в порядку зростання значень. Якщо необхідно зробити сортування по спаданню, то після імені відповідного поля потрібно записати покажчик DESC.

3. Створення таблиць баз даних

Наступний етап настроювання бази даних - створення таблиць. Це робиться за допомогою SQL-команди CREATE TABLE. Загальна форма оператора CREATE TABLE виглядає у такий спосіб:

CREATE TABLE tablename(columns)

create table books

( customerid int unsigned not null auto_increment primary key,

name char (30) not null,

title char(40) not null,

price char(20) not null

);

Кожна із таблиць створюється окремим оператором CREATE TABLE.

NOT NULL означає, що всі рядки таблиці повинні мати значення в цьому атрибуті. Якщо NOT NULL не зазначене, поле може бути порожнім (NULL).

AUTO_INCREMENT — спеціальна можливість MySQL, яку можна задіяти у числових стовпцях. Якщо при вставці рядків у таблицю залишати таке поле порожнім, MySQL автоматично генерує унікальне значення ідентифікатора. Це значення буде на одиницю більше максимального значення, що вже існує в стовпці. У кожній таблиці може бути не більше одного такого поля. Стовпці з AUTO_INCREMENT повинні бути проіндексованими.

PRIMARY KEY після імені стовпця визначає, що цей стовпець є первинним ключем для таблиці. Дані в цьому стовпці повинні бути унікальними. MySQL автоматично індексує цей стовпець. Помітьте, що раніше, при використанні його с customerid у таблиці customers, без AUTO_INCREMENT не обійшлося. Автоматичний індекс по первинному ключі зберігає індекс, необхідний AUTOJNCREMENT.

Указувати PRIMARY KEY після назви стовпця треба лише тоді, коли ми маємо справу з первинним ключем у вигляді одиночного стовпця.

UNSIGNED після цілочисленого типу означає, що його значення може бути або позитивним, або нульовим.

При створенні будь-якої таблиці необхідно прийняти рішення відносно типів стовпців. У таблиці customers, як позначено в схемі, існує чотири стовпці. Перший, customerid, — це первинний ключ, що визначений безпосередньо. Згідно нашому рішенню, він буде представлятися цілим числом (тип даних int), причому unsigned. Всі інші стовпці будуть містити строкові типи даних. Для них обраний тип char. Він визначає поля фіксованої ширини. Ширина вказується в дужках, тому, наприклад, ім'я складається з 30 символів.

Цей тип даних завжди буде призначати 30 символів для імені, навіть якщо не всі символи будуть використовуватися. Для дотримання необхідного розміру MySQL додасть до даних відповідну кількість пропусків.

Для реальних клієнтів, з реальними іменами й адресами, ширина цих стовпців напевно виявиться недостатньою. Зверніть увагу, що всі стовпці оголошені NOT NULL. Це мінімальна оптимізація, у результаті якої система буде працювати небагато швидше.

4. Робота з базою даних MySQL. Створення бази даних. Бібліотека

Створення бази даних починаємо відкривши сторінку за адресою http://localhost/Tools/phpMyAdmin/.Базу даних називаємо books. Створити. На мові SQL: CREATE DATABASE `books`;

Зробимо однотабличну базу даних. Створимо таблицю, яку назвемо book. Ця таблиця матиме 4 поля.

Конструюємо таблицю: опишемо кожне поле.

CREATE TABLE `books` (`isbn` CHAR(13) NOT NULL ,`author` CHAR(30) NOT NULL, `title` CHAR(60) NOT NULL, `price` FLOAT(4) NOT NULL, PRIMARY KEY (`isbn`));

Заповнюємо таблицю. Після заповнення таблиця має наступний вигляд.

Приклад 1. Створимо сайт Бібліотека.

<html>

<head>

<title>БІБЛІОТЕКА</title>

</head>

<body>

<h1>БІБЛІОТЕКА</h1>

<form action="insert.php" method="post">

<table border=0>

<tr><td>ISBN</td><td><input type=text name=isbn maxlength=13 size=13><br></td></tr>

<tr><td>АВТОР</td><td> <input type=text name=author maxlength=30 size=30><br></td></tr>

<tr><td>НАЗВА</td><td> <input type=text name=title maxlength=60 size=30><br></td></tr>

<tr><td>ЦІНА </td><td><input type=text name=price maxlength=7 size=7><br></td></tr>

<tr><td colspan=2><input type=submit value="Реєстрація"></td></tr>

</table> </form></body></html>

Файл "insert.php"

<html><head><title> БІБЛІОТЕКА </title></head>

<body><h1>БІБЛІОТЕКА</h1>

<?

$isbn=$_POST["isbn"];

$author=$_POST["author"];

$title=$_POST["title"];

$price=$_POST["price"];

if (!$isbn || !$author || !$title || !$price)

{echo "Не всі поля введені"."Повторіть ще раз.";

exit; }

$isbn = addslashes($isbn);//форматування полів перед внесеним в базу даних

$author = addslashes($author);

$title = addslashes($title);

$price = doubleval($price);

@ $db = mysql_pconnect("localhost","root");//з’єднаємося с базою даних

if (!$db)//є така база

{ echo "Error: Не можливо під’єднатися до БД";

exit; }

mysql_select_db("books");//вибрана база даних

$query = "insert into books values ('".$isbn."', '".$author."', '".$title."', '".$price."')";

// запит на вставку значень

$result = mysql_query($query);//виконуємо запит

if ($result)

echo mysql_affected_rows()." КНИГА ДОБАВЛЕНА";

//якщо все успішно з’являється повідомлення

?></body></html>

Пошук книги

<title>БІБЛІОТЕКА</title>

</head>

<body>

<h2>ПОШУК ІНФОРМАЦІЇ</h2>

<form action="results.php" method="post">

КРІТЕРІЙ Пошуку: <br>

<select name="searchtype">

<option value="author">АВТОР

<option value="title">НАЗВА

<option value="isbn">ISBN

</select><br>

ВВЕДІТЬ Значення: <br>

<input name="searchterm" type=text><br>

<input type=submit value="Search"> </form></body></html>

Файл "results.php"

<html><head><title>БІБЛІОТЕКА</title></head>

<body><h1>РЕЗУЛЬТАТИ ПОШУКУ</h1>

<?

$searchtype =$_POST["searchtype "];

$searchterm =$_POST["searchterm "];

if (!$searchtype || !$searchterm)

{echo "You have not entered search details. Please go back and try again.";

exit;}

$searchtype = addslashes($searchtype);

$searchterm = addslashes($searchterm);

$db = mysql_pconnect("localhost","root");

if (!$db)

{echo "Error: Немає з’єднання з базою даних.";

exit;}

mysql_select_db("books");

$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";

$result = mysql_query($query);

$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)

{ $row = mysql_fetch_array($result);

echo "<p><strong>".($i+1).". Title: ";

echo stripslashes($row["title"]);

echo "</strong><br>Author: ";

echo stripslashes($row["author"]);

echo "<br>ISBN: ";

echo stripslashes($row["isbn"]);

echo "<br>Price: ";

echo stripslashes($row["price"]);

echo "</p>";}

?></body></html>

5. Аутентифікація за допомогою РНР й MySQL

Розглянемо, як використати різні можливості РНР й MySQL для аутентифікації користувачів.

Web - це досить анонімне середовище, проте корисно знати, хто відвідав ваш сайт. На щастя, для конфіденційності відвідувачів, без їхньої допомоги можна одержати тільки дуже незначну інформацію.

Сервери можуть отримати інформації про комп'ютери й мережі, з якими з'єднуються, оскільки Web-браузер ідентифікує себе, вказуючи назву браузера, версію браузера й операційну систему, розміри вікна браузера.

Кожен підключений до Internet комп'ютер має унікальну IP-адресу. Можна довідатися, хто володіє цією адресою, та з певною імовірністю припустити географічне положення відвідувача. В основному, люди з постійним підключенням до Internet мають постійні IP-адреси. А клієнти, які додзвонюються до Internet-провайдерів, у більшості випадків одержують у тимчасове користування один з IP-адрес провайдера. Коли наступного разу ви побачите цю адресу вона вже може використовуватися іншим комп'ютером, а коли ви побачите попереднього користувача, у нього, можливо, буде інша IP-адреса.

Інформація, яку видає браузер, не дозволяє повністю ідентифікувати користувача. Якщо хочете знати ім'я відвідувача й інші деталі, варто запитати це безпосередньо у нього.

Прохання до користувача довести свою особистість називається аутентифікаціею. Звичайний метод аутентифікації в Web - це вимога до відвідувачів надати унікальне ім'я користувача й пароль. Аутентифікація звичайно використовується для дозволу або заборони доступу до певних сторінок або ресурсів. Аутентифікація може бути необов'язкової або використовуватися для інших цілей, наприклад, для персоналізації.

Найпростіший спосіб реалізації контролюю доступу. Існує тільки одне значення логіна та пароля.

Приклад 2. Приклад організації контролю доступу.

<?

if(!isset($name)&&!isset($password))

{

//Введіть логін та пароль

?>

<h1>Please Log In</h1>

This page is secret.

<form method = post action = "secret.php">

<table border = 1>

<tr>

<th> Username </th>

<td> <input type = text name = name> </td></tr> <tr>

<th> Password </th>

<td> <input type = password name = password> </td>

</tr><tr>

<td colspan =2 align = center>

<input type = submit value = "Log In">

</td>

</tr></form>

<?

}

else if($name=="user"&&$password=="pass")

{

// якщо логін та пароль співпадають

echo "<h1>Here it is!</h1>";

echo "I bet you are glad you can see this secret page.";

} else

{

// якщо логін та пароль не співпадають

echo "<h1>Go Away!</h1>";

echo "You are not authorized to view this resource.";

}

?>

6. Зберігання паролів

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

Всередині сценарію або іншого файлу даних існує обмеження на кількість користувачів, яких можна обслуговувати. Якщо планується зберігати велику кількість елементів у файлі або робити пошук у рамках великої кількості елементів, варто розглянути можливість використання бази даних замість двовимірного файлу. Практичний метод вибору між файлом і базою даних говорить: якщо ви збираєтеся зберігати й робити пошук у більш ніж 100 елементах, варто віддати перевагу базі даних.

Використання бази даних для зберігання імен і паролів відвідувачів не сильно ускладнить сценарій, але дозволить швидко проводити аутентифікаціею безлічі користувачів. Це також спростить створення сценарію для додавання й видалення користувачів, а також дасть можливість користувачам змінювати свої паролі.

Сценарій для аутентифікації відвідувачів сторінки з використанням бази даних наведений далі.

Приклад 3. Аутентифікації відвідувачів.

create database auth;

use auth;

create table auth (

name varchar(10) not null,

pass varchar(30) not null,

primary key (name)

);

insert into auth values

('user', 'pass');

insert into auth values

('testuser', password('test123')); grant select, insert, update, delete on auth.*

to webauth@localhost

identified by 'webauth';

Файл secretdb.php

<?

$name =$_POST["name"];

$password =$_POST["password "];

if(! isset($name)&&! isset($password))

{

//Відвідувач має ввести логін та пароль

?>

<hl>Введіть логін та пароль</hl>

<form method = post action = "secretdb.php">

<table border = 1 >

<tr>

<th> Username </th>

<td> <input type = text name = name> </td>

</tr> <tr>

<th> Password </th>

<td> <input type = password name = password> </td> </tr> <tr>

<td colspan =2 align = center> <input type = submit value = "Log In">

</td> </tr> </form>

<?

} else

{

// підключення до бази даних

$mysql = mysql_connect( 'localhost', 'webauth', 'webauth');

if(!$mysql)

{ echo 'Cannot connect to database.';

exit;

}

// вибір нашої бази даних

$mysql = mysql_select_db( 'auth' );

if(!$mysql)

{

echo 'Cannot select database.'; exit;

}

// запит до бази даних

$query = "select count(*) from auth where

name = '$name' and

pass = '$password'"; $result = mysql_query( $query ); if(!$result)

{ echo 'Cannot run query.';

exit;

}

$count = mysql_result( $result, 0, 0 );

if($count>0)

{

// якщо логін та пароль існують в базі даних

echo "<hl>Привіт!</hl>";

} else

{

// якщо логін та пароль не існують в базі даних

echo "<hl> До побачення! </hl>";

} }