Форум MySQL

Помогите составить запрос к базе:

Из двух таблиц нужно вывести название категории и количество элементов данной категории, если нет элементов одной из категорий - то ее не нужно выводить.

Вывод примерно так хотелось бы чтобы выглядел:

Рестораны - 25
Бары - 10
Кафе - 11
...

Спасибо.

Дамп таблиц во вложении.

наверное что-то похожее на это

SELECT cat_catalog.name, COUNT(catalog.*) AS cnt     FROM cat_catalog LEFT JOIN catalog     ON catalog.category = cat_catalog.id AND cnt != 0     ORDER BY cat_catalog.id

EXP пора начинать думать, а не писать ерунду...

Не подскажете?

По структуре таблицы, вообще говоря внешний ключ никак не должен иметь тип VARCHAR.
По запросу: Корневые категории - учитывать или нет ? Поясню, допустим есть у нас цепочка
Развлечения - Стадионы- Стадион им Кирова
Развлечения - Стадионы- Стадион Спартак
Что хотите увидеть на выходе ?

Может это подойдет?

<? // Запрашиваем категории $query_cat = mysql_query ("SELECT * FROM cat_catalog ORDER by name");  $count_cat = mysql_num_rows ($query_cat); // если есть хоть одна -  if ($count_cat)  {      // в цикле лепим ассоц. массив      while ($cat = mysql_fetch_assoc($query_cat))     {         // зпрашиваем наименования          $query = mysql_query ("SELECT * FROM catalog WHERE category = '".$cat['id']."'");          $count_query = mysql_num_rows ($query);         // если в этой категории что то есть -          if ($count_query)          {             // выводим её название и количество наименований             echo $cat['name']." - ".$count_query;          }     } } ?>

Ваш код работает. ))

Но, некоторые места в каталоге имеют по 2 категории (они записаны через запятую), можно ли учесть их тоже.

Т.е. если например, стадион им. Кирова имеет 2 категории (допустим, стадионы и бассейны), то при выводе посчитать его и в категории стадионы и в категории бассейны.

.

- - -

>> Развлечения - Стадионы- Стадион им Кирова
>> Развлечения - Стадионы- Стадион Спартак
>> Что хотите увидеть на выходе ?

На выходе - Стадионы (2)

oradev прав , но ничего не могу с собой поделать ,) просто так я и пытаюсь всё понять, глядиш и поправит кто-то в чём-то , спасибо ему за это.
откуда я узнал-бы что count непроходит в таких запросах , и где-бы ещё сталкнулся-бы с таким :))

SELECT DISTINCT cat_catalog.name, ( @catid := cat_catalog.id ) AS catid, ( SELECT COUNT(*) FROM catalog WHERE category = @catid ) AS cnt      FROM cat_catalog LEFT JOIN catalog      ON 1

без DISTINCT выдаёт результатов что страница еле грузится, но все правильные :)
с DISTINCT правда тоже хоть и выдаёт поменьше почти секунду пыхтит такой запрос )
буду теперь думать зато в чём-же тут можно ещё применить чего-нибудь,
и понимать как лучше строить таблицы

// спустя немного времени ))

так работает быстрее , примерно в 20 раз ,)

SELECT DISTINCT cat_catalog.name, ( @catid := cat_catalog.id ) AS catid, ( SELECT COUNT(*) FROM catalog WHERE category = @catid ) AS cnt      FROM cat_catalog LEFT JOIN catalog      ON cat_catalog.id =catalog.category

но ещё не пропускать с нулём совпадений помоему нереально

Простите, но не пойму, а как будет выглядеть сам вывод?

И еще, некоторые места в каталоге имеют по 2 категории (они записаны через запятую), можно ли учесть их тоже.

Т.е. если например, стадион им. Кирова имеет 2 категории (допустим, стадионы и бассейны), то при выводе посчитать его и в категории стадионы и в категории бассейны.

Спасибо ОГРОМНОЕ!

вывести-то можно

<?php // здесь подключение $query = "SELECT DISTINCT ( @catid := cat_catalog.id ) AS catid, cat_catalog.name,      ( SELECT COUNT(*) FROM catalog WHERE LOCATE(@catid, catalog.category ) != 0 ) AS cnt      FROM cat_catalog LEFT JOIN catalog      ON LOCATE(cat_catalog.id, catalog.category ) != 0     ORDER BY cnt DESC";      $dump = mysql_query($query) or exit; if(mysql_num_rows($dump) > 0) {     while($cat = mysql_fetch_array($dump, MYSQL_NUM) )     {         if ($cat[2] == 0) break;         echo '..... <a href="./?id_cat=' . $cat[0] . '">' . $cat[1] . '</a> ' . $cat[2] . " наименований<br />\n";     }  } // здесь отключение  ?>

только не будет-ли быстрее работать с несколькими запросами к базе

тут несмотря на дважды проверяние присутствия номера категории в строке
всёравно без DISTINCT возвращает результатов больше чем надо
а времени занимает столько-же , не могу найти как это исправить

лучше такой вариант помоему не использовать

А если использовать вариант provodnikа, то можно как-то учесть это:

>> И еще, некоторые места в каталоге имеют по 2 категории (они записаны через запятую), можно ли учесть их тоже.

>> Т.е. если например, стадион им. Кирова имеет 2 категории (допустим, стадионы и бассейны), то при выводе посчитать его и в категории стадионы и в категории бассейны.

Если я все правильно понял, то :

SELECT t.id_cat, ifnull(t.sub_cat, 'Всего') as name_catalog, count(c.id) as c_num   FROM    (SELECT cat1.id id_cat, cat2.name sub_cat, cat2.id sub_cat_id              FROM cat_catalog cat1 LEFT JOIN cat_catalog cat2                      ON (cat1.id = cat2.subcat)             WHERE cat1.subcat = 0) t        LEFT JOIN           catalog c        ON (t.sub_cat_id = c.category)  GROUP BY t.id_cat, t.sub_cat WITH ROLLUP;

а какой вариант использовать лучше Ваш или provodnikа, а Вы учли условие нескольких категорий? Спасибо.

Не вижу смысла делать что-то средствами php то что можно сделать одним единственным sql-запросом, причем весьма тривиальным. Это по поводу какой вариант оптимальнее. Насчет своего варианта: я думаю что будет правильно вести подсчет не только в листах дерева - конечные узлы, но и в корне дерева.

а Вы учли условие нескольких категорий?
А сам вывод как будет выглядеть?

То что пытался сделать EXP на самом деле пишется одним запросом, без каких-либо подзапросов:

select MAX(cat1.name), count(c.id)  from cat_catalog cat1 LEFT JOIN catalog c ON (cat1.id = c.category)  where cat1.subcat <> 0  group by cat1.id;

Дык а какого типа у Вас поле КАТЕГОРИЯ то, если там допустимы значения с запятыми?
Если по мне - так проще изначально грамотно организовать структуру БД, чем работать с такими значениями.
Хотя вот, попробуйте:

<?  // Запрашиваем категории  $query_cat = mysql_query ("SELECT * FROM cat_catalog ORDER by name");   $count_cat = mysql_num_rows ($query_cat);  // если есть хоть одна -   if ($count_cat)   {      // в цикле лепим ассоц. массив      while ($cat = mysql_fetch_assoc($query_cat))      {          // зпрашиваем наименования         $cat_id = intval($cat['id']);         $query = mysql_query ("SELECT * FROM catalog WHERE category LIKE '%".$cat_id."%'")          $count_query = mysql_num_rows ($query);          // если в этой категории что то есть -           if ($count_query)           {              // выводим её название и количество наименований              echo $cat['name']." - ".$count_query;           }      }  }  ?>

Я не код проверял... Пишу на коленке..

>> Я не код проверял... Пишу на коленке..

Ошибка - Parse error: parse error, unexpected T_VARIABLE
в этой строке - $count_query = mysql_num_rows ($query);

>> Дык а какого типа у Вас поле КАТЕГОРИЯ то, если там допустимы значения с запятыми?
Знаю что тип поля лучше использовать INT, но как тогда быть с двойными категориями? )) У Вас есть предложения?

В этой строке точку с запятой в конце поставьте ;
query = mysql_query ("SELECT * FROM catalog WHERE category LIKE '%".$cat_id."%'")

>> В этой строке точку с запятой в конце поставьте ;
Точно ))

>> Хотя вот, попробуйте:
Все работает как нужно, спасибо. )) Очень признателен!

оказывается просто делалось то что я пробовал сделать

SELECT ( @catid := id ) AS catid, name,      ( SELECT COUNT(*) FROM catalog  WHERE LOCATE(@catid, category ) != 0  ) AS cnt      FROM cat_catalog      ORDER BY cnt DESC

незнаю только 0.01 секунд это много или нет
и как сравнить два варианта

//

попробовал сравнить так:

<?php list($headtime, $time) = explode(chr(32), microtime()); $headtime = ($time + $headtime);  # время начала работы require_once("config.php"); // /* поочерёдно раскомментируя комментарии :) $query = "SELECT ( @catid := id ) AS catid, name,       ( SELECT COUNT(*) FROM catalog   WHERE LOCATE(@catid, category ) != 0  ) AS cnt       FROM cat_catalog       ORDER BY cnt DESC";      $dump = mysql_query($query) or exit; if(mysql_num_rows($dump) > 0) {     while($cat = mysql_fetch_array($dump, MYSQL_NUM) )     {         if ($cat[2] == 0) break;         echo '..... <a href="./?id_cat=' . $cat[0] . '">' . $cat[1] . '</a> ' . $cat[2] . " наименований<br />\n";     }  }    // */  // /*      $query_cat = mysql_query("SELECT id, name FROM cat_catalog ORDER BY name");    // если есть хоть одна -    if (mysql_num_rows($query_cat))    {       // в цикле лепим массив       while ($cat =mysql_fetch_array($query_cat, MYSQL_NUM))       {           $query = mysql_query("SELECT COUNT(*) FROM catalog WHERE LOCATE('" . $cat[0] . "', category ) != 0")  ;         $count  = mysql_result($query, 0);             if ($count == 0) continue;         echo '..... <a href="./?id_cat=' . $cat[0] . '">' . $cat[1] . '</a> ' . $count  . " наименований<br />\n";         }         } // */                  list($msec,$sec) = explode(chr(32), microtime());  echo  '<hr />[_' . round(($sec + $msec) - $headtime, 4) . 's_]';         ?>

извиняюсь дописал невидя следующего поста

Все таки есть ошибка...

Если категория имеет идентификатор состоящий из двух цифр, например - 26, то его считают не как 26, а как 2 и 6. Как это можно исправить?

наверное придётся добавлять в таблицу перед каждой цифрой пробел и после каждой запятую , и искать вместе с пробелом и запятой , например так
... " WHERE LOCATE(' " . $cat[0] . ",', category ) != 0"....

2provodnik

Все таки есть ошибка...

Если категория имеет идентификатор состоящий из двух цифр, например - 26, то его считают не как 26, а как 2 и 6. Как это можно исправить?

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

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

Я в принципе могу распарсить значение переменной по запятым, загнать в массив, сделать запросы относительно каждого значения этого массива и вывести данные... Но лучше рекомендую сделать адекватную структуру БД!

В чем проблема, взять и выполнить запрос. Навороченных запросов здесь нет, надо только разобраться. А если разбираться не хочется, это уже другой вопрос. Мой первый запрос возвращает:

1  Бассейны-----2 1  Бильярдные--1 1  Спортзалы--- 2 1  Стадионы-----4 1  Всего------------9 2  Галереи--------1  2  Зоопарки------2  2 Кинотеатры----2 2 Клубы------------5 2 Конц. залы-----3 2 Музеи------------4 2 Театры----------2 2 Всего-----------19 ............................... и т.д.

Поясню
1-ый столбец - это принадлежность к корню каталога ( 1 - Активный отдых, 2 - Афиша и т.д.)
2-ой - собсна сами подкаталоги
3-ий - подсчет количества эл-тов в каждом подкаталоге
После перечисления всех подкаталогов в каталоге ведется подсчет общего количества эл-тов в корне каталога.

и правда увлёкся и незаметил :) неделю теперь буду понимать почему-же работает Ваш код )
Спасибо

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

Это действительно так, с такими запросами не работал. Сейчас попробую еще раз ))

>> Но лучше рекомендую сделать адекватную структуру БД!

Как по Вашему она должна выглядеть, чтобы быть адекватной?

Как по Вашему она должна выглядеть, чтобы быть адекватной?
Оставьте структуру, но не назначайте одной фирме неколько категорий в одном поле БД. Сделайте это поле численным типом...

На моём портале, в каталоге фирм и организаций порядка 1500 записей.
И если организация пногопрофильная, и подходит и в категорию "Бассейны" и в категорию "Библиотеки", то она занесена в БД два раза, но как разные организации - как организация "бассейн" и как фирма "библиотека", и каждая в свою категорию...

По моему скромному мнению...

Обычно в таких случаях добавляют дополнительную таблицу-связку, в кот. указывается Id организации и id раздела, к кот. она относится.

Работает ))))

$query = "SELECT t.id_cat, ifnull(t.sub_cat, 'Всего') as name_catalog, count(c.id) as c_num  FROM (SELECT cat1.id id_cat, cat2.name sub_cat, cat2.id sub_cat_id FROM cat_catalog cat1 LEFT JOIN cat_catalog cat2  ON (cat1.id = cat2.subcat) WHERE cat1.subcat = 0) t LEFT JOIN  catalog c ON (t.sub_cat_id = c.category) GROUP BY t.id_cat, t.sub_cat WITH ROLLUP"; $result=mysql_query($query) or die("<B>Error ".mysql_errno()." :</B> ".mysql_error().""); if (mysql_num_rows($result)!=0) {  while($list = mysql_fetch_array($result)) { echo "".$list['name_catalog']." - ".$list['c_num']."<br>"; } }

А как сделать чтобы (Всего - 129, Всего - 45, и т.д.) не считались и не выводились? И категории интересует только те, где subcat=3 и все. Спасибо большое Вам. А двойные категории учитываются?

Наверное, обратить внимание на второй мой вышепривиденный запрос. Там немного подправить его нужно. Думаю сами додумаете.

Ваш второй запрос считает все, даже если там 0 элементов, а также к сожалению не считает двойные категории.

>>> Оставьте структуру, но не назначайте одной фирме неколько категорий в одном поле БД. Сделайте это поле численным типом...
Это не всегда возможно, например, в данном случае ))
Вообще все это для того чтобы сделать вот так - http://www.afisha.ru/msk/restaurants/restaurant_list/ но только для моего города.

kis-kis - прислушайтесь к Thrasher от 24.10.2007 в 12:41.
И обратите в конце концов на слова oradev ...

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

Просто я знаю что можно сделать так, как я говорю (но не знаю как это реализовать) . Так, например, работает, DataLife engine...

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

Откуда это? ))

Хотя должно быть наоборот

kis-kis - Вы о чем?
to oradev: развиваюсь, стремлюсь к совершенству. Но к сожалению учебники читать нет времени, лишь здесь, на форуме, помогая другим, смотря на чужие примеры кода что-то выхватываю, экспериментирую... Что-то откладывается, что-то ждет следующего примера...
Да и всегда навороченные запросы забирают времени и серверных ресурсов меньше, нежели ряд циклических и простых...

Прислушился к Thrasher ))

Можно ли эти запросы собрать в один?

$query = "SELECT category_id as cat, count(id) as num FROM 2cat_catalog where category_id <> '0' group by category_id"; $result = mysql_query($query) or die("<B>Error ".mysql_errno()." :</B> ".mysql_error().""); if (mysql_num_rows($result)!=0) {  while($cafe = mysql_fetch_array($result))  { $resultc=mysql_query("SELECT * FROM cat_catalog where id = '$cafe[cat]'") or die("<B>Error ".mysql_errno()." :</B> ".mysql_error().""); $rowcat = mysql_fetch_array($resultc); echo "<a href=\"".$rowcat['en_name']."/\">".$rowcat['name']."</a> (".$cafe['num'].")<br>"; } }

Спасибо.