MySQL: Suchfunktion in Verbindung mit Checkboxen

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Leider habe ich schon wieder ein MySQL Problem , an welchem ich mir die Zähne ausbeiße. Es ist - versprochen - das letzte dieser Art. Konkret möchte ich Checkboxen in die Suchfunktion meiner Internetseite einbauen. Zu Demonstrationszwecken habe ich eine kleine Datenbank erstellt, welche Musiker, deren Herkunftsland (Anname 1:n Beziehung) und die jeweilige Musikrichtung beinhaltet (m:n Beziehung):

±-------------±--------------------±--------+
| interpret_id | interpret_name | land_id |
±-------------±--------------------±--------+
| 1 | Green Blues Band | 1 |
| 2 | Pop Big Band Bern | 3 |
| 3 | Jon Doe Crew | 1 |
| 4 | Pop Sound Orchestra | 3 |
| 5 | Hans Maier Band | 2 |
±-------------±--------------------±--------+ (Tabelle: interpret)

±--------±------------+
| land_id | land_name |
±--------±------------+
| 1 | Deutschland |
| 2 | Österreich |
| 3 | Schweiz |
±--------±------------+ (Tabelle: land)

±---------±-----------+
| genre_id | genre_name |
±---------±-----------+
| 1 | Blues |
| 2 | Pop |
| 3 | Rock |
| 4 | Soul |
±---------±-----------+ (Tabelle: genre)

±-------------±---------+
| interpret_id | genre_id |
±-------------±---------+
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 3 | 4 |
| 4 | 2 |
| 5 | 2 |
| 5 | 3 |
±-------------±---------+ (Tabelle: interpret_genre)

Dazu mein Suchformular (vereinfacht):

Interpret (Input Text):

Land (Select):
DeutschlandÖsterreichSchweiz

Genre (Checkbox):
Blues
Pop
Rock
Soul

Und schließlich mein PHP Skript (wiederum vereinfacht):

<?php $interpret_name = $_GET['interpret_name'];
$land\_id = $\_GET['land\_id']; // leere Checkboxen werden anscheinend nicht übergeben if isset ($\_GET['genre\_id']) { $genre\_id = $\_GET['genre\_id']; } else { $genre\_id = ''; $db = new mysqli ('localhost', 'root', '', 'musik'); $query = "select interpret.\*, land.land\_name, group\_concat(distinct genre.genre\_id separator ', ') as genre\_id, group\_concat(distinct genre.genre\_name order by genre.genre\_id separator ', ') as genre\_name from interpret join land on interpret.land\_id = land.land\_id join interpret\_genre on interpret.interpret\_id = interpret\_genre.interpret\_id join genre on interpret\_genre.genre\_id = genre.genre\_id where (interpret.interpret\_name like '%$interpret\_name%' or '' = '$interpret\_name') and (land.land\_id = '$land\_id' or '' = '$land\_id')"; $result-\>query($query); ?\> Leider verstehe ich nicht, wie die Checkboxen in die Suche eingebracht werden können. Die Checkboxen sollen mit einem "oder" verknüpft sein (z.B.: zeige alle Rock ODER Blues Musiker aus Deutschland). Ich vermute, die Suche muss über zwei Abfragen realisiert werden. Zuerst werden die passenden Interpreten (interpret\_id) gesucht und schließlich werden alle Daten mithilfe der gefundenen Interpreten (interpret\_id) ausgegeben. Liege ich mit dieser Vermutung richtig? Hat bereits jemand hier im Forum eine Suchfunktion mit Checkboxen erstellt und hat den ein oder anderen Tipp für mich? Schöne Grüße
#2

Hier noch meine Datenbank:

create database musik;

use musik;

create table interpret
(
interpret_id int unsigned not null auto_increment primary key,
interpret_name varchar(30) not null,
land_id tinyint unsigned not null references land(land_id)
) engine = InnoDB;

create table land
(
land_id tinyint unsigned not null primary key,
land_name varchar(30) not null
) engine = InnoDB;

create table genre
(
genre_id tinyint unsigned not null primary key,
genre_name varchar(30) not null
) engine = InnoDB;

create table interpret_genre
(
interpret_id int unsigned not null references interpret(interpret_id),
genre_id tinyint unsigned not null references genre(genre_id),
primary key (interpret_id, genre_id)
) engine = InnoDB;

insert into interpret values
(’’, ‚Green Blues Band‘, ‚1‘),
(’’, ‚Pop Big Band Bern‘, ‚3‘),
(’’, ‚Jon Doe Crew‘, ‚1‘),
(’’, ‚Pop Sound Orchestra‘, ‚3‘),
(’’, ‚Hans Maier Band‘, ‚2‘);

insert into land values
(‚1‘, ‚Deutschland‘),
(‚2‘, ‚Österreich‘),
(‚3‘, ‚Schweiz‘);

insert into genre values
(‚1‘, ‚Blues‘),
(‚2‘, ‚Pop‘),
(‚3‘, ‚Rock‘),
(‚4‘, ‚Soul‘);

insert into interpret_genre values
(‚1‘, ‚1‘),
(‚1‘, ‚3‘),
(‚2‘, ‚2‘),
(‚3‘, ‚3‘),
(‚3‘, ‚4‘),
(‚4‘, ‚2‘),
(‚5‘, ‚2‘),
(‚5‘, ‚3‘);

#3

Hmm , wie geht man sowas an .

Also es gibt sowas wie

SELECT * FROM tabelle WHERE feld IN (‚Blues‘,‚Rock‘)

MYSQL IN AND BETWEEN

#4

Danke, das funktioniert grundsätzlich tatsächlich. Meine PHP Skript sieht nun so aus:

<?php $interpret_name = $_GET['interpret_name'];<br />$land\_id = $\_GET['land\_id'];
if isset ($\_GET['genre\_id']) {
$genre\_id = $\_GET['genre\_id'];
**$genre\_id = join(', ', $genre\_id);  
} else {  
$genre\_id = '';  
  
$db = new mysqli ('localhost', 'root', '', 'musik');  
  
$query = "select interpret.\*, land.land\_name,  
group\_concat(distinct genre.genre\_id separator ', ') as genre\_id,  
group\_concat(distinct genre.genre\_name order by genre.genre\_id separator ', ') as genre\_name  
from interpret  
join land  
on interpret.land\_id = land.land\_id  
join interpret\_genre  
on interpret.interpret\_id = interpret\_genre.interpret\_id  
join genre  
on interpret\_genre.genre\_id = genre.genre\_id  
where (interpret.interpret\_name like '%$interpret%' or '' = '$interpret')  
and (land.land\_id = '$land\_id' or '' = '$land\_id')  
and (interpret\_genre.genre\_id in ('$genre\_id') or '' = '$genre\_id')";  
  
$result-\>query($query);  
  
?\>**

Suche ich etwa nach einem Interpreten aus Deutschland mit „Band“ im Namen, welcher „Blues“ spielt erhalte ich folgende Ausgabe:

+--------------+------------------+---------+-------------+----------+------------+
| interpret\_id | interpret\_name | land\_id | land\_name | genre\_id | genre\_name |
+--------------+------------------+---------+-------------+----------+------------+
| 1 | Green Blues Band | 1 | Deutschland | 1 | Blues |
+--------------+------------------+---------+-------------+----------+------------+

Das Ergebnis ist generell richtig, allerdings wird mir mit meiner Abfrage „unterschlagen“, dass die „Green Blues Band“ auch „Rock“ in ihrem Repertoire hat. Ich werde mein Problem also wie anfangs bereits vermutet mit zwei Abfragen lösen.

Suche jenes Datensatz (interpret_id), welcher alle Eingaben erfüllt:

select interpret.interpret_id
from interpret
join land
on interpret.land_id = land.land_id
join interpret_genre
on interpret.interpret_id = interpret_genre.interpret_id
join genre
on interpret_genre.genre_id = genre.genre_id
where (interpret.interpret_name like ‚%$interpret%‘ or ‚‘ = ‚$interpret‘)
and (land.land_id = ‚$land_id‘ or ‚‘ = ‚$land_id‘)
and (interpret_genre.genre_id in (’$genre_id’) or ‚‘ = ‚$genre_id‘);

Auslesen aller Daten, anhand der gefunden IDs:

select interpret.*, land.land_name,
group_concat(distinct genre.genre_id separator ', ') as genre_id,
group_concat(distinct genre.genre_name order by genre.genre_id separator ', ') as genre_name
from interpret
join land
on interpret.land_id = land.land_id
join interpret_genre
on interpret.interpret_id = interpret_genre.interpret_id
join genre
on interpret_genre.genre_id = genre.genre_id
where interpret.interpret_id = ‚$interpret_id‘"

#5

Nein, das funktioniert doch nicht ganz wie es soll. Suche ich nach zwei oder mehr Checkboxen, wird nur nach der ersten Checkbox gesucht (?!?). Ich klemme mich nochmals dahinter.

#6

$query = „select interpret.*, land.land_name,
group_concat(distinct genre.genre_id separator ', ') as
genre_id,
group_concat(distinct genre.genre_name order by genre.genre_id
separator ', ') as genre_name
from interpret
join land
on interpret.land_id = land.land_id
join interpret_genre
on interpret.interpret_id = interpret_genre.interpret_id
join genre
on interpret_genre.genre_id = genre.genre_id
where (interpret.interpret_name like ‚%$interpret%‘ or ‚‘ =
‚$interpret‘)
and (land.land_id = ‚$land_id‘ or ‚‘ = ‚$land_id‘)
and (interpret_genre.genre_id in (’$genre_id’) or ‚‘ =
‚$genre_id‘)“;

ist richtig :smile:

Test
select interpret.*, land.land_name,
group_concat(distinct genre.genre_id separator ', ') as genre_id,
group_concat(distinct genre.genre_name order by genre.genre_id separator ', ') as genre_name
from interpret
join land
on interpret.land_id = land.land_id
join interpret_genre
on interpret.interpret_id = interpret_genre.interpret_id
join genre
on interpret_genre.genre_id = genre.genre_id
where (interpret.interpret_name like ‚Green%‘ or ‚‘ = ‚Green‘)
and (land.land_id = ‚1‘ or ‚‘ = ‚1‘)
and (interpret_genre.genre_id in (1,3) or ‚‘ = ‚1,3‘)

ergibt

interpret_id interpret_name land_id land_name genre_id genre_name
1 Green Blues Band 1 Deutschland [BLOB - 4Bytes] Blues, Rock

#7

Dazu mein Suchformular (vereinfacht):

Interpret (Input Text):

Land (Select):

Deutschland
Österreich
Schweiz

Genre (Checkbox):

Blues
Pop
Rock
Soul

was ist den hier der sin ?

das soll wohl heissen

Blues
Pop
Rock
Soul

$genre_id = $_GET[‚genre_id‘];

dann sollte es auch gehen .

#8

Ja, aber wenn ich nur nach „Blues“ suche, zeigt es mir mit dieser Abfrage nicht an, dass die Gruppe „Green Blues Band“ neben Blues auch Rock spielt. Siehe

select interpret.*, land.land_name,
group_concat(distinct genre.genre_id separator ', ') as genre_id,
group_concat(distinct genre.genre_name order by genre.genre_id separator ', ') as genre_name
from interpret
join land
on interpret.land_id = land.land_id
join interpret_genre
on interpret.interpret_id = interpret_genre.interpret_id
join genre
on interpret_genre.genre_id = genre.genre_id
where (interpret.interpret_name like ‚Green%‘ or ‚‘ = ‚Green‘)
and (land.land_id = ‚1‘ or ‚‘ = ‚1‘)
and (interpret_genre.genre_id in (1) or ‚‘ = ‚1‘);

+--------------+------------------+---------+-------------+----------+------------+
| interpret\_id | interpret\_name | land\_id | land\_name | genre\_id | genre\_name |
+--------------+------------------+---------+-------------+----------+------------+
| 1 | Green Blues Band | 1 | Deutschland | 1 | Blues |
+--------------+------------------+---------+-------------+----------+------------+

Darum die Idee mit den zwei Abfragen.

#9

Verzeihung, mein Fehler. Ich habe nun folgenden Code:

if (isset($_POST[‚genre_id‘])) {
$genre_id = join(’, ', $_POST[‚genre_id‘]);
} else {
$genre_id = ‚‘;

Soweit scheint mein Skript auch zu funktionieren. Ein „echo $genre_id;“ ergibt für Blues und Rock etwa die Ausgabe „1, 3“. Trotzdem sucht es aber in diesem Fall nur nach Blues (value=„1“). Ich arbeite allerdins an einem etwas komplexeren Skript, als dem hier geposteten Beispiel. Sobald ich den Fehler finde, melde ich ich. Zuerst muss ich allerdings kurz an die frische Luft :wink:

#10

da ist noch ein viel grösseres problem , da du group machst, wird nur der erste wert bei IN genommen eben auf die Gruppierte ausgabe , somit

ist ein gendre_id in (3) schon kein erfolg .
löscht man 1 , so wird es war, weil 3 das erste ist.

kann man so also nicht anwenden , da immer nur in group by der erste wert genommen wird .

#11

Ja stimmt, damit bin ich leider mit meinem Latein am Ende. Ich habe versucht nur die IDs (interpret_id) auszulesen, aber es klappt ganz einfach nicht wie es soll. Die Lösung sollte auf jeden Fall auch praktikabel sein, wenn weitere m:n Beziehungen vorhanden sind. Hat eventuell jemand einen Link zu einem Tutorial?

#12

ich sehe das problem eher in der group_concat ausgabe select.
Du sammelst ja die Zeilen des Selectes zu einer und dann braucht es nun einmal alle Zeilen , somit ist ein WHERE unsinn

Das ist also eigentlich rein zur ausgabe gedacht .
. Hab doch gelesen das die menge bei 1024 Zeichen liegt , siehe hinweis unten auf der seite

Du kannst aber diese Ausgabe nun prüfen auf inhalte z.b.

also wenn group_concat genre_id
1,2,3,4,5,6 ergibt , dann noch ergänzen mit , zum eindeutigen suchen.

1,2,3,4,5,6,

und mit REGEXP vergleichen

REGEXP ‚1,|4,|10,‘

select interpret.*, land.land_name,
concat(group_concat(distinct genre.genre_id separator ‚, ‚),‘,‘) REGEXP ‚4,|2,‘ as genre_id_found,
group_concat(distinct genre.genre_name order by genre.genre_id separator ', ') as genre_name
from interpret
join land
on interpret.land_id = land.land_id
join interpret_genre
on interpret.interpret_id = interpret_genre.interpret_id
right join genre
on interpret_genre.genre_id = genre.genre_id
where (interpret.interpret_name like ‚Green%‘ or ‚‘ = ‚Green‘)
and (land.land_id = ‚1‘ or ‚‘ = ‚1‘)

Bringt aber auch nix, da man das nicht ins where packen kann, denn im where geht es genau um eine zeile die man überpüft.

#13

Also musst du wohl einfach ein Subselect nehmen um die genre werte vom interpreten zu bekommen .

SELECT interpret.\*, land.land\_name,
group\_concat(distinct genre.genre\_name order by genre.genre\_id separator ', ') as genre\_found ,
**(  
SELECT group\_concat(genre.genre\_name separator ', ' ) FROM interpret   
 JOIN interpret\_genre  
 ON interpret.interpret\_id = interpret\_genre.interpret\_id  
 JOIN genre  
 ON interpret\_genre.genre\_id = genre.genre\_id  
WHERE interpret.interpret\_name LIKE 'Green%' OR '' = 'Green'   
)**   
AS genre\_all
FROM interpret
 JOIN land
 ON interpret.land\_id = land.land\_id
 JOIN interpret\_genre
 ON interpret.interpret\_id = interpret\_genre.interpret\_id
 JOIN genre
 ON interpret\_genre.genre\_id = genre.genre\_id
WHERE (interpret.interpret\_name like 'Green%' OR '' = 'Green')
 AND (land.land\_id = '1' OR '' = '1')
 **AND (interpret\_genre.genre\_id IN (3)**)

jetzt funktioniert das IN auch richtig :smile:

interpret_id interpret_name land_id land_name genre_found genre_all
1 Green Blues Band 1 Deutschland Rock Blues, Rock

einfach die interpret_genre.genre_id nicht mit group_concat behandeln , ergo hab ichs rausgenommen.

#14

Danke für die Lösung. Ich werde sie mir morgen genau ansehen und melde mich dann, bin heute leider nicht zuhause.

Schöne Grüße, Q_5

#15

So, ich bin gerade nach Hause gekommen und habe die Abfrage auch gleich getestet. Erstmal ein großes Danke für die Mühe die du dir gemacht hast, bewundernswert wie hilfsbereit die Leute in diesem Forum sind. Daumen nach oben!

Mit der Abfrage selbst, gibt es leider noch ein kleines Problem. Wird kein Interpret in das Suchformular eingegeben kommt die Spalte „genre_all“ ins trudeln.

+--------------+------------------+---------+-------------+-------------+----------------------------------------------+
| interpret\_id | interpret\_name | land\_id | land\_name | genre\_found | genre\_all |
+--------------+------------------+---------+-------------+-------------+----------------------------------------------+
| 1 | Green Blues Band | 1 | Deutschland | Blues | Blues, Pop, Pop, Pop, Rock, Rock, Rock, Soul |
+--------------+------------------+---------+-------------+-------------+----------------------------------------------+

Das liegt wohl an der „where“ Bedingung in der Sub-Select Abfrage.

#16

group_concat(distinct genre.genre_name order by genre.genre_id separator ', ') as genre_found ,
(
SELECT group_concat(genre.genre_name separator ', ’ ) FROM interpret

gug doch mal was da fehlt, oben hab ichs richtig gemacht unten vergessen

distinct , so ist das wenn man doppelte hat, aber da hatten wir ja schon ganz am anfang , gell .

#17

Ja, das stimmt schon. Es bleiben aber trotzdem noch einige Probleme. Suche ich z.B. nur nach „Pop“ sollten drei Datensätze aufgelistet werden.

SELECT interpret.\*, land.land\_name,
group\_concat(distinct genre.genre\_name order by genre.genre\_id separator ', ') as genre\_found ,
(
SELECT group\_concat(distinct genre.genre\_name separator ', ' ) FROM interpret 
JOIN interpret\_genre
ON interpret.interpret\_id = interpret\_genre.interpret\_id
JOIN genre
ON interpret\_genre.genre\_id = genre.genre\_id
WHERE interpret.interpret\_name LIKE '' OR '' = '' 
) 
AS genre\_all
FROM interpret
JOIN land
ON interpret.land\_id = land.land\_id
JOIN interpret\_genre
ON interpret.interpret\_id = interpret\_genre.interpret\_id
JOIN genre
ON interpret\_genre.genre\_id = genre.genre\_id
WHERE (interpret.interpret\_name like '' OR '' = '')
AND (land.land\_id = '' OR '' = '')
AND (interpret\_genre.genre\_id IN ( **2** ));

Ich erhalte allerdings nur einen Datensatz:

+--------------+-------------------+---------+-----------+-------------+------------------------+
| interpret\_id | interpret\_name | land\_id | land\_name | genre\_found | genre\_all |
+--------------+-------------------+---------+-----------+-------------+------------------------+
| 2 | Pop Big Band Bern | 3 | Schweiz | Pop | Blues, Pop, Rock, Soul |
+--------------+-------------------+---------+-----------+-------------+------------------------+

Auch fällt auf, dass durch die fehlende Eingabe eines Interpreten nun alle Genres der Zuordnungstabelle angezeigt werden, anstelle jener Genres, welche dem jeweiligen Interpreten zugeordnet werden.

Ich sitze nun schon den halben Tag daran, komme aber auf keinem grünen Zweig. Ganz schön kniffelig.

#18

SELECT interpret.*, land.land_name,
group_concat(distinct genre.genre_name order by genre.genre_id separator ', ') as genre_found
FROM interpret
JOIN land
ON interpret.land_id = land.land_id
JOIN interpret_genre
ON interpret.interpret_id = interpret_genre.interpret_id
JOIN genre
ON interpret_genre.genre_id = genre.genre_id
WHERE (interpret.interpret_name like ‚‘ OR ‚‘ = ‚‘)
AND (land.land_id = ‚‘ OR ‚‘ = ‚‘)
AND (interpret_genre.genre_id IN (2)) GROUP by interpret.interpret_id,interpret.land_id

war ein group zuviel

#19

Also
heute lernst du VIEWS

**CREATE VIEW genre\_all   
AS**   
 SELECT interpret.interpret\_id, 
 group\_concat(distinct genre.genre\_name order by genre.genre\_id separator ', ') as genre
 FROM interpret
 JOIN land
 ON interpret.land\_id = land.land\_id
 JOIN interpret\_genre
 ON interpret.interpret\_id = interpret\_genre.interpret\_id
 JOIN genre
 ON interpret\_genre.genre\_id = genre.genre\_id
**GROUP BY interpret.interpret\_id** 






SELECT 
 interpret.\*, 
 land.land\_name,
 group\_concat(distinct genre.genre\_name order by genre.genre\_id
 separator ', ') as genre\_found , 
**genre\_all.genre as all\_genre**
 FROM interpret
**JOIN genre\_all  
 ON interpret.interpret\_id = genre\_all.interpret\_id**   
 JOIN land
 ON interpret.land\_id = land.land\_id
 JOIN interpret\_genre
 ON interpret.interpret\_id = interpret\_genre.interpret\_id
 JOIN genre
 ON interpret\_genre.genre\_id = genre.genre\_id
 WHERE (interpret.interpret\_name like '' OR '' = '')
 AND (land.land\_id = '' OR '' = '')
 AND (interpret\_genre.genre\_id IN (2)) 
**GROUP by  
 interpret.interpret\_id,  
 interpret.land\_id**

VIOLA :smile: ach ne , voila , ce ca :smile:

#20

Und nochmal für alle Interpreten ohne Genre Filter

SELECT interpret.*, land.land_name,
group_concat(distinct genre.genre_name order by genre.genre_id
separator ', ') as genre_found , genre_all.genre as all_genre
FROM interpret
JOIN genre_all
ON interpret.interpret_id = genre_all.interpret_id
JOIN land
ON interpret.land_id = land.land_id
JOIN interpret_genre
ON interpret.interpret_id = interpret_genre.interpret_id
JOIN genre
ON interpret_genre.genre_id = genre.genre_id
WHERE (interpret.interpret_name like ‚‘ OR ‚‘ = ‚‘)
AND (land.land_id = ‚‘ OR ‚‘ = ‚‘)
GROUP by
interpret.interpret_id

ergibt

interpret_id interpret_name land_id land_name genre_found all_genre
1 Green Blues Band 1 Deutschland Blues, Rock Blues, Rock
2 Pop Big Band Bern 3 Schweiz Pop Pop
3 Jon Doe Crew 1 Deutschland Rock, Soul Rock, Soul
4 Pop Sound Orchestra 3 Schweiz Pop Pop
5 Hans Maier Band 2 Österreich Pop, Rock Pop, Rock

ich hab interpret.land_id bei GROUP BY entfernt, da unötig :smile: