Mysql Выборка данных с сортировкой из трех таблиц

B1ackGh0st · 10 Янв 2014

Всем привет и С Новым годом!
Вопрос вот в чем.
Есть в базе данных три таблицы:
1 - содержит категории оборудования и материалов
2- Содержит материалы и id к категории которой материал относится
3 - Заказанное оборудование с id материала

|id|name_cat |
-----------------
|1 |метизы |
|2 |строительные|
|3 |концелярия |

|id|id_cat|name_meterial |
-----------------------------
|1 |1 |болт м40 |
|2 |1 |гайка м40 |
|3 |1 |шайба М40 |
|4 |3 |папка для бумаги |
|5 |3 |ручка |
|6 |2 |Цемент 10кг |
|7 |2 |Цемент 20кг |
|8 |2 |Цемент 30кг |

|meterial_id| colichestvo | cena
-----------------------------------
|4 | 10 | 20
|5 | 20 | 80
|6 | 30 | 90
|7 | 40 | 50
|8 | 50 | 60
|4 | 60 | 20
|5 | 70 | 80
|6 | 80 | 90

ни как не получается сделатьтак чтобы выводилась сначала категория оборудования (если в заказе нет материалов из этой категории то имя категории не выводится).
если есть одинаковые материалы то их колличество суммируется и считается их стоимость.
кароче выводу придать такой вид. (естественно все это в html <table>):

|наименование | колличество | стоимость
|-----------------------------------------------------
|строительные
|-----------------------------------------------------
|Цемент 10кг |110 | 9900
|Цемент 20кг |40 | 2000
........
........
если нету категории тоничего не выводим
........
........
|-----------------------------------------------------
|концелярия
|-----------------------------------------------------
|ручка |90 | 7200
........
........

прошу помоч хотябы с выводом, без суммирования оборудования и расчета стоимост
с J0IN не сталкивался

B1ackGh0st · 10 Янв 2014

Из двух таблиц вывести не проблема а вот сзапросом в третюю не получается

riff · 10 Янв 2014

Тестовые таблицы для вопроса лучше сделать в http://sqlfiddle.com

На первый взгляд проблем нет:

Код:

SELECT * FROM table1
-- или select *, SUM(cena) /*при наличии group*/ from table1
INNER JOIN table2 ON table1.field = table2.field -- field - соответствующие связки id
INNER JOIN table3 ON table2.field = table3.field
-- ну и при желании
GROUP BY table2.name

B1ackGh0st · 11 Янв 2014

Спасибо вроде получилось.
Но вывести через php нормально не могу.

Получается что выводится только первая категория а дальше заказанные материалы из всех категорий

PHP:

$sql = "
  SELECT * from cat c
  INNER JOIN materials m ON c.id = m.id_cat
  INNER JOIN ordered_equipment oe ON m.id = oe.name
";

$res_report_generation  = mysql_query ($sql);
$array_report_generation = mysql_fetch_array($res_report_generation);

while ($array_report_generation = mysql_fetch_array($res_report_generation)){

  echo '<strong>'.$array_report_generation['name_category'].'</strong><br>';

  while ($array_report_generation = mysql_fetch_array($res_report_generation)){

    echo $array_report_generation['name_materials'].'<br>';
  
  }
}

извиняюсь за длинные названия переменных

WMix · 11 Янв 2014

извиняюсь за длинные названия переменных

B1ackGh0st, не это мешает

PHP:

$sql = "
  SELECT * from cat 
  INNER JOIN materials ON cat.id = materials.id_cat
  INNER JOIN ordered_equipment ON materials.id = ordered_equipment.name
";

а вообще так

PHP:

$sql = "
  SELECT * from cat c
  INNER JOIN materials m ON c.id = m.id_cat
  INNER JOIN ordered_equipment oe ON m.id = oe.name
";

B1ackGh0st · 11 Янв 2014

поправил

WMix · 11 Янв 2014

убедись с помощью phpMyAdmin что твой запрос возвращает правильный результат
прочти тут http://www.php.net/manual/ru/function.mysql-fetch-array.php обрати внимание на примеры и сообрази что у тебя иначе (я о вложеном цикле) и измени
прочитай этот труд http://phpfaq.ru/debug#debug и добавь команды var_dump для отладки

B1ackGh0st · 12 Янв 2014

WMix написал(а):
убедись с помощью phpMyAdmin что твой запрос возвращает правильный результат

Вроде все ОК

WMix написал(а):
прочти тут http://www.php.net/manual/ru/function.mysql-fetch-array.php обрати внимание на примеры и сообрази что у тебя иначе (я о вложеном цикле) и измени

Примеры посмотрел, я так понимаю что надо использовать цикл for

WMix · 12 Янв 2014

я так понимаю что надо использовать цикл for

мне почем знать?

riff · 12 Янв 2014

B1ackGh0st написал(а):
Получается что выводится только первая категория а дальше заказанные материалы из всех категорий

Примеры по ссылке WMix я не смотрел, просто от себя добавлю:
Во-первых: добавь "Order By name_category" (или category_id), что бы объединить одинаковые категории в группы.
Во-вторых: можешь обойтись одним циклом (по крайне мере в твоём простом примере):

PHP:

$res = mysql_query ($sql);
$category_id = 0;
while ($row = mysql_fetch_assoc($res))
{
    if ($category_id !== $row['category_id'])
    {
        echo '<strong>'.$row['name_category'].'</strong><br>';
        $category_id = $row['category_id'];
    }

    echo $row['name_materials'].'<br>';
}

B1ackGh0st · 12 Янв 2014

Нет, так не получается - выводит только одну категорию а дальше материалы (включая материалы из других категорий)

B1ackGh0st · 12 Янв 2014

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

artoodetoo · 12 Янв 2014

B1ackGh0st, ищи где ты накосячил. пример от riff рабочий.

B1ackGh0st · 13 Янв 2014

PHP:

<?
$nameDB = "db";//Название БД
$nameSERVER = "localhost";//Сервер
$nameUSER = "root";//Имя пользователя БД
$passUSER = "1a2a3a1989";//Пароль пользователя БД

//Создание  соединения с БД
$connect_database  =  mysql_connect($nameSERVER,  $nameUSER,  $passUSER);
if(!$connect_database)  die("Ошибка при подключении к серверу базы данных");
mysql_select_db($nameDB)  or  die("Не  могу  открыть  $db:  ".mysql_error());
// Вывожу данные из базы в кодировке UTF-8
mysql_query("SET NAMES 'utf8'");
$sql = "
  SELECT * from cat c
  INNER JOIN materials m ON c.id = m.id_cat
  INNER JOIN ordered_equipment oe ON m.id = oe.name
";
$res = mysql_query ($sql);
$category_id = 0;
while ($row = mysql_fetch_assoc($res))
{
    if ($category_id !== $row['category_id'])
    {
        echo '<strong>'.$row['name_category'].'</strong><br>';
        $category_id = $row['category_id'];
    }
    echo $row['name_materials'].'<br>';
}
?>

Вот кусок дампа базы

[SQL]
CREATE TABLE IF NOT EXISTS `materials` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`id_cat` int(255) NOT NULL,
`name_materials` varchar(255) NOT NULL,
`gost` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=617 ;

--
-- Дамп данных таблицы `materials`
--

INSERT INTO `materials` (`id`, `id_cat`, `name_materials`, `gost`) VALUES
(1, 89, 'Ветошь протирочная новая хлопчатобумажная трикотажная крупная (размер лоскута свыше 20 см.)', NULL),
(2, 89, 'Губки хозяйственные', NULL),
(3, 89, 'Дезинфицирующее средство Доместос', NULL),
(4, 89, 'Мешки одноразовые под мусор 30л (по 50 шт.)', NULL),
(5, 89, 'Мешки полипропиленовые техн., типа I,обыкн.размер 56х104 под строительный мусор и шлам', NULL),
(6, 89, '"Моющее средство для сантехнических приборов ""Санокс"""', NULL),
(7, 89, '"Моющее средство для стекла 0,5 л типа ""HELP"""', NULL),
(8, 89, '"Моющее средство для металлических поверхностей ""Санита"""', NULL),
(9, 89, 'Мыло жидкое 5л', NULL),
(10, 89, 'Мыло жидкое 0,5л', NULL),
(11, 89, 'Освежитель воздуха', NULL),
(203, 21, 'Гвозди L= 20мм', NULL),
(204, 21, 'Гвозди L= 30мм', NULL),
(205, 21, 'Гвозди L= 40мм', NULL),
(206, 21, 'Гвозди L= 50мм', NULL),
(207, 21, 'Гвозди L= 70мм', NULL),
(208, 21, 'Гвозди L= 80мм', NULL),
(209, 21, 'Гвозди L= 100мм', NULL),
(210, 21, 'Гвозди L= 120мм', NULL);

CREATE TABLE IF NOT EXISTS `cat` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name_category` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=92 ;

INSERT INTO `cat` (`id`, `name_category`) VALUES
(21, 'метизы'),
(89, 'моющие средства'),
(90, 'Материалы'),
(91, 'Радиокомпоненты');

CREATE TABLE IF NOT EXISTS `ordered_equipment` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name` text,
`cost` varchar(255) DEFAULT NULL,
`units_measurement` varchar(255) NOT NULL,
`number_in_year` varchar(255) DEFAULT NULL,
`cost_in_year` varchar(255) DEFAULT NULL,
`quarter_1` varchar(255) DEFAULT NULL,
`cost_quarter_1` varchar(255) DEFAULT NULL,
`quarter_2` varchar(255) DEFAULT NULL,
`cost_quarter_2` varchar(255) DEFAULT NULL,
`quarter_3` varchar(255) DEFAULT NULL,
`cost_quarter_3` varchar(255) DEFAULT NULL,
`quarter_4` varchar(255) DEFAULT NULL,
`cost_quarter_4` varchar(255) DEFAULT NULL,
`memo` text,
`id_sheet` int(255) DEFAULT NULL,
`id_department` int(255) NOT NULL,
`year` int(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

INSERT INTO `ordered_equipment` (`id`, `name`, `cost`, `units_measurement`, `number_in_year`, `cost_in_year`, `quarter_1`, `cost_quarter_1`, `quarter_2`, `cost_quarter_2`, `quarter_3`, `cost_quarter_3`, `quarter_4`, `cost_quarter_4`, `memo`, `id_sheet`, `id_department`, `year`) VALUES
(4, '203', '400', '3', '640', '256000', '100', '40000', '100', '40000', '220', '88000', '220', '88000', NULL, 1, 4, 2013),
(5, '204', '500', '1', '1320', '660000', '330', '165000', '330', '165000', '330', '165000', '330', '165000', NULL, 1, 4, 2013),
(6, '205', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(7, '206', '0', '5', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(8, '207', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(9, '208', '0', '7', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(10, '1', '0', '7', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(11, '2', '0', '3', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(12, '3', '0', '5', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(13, '4', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(14, '5', '0', '7', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(15, '6', '0', '7', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013),
(16, '7', '0', '3', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NULL, 1, 4, 2013);
[/SQL]

Извиняюсь за многобукв спойлер найти не смог

riff · 13 Янв 2014

B1ackGh0st написал(а):
PHP:

<? while ($row = mysql_fetch_assoc($res)) { if ($category_id !== $row['category_id']) { echo '<strong>'.$row['name_category'].'</strong><br>'; $category_id = $row['category_id']; } echo $row['name_materials'].'<br>'; } ?>

Вот кусок дампа базы

[SQL]
CREATE TABLE IF NOT EXISTS `materials` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`id_cat` int(255) NOT NULL,
`name_materials` varchar(255) NOT NULL,
`gost` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=617 ;
[/SQL]

Я даже ответ не успел прочитать, как в пределах одного экрана увидел...
Ты мозг хоть на секунду включи, да даже не включая, просто прочти две части, и найди отличия.

P.S. :eek:

int(255)

riff · 13 Янв 2014

И на потом ещё: сравни два результата:

Код:

SELECT * from cat c
INNER JOIN materials m ON c.id = m.id_cat
INNER JOIN ordered_equipment oe ON m.id = oe.name

Код:

SELECT * from cat c
RIGHT JOIN materials m ON c.id = m.id_cat
LEFT JOIN ordered_equipment oe ON m.id = oe.name

Я не знаю какой тебе нужен.

B1ackGh0st · 13 Янв 2014

Я уже дотяпил. Спасибо

B1ackGh0st · 14 Янв 2014

INNERИзвиняюсь что не по теме.
Есть SQL запрос :

SELECT * from cat c
INNER JOIN materials m ON c.id = m.id_cat
INNER JOIN ordered_equipment oe ON m.id = oe.name

еси в него добавить еще один JOIN то он не срабатывает. phpMyAdmin выдает ошибку синтаксиса
SELECT * FROM group g
INNER JOIN cat c ON c.id_group = g.id
INNER JOIN materials m ON c.id = m.id_cat
INNER JOIN ordered_equipment oe ON m.id = oe.name

WMix · 14 Янв 2014

FROM `group` g

B1ackGh0st · 15 Янв 2014

Я наверное уже Вас достал.

Как сделать чтобы рядом с названием категории выводилась сумма чисел столбца "cost_in_year" (SQL дамп выше) материалов из этой категории

Mysql Выборка данных с сортировкой из трех таблиц

Новичок

Новичок

Новичок

Новичок

герр M:)ller

Новичок

герр M:)ller

Новичок

герр M:)ller

Новичок

Новичок

Новичок

великий и ужасный

Новичок

Новичок

Новичок

Новичок

Новичок

герр M:)ller

Новичок