Php database query

Работа с базами данных SQL в PHP для новичков

Учебник PHP

Практика

Важное

Регулярки

Работа с htaccess

Файлы, папки

Сессии и куки

Работа с БД

Практика по работе с БД в PHP

Перед чтением см. новые уроки раздела «Важное», которые появились выше.

Практика

Движок PHP

Продвинутые БД

Аутентификация

Практика

ООП и MVC

Абстрактные классы и интерфейсы

Трейты

ООП Магия

Практика

Практика: классы как набор методов

• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс ArrayConvertor
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс TagHelper
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс FormHelper
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс TableHelper
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс SessionShell
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс CookieShell
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс FileManipulator
• Урок №
  новая вкладка с new.code.mu
  . текст, код Практика: класс databaseShell TODO cart корзина flash шаблонизатор роутер контроллер кеш логи фалидатор

Что такое база данных

База данных (сокращенно БД) — это место, в котором хранятся данные сайта.

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

База данных состоит из таблиц. Что такое таблица вы знаете из жизни: это строки и столбцы. А на пересечении строк и столбцов располагаются ячейки.

В базах данных столбцы часто называют полями.

Это легко можно вообразить себе, представив документ Excel. Базой данных будет являться сам документ (книга), а таблицами — каждый лист этой книги.

PhpMyAdmin

Для редактирования баз данных часто пользуются программой PhpMyAdmin.

PhpMyAdmin (читается PHPмайадмин, часто пишут аббревиатуру PMA или ПМА) — это оболочка для работы с базами данных прямо у вас в браузере.

Вы можете править содержимое таблиц, создавать новые базы данных и таблицы — и все это через веб-интерфейс, не зная SQL.

Видео на PhpMyAdmin

Задачи на PhpMyAdmin

Давайте откроем PhpMyAdmin и приступим к изучению его интерфейса.

Чтобы вам было проще с ним освоится, сделайте следующие практические задачи:

1. Создайте базу данных test.
2. В ней создайте таблицу users.
3. В этой таблице сделайте 4 поля (столбца):
   • id (для него нужно поставить галочку AUTO_INCREMENT или A.I.), тип integer,
   • name, тип varchar, 32 символа,
   • age, тип integer,
   • birthday (англ. день рождения), тип date.
4. Найдите вкладку ‘вставить’ и с ее помощью вставьте несколько строк в эту таблицу При этом поле id заполнять не нужно! Оно заполнится автоматически!
5. Поредактируйте какую-нибудь запись.
6. Удалите какую-нибудь запись.
7. Поменяйте кодировку для таблицы и для всей базы данных (на utf8_general_ci).
8. Переименуйте таблицу.
9. Переименуйте базу данных.

AUTO_INCREMENT

Обратите внимание на то, что мы создали поле id и поставили ему галочку AUTO_INCREMENT. Это очень важный шаг!

Теперь при вставке новой записи (строки) в таблицу это поле будет заполняться автоматически уникальным номером.

При этом если мы удалим строку с каким-то id (например 1), то такого id больше никогда не будет.

Зачем нужно поле id?

Затем, чтобы мы всегда могли обратиться к конкретной строке по ее id, например с целью удаления или редактирования.

Типы переменных

В SQL довольно много типов переменных, но чаще всего приходится пользоваться следующими:

• integer – целочисленный.
• text – большое текстовое поле.
• varchar – не очень большое текстовое поле, при этом мы должны задать его размер (он должен быть степенью двойки: 8, 16, 32, 64, 128, 256 и т.д.).
• date — поле для хранения даты (дата хранится в SQL-формате: год-месяц-день, пример: 2013-06-24).

Как работать с mySQL через PHP

Работа с БД из PHP осуществляется всего лишь с помощью трех функций:

• mysqli_connect – соединение с сервером и базой данных.
• mysqli_query — универсальная функция отправки запросов к БД, с помощью нее можно сделать все.
• mysqli_error — вывод ошибок.

Далее подробнее про каждую из функций.

Устанавливаем соединение с БД

Данный материал есть также в виде видео: https://youtu.be/J8GFuyA_k_8

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

Делается это с помощью функции PHP mysql_connect, которая принимает 3 параметра: имя хоста (сервера), имя пользователя, под которым мы работаем с базой и пароль для этого пользователя.

Если вы работаете на своем компьютере то это будут ‘localhost’, ‘root’ и пароль в виде пустой строки (на некоторых серверах он тоже может быть root). Если ваша база данных в интернете — то эти данные дает вам хостер.

Давайте установим соединение с базой данных:

Посылаем запросы к базе данных

Запросы к базе данных представляют собой обычные строки, которые мы вставляем в функцию PHP mysqli_query (первым параметром это функция принимает переменную, в которую мы записали результат mysqli_connect, в нашем случае это переменная $link):

Принято правило, по которому команды SQL следует писать в верхнем регистре (то есть большими буквами), а все остальное — в нижнем.

Это относиться к командам SELECT, UPDATE, FROM, DELETE, WHERE и другим такого рода.

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

Отлавливаем ошибки базы данных

Многие начинающие зачастую не умеют отлавливать ошибки, которые вернула база данных.

Поэтому при работе с БД у них постоянно возникают сложности. Что-то не работает, а что — не понятно, так как ошибок они не видят, так как PHP не выводит ошибки mySQL, если ему об этом не сказать

Чтобы вывести ошибки, следует пользоваться конструкцией or die ( mysqli_error($link) ), которую необходимо добавлять к каждому запросу к БД.

Смотрите пример: mysqli_query($link, $query) or die( mysqli_error($link) );

Таким образом вы сразу будете получать сообщения об ошибках синтаксиса SQL. Обратите внимание на то, что на рабочем сайте эти конструкции следует удалять, чтобы пользователи и тем более хакеры не видели ошибок БД.

Проблемы с кодировками

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

Чтобы не было проблем с кодировками следует придерживаться простых правил:

• Базу данных следует создавать в кодировке utf8_general_ci.
• Документ PHP должен быть в кодировке utf8.
• Таблицы в БД должны быть в utf8_general_ci.
• На всякий случай сразу после команды mysqli_connect добавьте такое запрос: mysqli_query($link, «SET NAMES ‘utf8′»);

Начнем практиковаться

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

Итак, создайте свою первую базу данных с помощью PhpMyAdmin.

Назовите ее «test».

Создайте в этой базе новую таблицу.

Назовите ее «workers» (англ. работники).

В ней создайте 4 столбца (столбцы по другому называются поля):

• id – тип integer, не забудьте поставить ему галочку AUTO_INCREMENT (чтобы в этом столбце номера проставлялись автоматически).
• name (англ. имя) – тип varchar, размером в 256 знаков.
• age (англ. возраст) — тип integer.
• salary (англ. зарплата) — тип integer.

Ее заполните тестовыми данными, как показано в таблице ниже (этот шаг обязателен, так как дальше все задачи будут по этой таблице):

Итак, у нас есть таблица с работниками фирмы, в которой указаны их имена, возрасты и зарплаты (в $). Далее мы будем работать с этой таблицей.

Тестируем работоспособность

Прежде чем начать работу, следует протестировать работоспособность — попробуем сделать какой-нибудь запрос к нашей базе.

Просто скопируйте этот код и запустите его у себя:

Если var_dump($result) вернет resource, то все работает, если же null – то возникли какие-то проблемы. в новых версиях PHP в $result будет лежать объект с данными (всегда будет непустой). Если обработать его через mysqli_fetch_assoc будет или результат или null (про mysqli_fetch_assoc см ниже или видео https://youtu.be/J8GFuyA_k_8 , которое уже было выше в пункте «Устанавливаем соединение»)

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

Как достать результат

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

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

Достать результат в нормальном виде (в массиве) можно с помощью следующего кода:

Как работает последняя строка?

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

В цикле for мы считываем построчно результат из базы.

Когда цикл дойдет до последней строки — mysqli_fetch_assoc вернет false и цикл for закончит свою работу.

А результат из БД будет лежать в нормальном виде в массиве $data.

How do I construct a cross database query in PHP?

In our last episode (How I constructed a cross database query in MySQL) I learned how to construct a cross database query in MySQL. This worked great but when our hero tried to use this newfound knowledge in PHP he found his best friend FAIL waiting for him.

I took a look at mysql_select_db for PHP. This seems to imply that if I want to use MySQL with PHP, I have a couple of choices:

Use mysql_select_db but be stuck with only using one db at a time. This is our current setup and putting a database as a namespace identifier doesn’t seem to work (it works fine in the MySQL shell so I know it’s not a problem with our MySQL server setup).

Don’t use mysql_select_db . From some of the examples I’ve seen, this seems to mean that I have to specify the db for every query that I make. This makes sense since I haven’t used mysql_select_db to tell PHP what db I want to access. This also makes sad since I don’t want to go through all my code and prepend a db name to every query.

Is there something better than this? Is there a way for me to do a cross db MySQL query in PHP without having to something crazy like (2)?

CLARIFICATION: None of the proposed answers actually let me do a cross db query. Instead, they allow me to access two different DBs separately. I want a solution that allows me to do something like SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where . NOT just make different queries to different DBs. For what it’s worth, (2) doesn’t work for me.

9 Answers 9

You will need your databases to run on the same host.

If so, you should be able to use mysql_select_db on your favourite/default db and manually specify a foreign database.

If your databases run on a different host, you won’t be able to join directly. But you can then make 2 queries.

After reading your clarification, I am under the impression that you actually want to query tables residing in two separate MySQL server instances. At least, your clarification text:

SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where

suggests that you want to run one query while being logged in as two users (which may or may not reside on the same mysql server instance).

In your question, you said you wanted to query data from two different databases, but it is important to realize that one MySQL instance can have many, many databases. For multiple databases managed by the same mysql instance, the solution proposed in the question you linked to simply works: just prefix the table name with the name of the databases, separating database and table names with a dot: ..

But, like i pointed out, this only works if:

1. all databases you access in one query reside on the same server — that is, are managed by the same MySQL instance
2. the user that is connected to the database has the right privileges to access both tables.

Scenario1: databases on same host: grant appopriate privileges and qualify table names

So if the tables actually reside on the same mysql instance, there is no need for a second login or connection — simply grant the database user you use to connect to the datbase the appropriate privileges to select from all tables you need. You can do that with the GRANT syntax, documented here: http://dev.mysql.com/doc/refman/5.1/en/grant.html

For example, GRANT SELECT ON sakila.film TO ‘test’@’%’ will allow the user test@% to select data from the film table in the sakila database. After doing that, said user can refer to this table using sakila.film (so-called qualified table name), or if the current database is set to sakila , simply as film

Scenario2: databases managed by different MySQL instances: FEDERATED engine

If the tables you want to access are actually managed by two different MySQL instances, there is one trick that may or may not work, depending on your configuration. Since MySQL 5.0 mysql supports the FEDERATED storage engine. This lets you create a table that is not actually a table, but a peephole to a table on a remote server. This engine is documented here: http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

For example, if you know there is this table in the misc database on the remote host:

you can make a local ‘pointer’ to that remote table using this:

Unfortunately, the FEDERATED engine is not always available, so you have to check first if you can even use that. But suppose it is, then you can simply use the local table t in your queries, just like any other table, and MySQL will communicate with the remote server and perform the appropriate operations on the physical table on the other side.

Caveat: there are several optimization issues with FEDERATED tables. You should find out if and to what extent these apply to you. For instance, applying a WHERE to a federated table can in many cases result in the entire table contents being pullled over the wire to your local server, where the actual filtering will be appplied. Another issue is with table creation: you have to be very sure that the definitions of the federated table and the table it is pointing to match exacty, except for the ENGINE clause (and CONNECTION). If you have for example a different character set, the data may arrive completely garbled after travelling over the wire.

If you want to use FEDERATED tables, do read this article http://oreilly.com/pub/a/databases/2006/08/10/mysql-federated-tables.html to decide if its right for your particular use case.

If you think you do need it, I have a utility to create federated tables here: http://forge.mysql.com/tools/tool.php? >

Scenario3: can’t use FEDERATED, but tables on different MySQL instances

Finally, if you have tables on different MySQL instances, but cannot for some reason use the federated table engine, your a out of luck I’m afraid. You are simply going to have to execute queries to both MySQL instances, receive the results and do something intelligent with it in PHP. depending on your exact requirements, this may be a perfectly viable solution

I guess you need to decide for yourself which part of my answer best appeals to your problem, and add a comment in case you need more help. TIA Roland.

PHP MySQL Select Data

Select Data From a MySQL Database

The SELECT statement is used to select data from one or more tables:

or we can use the * character to select ALL columns from a table:

To learn more about SQL, please visit our SQL tutorial.

Select Data With MySQLi

The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:

Example (MySQLi Object-oriented)

connect_error) <
die(«Connection failed: » . $conn->connect_error);
>

$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);

if ($result->num_rows > 0) <
// output data of each row
while($row = $result->fetch_assoc()) <
echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else <
echo «0 results»;
>
$conn->close();
?>

Code lines to explain from the example above:

First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the function num_rows() checks if there are more than zero rows returned.

If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.

The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)

0) <
// output data of each row
while($row = mysqli_fetch_assoc($result)) <
echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else <
echo «0 results»;
>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)

connect_error) <
die(«Connection failed: » . $conn->connect_error);
>

$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);

if ($result->num_rows > 0) <
echo »

«;
// output data of each row
while($row = $result->fetch_assoc()) <
echo »

«;
>
echo «

«;
> else <
echo «0 results»;
>
$conn->close();
?>

Select Data With PDO (+ Prepared Statements)

The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:

Example (PDO)

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare(«SELECT id, firstname, lastname FROM MyGuests»);
$stmt->execute();

Работа с MySQL в PHP

PHP поддерживает работу с базой данных MySQL.
Специальные встроенные функции для работы с MySQL позволяют просто и эффективно работать с этой СУБД: выполнять любые запросы, читать и записывать данные, обрабатывать ошибки.
Сценарий, который подключается к БД, выполняет запрос и показывает результат, будет состоять всего из нескольких строк. Для работы с MySQL не надо ничего дополнительно устанавливать и настраивать; всё необходимое уже доступно вместе со стандартной поставкой PHP.

Что такое mysqli?

mysqli (MySQL Improved) — это расширение PHP, которое добавляет в язык полную поддержку баз данных MySQL. Это расширение поддерживает множество возможностей современных версий MySQL.

Как выглядит работа с базой данных

Типичный процесс работы с СУБД в PHP-сценарии состоит из нескольких шагов:

1. Установить подключение к серверу СУБД, передав необходимые параметры: адрес, логин, пароль.
2. Убедиться, что подключение прошло успешно: сервер СУБД доступен, логин и пароль верные и так далее.
3. Сформировать правильный SQL запрос (например, на чтение данных из таблицы).
4. Убедиться, что запрос был выполнен успешно.
5. Получить результат от СУБД в виде массива из записей.
6. Использовать полученные записи в своём сценарии (например, показать их в виде таблицы).

Функция mysqli connect: соединение с MySQL

Перед началом работы с данными внутри MySQL, нужно открыть соединение с сервером СУБД.
В PHP это делается с помощью стандартной функции mysqli_connect() . Функция возвращает результат — ресурс соединения. Данный ресурс используется для всех следующих операций с MySQL.

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

Если вы следовали стандартной процедуре установки MySQL или используете OpenServer, то адресом сервера будет localhost , логином — root . При использовании OpenServer пароль для подключения — это пустая строка ‘’, а при самостоятельной установке MySQL пароль вы задавали в одном из шагов мастера установки.

Базовый синтаксис функции mysqli_connect() :

Проверка соединения

Первое, что нужно сделать после соединения с СУБД — это выполнить проверку, что оно было успешным.
Эта проверка нужна, чтобы исключить ошибку при подключении к БД. Неверные параметры подключения, неправильная настройка или высокая нагрузка заставит MySQL отвеграть новые подключения. Все эти ситуации приведут к невозможности соединения, поэтому программист должен проверить успешность подключения к серверу, прежде чем выполнять следующие действия.

Соединение с MySQL устанавливается один раз в сценарии, а затем используется при всех запросах к БД.
Результатом выполнения функции mysqli_connect() будет значение специального типа — ресурс.
Если подключение к MySQL не удалось, то функция mysqli_connect() вместо ресурса вернет логическое значение типа «ложь» — false .
Хорошей практикой будет всегда проверять значение результа выполнения этой функции и сравнивать его с ложью.

Соединение с MySQL и проверка на ошибки:

Функция mysqli_connect_error() просто возвращает текстовое описание последней ошибки MySQL.

Установка кодировки

Первым делом после установки соединения крайне желательно явно задать кодировку, которая будет использоваться при обмене данными с MySQL. Если этого не сделать, то вместо записей со значениями, написанными кириллицей, можно получить последовательность из знаков вопроса: ‘. ’.
Вызови эту функцию сразу после успешной установки соединения: mysqli_set_charset($con, «utf8»);

Выполнение запросов

Установив соединение и определив кодировку мы готовы выполнить свои первые SQL-запросы. Вы уже умеете составлять корректные SQL команды и выполнять их через консольный или визуальный интерфейс MySQL-клиента.
Те же самые запросы можно отправлять без изменений и из PHP-сценария. Помогут в этом несколько встроенных функций языка.

Два вида запросов

Следует разделять все SQL-запросы на две группы:

1. Чтение информации (SELECT).
2. Модификация (UPDATE, INSERT, DELETE).

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

Добавление записи

Вернёмся к нашему проекту — дневнику наблюдений за погодой. Начнём практическую работу с заполнения таблиц данными. Для начала добавим хотя бы один город в таблицу cities.

Выражение INSERT INTO используется для добавления новых записей в таблицу базы данных.

Составим корректный SQL-запрос на вставку записи с именем города, а затем выполним его путём передачи этого запроса в функцию mysqli_query() , чтобы добавить новые данные в таблицу.

Обратите внимание, что первым параметром для функциии mysqli_query() передаётся ресурс подключения, полученный от функции mysqli_connect() , вторым параметром следует строка с SQL-запросом.
При запросах на изменение данных (не SELECT) результатом выполнения будет логическое значение — true или false.
false будет означать, что запрос выполнить не удалось. Для получения строки с описанием ошибки существует функция mysqli_error($link) .

Функция insert id: как получить идентификатор добавленной записи

Следующим шагом будет добавление погодной записи для нового города.
Погодные записи хранит таблица weather_log, но, чтобы сослаться на город, необходимо знать идентификатор записи из таблицы cities.
Здесь пригодится функция mysqli_insert_id() .
Она принимает единственный аргумент — ресурс соединения, а возвращает идентификатор последней добавленной записи.

Теперь у нас есть всё необходимое, чтобы добавить погодную запись.
Вот как будет выглядеть комплексный пример с подключением к MySQL и добавлением двух новых записей:

Чтение записей

Другая частая операция при работе с базами данных в PHP — это получение записей из таблиц (запросы типа SELECT).
Составим SQL-запрос, который будет использовать SELECT выражение. Затем выполним этот запрос с помощью функции mysqli_query() , чтобы получить данные из таблицы.

В этом примере показано, как вывести все существующие города из таблицы cities:

В примере выше результат выполнения функции mysqli_query() сохранён в переменной $result .
Важно понимать, что в этой переменной находятся не данные из таблицы, а специальный тип данных — так называемая ссылка на результаты запроса.

Чтобы получить действительные данные, то есть записи из таблицы, следует использовать другую функцию — mysqli_fetch_array() — и передать ей единственным параметром эту самую ссылку.
Теперь каждый вызов функции mysqli_fetch_array() будет возвращать следующую запись из всего результирующего набора записей в виде ассоциативного массива.

Цикл while здесь используется для «прохода» по всем записям из полученного набора записей.
Значение поля каждой записи можно узнать просто обратившись по ключу этого ассоциативного массива.

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

Иногда бывает удобно после запроса на чтение не вызывать в цикле mysqli_fetch_array для извлечения очередной записи по порядку, а получить их сразу все одним вызовом. PHP так тоже умеет. Функция mysqli_fetch_all($res, MYSQLI_ASSOC) вернёт двумерный массив со всеми записями из результата последнего запроса.
Перепишем пример с показом существующих городов с её использованием:

Как узнать количество записей

Часто бывает необходимо узнать, сколько всего записей вернёт выполненный SQL запрос.
Это может помочь при организации постраничной навигации, или просто в качестве информации.
Узнать число записей поможет функция mysqli_num_rows() , которой следует передать ссылку на результат запроса.