Как сделать множественную выборку?

Question

[Chesterfield25]

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

CREATE TABLE IF NOT EXISTS `country` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `shop` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `sections` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    `section_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `subcategory` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    `category_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    `description` varchar(256) NOT NULL,
    `url` varchar(256) NOT NULL,
    `price` double NOT NULL,
    `created` datetime NOT NULL,
    `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `product_country` (
    `product_id` int(11) NOT NULL,
    `country_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `product_shop` (
    `product_id` int(11) NOT NULL,
    `shop_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `product_section` (
    `product_id` int(11) NOT NULL,
    `section_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `product_category` (
    `product_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `product_subcategory` (
    `product_id` int(11) NOT NULL,
    `subcategory_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `section_country` (
    `section_id` int(11) NOT NULL,
    `country_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Есть три таблицы

Страны

CREATE TABLE IF NOT EXISTS `country` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Продукты

CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    `description` varchar(256) NOT NULL,
    `url` varchar(256) NOT NULL,
    `price` double NOT NULL,
    `created` datetime NOT NULL,
    `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Смежная таблица

CREATE TABLE IF NOT EXISTS `product_country` (
    `product_id` int(11) NOT NULL,
    `country_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

На главной странице кроме того что вывести все страны которые есть, нужно вывести продукты которые относятся ко всем странам которые есть в таблице country

Answer 1

[Дмитрий]

select 
     country.* ,
    products.*
from country
join product_country on country.id = product_country.country_id
join products on products.id = product_country.product_id

Comments

[Chesterfield25]

Создал такую функцию

// метод readCountryIdAll() - получение абсолютно всех товаров которы продаются во всех странах
   function readCountryIdAll() {

    // выбираем все записи
    $query = "select 
    country.* ,
   products.*
from country
join product_country on country.id = product_country.country_id
join products on products.id = product_country.product_id";

    // подготовка запроса
    $stmt = $this->conn->prepare($query);

    // выполняем запрос
    $stmt->execute();

    return $stmt;
}

В файле readAllCountry.php вывожу в JSON формате. То если я перехожу по ссылке /api/products/readAllCountry.php получаю два раза один и тот же продукт c id1 и один продукт с id2. Всего в бд 2 продукта

<?php
// необходимые HTTP-заголовки
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// подключение базы данных и файл, содержащий объекты
include_once "../config/database.php";
include_once "../models/products.php";

// получаем соединение с базой данных
$database = new Database();
$db = $database->getConnection();

// инициализируем объект
$product = new Product($db);
 
// запрашиваем товары
$stmt = $product->readCountryIdAll();
$num = $stmt->rowCount();

// проверка, найдено ли больше 0 записей
if ($num>0) {

    // массив товаров
    $products_arr = array();
    $products_arr["records"] = array();

    // получаем содержимое нашей таблицы
    // fetch() быстрее, чем fetchAll()
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        // извлекаем строку
        extract($row);

        $product_item = array(
            "id" => $id,
            "name" => $name,
            "description" => html_entity_decode($description),
            "price" => $price,
            "subcategory_id" => $subcategory_id,
            "subcategory_name" => $subcategory_name
        );

        array_push($products_arr["records"], $product_item);
    }

    // устанавливаем код ответа - 200 OK
    http_response_code(200);

    // выводим данные о товаре в формате JSON
    echo json_encode($products_arr);
} else {

    // установим код ответа - 404 Не найдено
    http_response_code(404);

    // сообщаем пользователю, что товары не найдены
    echo json_encode(array("message" => "Товары не найдены."), JSON_UNESCAPED_UNICODE);
}

Только у него разные подкатегории и цены

{"records":[
{"id":"1",
"name":"Sony Noise Cancelling Headphones WHCH710N: Wireless Bluetooth Over the Ear Headset with Mic for Phone-Call, Black","description":"Sony Noise Cancelling Headphones WHCH710N: Wireless Bluetooth Over the Ear Headset with Mic for Phone-Call, Black","price":"98","subcategory_id":"1","subcategory_name":null},
{"id":"1",
"name":"Sony Noise Cancelling Headphones WHCH710N: Wireless Bluetooth Over the Ear Headset with Mic for Phone-Call, Black","description":"Sony Noise Cancelling Headphones WHCH710N: Wireless Bluetooth Over the Ear Headset with Mic for Phone-Call, Black","price":"98","subcategory_id":"1","subcategory_name":null},
{"id":"2",
"name":"PlexDisc DVD-R 4.7GB 16x Branded Logo Recordable Media Disc - 100 Disc (no Container) FFP 632-817-BX","description":"PlexDisc DVD-R 4.7GB 16x Branded Logo Recordable Media Disc - 100 Disc (no Container) FFP 632-817-BX","price":"21","subcategory_id":"2","subcategory_name":null}]}

[Дмитрий]

Chesterfield25, ну значит продукт завязан на 2 страны. Что тут удивительного. Вопрос только зачем вам страны если вы в коде инфу о них не используете

[Chesterfield25]

Дмитрий, 1 продукт привязан к двум странам а 2 к одной
В файле readAllCountry.php
Добавил строку

$product->country_id = isset($_GET["country_id"]) ? $_GET["country_id"] : die();

И при переходе по ссылке /api/products/readAllCountry.php?country_id=1 и по ссылке /api/products/readAllCountry.php?country_id=2
Я получаю один и тот же результат

[Дмитрий]

Chesterfield25, и? Вопрос то в чем?

[Chesterfield25]

Дмитрий, При переходе по ссылке /api/products/readAllCountry.php?country_id=1
Получить товары у которых в product_country на против в таблице country_id=1


А по ссылке /api/products/readAllCountry.php вывести только те товары у которых есть напротив в таблице и country_id=1 и country_id=2 то есть все города.

Исходя из таблицы product_country выше если переход на страницу /api/products/readAllCountry.php должен вывестись только товар с id 1 так как этот товар продается во всех странах в данном случае в двух. А перейдя по ссылке /api/products/readAllCountry.php?country_id=1 я должен получить уже два товара с id=1 и 2

[Chesterfield25]

Дмитрий, Если у меня запрос такого рода

// выбираем все записи по странам где cp.country_id IN (1) это id страны
    $query = "SELECT p.name, p.id 
    FROM products AS p
    LEFT JOIN product_country AS cp 
    ON cp.product_id = p.id 
    WHERE cp.country_id IN (1)";

или такого

// выбираем все записи по странам где cp.country_id IN (2) это id страны
    $query = "SELECT p.name, p.id 
    FROM products AS p
    LEFT JOIN product_country AS cp 
    ON cp.product_id = p.id 
    WHERE cp.country_id IN (2)";

То выбирает все записи по стране где cp.country_id IN (2) это id страны

А если запрос такого рода

// выбираем все записи по странам где cp.country_id IN (2) это id страны
    $query = "SELECT p.name, p.id 
    FROM products AS p
    LEFT JOIN product_countryNj  AS cp 
    ON cp.product_id = p.id 
    WHERE cp.country_id IN (1,2)";

То выбирает не просто все записи а дублирует их как в таблице product_country
Товар 1 и товар 3 выводит по два раза.



Если взять такое условие то выводятся всего три товар то есть либо у которых country_id=1 или country_id=2
WHERE cp.country_id IN (1 || 2)";
А вот условие ниже работает по какой то причине точно так же как и то что выше, а должно выводить товары у которых и country_id=1 и country_id=2
WHERE cp.country_id IN (1 && 2)";

[Дмитрий]

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

[Chesterfield25]

Дмитрий, товары которые продаются во всех странах, а по ссылке /api/products/readAllCountry.php?country_id=1 вывести те товары которые продаются в стране с id=1

[Chesterfield25]

Такое условие тоже не работает

// выбираем все записи
    $query = "SELECT p.name, p.id 
    FROM products AS p
    LEFT JOIN product_country AS cp 
    ON cp.product_id = p.id 
    WHERE cp.country_id IN (1 AND 2)";

[Дмитрий]

Chesterfield25,
1. кто вам подсказал пихать в запрос IN AND OR и прочее? С чего вы вообще взяли это? Может глянуть в доку и увидеть что это нужного результата вам не даст?
2. Если вам нужны товары которые есть во всех странах - то просто
select * from products;
В случае если вам не нужны в выборке товары без стран

select * from products where exists(select 1 product_country from product_country, country where product_country.product_id = products.id and product_country.country_id = country.id);

3. Для товаров из конкретной страны

select * from products where exists(select 1 product_country from product_country where product_country.product_id = products.id and product_country.country_id = 3);

3. Для выборки товаров которые есть обязательно в 2 странах

select * from products where (select count(*) from product_country where product_country.product_id = products.id and product_country.country_id in (1,2)) == 2;

[Chesterfield25]

Дмитрий, Первые 2 варианта срабатывают, очень благодарен за помощь а с третьим ошибка

[Дмитрий]

Chesterfield25, какая ошибка? я должен угадать? у меня есть подсказки? звонок другу, 50 на 50, выбор зала?

[Chesterfield25]

Дмитрий, HTTP ERROR 500 А логи на хосте пустые

[Дмитрий]

Chesterfield25, ну может попробовать выполнить запрос через mysql client, или чего еще что бы определиться - может не в запросе ошибка? ну или включить display_errors on?

[Chesterfield25]

Дмитрий, Перешел с 8 версии php на 7 показало

Parse error:  syntax error, unexpected 'products' (T_STRING) in /api/models/products.php on line 50

[Дмитрий]

Chesterfield25, ну а как связана синтаксическая ошибка с запросом?

[Chesterfield25]

Дмитрий, С ошибкой мой косяк, разобрался только не находит товары, выдает что товар не найден.

// сообщаем пользователю, что товары не найдены
    echo json_encode(array("message" => "Товары не найдены."), JSON_UNESCAPED_UNICODE);

[Дмитрий]

Chesterfield25, ну значит таких товаров нет.

[Chesterfield25]

Дмитрий, Ну они есть это товар 1 и 3

[Дмитрий]

Chesterfield25, выполните запрос в пхпмайадмин

select count(*), product_id, group_concat(country_id) from product_country where product_country.product_id in (1,3) group by product_id;

[Chesterfield25]

Дмитрий,

[Дмитрий]

Chesterfield25, ну а теперь сделайте там же запрос

select *, (select count(*) from product_country where product_country.product_id = products.id and product_country.country_id in (1,2)) from products;

а потом

select * from products where (select count(*) from product_country where product_country.product_id = products.id and product_country.country_id in (1,2)) = 2;

[Chesterfield25]

Дмитрий, Сейчас работает как и должно