Verification: a143cc29221c9be0

Mysql работа с помощью php

Mysql работа с помощью php

Создание БД и таблиц

Создать БД можно из панели управления хостингом, если есть такая возможность, а если нет, то из phpMyAdmin. Сравнение ставим utf8_general_ci.

Заходим в БД и создадим таблицу users, которую будем использовать для примера. Она будет включать колонки: ID(int), login(varchar) и pass(varchar). ID будет первичным ключом, а также будет иметь галочку напротив AI(auto increment), что позволит каждой новой записи присваивать id на 1 больше, чем у прошлой записи. Кроме этого, не забываем проставить длину varchar’ам и поставить сравнение utf8_general_ci к полям login и pass.

Получаем:

База данных

Теперь мы имеем готовую БД и таблицу для работы с ней.

Подключение к БД с помощью PHP

Создаём файл index.php и прописываем:

$user = 'root'; // пользователь

$password = ''; // пароль

$db = 'mydb'; // название бд

$host = 'localhost'; // хост

$charset = 'utf8'; // кодировка

// Создаём подключение

$pdo = new PDO("mysql:host=$host;dbname=$db;cahrset=$charset", $user, $password);

?>

Мы создали подключение к БД. Подключение от PDO не нужно закрывать, оно само закрывается, когда скрипт завершает свою работу.

Выборка данных

Здесь я сделаю небольшое отступление. Выбирать данные можно:

  • Ассоциативным массивом

PDO::FETCH_ASSOC — возвращает массив с названиями столбцов в виде ключей.

  • Объектом

PDO::FETCH_OBJ — возвращает анонимный объект со свойствами, соответствующими именам столбцов.

Fetch() — каждый раз извлекает следующую строку.

Конец отступления.

Продолжаем скрипт:

// Создаём запрос

$query = $pdo -> query('SELECT * FROM users');

// Перебираем способом ассоциативного массива

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {

    echo $row['login']."
";
}

Здесь мы обращаемся к массиву $row по индексу login.

В данном случае код выведет логин всех, кто есть в таблице.

// Перебираем способом объекта

while ($row = $query->fetch(PDO::FETCH_OBJ)) {

    echo $row->login."
";
}

Здесь мы обращаемся к объекту $row по login.

В данном случае код выведет также логин всех, кто есть в таблице.

Выборка данных по параметру + защита

Мы можем выбирать определённые данные из таблицы. Например, мы можем выбрать все записи, где pass = 123, и тому подобные.

Вот первый пример выборки по логину + защита:

$login = 'Andre'; // Подставиться вместо знака вопроса

$sql = 'SELECT * FROM users WHERE login = ?'; // Формируем запрос

$query = $pdo -> prepare($sql); // Возвращает объект

$query -> execute([$login]); // В скобках указываем то, что заменит знак вопроса.


// Также перебираем массив, но теперь в массиве только те строки, где login = Andre

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {

    echo $row['id'];

}

Можно указать несколько вопросительных знаков, а в execute перечислить замены для них через запятую.

Вот второй пример выборки по логину + защита:

$login = 'Andre'; // Подставится вместо :login

// Формируем запрос с помощью ключа

$sql = 'SELECT * FROM users WHERE login = :login'; 

$query = $pdo -> prepare($sql); // Возвращает объект

// В кавычках нужно указать ключ, который мы указали в запросе.

// А после передать значение, которое должно подставится.

$query -> execute(['login' => $login]); 


// Также перебираем массив, но теперь в массиве только те строки, где login = Andre

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {

    echo $row['id'];

}

Можно указать несколько ключей, а в execute перечислить замены для них через запятую в формате ‘ключ’ => значение.

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

Вставка данных по параметру + защита

$login = 'Lorem'; // Подставиться вместо :login

$pass = 444; // Подставиться вместо :pass


// Формируем запрос с помощью ключей

$sql = 'INSERT INTO users (login, pass) VALUES (:login, :pass)';

$query = $pdo -> prepare($sql); // Возвращает объект


// В кавычках нужно указать ключи, которые мы указали в запросе.

// А после передать значение, которое должно подставится.

$query -> execute(['login' => $login, 'pass' => $pass]);

Теперь можем проверить таблицу. Видим, что всё сработало правильно.

Обновление данных по параметру + защита

$id = 7; // Подставиться вместо :id

$pass = 222; // Подставиться вместо :pass


// Формируем запрос с помощью ключей

$sql = 'UPDATE users SET pass = :pass WHERE id = :id ';

$query = $pdo -> prepare($sql); // Возвращает объект


// В кавычках нужно указать ключи, которые мы указали в запросе.

// А после передать значение, которое должно подставится.

$query -> execute(['pass' => $pass, 'id' => $id]);

Теперь можем проверить таблицу. Видим, что всё сработало правильно.

Начало работы

Создание базы данных Amazon RDS Aurora MySQL

Перед тем как создавать кластер Aurora, необходимо сначала выполнить подготовительные действия, такие как создание VPC и группы подсетей для базы данных (DB subnet group). Более подробную информацию о том, как это сделать, можно найти в документации в разделе DB cluster prerequisites. После этого, для создания базы данных выполните следующие действия.

  1. Выполните команду create-db-cluster в AWS CLI для создания кластера Aurora MySQL.
    aws rds create-db-cluster \
    --db-cluster-identifier sample-cluster \
    --engine aurora-mysql \
    --engine-version 5.7.12 \
    --master-username admin \
    --master-user-password secret99 \
    --db-subnet-group-name default-vpc-6cc1cf0a \
    --vpc-security-group-ids sg-d7cf52a3 \
    --enable-iam-database-authentication true
  2. Добавьте новый экземпляр базы данных в кластер.
    aws rds create-db-instance \
        --db-instance-class db.r5.large \
        --db-instance-identifier sample-instance \
        --engine aurora-mysql  \
        --db-cluster-identifier sample-cluster
  3. Сохраните учётные данные от созданной базы в виде секрета в AWS Secrets Manager.
    aws secretsmanager create-secret \
    --name MyTestDatabaseSecret \
    --description "My test database secret created with the CLI" \
    --secret-string '{"username":"admin","password":"secret99","engine":"mysql","host":"","port":"3306","dbClusterIdentifier":""}'

    Сохраните ARN секрета, который будет возвращён после выполнения команды. Он понадобится на следующем шаге.

    {
        "VersionId": "eb518920-4970-419f-b1c2-1c0b52062117", 
        "Name": "MySampleDatabaseSecret", 
        "ARN": "arn:aws:secretsmanager:eu-west-1:1234567890:secret:MySampleDatabaseSecret-JgEWv1"
    }

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

  4. Создайте политику IAM, которая предоставляет права на вызовы secretsmanager к указанному секрету (замените поле на ARN из предыдущего шага).
    aws iam create-policy \
    --policy-name my-rds-proxy-sample-policy \
    --policy-document '{
      "Version": "2012-10-17",
      "Statement": [
        {
          "Sid": "VisualEditor0",
          "Effect": "Allow",
          "Action": [
            "secretsmanager:GetResourcePolicy",
            "secretsmanager:GetSecretValue",
            "secretsmanager:DescribeSecret",
            "secretsmanager:ListSecretVersionIds"
          ],
          "Resource": [
            "”
          ]
        },
        {
          "Sid": "VisualEditor1",
          "Effect": "Allow",
          "Action": [
            "secretsmanager:GetRandomPassword",
            "secretsmanager:ListSecrets"
          ],
          "Resource": "*"
        }
      ]
    }'

    Сохраните ARN созданной политики. Он понадобится, чтобы связать эту политику с новой ролью, которую мы создадим.

    {
        "Policy": {
            "PolicyName": "my-rds-proxy-sample-policy", 
            "PermissionsBoundaryUsageCount": 0, 
            "CreateDate": "2020-06-04T12:21:25Z", 
            "AttachmentCount": 0, 
            "IsAttachable": true, 
            "PolicyId": "ANPA6JE2MLNK3Z4EFQ5KL", 
            "DefaultVersionId": "v1", 
            "Path": "/", 
            "Arn": "arn:aws:iam::1234567890112:policy/my-rds-proxy-sample-policy", 
            "UpdateDate": "2020-06-04T12:21:25Z"
         }
    }
  5. Создайте роль IAM, которая будет использована сервисом RDS Proxy. С помощью этой роли RDS Proxy сможет получить доступ к учётным данным базы данных.
    aws iam create-role --role-name my-rds-proxy-sample-role --assume-role-policy-document '{
     "Version": "2012-10-17",
     "Statement": [
      {
       "Sid": "",
       "Effect": "Allow",
       "Principal": {
        "Service": "rds.amazonaws.com"
       },
       "Action": "sts:AssumeRole"
      }
     ]
    }'
  6. Добавьте созданную ранее политику в роль:
    aws iam attach-role-policy \
    --role-name my-rds-proxy-sample-role \
    --policy-arn arn:aws:iam::123456789:policy/my-rds-proxy-sample-policy

Создание RDS Proxy

  1. Используйте AWS CLI, чтобы создать новый RDS Proxy. Замените значения -role-arn и SecretArn на соответствующие ARN из предыдущих шагов.
    aws rds create-db-proxy \
    --db-proxy-name sample-db-proxy \
    --engine-family MYSQL \
    --auth '{
            "AuthScheme": "SECRETS",
            "SecretArn": "arn:aws:secretsmanager:eu-west-1:123456789:secret:exampleAuroraRDSsecret1-DyCOcC",
             "IAMAuth": "REQUIRED"
          }' \
    --role-arn arn:aws:iam::123456789:role/my-rds-proxy-sample-role \
    --vpc-subnet-ids  subnet-c07efb9a subnet-2bc08b63 subnet-a9007bcf

    Для принудительной аутентификации пользователей RDS Proxy через IAM значение IAMAuth установлено в REQUIRED. Это более безопасная альтернатива встраиванию учётных данных базы данных в исходный код приложения.

    Кластер баз данных Aurora и его экземпляры называются целями (targets) созданного прокси.

  2. Добавьте кластер баз данных в прокси с помощью команды register-db-proxy-targets.
    aws rds register-db-proxy-targets \
    --db-proxy-name sample-db-proxy \
    --db-cluster-identifiers sample-cluster

Развёртывание Lambda-функции на PHP с доступом к VPC

В GitHub репозитории находится Lambda-функция со средой запуска PHP, настроенной с использованием слоя Lambda. Эта функция использует расширение MySQLi для подключения к RDS Proxy. Расширение было установлено и собрано вместе с исполняемым файлом PHP с помощью следующей команды:

Исполняемый файл PHP был упакован вместе с bootstrap-файлом для создания среды запуска PHP в Lambda. Более подробную информацию о создании своей среды запуска для PHP вы можете найти в предыдущей статье серии.

Установите стек приложения с помощью AWS Serverless Application Model (AWS SAM) CLI:

sam deploy -g

При запросе введите значения SecurityGroupIds и SubnetIds для вашего кластера Aurora.

Шаблон SAM передаёт параметры SecurityGroupIds и SubnetIds в Lambda-функцию, используя подресурс VpcConfig.

Lambda создаёт эластичный сетевой интерфейс (elastic network interface, ENI) для каждой комбинации группы безопасности (security group) и подсети в конфигурации VPC соответствующей функции. Функция может получить доступ к другим ресурсам (и интернету) только через этот VPC.

Добавление RDS Proxy к Lambda-функции

  1. Перейдите в консоль Lambda.
  2. Выберите только что созданную функцию PHPHelloFunction.
  3. Нажмите Add database proxy внизу страницы.
  4. Выберите опцию Choose an existing database proxy, затем выберите sample-db-proxy.
  5. Нажмите Add.

Использование RDS Proxy из Lambda-функции

Lambda-функция импортирует три библиотеки из AWS SDK для PHP. Они используются для генерации токена для подключения к базе данных на основе учётных данных, хранящихся в Secrets Manager.

Библиотеки AWS SDK для PHP предоставляются слоем PHP-example-vendor. Использование слоёв Lambda таким образом позволяет создать механизм по встраиванию дополнительных библиотек и зависимостей по мере развития приложения.

Обработчик функции под названием index представляет собой точку входа в код функции. Вначале вызывается getenv() для того, чтобы получить переменные окружения, установленные при развёртывании приложения через SAM. После этого они доступны в локальных переменных до окончания вызова Lambda-функции.

Класс AuthTokenGenerator используется при аутентификации IAM и генерирует токен для аутентификации в RDS. Для его инициализации необходимо передать в конструктор объект credential provider. Затем вызывается метод createToken(), в который в качестве параметров передаются адрес конечной точки прокси, порт, регион и имя пользователя базы данных. Полученный таким образом токен впоследствии используется для подключения к прокси.

Класс mysqli в PHP используется для подключения к базе данных MySQL. Метод real_connect() используется для открытия подключения к базе данных через RDS Proxy. Вместо адреса базы данных первым параметром необходимо передать адрес конечной точки прокси. Также передаются имя пользователя базы данных, временный токен, название базы данных и порт. Кроме того, указана константа MYSQLI_CLIENT_SSL, чтобы убедиться, что подключение будет использовать шифрование SSL.

После успешного подключения вы можете использовать соответствующий объект для работы с базой данных. В примере ниже выполняется запрос SHOW TABLES. После этого подключение закрывается, а результат конвертируется в JSON и возвращается из Lambda-функции.

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

Мониторинг RDS Proxy и настройка производительности

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

Установите максимальное время ожидания в соответствии с требованиями вашего приложения через настройку Connection borrow timeout. Она указывает на то, как долго сервис будет ждать доступного подключения из пула, перед тем как вернёт ошибку.

Измените интервал Idle client connection timeout, чтобы помочь вашим приложениям обрабатывать устаревшие ресурсы. С помощью него можно уберечь ваши приложения от ошибочного оставления открытых подключений к базе данных, которые тратят важные системные ресурсы.

Несколько приложений, использующих одну базу данных, могут каждый использовать RDS Proxy для разделения общей квоты подключений между ними. Вы можете установить максимальное количество подключений для каждого прокси в виде процента от значения max_connections (для MySQL).

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

aws rds modify-db-proxy-target-group \
--db-proxy-name sample-db-proxy \
--target-group-name default \
--connection-pool-config '{"MaxConnectionsPercent": 75 }'

Ответ:

{
    "TargetGroups": [
        {
            "DBProxyName": "sample-db-proxy",
            "TargetGroupName": "default",
            "TargetGroupArn": "arn:aws:rds:eu-west-1:####:target-group:prx-tg-03d7fe854604e0ed1",
            "IsDefault": true,
            "Status": "available",
            "ConnectionPoolConfig": {
            "MaxConnectionsPercent": 75,
            "MaxIdleConnectionsPercent": 50,
            "ConnectionBorrowTimeout": 120,
            "SessionPinningFilters": []
        	},            
"CreatedDate": "2020-06-04T16:14:35.858000+00:00",
            "UpdatedDate": "2020-06-09T09:08:50.889000+00:00"
        }
    ]
}

Если RDS Proxy обнаружит, что сессия находится в таком состоянии, что её нельзя повторно использовать, он будет использовать для неё одно и то же подключение до завершения этой сессии. Такое поведение называется закреплением (pinning). При настройке производительности RDS Proxy необходимо максимизировать повторное использование подключений путём минимизации количества закреплений.

Вы можете следить за метрикой Amazon CloudWatch под названием DatabaseConnectionsCurrentlySessionPinned, чтобы понять как часто в вашем приложении происходит закрепление.

Amazon CloudWatch собирает и обрабатывает сырые данные из RDS Proxy и строит на их основе удобочитаемые метрики практически в реальном времени. Используйте указанные в документации метрики, чтобы следить за количеством подключений и расходуемой на управление подключениями памятью. Благодаря им, вы можете понять, будет ли польза вашему экземпляру или кластеру базы данных от использования RDS Proxy. Например, в случае если он обрабатывает много коротких подключений или подключений, которые открываются и закрываются с высокой скоростью.

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

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

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

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

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

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

  • Адрес сервера СУБД;
  • Логин;
  • Пароль.

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

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

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 и добавлением двух новых записей:

Соединение с MySQL:

Для начала нам надо подключить класс mysqli, при его объявление он подключает БД.

// Здесь мы объявляем сам класс mysqli;

$mysqli = new mysqli($nameServer, $userName, $password, $DBname);

// тут проверяем, удалось соединится с сервером,

// если соединения нет, то останавливаем программу и выводим сообщение;

if ($mysqli -> connect_error) {

  printf("Соединение не удалось: %s\n", $mysqli -> connect_error);

  exit();

};

Давайте разберём каждый параметр тут.

  • $nameServer — Адрес или имя сервера на котором хранится БД.
  • $userName — Имя пользователя, который может управлять БД.
  • $password — Пароль пользователя.
  • $DBname — Название базы данных, которой нужно подключится.

Остальное и так должно быть понятно, за счёт комментариев.

Отправка запросов:

Для того что бы отправить запрос надо прописать:

// Тут создаём SQL запрос;

$sql = "SELECT * FROM `users`";

// Отправляем запрос;

$res = $mysqli -> query($sql);

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

Что бы проверить это нам надо использовать функцию print_r($res), вот результат программы.

Результат вывода массива с данными о дб

Как можете заметить, не каких данных из таблицы нет, так как, метод query($sql), скорей возвращает данные о таблице или о данных которые мы получили.

Если быть совсем точным, то возвращает уникальную сущность, которую очень сложно описать.

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

Вывод таблицы на экран:

Для вывода на экран сделаю функцию, с достаточно простым алгоритмам, назову её out_bd_tablе().

function out_bd_table($res) {

  // Условие делаем для проверки, есть ли вообще что то в этой таблице;

  if ($res -> num_rows > 0) {

      // Цикл будет работать пока не пройдёт все строки;

      // При каждой новой итерации цикла,

      // Он переходит на новое значение;

      while ($row = $res -> fetch_assoc()) {

        // Вывод на экран;

        echo "Имя: {$row["name"]};
Фамилия: {$row["
surname"]};

              


";

      }

    // Если таблица пустая, будет выведено "Данных нет";

    } else {

      echo "Данных нет";

    }

}

После этого вызываем эту функцию и вот что у нас получилось.

Вывод данных из БД