Введение в базу данных Sqlite

Дата публикации: 2012-07-30

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

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

План урока

1. Введение

2. Подключение модуля базы данных sqlite

3. Создание базы данных и таблиц

Фреймворк YII2. Быстрая разработка с современным PHP фреймворком

Узнай тонкости современной веб-разработки с помощью фреймворка YII2

Узнать подробнее

4. Заносим данные в базу данных

5. Вывод информации из базы данных

Детали учебника

Тема: PHP

Сложность: Средняя

Урок: Видео (.mp4)

Время: 01:38:51

Размер архива: 110 Mb

1. Введение.

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

Sqlite – это встраиваемая реляционная база данных, с открытым исходным кодом. То есть она не использует привычную нам модель работы базы данных клиент-сервер и не является отдельным работающим процессом. Как например база данных Mysql. Другими словами движок Sqlite становится как бы частью нашего веб-приложения. При таком подходе база данных Sqlite (со всеми таблицами) представляет собой обычный текстовый файл, который вы можете расположить в удобном для Вас месте.

Теперь давайте рассмотрим основные преимущества Sqlite.

Самодостаточность – базе данных Sqlite не требуется отдельный сервер для работы. Движок Sqlite встраивается прямо в приложение и требует только доступ к файлам.

Простота – установка и настройка новой базы очень проста и не требуется вмешательство системных администраторов.

Встраиваемость – движок Sqlite полностью встроен в PHP 5 и не требуется установка дополнительного программного обеспечения.

Предоставляет как процедурный, так и объектно-ориентированный интерфейс для работы.

Высокая производительность – движок потребляет очень мало ресурсов и не тратится время на отправку данных к отдельному выделенному серверу.

Sqlite замечательно подходит для веб-приложений, когда основная масса запросов представляет собой запросы на чтение, также очень хорошо подойдет для небольших проектов и проектов которые в дальнейшем Вы собираетесь развивать. Так как Sqlite работает с такими же SQL запросами, что и базы данных серверного типа а это дает нам возможность легко перейти на другую базу данных, не внося кардинальных изменений в логику веб-приложения (особенно используя расширение PDO).

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

2. Подключение модуля базы данных sqlite

Для начала необходимо убедиться, что модуль базы данных sqlite, подключен в файле php.ini. Для этого откройте данный файл (для тех, у кого установлен Денвер, файл находится по адресу \usr\local\php5\php.ini) и найдите строчку:

;extension=php_sqlite.dll

Далее просто уберите точку с запятой в начале строки, сохраните изменения в файле и перезапустите сервер Apache (или Денвер), у вас строка должна быть вот такого вида:

extension=php_sqlite.dll

На реальных серверах (хостингах) этот модуль уже поключен.

3. Создание базы данных и таблиц.

Первым делом необходимо создать базу данных и необходимые таблицы в ней. Для этого давайте создадим файл config.php, в котором создадим константу DB_NAME для хранения имени базы данных. Вот код файла config.php:

<?php
define('DB_NAME','mydb.db');
?>

Как я и говорил в начале, база данных в sqlite – это обычный текстовый файл (специально отформатированный), поэтому указывая имя базы данных, не забывайте указывать расширение, в моем случае это .db, но это не принципиально, расширение можно ставить какое угодно.

Теперь давайте создадим файл install.php, в котором и создадим базу данных с требуемым набором таблиц. Для этого открываем в файле PHP –теги и начинаем кодировать. Для начала подключим файл конфигурации config.php:

include 'config.php';

Затем создадим базу данных, используя функцию sqlite_open(‘имя базы данных’). Если базы данных еще не существует, то эта функция создает базу данных, с именем переданным ей параметром, открывает ее для работы и возвращает дескриптор подключения (в нашем случае дескриптор сохранился в переменную $db). Если же база данных существует – то функция просто открывает базу данных и возвращает дескриптор подключения. Ниже привожу код необходимый для создания базы данных:

$db = sqlite_open(DB_NAME);
if(!$db) {
 exit('ERROR'.sqlite_error_string(sqlite_last_error($db)));
}
echo 'База данных '.DB_NAME. ' создана<br>';

Как Вы видите, я после вызова функции sqlite_open(имя базы данных), сделал небольшую проверку – на то не возникли ли при создании базы данных ошибки. Если возникла ошибка, то происходит выход из скрипта с выводом текста ошибки на экран.

Для вывода ошибок базы данных sqlite на экран необходимо использовать две функции. Первая это функция: sqlite_last_error(дескриптор подключения к базе), которая возвращает код ошибки. Вторая — sqlite_error_string(код ошибки), которая расшифровывает код ошибки, и возвращает непосредственно текст ошибки.

Теперь давайте перейдем в браузер и запустим файл install.php. На экране появилась строка — База данных mydb.db создана. Теперь посмотрите в папку где располагается наш скрипт и Вы увидите что был создан текстовый файл База mydb.db – это и есть наша база данных.

Теперь необходимо создать две таблицы: таблицу menu для хранения пунктов меню и таблицу articles для хранения, каких-то статей нашего мини-сайта. Первым делом создадим таблицу menu. Вот код для ее создания:

// запрос к базе данных
 $sql = "CREATE TABLE articles 
 (
 id_articles INTEGER PRIMARY KEY,
 name_articles,
 text_articles
 );
 ";
 //выполняем запрос
 $result = sqlite_query($db,$sql);
 if(!$result) {
  exit('ERROR'.sqlite_error_string(sqlite_last_error($db)));
 }
 else {
  echo 'Таблица articles создана<br>';
 }

Как Вы видите, первым делом создаем sql запрос к базе данных. Он очень похож на запрос к базе данных MySql. Но есть отличия в объявлении типов данных, которые должны хранится в столбцах таблицы. В простейшем случае типы данных можно вообще не указывать, при этом данные будут вставлены как строки. База данных sqlite версии 2 имела всего несколько типов данных, а именно INTEGER и текстовый тип данных, для хранения больших объемов текста.

В более старших версиях, начиная с версии 2.7.0 были введены различные типы текстовых полей, такие как BLOB, CHAR, CLOB, TEXT, в версии 3.0 созданы специальные классы данных NULL, INTEGER, REAL,TEXT, BLOB, которые включают в себя дополнительные типы данных.

Но в любом случае типы данных в базе данных sqlite – декларативные, то есть какой бы не был указан столбцу класс – в него можно положить любые данные, как текстовые так и числовые. Исключение составляет только поля, у которых назначен тип данных INTEGER PRIMARY KEY, — эта запись означает, что поле является первичным ключом и тип данных в нем должен быть обязательно числовым. При этом поле автоматически получает свойство AUTOINCREMENT.

После создания запроса к базе данных, необходимо его выполнить. Для этого применяем функцию sqlite_query(дескриптор подключения, запрос). Эта функция аналогична функции mysql_query() – то есть она выполняет запрос к базе данных и возвращает результат запроса. Первым параметром ей передаем дескриптор подключения к базе данных, вторым – непосредственно сам запрос. Затем выполняем стандартную проверку на наличие ошибок.
Давайте перейдем в браузер и посмотрим что у нас получилось. После обновления страницы на экране появляется строка – Таблица articles создана. Значит все правильно.

Теперь создадим вторую таблицу menu. Вот код для ее создания:

Фреймворк YII2. Быстрая разработка с современным PHP фреймворком

Узнай тонкости современной веб-разработки с помощью фреймворка YII2

Узнать подробнее

// запрос к базе данных
 $sql2 = "CREATE TABLE menu 
 (
 id_menu INTEGER PRIMARY KEY,
 name_menu,
 link_menu
 )
 ";
 //выполняем запрос 
 $result2 = sqlite_query($db,$sql2);
 if(!$result2) {
  exit('ERROR'.sqlite_error_string(sqlite_last_error($db)));
 }
 else {
  echo 'Таблица menu создана<br>';
 }

Как Вы видите здесь все аналогично таблице articles. Теперь давайте приведу весь код файла install.php:

<?php
include 'config.php';

if(!file_exists(DB_NAME)) {
 $db = sqlite_open(DB_NAME);
 if(!$db) {
  exit('ERROR'.sqlite_error_string(sqlite_last_error($db)));
 }
 echo 'База данных '.DB_NAME. ' создана<br>';
 
 // запрос к базе данных
 $sql = "CREATE TABLE articles 
 (
 id_articles INTEGER PRIMARY KEY,
 name_articles,
 text_articles
 );
 ";
 //выполняем запрос
 $result = sqlite_query($db,$sql);
 if(!$result) {
  exit('ERROR'.sqlite_error_string(sqlite_last_error($db)));
 }
 else {
  echo 'Таблица articles создана<br>';
 }
 
 // запрос к базе данных
 $sql2 = "CREATE TABLE menu 
 (
 id_menu INTEGER PRIMARY KEY,
 name_menu,
 link_menu
 )
 ";
 //выполняем запрос 
 $result2 = sqlite_query($db,$sql2);
 if(!$result2) {
  exit('ERROR'.sqlite_error_string(sqlite_last_error($db)));
 }
 else {
  echo 'Таблица menu создана<br>';
 }
 sqlite_close($db);
 echo 'База даных закрыта...'; 
}
else {
 exit('База данных уже существует');
}
?>

Обратите внимание, я сделал еще одну проверку на существование файла базы данных. Если его нет, то мы создаем базу данных и необходимые таблицы, если же он есть, то выводим на экран сообщение ‘База данных уже существует’. В конце попросту закрываем соединение с базой данных при помощи функции sqlite_close(дескриптор подключения); Давайте перейдем в браузер и посмотрим что получилось:

Введение в базу данных Sqlite

Как видите, база данных создана с двумя табличками.

4. Заносим данные в базу данных.

Теперь, когда у нас есть база данных необходимо ее наполнить. Для этого предлагаю сделать очень простую администраторскую панель для нашего сайта. В которой, будет всего две формы для добавления данных. Первая для добавления пунктов меню, вторая для добавления статей.

Давайте создадим файл admin.php, с таким содержимым:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Admin panel</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div id="wrap">
<h2>ADMIN PANEL</h2>
 <div id="insert_menu">
 <p>Добавление нового пункта меню</p>
  <form name="insert_menu" method="POST">
 Name menu<br>
 <input type="text" name="name_menu">
 <br>Link<br> 
 <input type="text" name="link_menu">
 <br> 
 <input type="submit" name="submit_menu">
  </form>
 </div>
 
 <div id="insert_articles">
 <p>Добавление новой статьи</p>
  <form name="insert_articles" method="POST">
 Name article<br>
 <input type="text" name="name_articles">
 <br>Text<br> 
 <textarea name="text_articles" rows="10" cols="34"></textarea>
 <br> 
 <input type="submit" name="submit_articles">
  </form>
 </div>
 <div id="footer">
  <h4>footer</h4>
 </div>
</div>
</body>
</html>

Как Вы видите – обычная html-разметка, две формы заключенные в общий контейнер, небольшая шапка и футер. Так же обратите внимания, что я подключаю файл стилей style.css, который необходимо также создать. Вот его код:

#insert_menu {
 width:300px;
 float:left;
 padding:10px;
}
#insert_articles{
 width:300px;
 float:left;
 padding:10px;
 border-left:1px solid #074776;
 margin-bottom:20px;
}
h2 {
 border-bottom:20px solid #d3d3d3;
}
#wrap{
 width:950px;
 margin:0px auto 0px auto;
 border:1px solid #074776;
 
 padding:10px;
}
#footer {
 clear:both;
 text-align:center;
 border-top:20px solid #d3d3d3;
}

Теперь если набрать в браузере только что созданный файл admin.php, мы увидим следующее:

Введение в базу данных Sqlite

Теперь давайте создадим файл functions.php, в котором мы будем хранить наши пользовательские функции. Первая функция, что мы опишем будет функция очистки данных clear($str), так как нам приходят данные из формы, а их необходимо фильтровать. Итак, код функции:

function clear($str) {
 $str = trim($str);
 $str = strip_tags($str);
//проверяем включена ли директива конфигурации magic_quotes_gpc
 if(get_magic_quotes_gpc()) {
  $str = stripslashes($str);
 }
 $str = sqlite_escape_string($str);
 
 return $str;
}

Как Вы видите все просто, стандартные функции. Но здесь есть одно замечание: не рекомендуется вставлять данные в базу данных, у которых экранированы спецсимволы при помощи функции addcslashes(). Поэтому проверяем, если у пользователя включено экранирование спецсимволов (директива конфигурации magic_quotes_gpc), то убираем экранирующие бэкслэши при помощи функции stripslashes($str). Для экранирования спецсимволов у базы данных sqlite есть своя функция sqlite_escape_string(строка), которая экранирует спецсимволы в строке для дальнейших запросов.

Возвращаемся в файл admin.php и создадим обработчик формы, вот его код (вставляем перед тегами html):

<?php
session_start();
include 'config.php';
include 'functions.php';

//проверяем отправлена ли форма
if($_SERVER['REQUEST_METHOD'] == 'POST') {
 
 //открываем базу данных
 $db = sqlite_open(DB_NAME);
 if(isset($_POST['submit_menu'])) {
 
  //Очищаем полученные данные
  $name_menu = clear($_POST['name_menu']);
  $link_menu = clear($_POST['link_menu']);
 
  //вставляем данные в базу данных
  $msg = insert_menu($db,$name_menu,$link_menu);
  if($msg === TRUE) {
 $_SESSION['msg'] = 'Пункт меню добавлен';
  }
  else {
 $_SESSION['msg'] = $msg;
  }
 }
//перенаправляем страницу саму на себя для очистки POST данных
 header('Location:'.$_SERVER['PHP_SELF']);
}
?>

Итак, в начале открываем сессию – для системных сообщений. Затем подключаем файл functions.php. Далее если была отправлена форма, вначале открываем базу данных, очищаем полученные данные и вызываем функцию insert_menu ($db,$name_menu,$link_menu), которая вставляет данные в базу данных (мы ее чуть позже опишем). Функция принимает три параметра: первый дисриптор подключения к базе данных, второй и третий данные которые необходимо вставить. Она будет возвращать TRUE, если данные успешно вставились в базу, и сообщение о ошибке, в случае неудачной вставки данных.

Теперь пока у нас открыт файл admin.php давайте вставим вывод системных сообщений, которые будут хранится в сессии. Этот код вставляем после заголовка второго уровня ADMIN PANEL:

<p>
<? echo $_SESSION['msg'];?>
</p>

Теперь открываем файл functions.php и описываем функцию insert_menu():

function insert_menu($db,$name_menu,$link_menu) {
 
 //SQL запрос
 $sql = "INSERT INTO 
 menu(name_menu,link_menu) 
 VALUES('$name_menu','$link_menu')";
 
 //выполняем запрос
 $result = sqlite_query($db,$sql);
 if($result) {
  return TRUE;
 }
 else {
  return 'Ошибка выполнения запроса '.sqlite_error_string(sqlite_last_error($db));
 }
}

Как Вы видите, данные в базу данных sqlite вставляются точно также как и в базу данных mysql. Смотрите, точно такой же SQL-запрос, единственное отличие – это то, что мы используем функцию sqlite_query() для выполнения запроса. Если запрос успешно выполнен возвращаем TRUE, если нет, то возвращаем сообщение об ошибке. Давайте перейдем в браузер и попробуем добавить данные в таблицу menu. Запускаем файл admin.php и вводим данные в форму. После нажатия на кнопку отправки формы, на экран выводится надпись что данные успешно добавлены.

Введение в базу данных Sqlite

Теперь осталось реализовать добавление статей в базу данных. Для этого создадим функцию insert_articles(), которая аналогично функции insert_menu(), будет вставлять данные в базу, и возвращать при этом либо TRUE, либо сообщение об ошибках, в зависимости от результата.

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

function insert_articles($db,$name_articles,$text_articles) {
 
 //SQL запрос
 $sql = "INSERT INTO 
 articles(name_articles,text_articles) 
 VALUES('$name_articles','$text_articles')";
 
 $result = sqlite_query($db,$sql);
 
 if($result) {
  return TRUE;
 }
 else {
  return 'Ошибка выполнения запроса '.sqlite_error_string(sqlite_last_error($db));
 }
}

Как Вы видите здесь все аналогично. Теперь возвращаемся в файл admin.php и добавляем обработчик для второй формы. Для этого вставляем следующий код, сразу после закрывающей скобки условного оператора if, который проверяет, существует ли в массиве POST ячейка с ключем $_POST['submit_menu']:

if(isset($_POST['submit_articles'])) {
 
  //Очищаем полученные данные
  $name_articles = clear($_POST['name_articles']);
  $text_articles = clear($_POST['text_articles']);
 
  //вставляем данные в базу данных
  $msg = insert_articles($db,$name_articles,$text_articles);
  if($msg === TRUE) {
 $_SESSION['msg'] = 'Статья добавлена';
  }
  else {
 $_SESSION['msg'] = $msg;
  }
 }

То есть, попросту говоря, проверяем нажата ли кнопка отправки формы. Если да то очищаем данные и вызываем функцию insert_articles().

Теперь давайте попробуем добавить текст в базу. После отправки формы на экран вывелось сообщене Статья добавлена, значит мы все делаем правильно.

5. Вывод информации из базы данных.

Теперь, когда данные добавлены в базу данных, давайте реализуем вывод этих данных на экран. Для этого создадим файл index.php вот с такой html-разметкой:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Добро пожаловать</title>
<meta name="" content="">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
<link rel="stylesheet" href="style.css">
</head>
<body>
<div id="wrap">
 <div id="header">
  <h1>Visitka</h1>
 </div>
 <div id="menu">
 
 </div>
 
 <div id="text">
 
 </div>
 <div id="footer">
 <h4>footer</h4>
 </div>
</div>
</body>
</html>

И в файл style.css добавим несколько правил:

#menu {
 width:200px;
 float:left;
 
}
#text {
 float:left;
 width:700px;
 border-left:2px solid #d3d3d3;
 padding:10px;
}
#header{
 height:150px;
 width:100%;
 border-bottom:20px solid #d3d3d3;
}
h1 {
 text-align:center;
}

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

Введение в базу данных Sqlite

Теперь вернемся к файлу index.php и перед html- разметкой откроем php теги. Затем откроем сессию, подключим конфигурационный файл и файл functions.php, откроем базу данных для работы и вызовем функцию get_menu(), которая будет возвращать массив пунктов меню, хранящихся в базе данных, и функцию get_articles(), которая будет возвращать массив статтей. Вот данный код:

<?php
session_start();
//подключаем файли
include 'config.php';
include 'functions.php';
//открываем базу для работы
$db = sqlite_open(DB_NAME);
//получаем массив пунктов меню
$menu = get_menu($db);
if(!$menu) {
 exit(sqlite_error_string(sqlite_last_error($db)));
}
//получаем массив статтей
$text = get_articles($db);
if(!$text) {
 exit(sqlite_error_string(sqlite_last_error($db)));
}
?>

Теперь перейдем в файл functions.php и опишем функции get_menu() и get_articles(). Вот код для этих функций:

function get_articles($db) {
 // SQL - запрос
 $sql = "SELECT id_articles,name_articles,text_articles FROM articles";
 
 //выполняем запрос и возвращаем асоциативный массив данных
 $result = sqlite_array_query($db,$sql,SQLITE_ASSOC);
 
 if(!$result) {
  return FALSE;
 }
 return $result;
 
}

function get_menu($db) {
 
 // SQL - запрос
 $sql = "SELECT name_menu,link_menu FROM menu";
 
 //выполняем запрос и возвращаем асоциативный массив данных
 $result = sqlite_array_query($db,$sql,SQLITE_ASSOC);
 
 if(!$result) {
  return FALSE;
 }
 return $result;
}

Как Вы видите, функции принимают параметром дискриптор подключения к базе данных. Давайте поясню работу функций на примере функции get_menu(), так как функция get_articles() — аналогична ей.

Как обычно формируем SQL – запрос, он точно такой же как и в базе данных Mysql. Выбираем поля name_menu и link_menu из базы данных menu. Далее обратите внимание на очень интересную функцию sqlite_array_query(дескриптор, SQL – запрос, параметр как возвращать данные). Такой функции нет в базе данных Mysql!!. Смотрите — эта функция выполняет переданный ей SQL – запрос и сразу же возвращает уже готовый реальный массив данных (в нашем случае ассоциативный так как мы передали ей константу SQLITE_ASSOC, что означает вернуть данные в виде ассоциативного массива). Так что нам остается лишь возвратить этот массив данных.

Конечно же, можно было воспользоваться функцией sqlite_query(), а затем в цикле получить ассоциативный массив с помощью функции sqlite_fetch_array(результат выполненного запроса, параметр как возвращать данные), которая выполняет абсолютно те же действия, что и для базы данных mysql(mysql_fetch_array()).

Но, согласитесь, использовать функцию sqlite_array_query() намного удобнее и быстрее.

Теперь, когда у нас есть данные в виде массивов, осталось только их вывести на экран с помощью цикла foreach. Вот код который нужно вставить в файл index.php в блоки div с идентификаторами menu и text соответственно:

Для блока menu

<?php
if($menu) {
 echo "<ul>";
 foreach($menu as $item) {
  echo "<li><a href='".$item['link_menu']."'>".$item['name_menu']."</a></li>";
 }
 echo "</ul>";
}
?>

Для блока text

<?php
if($text) {
 
 foreach($text as $item2) {
 echo "<h3><a href='?id=".$item2['id_articles']."'>".$item2['name_articles']."</a></h3>";
 echo "<p>".$item2['text_articles']."</p>";
 }
 
}
?>

Итак, перейдем в браузер и посмотрим что получилось:

Введение в базу данных Sqlite

Как Вы видите данные успешно вывелись на экран.

Итак в этом видео уроке мы с вами кратко рассмотрели базу данных Sqlite. Мы научились создавать базу данных, добавлять в нее требуемые таблицы, вставлять данные а также научились выводить данные на экран. Самые основные базовые операции с движком Sqlite мы с Вами изучили, остальные же точно такие как и в базе данных Mysql (к примеру для удаления или обновления данных — нужно все также составить SQL запрос а далее просто его выполнить с помощью функции sqlite_query()).

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

На этом урок закончен. Если возникнут вопросы пишите, постараемся на них ответить! А на этом все. Удачного Вам кодирования и до новых встреч.

Фреймворк YII2. Быстрая разработка с современным PHP фреймворком

Узнай тонкости современной веб-разработки с помощью фреймворка YII2

Узнать подробнее