Форум пользователей MySQL

Debian · 14.08.2013 19:36:14

Здравствуйте! Не знаю в правильную ли я тему пишу. Так что если что-то не туда то извините.

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

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

Что бы в итоге получилось вот так:

автомобиль (52), красный (42), BMW (12), дорога (9), мокрая (0)

Я эту задачку решил вот так:

Вот структура таблички:

CREATE TABLE IF NOT EXISTS `tags` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `image_id` int(10) unsigned NOT NULL,

  `tag` varchar(50) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `image_id` (`image_id`),

  KEY `tag` (`tag`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

А вот код с запросами.

Сразу поясню что тут может быть не понятно:
Метод - sql_fetch_assoc() это обычный fetch_assoc() только прогнанный через foreach(), чтобы получить сразу массив со всеми значениями.

Метод - sql_fetch_row() это обычный fetch_row().

Все остальное по моему понятно )

public function show_tags($id){

        $query = "SELECT `tag` FROM `tags` WHERE `image_id` = '$id'";

        if($tags = $this->sql_fetch_assoc($query, true)){

            $show_tags = '';

            foreach($tags as $tag){

                $query = "SELECT COUNT(*) FROM `tags` WHERE `tag` = '".$tag['tag']."' AND `image_id` != '$id'";

                if($tag_num = $this->sql_fetch_row($query)){

                    if($tag_num[0] == 0){

                        $show_tags .= $tag['tag'].'&nbsp;(0), ';

                    }else{

                        $show_tags .= '<a href="index.php?teg='.$tag['tag'].'">'.$tag['tag'].'</a>&nbsp;('.$tag_num[0].'), ';

                    }    

                }

            }

        }else{

            return false;

        }

        return preg_replace('/,\s$/', '', $show_tags);

    }

}

Но я так понимаю, что эту задачу можно решить намного проще, с помощью более сложных запросов. Потому, что во первых, тут 2 запроса, один из которых вообще в цикле, что очень плохо, получится довольно ресурсоёмкая задача, может можно тут хотябы как-то от цикла избавиться?.

Помогите пожалуйста если конечно это возможно решить задачку )

deadka · 14.08.2013 21:06:35

Предлагаю отделить php от sql.

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

SELECT `tag`, count(*) FROM `tags` GROUP BY `tag`;

Если хотите не на всю таблицу, а на конкретную картинку, то

SELECT `tag`, count(*) FROM `tags` WHERE image_id=АЙДИ_НУЖНОЙ_КАРТИНКИ GROUP BY `tag`;

Debian · 14.08.2013 21:31:18

Таким образом получается вот что:

mysql> SELECT `tag`, count(*) FROM `tags` GROUP BY `tag`;

+------------------------+----------+

| tag                    | count(*) |

+------------------------+----------+

| Бассейн                |        2 |

| бельё                  |        1 |

| блондинка              |        4 |

| брюнетка               |        2 |

| вода                   |        1 |

| девушка                |        6 |

| красивая               |        2 |

| красотка               |        3 |

| кровать                |        3 |

| купальник              |        1 |

| личико                 |        1 |

| мех                    |        1 |

| пол                    |        1 |

| попка                  |        1 |

| розовое                |        1 |

| розовые                |        1 |

| секс                   |        1 |

| сексуальная            |        2 |

| стол                   |        1 |

| чулки                  |        2 |

| шикарная               |        1 |

| эротика                |        1 |

+------------------------+----------+

22 rows in set (0.00 sec)

mysql> SELECT `tag`, count(*) FROM `tags` WHERE image_id=2 GROUP BY `tag`;

+--------------------+----------+

| tag                | count(*) |

+--------------------+----------+

| блондинка          |        1 |

| девушка            |        1 |

| красивая           |        1 |

| кровать            |        1 |

| эротика            |        1 |

+--------------------+----------+

5 rows in set (0.00 sec)

А как бы получить например так, чтобы было в результате, ну на пример как во втором варианте, только там показывает сколько слов относится к конкретно этой картинке, а хотелось бы чтобы было например также только в поле count(*), было общее количество этих слов в таблице, ну примерно вот так:

+--------------------+----------+

| tag                | count(*) |

+--------------------+----------+

| блондинка          |        4 |

| девушка            |        6 |

| красивая           |        2 |

| кровать            |        3 |

| эротика            |        1 |

+--------------------+----------+

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

Отредактированно Debian (14.08.2013 21:37:45)

deadka · 14.08.2013 21:35:28

Приведите простейший набор тестовых данных, то есть содержимое таблицы tags и чего Вы хотите получить оттуда. Без этого сложновато понять, что Вы хотите .

Debian · 14.08.2013 21:56:58

Постараюсь подробнее объяснить.

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

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

Допустим пользователь при загрузке, указал такие слова: машинка, красивая, дорога, мост, речка в табличку они будут писаться вот в таком виде:

mysql> select * from tags where image_id = 1;

+----+----------+--------------------+

| id | image_id | tag                |

+----+----------+--------------------+

|  1 |        1 | машинка           |

|  2 |        1 | красивая           |

|  3 |        1 | дорога            |

|  4 |        1 | мост               |

|  5 |        1 | речка         |

+----+----------+--------------------+

5 rows in set (0.00 sec)

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

машинка (52), красивая (42), дорога (12), мост (9), речка (0)

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

Вот мне и нужно сначало получить все теги, которые относятся к этой картинке, после чего посчитать сколько еще таких же слов есть в таблице. Что бы когда он нажал например на ссылку машинка (52) ему открылись 52 картинки со словом машинка.

Так понятнее?

Задача № 1 получить все теги которые относятся к конкретной картинке по ее id, который хранится в поле image_id, таблицы tags.

Задача № 2 посчитать сколько копий каждого из полученных слов есть еще в таблице.

Думаю теперь должно быть по понятнее.

Отредактированно Debian (14.08.2013 21:59:25)

Debian · 14.08.2013 22:08:39

Вот еще на всякий случай, как сейчас выглядит таблица tags, там просто мало, я несколько картинок для теста загрузил, с девушками .

mysql> select * from tags;

+----+----------+------------------------+

| id | image_id | tag                    |

+----+----------+------------------------+

|  1 |        1 | девушка                |

|  2 |        1 | красивая               |

|  3 |        1 | Бассейн                |

|  4 |        1 | вода                   |

|  5 |        1 | купальник              |

|  6 |        2 | блондинка              |

|  7 |        2 | кровать                |

|  8 |        2 | красивая               |

|  9 |        2 | девушка                |

| 10 |        2 | эротика                |

| 11 |        3 | красотка               |

| 12 |        3 | блондинка              |

| 13 |        3 | бассейн                |

| 14 |        3 | девушка                |

| 15 |        3 | бельё                  |

| 16 |        4 | шикарная               |

| 17 |        4 | блондинка              |

| 18 |        4 | девушка                |

| 19 |        4 | стол                   |

| 20 |        4 | розовое                |

| 21 |        4 | сексуальная            |

| 22 |        5 | пол                    |

| 23 |        5 | блондинка              |

| 24 |        5 | красотка               |

| 25 |        5 | личико                 |

| 26 |        5 | мех                    |

| 27 |        5 | секс                   |

| 28 |        6 | чулки                  |

| 29 |        6 | девушка                |

| 30 |        6 | попка                  |

| 31 |        6 | красотка               |

| 32 |        6 | брюнетка               |

| 33 |        7 | девушка                |

| 34 |        7 | кровать                |

| 35 |        7 | сексуальная            |

| 36 |        8 | брюнетка               |

| 37 |        8 | кровать                |

| 38 |        8 | чулки                  |

| 39 |        8 | розовые                |

+----+----------+------------------------+

39 rows in set (0.00 sec)

deadka · 14.08.2013 22:23:25

Вот этот запрос возвратит тэг и количество их в таблице - только тех тегов, которые указаны для image №2:

SELECT `tag`,count(*) FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 );

Debian · 14.08.2013 22:26:25

Это что-то не то, вот что вернуло.

mysql> SELECT `tag`,count(*) FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 );

+--------------------+----------+

| tag                | count(*) |

+--------------------+----------+

| блондинка          |       16 |

+--------------------+----------+

1 row in set (0.04 sec)

Debian · 14.08.2013 22:28:11

Там и слово блондинка то всего 4 раза повторяется, как вы сами можете подсчитать в таблице выше. От куда он насчитал 16? Что это он вообще посчитал не понятно И почему он вообще вернул слово блондинка, ведь об этом не где в запросе нет упоминаний )

Отредактированно Debian (14.08.2013 22:29:17)

deadka · 14.08.2013 22:38:07

Да, я недописал немного запрос (по хорошему он вообще не должен был выполниться, это уже тонкие особенности дельфина ).

SELECT `tag`,count(*) FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 ) GROUP BY `tag`;

Debian · 14.08.2013 22:45:19

Во вот это я понимаю супер, пупер запрос Ураааааа, спасибочки вам огроменное, вот это я понимаю форум. Я даже не сомневался что тут мне профессионально помогут. Рад что нашел ваш форум, буду постояльцем Я просто предварительно почитал тут у вас несколько тем и понял что лучше чем тут профи не где нет, странно что ваш форум такой не популярный, он должен в поисковиках быть на первом месте по запросу MySQL и т.д. Вот не справедливо ) Еще раз спасибочки Тему если можно пока не закрывайте, я просто еще сейчас или уже завтра буду с этим работать, может еще вопрос по этой теме будет. Класс Не могу нарадоваться!

deadka · 14.08.2013 22:49:47

Спасибо на добром слове, обращайтесь . А поисковик по запросу mysql на первом месте выдаёт mysql.com, но это как бе логично.

Debian · 14.08.2013 22:55:44

Нет, просто я по MySQL уже давно ищу всякую информацию, в процессе обучения, всех web технологий, а это уже как бы не один год, и наткнулся на ваш сайт только вчера. Я вот о чем, что на такой классный сайт хотелось бы попасть сразу, много бы головной боли лишился. И быстрее чему-то научился Еще раз огромное спасибо за ваш труд.

Debian · 14.08.2013 23:04:49

Вот что в итоге возвращает. Просто шикарно

mysql> SELECT `tag`,count(*) FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 ) GROUP BY `tag`;

+--------------------+----------+

| tag                | count(*) |

+--------------------+----------+

| блондинка          |        4 |

| девушка            |        6 |

| красивая           |        2 |

| кровать            |        3 |

| эротика            |        1 |

+--------------------+----------+

5 rows in set (0.00 sec)

var_dump

array(5) {

  [0]=>

  array(2) {

    ["tag"]=>

    string(18) "блондинка"

    ["count(*)"]=>

    string(1) "4"

  }

  [1]=>

  array(2) {

    ["tag"]=>

    string(14) "девушка"

    ["count(*)"]=>

    string(1) "6"

  }

  [2]=>

  array(2) {

    ["tag"]=>

    string(16) "красивая"

    ["count(*)"]=>

    string(1) "2"

  }

  [3]=>

  array(2) {

    ["tag"]=>

    string(14) "кровать"

    ["count(*)"]=>

    string(1) "3"

  }

  [4]=>

  array(2) {

    ["tag"]=>

    string(14) "эротика"

    ["count(*)"]=>

    string(1) "1"

  }

}

Отредактированно Debian (14.08.2013 23:05:39)

Debian · 14.08.2013 23:10:30

А нет ли у MySQL такой возможности чтобы он возвращал поле count(*) с другим именем, ну например num? Это конечно не столь важно просто интересно в целях обучения, ну и так было бы в коде более удобно обращаться к данным.

deadka · 14.08.2013 23:18:25

Да, есть. таблицы тоже можно "переименовывать" в запросе.

SELECT count(t.*) as num FROM tabl AS t WHERE...;

Debian · 14.08.2013 23:23:41

Супер Я про AS читал, но думал что он делает псевдонимы к которым можно обращаться только в запросе, но не думал что он и в результат вернет псевдоним. Дополнил ваш запросик. Вроде правильно?

mysql> SELECT `tag`, count(*) AS `num` FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 ) GROUP BY `tag`;

+--------------------+-----+

| tag                | num |

+--------------------+-----+

| блондинка          |   4 |

| девушка            |   6 |

| красивая           |   2 |

| кровать            |   3 |

| эротика            |   1 |

+--------------------+-----+

5 rows in set (0.02 sec)

Спасибочки, вот еще чему-то научился

deadka · 14.08.2013 23:27:08

Как видим, правильно - колонка ведь num теперь называется, а не count(*).

Debian · 14.08.2013 23:38:07

Еще вопросик возник, он их выводит вот так:

+--------------------+-----+

| tag                | num |

+--------------------+-----+

| блондинка          |   4 |

| девушка            |   6 |

| красивая           |   2 |

| кровать            |   3 |

| эротика            |   1 |

+--------------------+-----+

А в базе они хранятся вот так:

mysql> SELECT * FROM `tags` WHERE `image_id` = 2;

+----+----------+--------------------+

| id | image_id | tag                |

+----+----------+--------------------+

|  6 |        2 | блондинка          |

|  7 |        2 | кровать            |

|  8 |        2 | красивая           |

|  9 |        2 | девушка            |

| 10 |        2 | эротика            |

+----+----------+--------------------+

5 rows in set (0.00 sec)

Можно ли как-то, при этом еще оставить тот порядок, в котором они в базе?

deadka · 14.08.2013 23:47:29

Тот порядок, в котором они в базе нам (если исходить из стандарта ANSI SQL) неведом и мы не должны делать никаких предположений об этом. Но мы можем получать данные в том порядке, который нужен нам.

SELECT * FROM `tags` WHERE `image_id` = 2 ORDER BY id ASC;
SELECT * FROM `tags` WHERE `image_id` = 2 ORDER BY image_id DESC;
SELECT * FROM `tags` WHERE `image_id` = 2 ORDER BY id ASC, image_id DESC;

Debian · 14.08.2013 23:59:28

Я просто про ORDER BY знаю, но попробовал его впихнуть в ваш запрос, а оно не то.

mysql> SELECT `tag`, count(*) AS `count` FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 ) GROUP BY `tag` ORDER BY `id` DESC;

+--------------------+-------+

| tag                | count |

+--------------------+-------+

| эротика            |     1 |

| кровать            |     3 |

| блондинка          |     4 |

| красивая           |     2 |

| девушка            |     6 |

+--------------------+-------+

5 rows in set (0.00 sec)

mysql> SELECT `tag`, count(*) AS `count` FROM `tags` WHERE `tag` IN ( SELECT `tag` FROM `tags` WHERE `image_id` = 2 ) GROUP BY `tag` ORDER BY `id` ASC;

+--------------------+-------+

| tag                | count |

+--------------------+-------+

| девушка            |     6 |

| красивая           |     2 |

| блондинка          |     4 |

| кровать            |     3 |

| эротика            |     1 |

+--------------------+-------+

5 rows in set (0.00 sec)

Там наверно из-за GROUP BY не получается, я просто не знаю как он работает, нужно будет почитать.

deadka · 15.08.2013 00:06:32

Вы пытаетесь сортировать по id, а такой колонки ведь нету в выборке итоговой.
Эту выборку можно отсортировать либо по tag либо по count.

Debian · 15.08.2013 00:15:25

Тогда выходит что не получится? Нужно как раз по id сортировать. Что бы они вывелись в том порядке, как их вписывал пользователь. Это как бы не критично но желательно.

vasya · 15.08.2013 12:27:49

1. Подзапрос в части in лучше всегда переписывать через join (для производительности).

2. Для сортировки по id это необходимо.

SELECT t.tag, count(*) FROM `tags` p

JOIN ( SELECT id, `tag` FROM `tags` WHERE `image_id` = 2 ) t

ON p.tag=t.tag

GROUP BY t.tag ORDER BY t.id;

Debian · 15.08.2013 14:20:38

Супер Большое спасибо Ребят а мне нужна еще помощь, тоже нужно составить правильный запрос, теперь нужно, делать поиск картинок, по этим ключевым словам Продолжить эту тему, или создать новую?

Форум пользователей MySQL

#1 14.08.2013 19:36:14

Подсчитать количество одинаковых тегов.

#2 14.08.2013 21:06:35

Re: Подсчитать количество одинаковых тегов.

#3 14.08.2013 21:31:18

Re: Подсчитать количество одинаковых тегов.

#4 14.08.2013 21:35:28

Re: Подсчитать количество одинаковых тегов.

#5 14.08.2013 21:56:58

Re: Подсчитать количество одинаковых тегов.

#6 14.08.2013 22:08:39

Re: Подсчитать количество одинаковых тегов.

#7 14.08.2013 22:23:25

Re: Подсчитать количество одинаковых тегов.

#8 14.08.2013 22:26:25

Re: Подсчитать количество одинаковых тегов.

#9 14.08.2013 22:28:11

Re: Подсчитать количество одинаковых тегов.

#10 14.08.2013 22:38:07

Re: Подсчитать количество одинаковых тегов.

#11 14.08.2013 22:45:19

Re: Подсчитать количество одинаковых тегов.

#12 14.08.2013 22:49:47

Re: Подсчитать количество одинаковых тегов.

#13 14.08.2013 22:55:44

Re: Подсчитать количество одинаковых тегов.

#14 14.08.2013 23:04:49

Re: Подсчитать количество одинаковых тегов.

#15 14.08.2013 23:10:30

Re: Подсчитать количество одинаковых тегов.

#16 14.08.2013 23:18:25

Re: Подсчитать количество одинаковых тегов.

#17 14.08.2013 23:23:41

Re: Подсчитать количество одинаковых тегов.

#18 14.08.2013 23:27:08

Re: Подсчитать количество одинаковых тегов.

#19 14.08.2013 23:38:07

Re: Подсчитать количество одинаковых тегов.

#20 14.08.2013 23:47:29

Re: Подсчитать количество одинаковых тегов.

#21 14.08.2013 23:59:28

Re: Подсчитать количество одинаковых тегов.

#22 15.08.2013 00:06:32

Re: Подсчитать количество одинаковых тегов.

#23 15.08.2013 00:15:25

Re: Подсчитать количество одинаковых тегов.

#24 15.08.2013 12:27:49

Re: Подсчитать количество одинаковых тегов.

#25 15.08.2013 14:20:38

Re: Подсчитать количество одинаковых тегов.

Board footer