Oc-windows.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Sql php id

Вопросы и ответы по MySQL и MySql+PHP

Как узнать следующее значение AUTO_INCREMENT из таблицы в MySQL?

Обратите внимание, в интернете часто встречается ошибочный способ определения номера(id) записи, которая будет добавлена:

Это неверно! Т.к. если последняя запись была удалена, то id значение добавленной записи не будет соответствовать MAX+1!

Кроме того, при большом количестве одновременных запросоов в базу от разных клиентов id может стать больше, чем только что полученный функцией SHOW TABLE STATUS LIKE ‘tbl’ т.к. мог проскочить INSERT. Например id: 1,2,3,4 Удалил 3. Следующее добавляемое 5, а не 3.

Как установить начальное значение для поля с auto increment в MySQL?

Для сброса автоинкреметного поля в начальное значение уже существующей таблицы с данными выполните команду:

После этого, при добавлении, следующая запись будет имень номер самого большого id + 1

Если нужно при создании таблицы:

Как использовать свободные id записей?

После удаления записи в таблице базы данных, автоинкрементное id освобождается и уже никогда не будет использовано. Это сделано специально для обеспечения уникальности операций. Например, при решении вопроса синхронизации в распределённых базах данных.

Если Вы используете id для нумерации, когторую показываете пользоветлю, то «дырки» в нумерации — не очень красиво.

Есть два варианта:

После каждого удаления сбрасывать AUTO_INCREMENT в 1 с помощью команды:

После добавления записи для получения её id вместо стандартной функции mysql_insert_id() использовать следующую:

Она ищет «дырку» в нумерации (свободные id номера) и пытается занять первый свободный, если успешно, то последняя добавленная запись будет иметь id «дырки» и возвращен номер. На случай частого использования данные кешируются в переменную памяти.

Вывод из связанных баз

Вывести все поля из KATEG и поле name из GR. Осторожно с одинаковыми именами полей. Они берутся из второй базы.

Проверка наличия записи по условию

Если есть хотя бы одна запись удовлетворяющая условию:

Удаление в связанных базах

Иногда хочется написать запрос типа такого:

Но он работать не будет, вот эквивалентный работающий код:

Количество записей

Подсчитать количество записей, удовлетворяющих условию

Получение ключа только что добавленной записи

Для получение ключа только что добавленной записи используется функция

Перенумеровать поле id UNIQUE AUTO_INCREMENT в mysql

В 99 процентах случаях это делать не нужно. Уникальный ключ для того и создан, чтобы при удалении записи оставалась «дырка» в нумерации. Но, иногда приходится. Например, я создавал сайт и набирал справочник для клиента на локальной машине, естественно, было много правок. Хотелось при «выкладывании вживую» чтобы все выглядело красиво. Вот для такого случая и сделан следующий скриптик:

Преобразование IP адреса для записи в базу MySql

Как получить список id записей в строку по условию из MySql

Чтобы выбрать список записей в виде строки id кодов воспользуется функцией MySql GROUP_CONCAT

Найти в связанных таблицах потерянные записи
(записи не имеющие значение в справочнике)

Если нужно найти и удалить потерянные записи:

Соответствующее предложение DELETE будет таким:

Запрос, определяющий потомков без родителей, выглядит так:

Соответствующее предложение DELETE удаляет их:

Сколько сообщений было отправлено в течение каждого часа дня?

Преобразование TIMESTAMP в DATETIME и обратно

MyISAM или InnoDB ?

Mysql как игнорировать ошибки при загрузке дампа?

Используйте ключ «-f» — он позволяет продолжать даже если в процессе произошла ошибка. Например:

Долгий MySQL запрос и на что его заменить?

mysql изменить кодировку базы и всех полей

Запрос для конвертации базы:

Если у вас MySQL сервер версии 5+ вы счастливчик! который сможет выполнить вот такой запрос:

Замените! db_name на имя вашей Базы Данных.

После выполнения запроса получаем ответ SQL в стиле:

Копируем его, вставляем в форму запроса SQL и выполняем, результат — полностью изменённая кодировка всех таблиц на utf8 — наслаждаемся сэкономленным временем!

А если нужно utf8_general_ci таблицы конвертировать в utf8mb4_general_ci для того, чтобы смайлики Emoji сохранялись:

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

Работа с базами данных 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.

Читать еще:  Ошибка модема 651 как исправить

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.

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

idnameagesalary
1Дима23400
2Петя25500
3Вася23500
4Коля301000
5Иван27500
6Кирилл281000
Читать еще:  Написать правильно без ошибок

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

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

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

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

Если 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.

Защита от SQL инъекций

Внедрение SQL-кода (SQL инъекция) — один из распространённых способов взлома сайтов, работающих с базами данных. Способ основан на внедрении в запрос произвольного SQL-кода.
Внедрение SQL позволяет хакеру выполнить произвольный запрос к базе данных (прочитать содержимое любых таблиц, удалить, изменить или добавить данные).

Атака этого типа возможна, когда недостаточно фильтруются входные данные при использовании в SQL-запросах.

Принцип атаки внедрения SQL

Допустим, на нашем сайте есть страница показа истории погодных наблюдений для одного города. Идентификатор этого города передаётся в ссылке в параметре запроса: /weather.php?city_ > , где ID — это первичный ключ города.
В PHP-сценарии используем этот параметр для подстановки в SQL запрос:

Если на сервере передан параметр city_id, равный 10 ( /weather.php?city_ >), то выполнится SQL-запрос:

Но если злоумышленник передаст в качестве параметра id строку -1 OR 1=1 , то выполнится запрос:

Добавление во входные параметры конструкций языка SQL (вместо простых значений) изменяет логику выполнения всего SQL запроса!
В этом примере вместо показа данных по одному городу, будут получены данные по всем городам, потому что выражение 1=1 всегда истинно. Вместо выражения SELECT . могло быть выражение на обновление данных, и тогда последствия были бы ещё серьезнее.

Отсутствие должной обработки параметров SQL-запроса — это одна из самых серьёзных уязвимостей. Никогда не вставляйте данные от пользователя в SQL запросы «как есть»!

Приведение к целочисленному типу

В SQL-запросы часто подставляются целочисленные значения, полученные от пользователя. В примерах выше использовался идентификатор города, полученный из параметров запроса. Этот идентификатор можно принудительно привести к числу. Так мы исключим появление в нём опасных выражений. Если хакер передаст в этом параметре вместо числа SQL код, то результатом приведения будет ноль, и логика всего SQL-запроса не изменится.

PHP умеет присваивать переменной новый тип. Этот код принудительно назначит переменной целочисленный тип:

После преобразования переменную $city_id можно без опаски использовать в SQL-запросах.

Экранирование значений

Что делать, если в SQL запрос требуется подставить строковое значение? Например, на сайте есть возможность поиска города по его названию. Форма поиска передаст поисковый запрос в GET-параметр, а мы используем его в SQL-запросе:

Но если в параметре city_name будет символ кавычки, то смысл запроса можно кардинально изменить. Передав в search_text значение ‘)+and+(id<>‘0 , мы выполним запрос, что выведет список всех городов:

Смысл запроса поменялся, потому что кавычка из параметра запроса считается управляющим символом: MySQL определяет окончание значение по символу кавычки после него, поэтому сами значения кавычки содержать не должны.
Очевидно, приведение к числовому типу не подходит для строковых значений. Поэтому, чтобы обезопасить строковое значение, используют операцию экранирования.

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

За экранирование значений отвечает функция mysqli_real_escape_string() .
Этот код обработает значение из параметра, сделав его безопасным для использования в запросе:

Подготовленные выражения

Вид атак типа «SQL-инъекция» возможен, потому что значения (данные) для SQL-запроса передаются вместе с самим запросом. Так как данные не отделены от SQL-кода, они могут влиять на логику всего выражения. К счастью, MySQL предлагает способ передачи данных отдельно от кода. Такой способ называется подготовленными запросами.

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

Этап подготовки
На этапе подготовки формируется SQL-запрос, где на месте значений будут находиться знаки вопроса — плейсхолдеры. Эти плейсхолдеры в дальнейшем будут заменены на реальные значения. Шаблон запроса отправляется на сервер MySQL для анализа и синтаксической проверки.
Пример:

Этот код сформирует подготовленное выражение для выполнения вашего запроса.

За подготовкой идёт выполнение. Во время запуска запроса PHP привязывает к плейсхолдерам реальные значения и посылает их на сервер. За передачу значений в подготовленный запрос отвечает функция mysqli_stmt_bind_param() . Она принимает тип и сами переменные:

Читать еще:  Что означает ошибка 0xc000007b

После выполнения запроса получить его результат в формате mysqli_result можно функцией mysqli_stmt_get_result() :

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

mysql_insert_ >(PHP 4, PHP 5)

mysql_insert_id — Возвращает идентификатор, сгенерированный при последнем INSERT-запросе

Данное расширение устарело, начиная с версии PHP 5.5.0, и удалено в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:

Описание

Возвращает идентификатор, сгенерированный колонкой с AUTO_INCREMENT последним запросом (обычно INSERT).

Список параметров

Соединение MySQL. Если идентификатор соединения не был указан, используется последнее соединение, открытое mysql_connect() . Если такое соединение не было найдено, функция попытается создать таковое, как если бы mysql_connect() была вызвана без параметров. Если соединение не было найдено и не смогло быть создано, генерируется ошибка уровня E_WARNING .

Возвращаемые значения

Идентификатор, сгенерированный колонкой с AUTO_INCREMENT последним запросом в случае успеха , , если последний запрос не генерирует значение AUTO_INCREMENT value, и FALSE , если соединение MySQL не было установлено.

Примеры

Пример #1 Пример использования mysql_insert_id()

= mysql_connect ( ‘localhost’ , ‘mysql_user’ , ‘mysql_password’ );
if (! $link ) <
die( ‘Ошибка соединения: ‘ . mysql_error ());
>
mysql_select_db ( ‘mydb’ );

mysql_query ( «INSERT INTO mytable (product) values (‘kossu’)» );
printf ( «Идентификатор последней вставленной записи %dn» , mysql_insert_id ());
?>

Примечания

mysql_insert_id() конвертирует возвращаемый функцией MySQL C API тип значения функции mysql_insert_ >int в PHP). Если ваша колонка AUTO_INCREMENT имеет тип BIGINT (64 бита), то значение, возвращаемое функцией в результате преобразования может быть искажено. Используйте вместо данной функции внутреннюю MySQL-функцию LAST_INSERT_ID() в SQL-запросе. Подробнее о максимальных значениях целых чисел смотрите в разделе документации, посвященном целым числам.

Так как mysql_insert_id() работает с последним выполненным запросом, вызывайте mysql_insert_id() сразу же после запроса, генерирующего новое значение.

Значение в SQL функции MySQL LAST_INSERT_ID() всегда содержит последний сгенерированный ID и не обнуляется между запросами.

Смотрите также

  • mysql_query() — Посылает запрос MySQL
  • mysql_info() — Возвращает информацию о последнем запросе

User Contributed Notes 12 notes

I thought this would be relevant to all the people using mysqli and looking for the ID after INSERT command :

function insert_join ( $catid , $disc_id ) <
// insert a new item into the database
$conn = db_connect ();
// insert new item
$demande = «insert into categories_disc values (», ‘» . $catid . «‘, ‘» . $disc_id . «‘)» ;
$resultat = $conn -> query ( $demande );
if (! $resultat ) <
return false ;
> else <
return $conn -> insert_id ; // function will now return the ID instead of true.
>

>
?>

Then, on the other side, let us call this function as follows :

= insert_join ( $catid , $disc_id );
if( $cat_id !== false ) <

Category stuff was added to the database as follows :
» ;
echo » ID de la category : » . $cat_id . «

There’s nothing inherently wrong with using auto-increment fields. There’s also nothing wrong with the main competetive idea, which is for the database to supply a primitive sequence of non-repeating identifiers, typically integers. This is rather like which side of the road you drive on.

The bigger problem is when people don’t understand what they are doing with database access. It’s like driving a car without really knowing the rules of the road. Such people wind up making bad decisions without realizing it, and then, eventually, something breaks.

Databases are complex beasts, and worth taking the time to really understand. Learn about the implications and limitations of different approaches to solving problems. Then, you will be prepared to pick a solution based on what has to work.

// Create connection
$conn = mysqli_connect ( $servername , $username , $password , $dbname );
// Check connection
if (! $conn ) <
die( «Connection failed: » . mysqli_connect_error ());
>

$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)» ;

if ( mysqli_query ( $conn , $sql )) <
$last_id = mysqli_insert_id ( $conn );
echo «New record created successfully. Last inserted ID is: » . $last_id ;
> else <
echo «Error: » . $sql . «
» . mysqli_error ( $conn );
>

Forget about using MAX to get the last inserted id. Race conditions like other users inserting between your SELECT MAX(.. and your INSERT may render your id unusable.

The WAY to get the id is by using mysql_insert_id() or the mysql SQL function LAST_INSERT_ID().

Take care, if using mysql_insert_id() you should provide the resource returned by the mysql_connect, not the resultset returned by mysql_query.

Take care of setting an empty value for the AUTO_INCREMENT Field else you never get a value except zero returned from mysq_insert_id() .

I don’t get all the fuss around this.

I read:
«The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.»

I can’t really see what’s inaccurate about that.

«In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value; if no such value is generated, it returns the last last explicit value inserted into the AUTO_INCREMENT column.»

I must be missing something here but why would you insert multiple rows and then only handle the last one with some favoured behaviour? You could just as well insert them one at a time and then handle each row separately with the latest id.

Ссылка на основную публикацию
Adblock
detector