✔ [SQL] Suche mit Tags - sortieren nach Anz. der Treffer

[Julian Maicher]

Hallo!

Folgendes Szenario: Eine Tabelle mit den Spalten `tag1`, `tag2` und `tag3` in den jeweils Tags (also Schlagwörter) stehen.
Sucheingabe: Handy Sony k750i schwarz

Jetzt will ich folgende Ausgabe erzeugen:
tag1: handy, tag2: bla, tag3: schwarz => 2 Treffer
tag1: handy, tag2: sony, tag3: schwarz => 3 Treffer
tag1: bla, tag2: bla, tag3: bla => 0 Treffer

Es soll absteigend nach den meisten Treffern geordnet werden. Wie realisiere ich sowas?

 

[Julian Maicher]

Fall es noch jmd. braucht:

Das SQL-Statement

SELECT `tag1`, `tag2`, `tag3`, IF((`tag1` LIKE 'Handy' AND `tag2` LIKE 'Sony' AND `tag3` LIKE 'k750i') OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'Sony' AND `tag3` LIKE 'schwarz') OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'k750i' AND `tag3` LIKE 'Sony') OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'k750i' AND `tag3` LIKE 'schwarz') OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'schwarz' AND `tag3` LIKE 'Sony') OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'schwarz' AND `tag3` LIKE 'k750i') OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'Handy' AND `tag3` LIKE 'k750i') OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'Handy' AND `tag3` LIKE 'schwarz') OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'k750i' AND `tag3` LIKE 'Handy') OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'k750i' AND `tag3` LIKE 'schwarz') OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'schwarz' AND `tag3` LIKE 'Handy') OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'schwarz' AND `tag3` LIKE 'k750i') OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'Handy' AND `tag3` LIKE 'Sony') OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'Handy' AND `tag3` LIKE 'schwarz') OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'Sony' AND `tag3` LIKE 'Handy') OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'Sony' AND `tag3` LIKE 'schwarz') OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'schwarz' AND `tag3` LIKE 'Handy') OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'schwarz' AND `tag3` LIKE 'Sony') OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'Handy' AND `tag3` LIKE 'Sony') OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'Handy' AND `tag3` LIKE 'k750i') OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'Sony' AND `tag3` LIKE 'Handy') OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'Sony' AND `tag3` LIKE 'k750i') OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'k750i' AND `tag3` LIKE 'Handy') OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'k750i' AND `tag3` LIKE 'Sony'), 3, IF((`tag1` LIKE 'Handy' AND `tag2` LIKE 'Sony') OR (`tag2` LIKE 'Handy' AND `tag3` LIKE 'Sony') OR (`tag3` LIKE 'Handy' AND `tag1` LIKE 'Sony' ) OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'k750i') OR (`tag2` LIKE 'Handy' AND `tag3` LIKE 'k750i') OR (`tag3` LIKE 'Handy' AND `tag1` LIKE 'k750i' ) OR (`tag1` LIKE 'Handy' AND `tag2` LIKE 'schwarz') OR (`tag2` LIKE 'Handy' AND `tag3` LIKE 'schwarz') OR (`tag3` LIKE 'Handy' AND `tag1` LIKE 'schwarz' ) OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'Handy') OR (`tag2` LIKE 'Sony' AND `tag3` LIKE 'Handy') OR (`tag3` LIKE 'Sony' AND `tag1` LIKE 'Handy' ) OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'k750i') OR (`tag2` LIKE 'Sony' AND `tag3` LIKE 'k750i') OR (`tag3` LIKE 'Sony' AND `tag1` LIKE 'k750i' ) OR (`tag1` LIKE 'Sony' AND `tag2` LIKE 'schwarz') OR (`tag2` LIKE 'Sony' AND `tag3` LIKE 'schwarz') OR (`tag3` LIKE 'Sony' AND `tag1` LIKE 'schwarz' ) OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'Handy') OR (`tag2` LIKE 'k750i' AND `tag3` LIKE 'Handy') OR (`tag3` LIKE 'k750i' AND `tag1` LIKE 'Handy' ) OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'Sony') OR (`tag2` LIKE 'k750i' AND `tag3` LIKE 'Sony') OR (`tag3` LIKE 'k750i' AND `tag1` LIKE 'Sony' ) OR (`tag1` LIKE 'k750i' AND `tag2` LIKE 'schwarz') OR (`tag2` LIKE 'k750i' AND `tag3` LIKE 'schwarz') OR (`tag3` LIKE 'k750i' AND `tag1` LIKE 'schwarz' ) OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'Handy') OR (`tag2` LIKE 'schwarz' AND `tag3` LIKE 'Handy') OR (`tag3` LIKE 'schwarz' AND `tag1` LIKE 'Handy' ) OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'Sony') OR (`tag2` LIKE 'schwarz' AND `tag3` LIKE 'Sony') OR (`tag3` LIKE 'schwarz' AND `tag1` LIKE 'Sony' ) OR (`tag1` LIKE 'schwarz' AND `tag2` LIKE 'k750i') OR (`tag2` LIKE 'schwarz' AND `tag3` LIKE 'k750i') OR (`tag3` LIKE 'schwarz' AND `tag1` LIKE 'k750i' ), 2, IF((`tag1` LIKE 'Handy' OR `tag2` LIKE 'Handy' OR `tag3` LIKE 'Handy') OR (`tag1` LIKE 'Sony' OR `tag2` LIKE 'Sony' OR `tag3` LIKE 'Sony') OR (`tag1` LIKE 'k750i' OR `tag2` LIKE 'k750i' OR `tag3` LIKE 'k750i') OR (`tag1` LIKE 'schwarz' OR `tag2` LIKE 'schwarz' OR `tag3` LIKE 'schwarz'), 1, 0))) AS anzTreffer FROM offer HAVING anzTreffer > 0 ORDER BY anzTreffer DESC

PHP-Script zur Generierung:

$keywords = $_POST['keywords'];
				$keyword_array = split(" ", $keywords);
				$keyword_array_length = count($keyword_array);
				
				if($keyword_array_length < 2) {
					// WENN NUR 1 SUCHWORT EINGEGEBEN WURDE
					$sql = "SELECT `tag1`, `tag2`, `tag3`, IF(`tag1` LIKE '".$keyword_array[0]."' OR `tag2` LIKE '".$keyword_array[0]."' OR `tag3` LIKE '".$keyword_array[0]."', 1, 0) AS anzTreffer FROM offer HAVING anzTreffer > 0 ORDER BY anzTreffer DESC";
				} else {
					// WENN >= 2 SUCHWÖRTER EINGEGEBEN WURDE
					for($i=0;$i<$keyword_array_length;$i++) {
						if($if_2match != "") $if_1match .= " OR ";
						$if_1match .= "(`tag1` LIKE '".$keyword_array[$i]."' OR `tag2` LIKE '".$keyword_array[$i]."' OR `tag3` LIKE '".$keyword_array[$i]."')";
						for($j=0;$j<$keyword_array_length;$j++) {
							if($i != $j) {
								if($if_2match != "") $if_2match .= " OR ";
								$if_2match .=  "(`tag1` LIKE '".$keyword_array[$i]."' AND `tag2` LIKE '".$keyword_array[$j]."')";
								$if_2match .=  " OR (`tag2` LIKE '".$keyword_array[$i]."' AND `tag3` LIKE '".$keyword_array[$j]."')";
								$if_2match .=  " OR (`tag3` LIKE '".$keyword_array[$i]."' AND `tag1` LIKE '".$keyword_array[$j]."' )";
								for($k=0;$k<$keyword_array_length;$k++) {
										if($i != $k && $i != $j && $j != $k) {
											if($if_3match != "") $if_3match .= " OR ";
											$if_3match .= "(`tag1` LIKE '".$keyword_array[$i]."' AND `tag2` LIKE '".$keyword_array[$j]."' AND `tag3` LIKE '".$keyword_array[$k]."')";
										}
								}
							}
						}
					}
					if($keyword_array_length > 2)
						$sql = "SELECT `tag1`, `tag2`, `tag3`, IF(".$if_3match.", 3, IF(".$if_2match.", 2, IF(".$if_1match.", 1, 0))) AS anzTreffer FROM offer HAVING anzTreffer > 0 ORDER BY anzTreffer DESC";
					else
						$sql = "SELECT `tag1`, `tag2`, `tag3`, IF(".$if_2match.", 2, IF(".$if_1match.", 1, 0)) AS anzTreffer FROM offer HAVING anzTreffer > 0 ORDER BY anzTreffer DESC";
				}

Falls jmd. eine bessere, schnellere Lösung hat - immer her damit!