Ответы пользователя по тегу MySQL
  • Как извлечь данные записи бд по кнопке?

    @alexalexes
    В href записываете get параметр id строки.
    <a href="?id=1">см. запись</a>
    Если текущий список php используется для вывода всего списка, то скрипт нужно модифицировать так, что принимая параметр id, он начал работать по логике вывода сведений одной строки. Нет параметра - выводим все.
    if(isset($_GET['id']))
    {
      // тут код или вызов функции для работы с одной записью
    }
    else
    {
      // тут код или вызов функции для вывода всего списка
    }

    В ветке кода, где работаем с одной записью, подставляем get параметр в запрос:
    msqli_prepare("текст запроса ... where id = ?"); // ставим в нужное место запроса параметрические метки
    msqli_bind_param("i", $_GET["id"]); // передаем параметр на место меток-вопросов, в каком порядке они идут
    msqi_execute();
    // msqli_fetch функция и форматированный вывод сведений

    PS: msqli_* примерно накидал, смотрите документацию, как их вызывать. Идея и порядок кода примерно такой будет.
    Ответ написан
  • Насколько сложный пароль стоит иметь от БД?

    @alexalexes
    Второй пароль уязвим, если вы на всех системах, которые администрируете, устанавливаете пароль по похожей маске. Злоумышленнику достаточно узнать, как строится пароль к одной системе, чтобы иметь представление, как подбирать пароль к остальным. Злоумышленник будет использовать ровно ту логику, по которой вам удобнее запоминать пароль.
    Лучше не понижать энтропию ни у какой части пароля, использовать только генераторы случайных символов.
    Ответ написан
  • Как создать ссылку в базе данных?

    @alexalexes
    Общий ответ с точки зрения любой СУБД по отношению к любому способу вывода (выкатить что-то на веб-страницу) - никак. База данных ничего не знает, что такое ссылка в веб-документе. Она оперирует такими понятиями, которые позволяет хранить данные, как таблица, столбец, строка, значение атрибута. Из таблиц и связующих атрибутов таблиц - внешних и внутренних ключей строится структура данных для вашей предметной области.
    С другой стороны, у вас имеется ссылка в веб документе, которая имеет некоторые атрибуты, пригодные для хранения в базе данных: href - адрес ссылки, title - подсказка на ссылки, и возможно, какой-то текст, который обрамляет эта ссылка.
    Попробуйте значения этих атрибутов поместить в таблицу базы данных links, со столбцами:
    link_id, -- идентификатор записи в таблице ссылок
    link_href, -- ссылка
    link_title, --подсказка ссылки
    link_text -- текст ссылки
    И с помощью запроса к СУБД, а также с помощью PHP сделайте форматированный построчный вывод данных:
    // тут опущены действия связанные с формированием запроса,
    // на этом этапе вы каким-то способом получили выборку данных из таблицы links
    foreach($rows as $row)
    {
      echo '<a href="'.$row['link_href'].'" title="'.$row['link_title'].'">'.$row['link_text'].'</a><br/>';
    }

    Это и будет самый примитивный пример, как хранить сведения о ссылки и как их выводить.
    В реальном проекте будет несколько сложнее выглядеть таблица, где хранятся подобные сведения.
    Ответ написан
    Комментировать
  • Как добавить/обновить данные в таблице MySQL, если в другой таблице соблюдается определенное условие?

    @alexalexes
    Обновлять строки по условию с подзапросом. Но проблема в том, что в утверждении не говорится, чем связаны table_1 и table_2.
    update  table_1 t1
    set  t1.column_1 = A
    where exists (select 1 from table_2 t2 where t2.какой-то-связный-ключ-с-t1 = t1.какой-то-связный-ключ-с-t2 and t2.column_1 = B)
    Ответ написан
    6 комментариев
  • Как сделать вывод последнего сообщения между двумя юзерами?

    @alexalexes
    Каждый уважающий себя разработчик должен пройти путь создания своего велосипеда - мессенджера.
    От простой почтовой модели данных, где в записи сообщений есть отправитель и получатель, до модели данных, содержащий беседки (они же чаты, они же диалоги - называйте как хотите).
    Но вопрос не в этом. Давайте попробуем что-то сделать с существующей моделью.
    Время сообщения у нас нет - не проблема. Время летит вперед, и id увеличиваются в ту же сторону - по возрастанию. Значит id достаточно, чтобы различать, какое сообщение пришло раньше, а какое позже.
    Отсутствует сущность беседки - тоже не проблема. Будем считать совокупность сообщений, где отправитель, в которых может быть как сам автор, так и собеседник, или получатель также может быть как сам автор, так и собеседник одной беседкой. Такую совокупность сообщений можно выделить условием: (m2.to_id = m.to_id and m2.from_id = m.from_id or m2.to_id = m.from_id and m2.from_id = m.to_id) - что и будет выделять сущность беседки в упрощенном виде. Для выделения всех сообщений, где участвует пользователь from_id будет выражение: :from_id in (m.from_id, m.to_id). Осталось сообразить, как составить подзапрос с count, чтобы выделить самые поздние сообщения.
    Где-то такой запрос получится:
    select * from messages m
     where  :from_id in (m.from_id, m.to_id)
        and (select count(*)
                  from messages m2
                 where  (m2.to_id = m.to_id and m2.from_id = m.from_id
                       or   m2.to_id = m.from_id and m2.from_id = m.to_id) 
                   and m2.id > m.id
    ) < 1
    order by id desc

    Если понадобится выделить сообщения одной беседки, то скорее всего будет такой запрос:
    select * from messages m
     where (m.to_id = :to_id and m.from_id = :from_id
     or        m.to_id = :from_id and m.from_id = :to_id)
    order by id asc
    Ответ написан
    Комментировать
  • Как переделать запрос для получения нужного ответа?

    @alexalexes
    Как-то, так.
    SELECT
    city.id,
    city.name,
    country.name,
    salary,
    country.is_here
    FROM city
    LEFT JOIN country ON city.cid = country.id
    union all
    SELECT
    city.id,
    city.name,
    country.name,
    salary + 2000,
    1
    FROM city
    LEFT JOIN country ON city.cid = country.id
    where (country.id is null or country.is_here = 0)
    Ответ написан
    Комментировать
  • Как получить только первые строки из групп строк отсортированной таблицы?

    @alexalexes

    model_id = '269'
    AND partner_id = '0'
    AND size <= '32'

    Ай-ай-ай. Все Id-шники строками заданы, не тот тип данных выбрали для столбцов.
    Выбрать первые записи от какой-то категории можно c помощью коррелирующего группирующего подзапроса (где count считается).
    SELECT id, size, price, date
      FROM prices p
      WHERE model_id = 269
           AND partner_id = 0
           AND date_time <= '2021-10-19' 
           AND (select count(*)
               from prices p2
              where  -- сюда нужно прописать параметры строк, по которым будет определяться окно счетчика
                         -- то есть, отсчет счетчика будет в пределах одного размера, одной и той же модели, одного и того де партнера.
                          p2.size = p.size
                   and p2.partner_id = p.partner_id
                  and  p2.model_id = p.model_id
                  -- а тут параметр, чем отличается одна строка счетчика от другой
                  and p2.date > p.date 
            ) < 1 -- сколько строк отсечь от категории (n + 1) ?
    order by p.date desc
    Ответ написан
  • MySQL - выборка диалогов с тремя последними сообщениями?

    @alexalexes
    Вариант для MySQL ниже 8 версии:
    select A.*
    from 
    (
    SELECT d.id AS dialog_id, 
           d.date_latest_message,
           (select max(DM.added_date)
               from dialog_messages dm3
              where dm3.dialog_id = dm.dialog_id
           ) as datetime_latest_message,
           Dm.Id as Dm_Id,
           DM.message,
           DM.added_date
      FROM dialogs d
      join dialog_messages dm on DM.dialog_id = D.dialog_id
      WHERE (select count(*)
               from dialog_messages dm2
              where dm2.dialog_id = dm.dialog_id
              and dm2.id > dm.id -- по идент. будет эффективнее работать, чем по дате-время, к тому же первичный ключ, как правило, проиндексирован, не требуется доп. индексов
            ) < 3 -- берем только те сообщения, которые позднее опубликованы чем текущее (3 вышестоящих)
        and DATE(d.date_latest_message BETWEEN) '2020-10-01' AND '2021-10-08'
    ) A
    order by A.datetime_latest_message desc, A.Dm_Id desc

    Если MySQL версии 8 и выше, как вариант, можно взять оконную функцию row_number.
    select A.*
    from
    (
      SELECT 
             d.id AS dialog_id, 
             d.date_latest_message,
             (select max(DM.added_date)
               from dialog_messages dm3
              where dm3.dialog_id = dm.dialog_id
             ) as datetime_latest_message,
             Dm.Id as Dm_Id,
             DM.message,
             DM.added_date AS message_added_date,
             row_number() over (partition by d.id              -- окно счетчика в пределах идент. диалога
                                order by DM.Id desc    -- направление сортировки счетчика
                               ) dm_rownum -- счетчик для отсечения порций
      FROM dialogs d
      join dialog_messages dm on DM.dialog_id = D.dialog_id
      WHERE DATE(d.date_latest_message) BETWEEN '2020-10-01' AND '2021-10-08'
    ) A
    where A.dm_rownum <= 3 -- отсекаем нужное число "локальных" отсчетов
    ORDER BY A.datetime_latest_message DESC, A.Dm_Id DESC
    Ответ написан
  • Как массово изменить расширение изображений на свой фомат в MYSQL базе?

    @alexalexes
    Этим запросом крайне внимательно изучаете - удовлетворяет ли вас результат преобразования строк в result_mod_f1 из mod_f1, и result_mod_f2 из mod_f2.
    select mod_f1,  replace(mod_f1, '.png', '.webp') as result_mod_f1,
              mod_f2,  replace(mod_f2, '.png', '.webp') as result_mod_f2
    from table

    Если что-то не устраивает, переписываете replace-ы.
    Если что-то переписали, то заменяете выражения в правых частях после знака =, чтобы преобразование mod_f1 соответствовало mod_f1, а преобразование mod_f2 для mod_f2.
    /*update table
    set mod_f1 = replace(mod_f1, '.png', '.webp'),
          mod_f2 = replace(mod_f2, '.png', '.webp')*/

    Снимаете комментарий, словно собираетесь открыть защитную крышку на красной кнопке атомной бомбы и выполняете запрос.
    ПС: Желательно делать это в программном средстве, которое поддерживает режим незакрытой транзакции, чтобы можно было откатить.
    ППС: Если таблица очень большая, то лучше обновлять порциями.
    /*update table
    set mod_f1 = replace(mod_f1, '.png', '.webp'),
          mod_f2 = replace(mod_f2, '.png', '.webp')
    where mod_f1 like '%.png%'
          or mod_f2 like '%.png%'
    LIMIT 1000 */
    Ответ написан
    5 комментариев
  • Как лучше реализовать вывод в корзину?

    @alexalexes
    1. Ограничьте количество предметов в корзине каким-то макс. значением, оно должно быть явно задано, но не вызывало дискомфорта у пользователей (оно должно иметь такое значение, чтобы в нормальных человеческих условиях оно едва достижимо).
    $max_cart_item_count = 500;
    $curr_cart_item_count = min(count($_SESSION['cart']), $max_cart_item_count);
    for($i = 0; $i < $curr_cart_item_count; $i++)
    ....

    2. Есть опасность применения SQL инъекций.
    В этом месте, где подставляется переменная в текст запроса, если умело закомментировать кавычку, то можно получить полный доступ к базе.
    WHERE `type`= '$id'
    Избегайте подставлять любые переменные с пользовательскими данными в текст запроса ("с пользовательскими данными" и "в текст запроса" - нужно подчеркнуть и осмыслить как отдельные понятия с точки зрения безопасности). Для этого существует специальные методики подготовки запроса:
    $stmt = $conn->stmt_init();
    $stmt->prepare("SELECT * FROM `items` WHERE `type` = ?"); // подставляем текст запроса, причем на месте входных параметров ставим специальные маркеры - плейсхолдеры.
    $stmt->bind_param("i",  $id); // подставляем на место плейсхолдера значение параметра как целочисленный тип
    $stmt->execute(); // вот теперь можно выполнить запрос
    $result = $stmt->get_result();
    while($row = $result->fetch_assoc())
    {
     // Обработка результатов
    }
    Ответ написан
  • Как сравнить два поля VARCHAR и INT?

    @alexalexes
    Как выглядит ваша проблема:
    Таблица новостей:
    ID, CATEGORIES
    1, '1,13,14'

    Как нужно реализовать:
    Добавить еще таблицу Categories_Of_News
    ID, NEWS_ID, CATEGORY_ID
    1, 1, 1
    2, 1, 13
    3, 1, 14

    Тогда запросы не будут содержать никаких костылей с парсингом строк и у вас будут работать индексы (если их создать).
    Например, получить все категории, в которые входит новость :news_id
    select  C.Id
    from News N
    join Categories_Of_News CoN on CoN.News_Id = N.Id
    join Category C on C.Id = CoN.Category_Id 
    where N.Id = :news_id

    PS: Смотрите, как реализовать связь "многим-ко-многим".
    Ответ написан
    Комментировать
  • Как записать данные из запроса VK в бд?

    @alexalexes
    Отработать навык, как вставить в базу вообще что-то.
    Например, так https://html5css.ru/php/php_mysql_insert.php
    Обратите внимание, что у процесса взаимодействия с БД есть несколько фаз работы:
    подключение к базе, формирование запроса, добавление параметров к запросу, выполнение запроса и получение результата (если нужен действия статус или выборка).
    Ответ написан
    Комментировать
  • Структуризация и импорт базы для интернет магазина?

    @alexalexes
    артикулом/категорией/брендом/названием/ценой

    Редко какое свойство товара останется скалярным значением с развитием структуры базы и не перейдет в таблицы-справочники.
    Что же сразу должно бросится в глаза начинающему проектировщику БД - это то, что нужны таблицы:
    - Категория товара
    - Бренд
    - Цена на товар (одна запись соответствует цене товара за определенный период, может нести сведения о скидке и т.д.)
    Причем, валюта цены тоже может быть отдельным справочником.
    У товара могут быть габариты ДxВxШ.
    Название оси габарита - эта тоже справочник.
    Размерность - см, м, дюймы - тоже справочник.
    Так что, смотрите, любое свойство можно превратить в справочник, и довольно разобранный на составляющие.
    Ответ написан
    Комментировать
  • Сравнение времени mysql?

    @alexalexes
    Минус переставьте в нужное место.
    SELECT * FROM `test` WHERE `time` >= (now() - INTERVAL 10 MINUTE);
    Ответ написан
    3 комментария
  • Как сделать одно действие с несколькими значениями?

    @alexalexes
    Если нужно присвоить одно и то же значение атрибутам у нескольких определенных строк, то это делается элементарно.
    update `table` set атрибут_не_первичный_ключ = 1
    where id in (1, 2, 4, 10);

    Другое дело, если это касается атрибутов с уникальным свойством.
    Если нужно сделать рокировку значений, то поскольку первичный ключ имеет свойство уникального значения, то нужно действовать в два этапа.
    1. Переписать id-шники интересуемых строк так, чтобы новый id был за диапазоном присваиваемых id.
    Можно переписать в большую сторону, если у вас максимальное число записей пару тысяч, то воспользоваться новыми значениями, например, от миллиона.
    Но проблема в том, что если система не остановлена во время этой рокировки, то вероятно, вставка новых строк будет происходить с присвоением макс. последнего значения +1, тогда новые строки окажутся с миллионными id-шками.
    Нужно взять диапазон отрицательных чисел в качестве промежуточного состояния рокировки id.
    update table
    set id = case
       when id = 1 then -1
       when id = 2 then -2
       when id = 4 then -4
       when id = 10 then -10
       else id -- страховка от дурака, если неправильно описан in 
    end
    where id in (1, 2, 4, 10)

    2. Второй шаг заключается в том, чтобы переписать нашим отрицательным id новые положительные значения, чтобы каждая строка получила новые значения 1,2,4,10. Например, можно поменять местами id соседей.
    update table
    set id = case
       when id = -1 then 2
       when id = -2 then 4
       when id = -4 then 10
       when id = -10 then 1
       else id -- страховка от дурака, если неправильно описан in 
    end
    where id in (-1, -2, -4, -10)

    В итоге, такую операцию можно делать на работающей системе, не мешая созданию новых записей.
    Чтобы вообще исключить коллизии, то лучше оба запроса делать в одной транзакции, при этом можно использовать положительные значения не используемого диапазона значений id.
    Ответ написан
    3 комментария
  • Как сделать посуточный счетчик посещений (доработать старый)?

    @alexalexes
    Если вы понимаете, что делает этот запрос:
    UPDATE article SET visits=(visits+1) WHERE id='".$theme_idss."' LIMIT 1

    То по аналогии можно плюсовать интересуемый столбец:
    UPDATE article
    SET visits=(visits+1),
          today = today + 1 -- суточные посещения
     WHERE id='".$theme_idss."' -- когда разберетесь, что такое sql-инъекция, то перепишите все запросы, использующие такой стиль подстановки параметров (склеиванием строк)
    LIMIT 1 -- после того, как разберетесь, является ли id первичным ключом, можно убрать лимит

    Но при добавлении столбца нужно позаботиться, чтобы у столбца значение по умолчанию было 0.
    ALTER TABLE article
    ADD COLUMN today int default 0 AFTER visits

    В скрипте, который чистит таблицу раз в сутки предусмотрите запрос, обнуляющий суточный счетчик у всех статей:
    UPDATE article
    SET today = 0

    PS: Если хотите анализировать посещаемость, то лучше воспользоваться готовыми счетчиками от поисковых систем - Google Analytics или Яндекс-метрики.
    Если хотите все же использовать свой лог, то лучше его не удалять. Вдруг захотите выявить среднемесячное/суточное/годовое посещение или в каком то другом разрезе по разным разделам сайта. Для пересчета понадобится каждая запись посещения.
    Ответ написан
    2 комментария
  • Как уменьшить время выполнения запроса?

    @alexalexes
    Делаете отдельную таблицу, в единственном поле и записи которой храните значение этого кол-ва.
    Создаете триггеры для таблицы posts на операции вставки, обновления, удаления.
    В триггере используете этот запрос на подсчет кол-ва и вставку/обновление этого значения в таблице количеств.
    Этим триггером вы переложите 30мс из операции select подсчета кол-ва, на операции insert, update, delete, во время вставки нового значения (обновления категории, статуса, удаления записи), причем в триггере можно игнорировать пересчет, если запись вставляется не того вида (не категория 4 и не статус 1).
    Количество особых записей всегда можно получить моментально из той самой таблицы.
    Ответ написан
  • Как вырезать нужную часть предложения с помощью MySQL?

    @alexalexes
    Можно с помощью функции SUBSTRING_INDEX.
    Два раза применяете эту функцию:
    1. Получаете часть строки "Eau Claire - Home 83", указав разделитель " of ", а номер -1.
    2. Получаете часть строки "Eau Claire", указав разделитель " - ", а номер 1.
    Ответ написан
    2 комментария
  • Как отзеркалить сопутствующие товары?

    @alexalexes
    Вы этой таблицей кодируете граф (не важно чего).
    Если принимаете за правило, что каждая запись кодирует ребро графа только в одном направлении (граф является направленным), то, чтобы добавить другое направление, тогда вам нужно вставить еще одну запись.
    Например, существует ребро от вершины 1885 до вершины 4683, чтобы прописать ребро с обратным направлением, нужно вставить запись (4683, 1885).
    Список ребер к присоединенным вершинам от данной вершины достаточно выбирать таким запросом:
    select * from t
    where t.idProduct = :id_product and t.relatedProduct <> :id_product -- сравнение с <> исключает петлю на собственной вершине

    Если у вас граф ненаправленный, то можно обойтись одной записью. При получении выборки (ребер графа) вам нужно выбирать не только ребра от вершины idProduct до relatedProduct, но включать те записи, ребра которые прописаны от relatedProduct до idProduct.
    Скорее всего запрос будет иметь такой вид:
    select * from t
    where (    (t.idProduct = :id_product and t.relatedProduct <> :id_product) -- сравнение с <> исключает петлю на собственной вершине
                or (t.relatedProduct = :id_product and t.idProduct <> :id_product))

    Но обязательно нужно определиться, работает ли совместимость товаров в обе стороны, от этого зависит выбор типа графа и правила его чтения/записи.
    Ответ написан
    6 комментариев
  • Как объединить GROUP BY и JOIN?

    @alexalexes
    Чтобы найти читателей с последними взятыми книгами, нужно при помощи оконной функции row_number пронумеровать строки в пределах каждого читателя (задав партицию по айди читателя) в порядке убывания по дате taken_at. Из полученного результата взять строки, которые получили номера 1.
    select * from
    (select r.id as reader_id, r.first_name, r.last_name, b.id book_id, b.name, b.pub_date, row_number() over (partition by r.id order by l.taken_at desc) as rownum
    from readers r
    left join log_taking l on r.id = l.reader_id
    left join books b on b.id = l.book_id
    ) a
    where a.rownum = 1

    PS: Связка left join выведет всех читателей, в независимости, брали они книги или нет. Просто join даст список читателей, которые брали книги хоть один раз.
    PPS: Оконные функции доступны с MySQL версии 8.
    Если версия 5, то придется городить велосипед с пользовательскими переменными.
    Ответ написан
    4 комментария