User finden und deren Gruppen aus gleicher Tabelle

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • User finden und deren Gruppen aus gleicher Tabelle

    Ich habe eine Tabelle in der die User und Gruppen gleichermaßen vorhanden sind.

    Nun möchte ich eine Volltextsuche verwirklichen in der ich z.B. nach einem Namen suchen möchte.

    Dabei möchte ich die Gruppen NICHT ausgeben, also filtere ich diese auf Grund des fehlenden Geschlechtes aus.

    Aber jeder User kann Mitglied einer Gruppe sein und ich möchte in einer Spalte all diese Gruppen ausgeben lassen. Alternativ könnte ich mir vorstellen, dass nur die Gruppen angezeigt werden, die mit dem Suchbegriff übereinstimmen.

    Hier erstmal die Abfrage:
    PHP-Code:
        $sql 'SELECT u.uid, u.gender, u.username, u.hobby, u.group_name, u.dob, p.pid, p.ext' $select '
                    FROM users u' 
    $from '
                    LEFT OUTER JOIN pics p ON p.uid = u.uid
                    WHERE u.active = 1
                    AND u.gender <> 0
                    AND u.uid <> ' 
    $userdata['uid'] . '
                    ' 
    $where '
                    ' 
    $match '
                    ' 
    $order_by '
                LIMIT 200'

    Ich habe jetzt noch eine Tabelle mit den Verknüpfungen zwischen User ID und Gruppen ID.

    Ich könnte jetzt wie folgt vorgehen. Gibt ein Nutzer ein Suchwort an, dann suche ich erst in den Gruppen, ob ich was finde. z.B. lese ich dann nur die IDs aus. Das gleiche mache ich dann mit den Usern. Dann habe ich alle in Frage kommenden User IDs und Gruppen IDs.

    Jetzt müsste ich in der Gruppentabelle suchen, welche User Mitglieder der Gruppen IDs sind und addiere diese zu den bereits gefundenen User IDs.

    Als letztes lese ich die detailierten Daten aus von allen User IDs plus pro User eine weitere Abfrage, um alle seine Gruppen zu erhalten?!

    Damit hätte ich mal geschickt aus einer Datenbankabfrage mehrere gemacht und verliere meiner Meinung nach die Reihenfolge wegen der Relevanz.

    Ich gebe mal ein Datensatzbeispiel:

    User- und Gruppentabelle:
    ID NAME
    1 name1
    2 gruppe1
    3 name2
    4 name3
    5 gruppe2

    Verknüpfungstabelle
    UID GID
    1 2
    1 5
    3 2
    4 2

    Das Ergebnis bei der Suche nach dem Wort "gruppe2" sollte dann so aussehen:
    1 name1 "gruppe1, gruppe2"
    4 name3 "gruppe2"

    Ich hatte eigentlich erst geplant einfach nur die Gruppen auszugeben, aber es wäre logischer, wenn auch direkt die Mitglieder der Gruppe ausgegeben werden. Deswegen wollte ich ganz auf die Ausgabe der Gruppen in den Suchergebnissen verzichten.

    Vielleicht erstmal die primäre Frage: Wie ermittle ich für einen User alle seine Gruppen und gebe diese in EINER Spalte aus?

    EDIT:
    Ich habe da was gefunden was sich "find_in_set() > 0" nennt. Ist das mein Erlöser :?:

    EDIT2:
    Ich konnte nun das finden:
    PHP-Code:
    SELECT  
      t1
    .lastname,  
      
    GROUP_CONCAT(t2.firstname ORDER BY t2.firstname SEPARATOR ', '
    FROM tbl AS t1 INNER JOIN tbl AS t2 USING (id
    GROUP BY t1.lastname
    Im Moment verstehe ich daraus nicht wirklich viel, da ich GROUP_CONCAT noch nie benutzt habe.

    EDIT3:
    Stolz bin aufs erste Ergebnis

    Diese Abfrage liefert mir für jeden User in einer Spalte alle seine Gruppen (IDs) mit Komma getrennt:
    PHP-Code:
    SELECT u.uidu.genderu.usernameu.hobbyu.dobp.pidp.extGROUP_CONCATg.gid
    ORDER BY g
    .gid
    SEPARATOR 
    ', ' ) AS group_name
    FROM users u
    INNER JOIN groups g
    USING 
    uid )
    LEFT OUTER JOIN pics p ON p.uid u.uid
    WHERE u
    .active 1
    AND u.gender <> 0
    AND u.uid <> 9
    AND (
    MATCH (
    u.usernameu.firstnameu.lastnameu.group_nameu.hobbyu.postcodeu.cityu.websiteu.enjoyu.searchu.offer
    )
    AGAINST (
    'gutt'
    IN BOOLEAN
    MODE 
    )
    )
    GROUP BY u.uid
    LIMIT 200 
    Jetzt muss ich gerade mal sehen, wie ich stattdessen die Gruppennamen bekomme, aber das wird denke ich kein großes Problem werden.

    Gruß
    Zuletzt geändert von mgutt; 19.07.2007, 22:42.
    mein Honda Forum | meine Scripte

  • #2
    Ok. Ich bekomme es doch nicht hin.

    Dies ist meine Abfrage:
    PHP-Code:
    SELECT u.uidu.genderu.usernameu.hobbyu.dobp.pidp.ext
    GROUP_CONCATg.group_name ORDER BY g.group_name SEPARATOR ', ' group_name
    FROM users u
    LEFT OUTER JOIN pics p ON p
    .uid u.uid
    LEFT OUTER JOIN users g
    groups ug ON u.uid ug.uid AND ug.gid g.uid
    WHERE u
    .active 1
    AND u.gender <> 0
    AND u.uid <> 1
    GROUP BY u
    .uid
    ORDER BY u
    .regtime DESC
    LIMIT 200 
    Mit der Abfrage bekomme ich jedes Mitglied mit allen Gruppen, die da sind. Und zwar auch die Gruppen, in denen das Mitglied gar nicht ist.

    EDIT1:
    Das soll mal einer verstehen. Wenn ich die Tabellen beim LEFT OUTER JOIN umdrehe (also statt "users g, groups ug" das "groups ug, users g"), dann gehts
    PHP-Code:
    SELECT u.uidu.genderu.usernameu.hobbyu.dobp.pidp.ext
    GROUP_CONCATg.group_name ORDER BY g.group_name SEPARATOR ', ' group_name
    FROM users u
    LEFT OUTER JOIN pics p ON p
    .uid u.uid
    LEFT OUTER JOIN groups ug
    users g ON u.uid ug.uid AND ug.gid g.uid
    WHERE u
    .active 1
    AND u.gender <> 0
    AND u.uid <> 1
    GROUP BY u
    .uid
    ORDER BY u
    .regtime DESC
    LIMIT 200 
    EDIT2:
    Also das ist nun meine finale Abfrage:
    PHP-Code:
    SELECT u.uidu.genderu.usernameu.hobbyu.dobp.pidp.ext
    GROUP_CONCAT'<a href="./?p=groups&gid='g.uid'">'g.group_name'</a>' ORDER BY g.group_name SEPARATOR ', ' group_name
    FROM users u
    LEFT OUTER JOIN pics p ON p
    .uid u.uid
    LEFT OUTER JOIN groups ug
    users g ON u.uid ug.uid AND ug.gid g.uid
    WHERE u
    .active 1
    AND u.gender <> 0
    AND u.uid <> 1
    GROUP BY u
    .uid
    ORDER BY u
    .regtime DESC
    LIMIT 200 
    Wie man sieht lese ich nun alle Mitglieder aus und ihre Gruppen. Die Gruppen werden jeweils mit einem Link ausgestattet.

    So.. den ersten Part hätte ich damit überwunden. Nun kommt der schwere Teil

    Wie bereits in meinem ersten Beitrag erwähnt möchte ich nun die Volltextsuche auch auf die Gruppen auswirken lassen. Derzeit suche ich nur in den Feldern der Mitglieder wie folgt:
    PHP-Code:
    SELECT u.uidu.genderu.usernameu.hobbyu.dobp.pidp.ext
    GROUP_CONCAT'<a href="./?p=groups&gid='g.uid'">'g.group_name'</a>' ORDER BY g.group_name SEPARATOR ', ' group_name
    FROM users u
    LEFT OUTER JOIN pics p ON p
    .uid u.uid
    LEFT OUTER JOIN groups ug
    users g ON u.uid ug.uid AND ug.gid g.uid
    WHERE u
    .active 1
    AND u.gender <> 0
    AND u.uid <> 1
    AND (
    MATCH(
    u.usernameu.firstnameu.lastnameu.group_nameu.hobbyu.postcodeu.cityu.websiteu.enjoyu.searchu.offer)
    AGAINST('test' IN BOOLEAN MODE)
    )
    GROUP BY u.uid LIMIT 200 
    Doch wie suche ich so, dass er auch die Gruppendaten berücksichtigt? Mal rumprobieren...

    EDIT3:
    Ok, ich glaube das war es schon und das Ergebnis ist ziemlich genial. Erstmal zu dem was ich gemacht habe. Ich habe das "u.group_name" gegen "g.group_name" getauscht. Schon erhalte ich nur die Nutzer, die selbst was mit dem Suchbegriff zu tun haben oder wenn der Suchbegriff in einem der Vereinsnamen auftaucht.

    Und als Sahnestück sozusagen habe ich gerade festgestellt, dass alle nicht zutreffenden Vereine wegfallen.

    D.h. wenn ein Nutzer in 10 Gruppen ist und eine Gruppe davon deckt sich mit dem Suchbegriff, dann wir der Nutzer angezeigt und nur die eine Gruppe. Eigentlich genau das was ich wollte

    Ich werde jetzt noch weitere Spalten der Gruppentabellen hinzufügen und mal sehen was bei Städtenamen und Co. rauskommt.

    Vielleicht kann ja jemand mit meiner Doku was selbst mal anfangen.

    Ach ja und vielleicht erbarmt sich jemand und erklärt mir warum die Tabellen beim LEFT OUTER JOIN in der richtigen Reihenfolge angegeben werden müssen?! (siehe 1. EDIT dieses Beitrages)
    Zuletzt geändert von mgutt; 20.07.2007, 00:15.
    mein Honda Forum | meine Scripte

    Kommentar


    • #3
      Ich habs nur überflogen, aber das
      LEFT OUTER JOIN groups ug, users g ON u.uid = ug.uid AND ug.gid = g.uid
      ist mMn sowas ab 5. sonstwas nicht mehr möglich, es müsste
      LEFT OUTER JOIN (groups ug, users g) ON u.uid = ug.uid AND ug.gid = g.uid
      sein und rückt damit mehr an den SQL-Standard

      und OUTER hat mMn generell keinen Sinn in mysql oO
      ... gerade kurz gegoogelt, macht das ganze wohl für ODBC kompatibel

      Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

      bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
      Wie man Fragen richtig stellt

      Kommentar


      • #4
        Aber warum die Reihenfolge der beiden Tabellen stimmen muss weißt Du nicht oder?

        Bei FROM ist die Reihenfolge doch auch egal, daher hab ich einfach Glück gehabt, dass ich das rausgefunden habe.
        mein Honda Forum | meine Scripte

        Kommentar


        • #5
          Eigentlich sollte das egal sein - aber nachdem die JOIN-Syntax ja auch ohne Klammern funktioniert, scheints du eine 4er-Version zu verwenden - vielleicht ist die schlichtweg buggy...

          Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

          bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
          Wie man Fragen richtig stellt

          Kommentar


          • #6
            Hallo!

            Die scheint tatsächlich buggy zu sein.

            Ich nutze die aktuellste 4er Version (4.1.22).

            Aber ich habe ein Problem mit Umlauten.

            Wenn ich SELECT group_name nutze gibt er mir alle Umlaute (ä, ö, ü)korrekt aus. Fasse ich aber group_name mit GROUP_CONCAT() zusammen erhalte ich z.B. für das "ü" das "ü".

            Meine Collation ist utf8_general_ci. Es scheint als würde GROUP_CONCAT() das Ergebnis immer in Binary ausgeben.

            Gruß
            Marc
            mein Honda Forum | meine Scripte

            Kommentar


            • #7
              Original geschrieben von mgutt
              Es scheint als würde GROUP_CONCAT() das Ergebnis immer in Binary ausgeben.
              Ob Binary oder nicht, ist vollkommen Wurscht.

              Binary macht lediglich bei der Suche etwas aus - im Gegensatz zu Text-Typen werden dabei wirklich nur die Bytes verglichen, und deshalb nicht Groß- mit Kleinbuchstaben gleichgesetzt.
              Und ich bin mir gar nicht sicher, ob das in aktuellen Versionen immer noch so ist, oder sich mit der Einführung der Collations nicht erledigt hat - denn das ci steht schliesslich für case insensitive.
              [...]erhalte ich z.B. für das "ü" das "ü".
              Das ist UTF-8.
              Kann es sein, dass du die Daten nicht in einem entsprechenden Kontext ausgibst?
              I don't believe in rebirth. Actually, I never did in my whole lives.

              Kommentar


              • #8
                Das hängt irgendwie mit den zwei Tables zusammen. Ist doch nicht wegen GROUP_CONCAT() bzw. nicht alleine davon abhängig:

                PHP-Code:
                <?php
                    
                // 1.
                    
                $sql 'SELECT GROUP_CONCAT(group_name) AS gcusername
                                FROM users
                                WHERE gender = 0
                            GROUP BY uid
                            LIMIT 5'
                ;
                    
                $result $db->sql_query($sqlfalse__LINE____FILE__);
                    while ( 
                $row $db->sql_fetchrow($result) )
                    {
                    echo(
                $row['gcusername'] . '<br />');
                    }
                    
                // 2.
                    
                $sql 'SELECT GROUP_CONCAT(g.group_name) AS gcusername
                                FROM users u, users g
                                WHERE u.uid = g.group_owner
                            GROUP BY u.uid
                            LIMIT 5'
                ;
                    
                $result $db->sql_query($sqlfalse__LINE____FILE__);
                    while ( 
                $row $db->sql_fetchrow($result) )
                    {
                    echo(
                $row['gcusername'] . '<br />');
                    }
                ?>
                1.) gibt aus:
                Grüppchen

                2.) gibt aus:
                Grüppchen

                Im Header der Seite habe ich UTF8 initialisiert:
                PHP-Code:
                <meta http-equiv="content-type" content="text/html; charset=UTF-8" /> 
                Ich vermute im Moment einen Bug in MySQL.

                Gruß
                mein Honda Forum | meine Scripte

                Kommentar


                • #9
                  Den Bug habe ich hier gemeldet:
                  http://bugs.mysql.com/30040

                  Aber ich habe wieder ein Problem mit dieser Abfrage:
                  PHP-Code:
                  SELECT DISTINCT(u.uid), u.genderu.usernamep.hobbypic.pidpic.extGROUP_CONCAT'<a href="./?p=groups&gid='g.uid'">'g.group_name'</a>' ORDER BY g.group_name SEPARATOR ', ' group_namec.message
                  FROM contacts c
                  users u
                  LEFT JOIN pics pic ON pic
                  .uid c.cid
                  LEFT JOIN 
                  (groups ugusers gON u.uid ug.uid AND ug.gid g.uid
                  LEFT JOIN profiles p ON p
                  .user_id u.uid
                  WHERE c
                  .uid 1
                  AND c.cid u.uid
                  AND c.uagree 1
                  GROUP BY u
                  .uid
                  ORDER BY u
                  .username ASC 
                  Es tauchen plötzlich erneut User auf, deren Gruppen doppelt angezeigt werden.

                  Und zwar ist das, seit dem ich die Abfrage für die Tabelle "profiles" hinzugefügt habe.

                  Vielleicht zur Erklärung:
                  - jeder User KANN Mitglied einer Gruppe sein (groups)
                  - jeder User KANN ein Bild hochgeladen haben (pics)
                  - jeder User KANN ein Profil in einer anderen Sprache angelegt haben (profiles)

                  Derzeit ist es so, dass bei Mitgliedern, die mehrere Profile angelegt haben, in gleicher Anzahl Gruppen angezeigt werden. d.h. wenn er drei Profile angelegt hat, dann werden seine Gruppen alle jeweils drei mal ausgegeben. Ich kann es also "verstehen", aber weiß nicht, wie ich es unterbinden könnte
                  mein Honda Forum | meine Scripte

                  Kommentar


                  • #10
                    Original geschrieben von mgutt
                    - jeder User KANN ein Profil in einer anderen Sprache angelegt haben (profiles)
                    Na wenn jeder User mehrere Profile haben kann, taucht auch jedes Profil kombiniert mit den vorigen Tabellen auf.

                    Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

                    bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
                    Wie man Fragen richtig stellt

                    Kommentar

                    Lädt...
                    X