Лекція 6 Взаємодія PHP і MySQL

 

Лекція 6 Взаємодія PHP і MySQL

  1. Встановлення з'єднання з БД
  2. Вибір бази даних
  3. Одержання списку полів таблиці
  4. Запис даних у базу даних
  5. Відображення даних, що зберігаються в MySQL

Ця частина лекції ознайомить зі способами взаємодії PHP і СУБД MySql. Основна увага буде приділена встановленню з'єднання з базою даних, функціям відправлення запитів і обробці відповідей (mysql_connect, mysql_query, mysql_result, mysql_num_rows, mysql_close). Приклад - створення web -інтерфейсу для адміністрування бази даних віртуального музею історії.

У дистрибутив PHP входить розширення, що містить вбудовані функції для роботи з базою даних MySQL. У цій частині лекції ми познайомимося з деякими основними функціями для роботи з MySQL, що будуть потрібні для розв’язання задач побудови web-інтерфейсів з метою відображення і наповнення бази даних. Виникає питання, навіщо будувати такі інтерфейси? Для того щоб вносити інформацію в базу даних і переглядати її вміст могли люди, не знайомі з мовою запитів SQL. При роботі з web-інтерфейсом для додавання інформації в базу даних людині потрібно просто ввести ці дані в html-форму і відправити їх на сервер, а наш скрипт зробить все інше. А для перегляду вмісту таблиць досить просто клацнути по посиланню і зайти на потрібну сторінку.

Для наочності будемо будувати ці інтерфейси для таблиці Artifacts, у якій міститься інформація про експонати віртуального музею інформатики. У попередній частині лекції ми вже наводили структуру цієї колекції, а також її зв'язку з колекціями опису персон (Persons) і зображень (Images). Нагадаємо, що кожен експонат у колекції Artifacts описується за допомогою наступних характеристик:

  • назва (title);
  • автор (author);
  • опис (description);
  • альтернативна назва (alternative);
  • зображення (photo).

Назва й альтернативна назва є рядками менш ніж 255 символів довжиною (тобто мають тип VARCHAR(255)), опис - текстове поле (має тип TEXT), а в полях "автор" і "зображення" містяться ідентифікатори автора з колекції Persons і зображення експоната з колекції Images відповідно.

Отже, у нас є якась таблиця в базі даних. Щоб побудувати інтерфейс для додавання інформації в цю таблицю, потрібно її структуру (тобто набір її полів) відобразити в html-форму. Розіб'ємо цю задачу на наступні підзадачі:

  • установка з'єднання з БД;
  • вибір робочої БД;
  • одержання списку полів таблиці;
  • відображення полів у html-форму.

Після цього дані, введені у форму, потрібно записати в базу даних. Розглянемо всі ці задачі одну за одною.

1. Встановлення з'єднання із БД

Отже, перше, що потрібно зробити, - це встановити з'єднання з базою даних. Скористаємося функцією mysql_connect.

Синтаксис mysql_connect

mysql_connect ( [рядок server [, рядок username [, рядок password 
                        [, логічне new_link [, ціле client_flags]]]]])

Дана функція встановлює з'єднання із сервером MySQL і повертає вказівник на це з'єднання або FALSE у випадку невдачі. Для відсутніх параметрів встановлюються наступні значення за замовчуванням:

server = 'localhost:3306' 
username = ім'я користувача власника процесу сервера
password = порожній пароль 

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

Параметр client_flags - це комбінація наступних констант: MYSQL_CLIENT_COMPRESS (використовувати протокол стиску), MYSQL_CLIENT_IGNORE_SPACE (дозволяє вставляти пробіли після імен функцій), MYSQL_CLIENT_INTERACTIVE (чекати interactive_timeout секунд - замість wait_timeout - до закриття з'єднання).

Параметр new_link з'явився в PHP 4.2.0, а параметр client_flags - у PHP 4.3.0.

З'єднання із сервером закривається при завершенні виконання скрипта, якщо воно до цього не було закрито за допомогою функції mysql_close().

Отже, встановлюємо з'єднання з базою даних на локальному сервері для користувача nina з паролем "123":

<?
$conn = mysql_connect("localhost", "nina","123")
or die("Неможливо установити з'єднання: ". mysql_error());
echo "З'єднання встановлене";
mysql_close($conn);
?>

Дія mysql_connect рівносильна команді shell>mysql -u nina -p123

2. Вибір бази даних

Після встановлення з'єднання потрібно вибрати базу даних, з якою будемо працювати. Наші дані зберігаються в базі даних book. У MySQL вибір бази даних здійснюється за допомогою команди use:

mysql>use book;

У PHP для цього існує функція mysql_select_db.

Синтаксис mysql_select_db:

логічне mysql_select_db (рядок database_name [, ресурс link_identifier]) 

Ця функція повертає TRUE у випадку успішного вибору бази даних і FALSE - у протилежному випадку.

Зробимо базу даних book робочою:

<?
$conn = mysql_connect("localhost","nina","123") 
or die("Неможливо установити з'єднання: ". mysql_error());
echo "З'єднання встановлене";
mysql_select_db("book");
?> 

3. Одержання списку полів таблиці  

Тепер можна зайнятися власне розв’язанням задачі. Як одержати список полів таблиці? Дуже просто. У PHP і на цей випадок є своя команда - mysql_list_fields.

Синтаксис mysql_list_fields

ресурс mysql_list_fields (рядок database_name,рядок table_name[, ресурс link_identifier])

Ця функція повертає список полів у таблиці table_name у базі даних database_name. Отже, вибирати базу данних нам було необов'язково, але це знадобиться пізніше. Як можна помітити, результат роботи цієї функції - змінна типу ресурс. Тобто це не зовсім те, що ми хотіли отримати. Це посилання, яке можна використовувати для одержання інформації про поля таблиці, включаючи їх назви, типи і прапори.

Функція mysql_field_name повертає ім'я поля, отриманого в результаті виконання запиту. Функція mysql_field_len повертає довжину поля. Функція mysql_field_type повертає тип поля, а функція mysql_field_flags повертає список прапорів поля, записаних через пробіл. Типи полів можуть бути int, real, string, blob і т.д. Прапори можуть бути not_null, primary_key, unique_key, blob, auto_increment і т.д.

Синтаксис у всіх цих команд однаковий:

рядок mysql_field_name (ресурс result, ціле field_offset)
рядок mysql_field_type (ресурс result, ціле field_offset)
рядок mysql_field_flags (ресурс result, ціле field_offset)
рядок mysql_field_len (ресурс result, ціле field_offset)

Тут result - це ідентифікатор результату запиту (наприклад, запиту, відправленого функціями mysql_list_fields або mysql_query (про неї буде розказано пізніше)), а field_offset - порядковий номер поля в результаті.

Взагалі кажучи, те, що повертають функції типу mysql_list_fields або mysql_query, являє собою таблицю, а точніше, вказівник на неї. Щоб одержати з цієї таблиці конкретні значення, потрібно задіяти спеціальні функції, що порядково читають цю таблицю. До таких функцій і відносяться mysql_field_name і т.п. Щоб перебрати всі рядки в таблиці результату виконання запиту, потрібно знати кількість рядків у цій таблиці. Команда mysql_num_rows(ресурс result) повертає кількість рядків у безлічі результатів result.

А тепер спробуємо одержати список полів таблиці Artifacts (колекція експонатів).

<?
$conn = mysql_connect("localhost","nina","123")
or die("Неможливо установити з'єднання: ". mysql_error());
echo "З'єднання встановлене";
mysql_select_db("book");
$list_f = mysql_list_fields ("book","Artifacts",$conn);
$n = mysql_num_fields($list_f);
for($i=0;$i<$n; $i++){
$type = mysql_field_type($list_f, $i);
$name_f = mysql_field_name($list_f,$i);
$len = mysql_field_len($list_f, $i);
$flags_str =  mysql_field_flags ($list_f, $і);
echo "<br>Ім'я поля: ". $name_f;
echo "<br>Тип поля: ". $type;
echo "<br>Довжина поля: ". $len;
echo "<br>Рядок прапорів поля: ". $flags_str . "<hr>";
}
?>

У результаті повинно вийти приблизно от що (якщо в таблиці всього два поля, звичайно):

Ім'я поля: id
Тип поля: int
Довжина поля: 11
Рядок прапорів поля: not_null primary_key auto_increment
Ім'я поля: title
Тип поля: string
Довжина поля: 255
Рядок прапорів поля: 

Відображення списку полів у html-формі

Тепер дещо підкоректуємо попередній приклад. Будемо не просто виводити інформацію про поле, а відображати його як потрібний елемент html-форми. Так, елементи типу BLOB переведемо в textarea (помітимо, що поле description, що ми створювали з типом TEXT, відображається як таке, що має тип BLOB), числа і рядки відобразимо як текстові рядки введення <input type=text>, а елемент, що має мітку автоінкремента, взагалі не будемо відображати, оскільки його значення встановлюється автоматично.

Усе це розв’язується досить просто, за винятком виділення зі списку прапорів прапора auto_increment. Для цього потрібно скористатися функцією explode.

Синтаксис explode:

масив explode(рядок separator, рядок string [, int limit])

Ця функція розбиває рядок string на частини за допомогою роздільника separator і повертає масив отриманих рядків.

У нашому випадку як роздільник потрібно взяти пробіл " ", а як задану стрічку для розбивки - рядок прапорів поля.

Отже, створимо форму для введення даних у таблицю Artifacts:

<?
$conn=mysql_connect("localhost","nina","123"); // встановлюємо з’єднання
$database = "book";
$table_name = "Artifacts";
mysql_select_db($database); // обираємо базу даних для роботи 
$list_f = mysql_list_fields($database,$table_name); // отримуємо список полів в базі
$n = mysql_num_fields($list_f); 
        // кількість стрічок в результаті попереднього запиту
        //тобто скільки всього полів в таблиці Artifacts) 
echo "<form method=post action=insert.php>";  
        // створюємо форму для введення даних
echo "<TABLE BORDER=0 CELLSPACING=0 width=50%><tr>
        <TD  BGCOLOR='#005533' align=center>
        <font color='#FFFFFF'>
        <b> Add new row in $table_name</b>
        </font></td></tr>
        <tr><td></td></tr></TABLE>";
echo "<table border=0 CELLSPACING=1 cellpadding=0 width=50% >";
// для кожного поля отримуємо його ім’я, тип, довжину і прапори
for($i=0;$i<$n; $i++){
        $type = mysql_field_type($list_f, $i);
        $name_f = mysql_field_name ($list_f,$i);
        $len = mysql_field_len($list_f, $i);
        $flags_str = mysql_field_flags ($list_f, $i);
// із стрічки прапорів робимо масив, де кожен елемент масиву – прапор поля, 
        $flags = explode(" ", $flags_str); 
        foreach ($flags as $f){
if ($f == 'auto_increment') $key = $name_f; 
        // запам’ятовуємо ім’я інкремента
}
/* для кожного поля, що не автоінкрементом, в 
залежності від його типу виводимо потрібний елемент форми */
if ($key <> $name_f){ 
echo "<tr><td align=right bgcolor='#C2E3B6'>
        <font size=2><b>&nbsp;". $name_f ."</b></font> 
        </td>";
switch ($type){
        case "string":
        $w = $len/5;
        echo "<td><input type=text name=\"$name_f\" size = $w ></td>";
break;
case "int": 
        $w =  $len/4;
        echo "<td><input type=text name=\"$name_f\" size = $w ></td>";
break;
case "blob":
echo "<td><textarea rows=6 cols=60 name=\"$name_f\"></textarea></td>";
break;  
} 
}
echo "</tr>";
echo "</table>";
echo "<input type=submit name='add' value='Add'>";
echo "</form>";
?>

4. Запис даних у базу даних

Отже, форма створена. Тепер потрібно зробити саме головне - відправити дані з цієї форми в нашу базу даних. Як ви вже знаєте, для того щоб записати дані в таблицю, використовується команда INSERT мови SQL. Наприклад:

mysql> INSERT INTO Artifacts SET title='Петров';

Виникає питання, як можна скористатися такою командою (або будь-якою іншою командою SQL) у PHP скрипті. Для цього існує функція mysql_query().

Синтаксис mysql_query

ресурс mysql_query ( рядок query [, ресурс link_identifier])

mysql_query() посилає SQL-запит активній базі даних MySQL сервера, що визначається за допомогою вказівника link_identifier (це посилання на якесь з'єднання із сервером MySQL). Якщо параметр link_identifier опущений, використовується останнє відкрите з'єднання. Якщо відкриті з'єднання відсутні, функція намагається з'єднатися із СУБД, аналогічно функції mysql_connect() без параметрів. Результат запиту буферизується.

Зауваження: рядок запиту НЕ повинен закінчуватися крапкою з комою.

Тільки для запитів SELECT, SHOW, EXPLAIN, DESCRIBE, mysql_query() повертає вказівник на результат запиту, або FALSE, якщо запит не був виконаний. В інших випадках mysql_query() повертає TRUE, якщо запит виконаний успішно, і FALSE - у випадку помилки. Значення, не рівне FALSE, говорить про те, що запит був виконаний успішно. Воно не говорить про кількість змінених або повернутих рядів. Цілком можлива ситуація, коли успішний запит не торкнеться жодного ряду. mysql_query() також вважається помилковим і поверне FALSE, якщо в користувача недостатньо прав для роботи з зазначеною в запиті таблицею.

Отже, тепер ми знаємо, як відправити запит на вставку рядків у базу даних. Помітимо, що в попередньому прикладі елементи форми ми назвали іменами полів таблиці. Тому вони будуть доступні в скрипті insert.php, що обробляє дані форми, як змінні виду

$_POST['ім'я_поля'].
<?
$conn=mysql_connect("localhost","nina","123");// встановлюємо з’єднання
$database = "book";
$table_name = "Artifacts";
mysql_select_db($database); // обираємо базу даних
$list_f = mysql_list_fields($database,$table_name); 
        // отримуємо список полів в базі
$n = mysql_num_fields($list_f); 
        // кількість стрічок в результаті попереднього запиту
        // створимо один запит відразу для всіх полів таблиці
$sql = "INSERT INTO $table_name SET ";  
        // починаємо створювати запит, перебираємо всі поля таблиці
for($i=0;$i<$n; $i++){
$name_f = mysql_field_name ($list_f,$i); // визначаємо ім’я поля
$value = $_POST[$name_f]; // обчислюємо значення поля
        $j = $i + 1;
        $sql = $sql . $name_f." = '$value'";  
        // дописуємо в стрічку $sql пару ім’я=значення
        if ($j <> $n) $sql = $sql . ", ";  
        // якщо поле не останнєв списку, то ставимо кому
}
// перед тим, як записувати що небуть у базу можна подивитися, 
//який запит отримаєтьмя 
//echo $sql; 
$result = mysql_query($sql,$conn); // відправляємо запит 
// виводимо повідомлення чи успішно виконано запит
if (!result) echo " Can't add ($table_name) "; 
        else echo "Success!<br>"; 
?>

Отже, задачу додавання даних за допомогою web-інтерфейсу ми розв’язали. Однак тут є одна тонкість. При розв’язанні ми не враховували той факт, що значення деяких полів (author, photo) повинні братися з інших таблиць (Persons, Images). Потрібно дописати програму таким чином, щоб була можливість вводити в такі поля правильні значення. Але ми робити цього не будемо, оскільки завдання лекції полягає в тому, щоб познайомити читача з елементами технології, а не в тому, щоб створити працюючу систему. Тепер звернемося до іншої задачі - відображення даних, що зберігаються в базі даних СУБД MySQL.

5. Відображення даних, що зберігаються в MySQL

Щоб відобразити якісь дані в браузер за допомогою PHP, потрібно спочатку одержати ці дані у вигляді змінних PHP. При роботі з MySQL без посередника (такого, як PHP) вибірка даних робиться за допомогою команди SELECT мови SQL:

mysql> SELECT * FROM Artifacts;

У попередньому розділі ми говорили, що будь-який запит, у тому числі і на вибірку, можна відправити на сервер за допомогою функції mysql_query(); Там у нас була дещо інша задача - одержати дані з форми і відправити їх за допомогою запиту на вставку в базу даних. Результатом роботи mysql_query() там міг бути лище один з виразів, TRUE або FALSE. Тепер же потрібно відправити запит на вибірку всіх полів, а результат відобразити в браузері. І тут результат - це ціла таблиця значень, а точніше, вказівник на цю таблицю. Так що потрібні якісь аналоги функції mysql_field_name(), тільки щоб вони витягували з результату запиту не ім'я, а значення поля. Таких функцій у PHP декілька. Найбільш популярні - mysql_result() і mysql_fetch_array().

Синтаксис mysql_result

змішане mysql_result (ресурс result, ціле row [, змішане field])

mysql_result() повертає значення однієї комірки результату запиту. Аргумент field може бути порядковим номером поля в результаті, ім'ям поля або ім'ям поля з ім'ям таблиці через крапку tablename.fieldname. Якщо для імені поля в запиті застосовувався аліас ('select foo as bar from...'), використовуйте його замість реального імені поля.

Працюючи з великими результатами запитів, варто задіяти одну з функцій, що обробляє відразу цілий ряд результатів (наприклад, mysql_fetch_row(), mysql_fetch_array() і т.д.). Тому що ці функції повертають значення декількох комірок відразу, вони НАБАГАТО швидші mysql_result(). Крім того, потрібно врахувати, що вказівка чисельного зсуву (номера поля) працює набагато швидше, ніж вказівка стовпчика або стовпчиків і таблиці через крапку.

Виклики функції mysql_result() не повинні змішуватися з іншими функціями, що працюють з результатом запиту.

Синтаксис mysql_fetch_array

масив mysql_fetch_array (ресурс result [, ціле result_type])

Ця функція обробляє ряд результатів запиту, повертаючи масив (асоціативний, чисельний або обидва) з обробленим рядом результатів запиту, або FALSE, якщо рядів більше немає.

mysql_fetch_array() - це розширена версія функції mysql_fetch_row(). Крім збереження значень у масиві з чисельними індексами, функція повертає значення в масиві з індексами за назвою стовпчиків.

Якщо декілька колонок у результаті будуть мати однакові назви, буде повернутий останній стовпчик. Щоб одержати доступ до перших, варто використовувати чисельні індекси масиву або аліаси в запиті. У випадку аліасів саме їх ви не зможете використовувати

Важливо зауважити, що mysql_fetch_array() працює НЕ повільніше, ніж mysql_fetch_row(), і надає більш зручний доступ до даних.

Другий опційний аргумент result_type у функції mysql_fetch_array() є константою і може приймати наступні значення: MYSQL_ASSOC, MYSQL_NUM і MYSQL_BOTH. Ця можливість додана в PHP 3.0.7. Значенням за замовчуванням є: MYSQL_BOTH.

Використовуючи MYSQL_BOTH, одержимо масив, що складається як з асоціативних індексів, так і з чисельних. MYSQL_ASSOC поверне тільки асоціативні відповідності, а MYSQL_NUM - тільки чисельні.

Зауваження: імена полів, що повертаються цією функцією, регістронезалежні.

У цій лекції ми розглянули дві задачі: додавання даних у базу даних і їхнє відображення в браузері за допомогою мови PHP. Для цього ми розглянули ряд функцій, що дозволяють відправляти SQL-запити до бази даних і обробляти отримані відповіді. Використовуючи наведену тут технологію, можна розв’язати цілий ряд схожих задач, таких як задачі зміни і знищення даних, задачі маніпулювання таблицями бази даних (тобто їхнє створення, зміна і знищення) і т.п. Усе це типові задачі, що виникають при розробці систем керування даними, і вміння їх розв’язувати, як і вміння працювати з базами даних у цілому, дуже важливі для web-програміста.