Меню
Что нового?

Поиск похожих картинок(новостей) в БД MySQL по ключевым словам.

Macintosh

Macintosh

Новичок
Всем приветик!

Ребята, помогите пожалуйста упростить метод, и вообще объясните где я не прав, и не ужели, вот то, что я тут придумал, так делают все?
Уж очень ресурсоёмкая задача получается, или может получаться, в зависимости от того, как быстро найдётся нужная информация в БД.

Задача:

Есть таблица в которой хранится информация об изображениях.
На сайте, когда открываешь одно из изображений, нужно показать рядом еще 3 похожих изображения.
Тоесть, в базе нужно найти похожие поля. Сначала проверяем по ключевым словам, в тойже категории, сначала более старые(ранее загруженные) изображения, если не найдет 3 похожих изображения, ищем в обратном порядке, более новые(позже загруженные), если не насобиралось 3 похожих изображений, то повторяем процедуру, только уже во всех остальных категориях. Может быть уже сам принцип поиска не верен? И это как-то делают по другому?

В общем вот что у меня получилось:

Немного поясню что тут чего:

$id - содержит id изображения.
$keywords - содержит строку ключевых слов, типа 'девушка, платье, красотка, блондинка'.
$category - id категории.
sql_fetch_assoc - метод из другого класса, который из БД возвращает массив массивов. Тоесть, fetch_assoc() прогнанный через foreach.

PHP: 
public function similar_images($id, $keywords, $category){
	$keywords = explode(", ", $keywords);
	$similar_images = array();
	$limit = 3;
	$count = 1;

	while(count($similar_images) != 3 and $count != 5){
		foreach($keywords as $keyword){
			if($count == 1){
				$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
													 AND `category_id` = '$category' AND `id` < '$id' ORDER BY `id` DESC LIMIT $limit";
			}else if($count == 2){
				$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
												 	 AND `category_id` = '$category' AND `id` > '$id' ORDER BY `id` ASC LIMIT $limit";
			}else if($count == 3){
				$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
													 AND `id` < '$id' ORDER BY `id` DESC LIMIT $limit";
			}else if($count == 4){
				$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
													 AND `id` > '$id' ORDER BY `id` ASC LIMIT $limit";
			}
			
			if($similars = $this->sql_fetch_assoc($query, true)){
				foreach($similars as $similar){
					if($limit != 3){
						$check = true;
						
						foreach($similar_images as $image){
							if(in_array($similar['id'], $image)){
								$check = false;
								break;
							}
						}
						
						if($check){
							$similar_images[] = $similar;
							$limit--;
						}								
					}else{
						$similar_images[] = $similar;
						$limit--;
					}
				}
				
				if(count($similar_images) == 3){
					return $similar_images;
				}
			}
		}
		
		$count++;
	}
	
	return $similar_images;
}
Делаю такую задачу в первый раз, так что если полный бред сильно не ругайте )) Буду рад любым замечаниям и советам.
 
Macintosh

Macintosh

Новичок
Уже появился баг, пока не знаю где, но учитывайте )))) может кто-то найдет ) В одном из изображений не выводит 3 похожих картинки, хотя по словам, должен находить, странно, но на других с похожими словами находит 3. ))
 
AmdY

AmdY

Пью пиво
Команда форума
лучше уж fulltext поиск заюзай, чем регэкспами, не будет проблем с окончаниями.
 
Macintosh

Macintosh

Новичок
AmdY написал(а):
лучше уж fulltext поиск заюзай, чем регэкспами, не будет проблем с окончаниями.
Нажмите для раскрытия...
Не пользовался не разу, в google глянул, и вот на что сразу наткнулся, цитирую:

Если у тебя InnoDB, то проблема в отсутствии поддержки FULLTEXT. Не существует в InnoDB этого.
Нажмите для раскрытия...
Я использую InnoDB.
 
Macintosh

Macintosh

Новичок
Проблему нашел, не правильная логика с $limit. Буду дорабатывать пока )
 
Macintosh

Macintosh

Новичок
Да и с $check там не правильная логика, в общем буду переделывать, потом выложу новый вариант, поспешил я с сырым кодом )) Прошу прощения )
 
Macintosh

Macintosh

Новичок
Таксь ) Вроде все исправил, заодно и упростил немного. Теперь можно анализировать, что где можно улучшить?

PHP: 
		public function similar_images($id, $keywords, $category){
			$keywords = explode(", ", $keywords);
			$similar_images = array();
			$count = 1;
			
			while(count($similar_images) != 3 and $count != 5){
				foreach($keywords as $keyword){
					if($count == 1){
						$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
															 AND `category_id` = '$category' AND `id` < '$id' ORDER BY `id` DESC";
					}else if($count == 2){
						$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
														 	 AND `category_id` = '$category' AND `id` > '$id' ORDER BY `id` ASC";
					}else if($count == 3){
						$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
															 AND `id` < '$id' ORDER BY `id` DESC";
					}else if($count == 4){
						$query = "SELECT `id`, `path_small` FROM `images` WHERE `keywords` REGEXP '[[:<:]]".$keyword."[[:>:]]' 
															 AND `id` > '$id' ORDER BY `id` ASC";
					}
					
					if($similars = $this->sql_fetch_assoc($query, true)){
						foreach($similars as $similar){
							if(empty($similar_images)){
								$similar_images[] = $similar;
							}else{
								$check = false;
								
								foreach($similar_images as $image){
									if(!in_array($similar['id'], $image)){
										$check = false;
									}else{
										$check = true;
										break;
									}
								}
								
								if(!$check){
									if(count($similar_images) != 3){
										$similar_images[] = $similar;
									}
								}
							}
						}
					}
				}
				
				$count++;
			}
			
			return $similar_images;
		}
 
WMix

WMix

герр M:)ller
Партнер клуба
если тэги вытащить в отдельную табличку то весь пойск можно свести к одному запросу IN( )
 
Macintosh

Macintosh

Новичок
WMix написал(а):
если тэги вытащить в отдельную табличку то весь пойск можно свести к одному запросу IN( )
Нажмите для раскрытия...
Не совсем понимаю, как это повлияет в данном случае? Ну тоесть, все равно придется там писать к какому изображению принадлежат эти ключевые слова, к какой категории они принадлежат. И запрос получится примерно такойже, тоесть придется делать все это:
Тоесть, в базе нужно найти похожие поля. Сначала проверяем по ключевым словам, в тойже категории, сначала более старые(ранее загруженные) изображения, если не найдет 3 похожих изображения, ищем в обратном порядке, более новые(позже загруженные), если не насобиралось 3 похожих изображений, то повторяем процедуру, только уже во всех остальных категориях.
Нажмите для раскрытия...
 
WMix

WMix

герр M:)ller
Партнер клуба
я предлагаю эту вереницу комманд
Macintosh написал(а):
PHP: 
foreach($keywords as $keyword){...}
Нажмите для раскрытия...
заменить на готовый ответ по индексному столбику
PHP: 
select  image_id  /* выбрать индефикаторы картинок (тут можно при надобности заджоинить) */
from keywords 
where keyword in ( /* кде ключевые слова такие же как и */
    select keyword  /* выбрать ключевые слова */
    from keywords
    where image_id = $id /* выбранной картинки */
) and image_id != $id /* без выбранной картинки */
group by image_id /* по индефикатору на строчку */
order by count(*) desc /* сортировка по наибольшему количеству найденых слов*/
limit 3
 
Последнее редактирование:
Macintosh

Macintosh

Новичок
У меня просто еще не такой большой опыт, я не пользовался IN() не разу, как и многими другими конструкциями, понять бы еще логику вашего запроса, почитаю про IN() в документации, и интернете, попробую разобраться ))) Тоесть, что, таким запросом, можно практически заменить весь мой код? Я правильно понял, и логика будет таже?
 
WMix

WMix

герр M:)ller
Партнер клуба
при условии что ключевые слова будут в отдельной табличке по слову на строчку
PHP: 
CREATE TABLE IF NOT EXISTS `keywords` (
  `id` int(10) unsigned NOT NULL,
  `image_id` int(10) unsigned NOT NULL,
  `keyword` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `keyword` (`keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
Macintosh

Macintosh

Новичок
По слову на строчку? Так у меня, к каждой картинке, может быть большое количество ключевых слов! )
 
Macintosh

Macintosh

Новичок
И я так понял, хотя если честно пока не врублюсь вообще в логику и как это реализовать, но я так понял, что тут не учитывается категория, и как это по одному слову на строчку, как же это вообще реализовывать? У каждой картинки может быть от 3 до 12 ключевых слов, брррррррррррр сложно, простите, но не врублюсь ((
 
hell0w0rd

hell0w0rd

Продвинутый новичок
Macintosh
Ну вот я хотел себе rss ридер написать, вот структура источник-тэг, связь many-to-many
PHP: 
CREATE TABLE `tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL DEFAULT '',
  `color` char(6) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PHP: 
CREATE TABLE `sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL,
  `url` text NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Связующая таблица
PHP: 
CREATE TABLE `source_to_tag` (
  `source` int(11) unsigned NOT NULL,
  `tag` int(11) unsigned NOT NULL,
  KEY `source` (`source`),
  KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Не уверен что все идеально, вот на пример запрос на выборку всех урлов источников, с тегом программирование
PHP: 
SELECT sources.url
FROM sources
JOIN source_to_tag AS stt ON stt.source = sources.id
JOIN tags ON stt.tag = tags.id
WHERE tags.name = 'программирование'
Тебе нужно примерно то же самое, только в качестве источников может выступать любая другая таблица данных, которые можно размечать тегами
 
WMix

WMix

герр M:)ller
Партнер клуба
да хоть 500 слов, в чем проблема?, будет 500 строк! попробуй сделать без категорий, понять как работает, а после усложним запрос с условием категорий (просто добавить join и изменить order by)
 
Последнее редактирование:
WMix

WMix

герр M:)ller
Партнер клуба
hell0w0rd
можно усложнить еще на одну табличку, но это просто дополнительное усложнение. типа слова - теги вынести в отдельную табличку дабы не повторять.
 
Последнее редактирование:
hell0w0rd

hell0w0rd

Продвинутый новичок
WMix
Не понял. Теги и так же в отдельной табличке у меня?
 
Войдите или зарегистрируйтесь для ответа.
Сверху