4 Tabellen verknüpfen - evt Join? oder SubSelects? oder was anderes?

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

  • 4 Tabellen verknüpfen - evt Join? oder SubSelects? oder was anderes?

    Hallo,

    Ich habe ein Problem mit einer Abfrage in MySQL

    Folgender Datenbankaufbau ist vorhanden:

    user(userID|username|email|registrationDate)
    course(courseID|coursename|categoryID) (noch ein paar mehr Felder die für die Abfrage allerdings nicht relevant sind)
    categories(categoryID|categoryname|groupID)
    user_to_course(userID|courseID|valid|code)

    Die ID's der eigenen Gruppen habe ich in einem Array ($owngroups)

    Jetzt möchte ich mit einer Abfrage bzw einer Reihe von Abfragen folgendes Array erreichen:
    Code:
    Array
    (
        [2] => Array
            (
                [userID] => 2
                [username] => 13f1d1e67f
                [email] => 13f1d1e67f@example.com
                [registrationDate] => 1285463023
                [course] => Array
                    (
                        [0] => Array
                            {
                                [courseID] => 1
                                [courseName] => TestKurs
                                [categoryID] => 1
                                [categoryName] => TestKategorie
                                [own] => 1
                                [valid] => 1
                                [key] => 0
                            }
                    )
            )
    
        [1] => Array
            (
                [userID] => 1
                [username] => 1698e1f5ro
                [email] => 1698e1f5ro@example.com
                [registrationDate] => 1285449974
                [course] => Array
                    (
                        [0] => Array
                            {
                                [courseID] => 1
                                [courseName] => TestKurs
                                [categoryID] => 1
                                [categoryName] => TestKategorie
                                [own] => 1
                                [valid] => 0
                                [key] => f015efgh
                            }
    
                        [1] => Array
                            {
                                [courseID] => 2
                                [courseName] => TestKurs2
                                [categoryID] => 1
                                [categoryName] => TestKategorie
                                [own] => 1
                                [valid] => 1
                                [key] => 0
                            }
    
                        [2] => Array
                            {
                                [courseID] => 2
                                [courseName] => TestKurs2
                                [categoryID] => 2
                                [categoryName] => TestKategorie2
                                [own] => 0
                                [valid] => 0
                                [key] => fgths652
                            }
    
                    )
            )
    
    )
    Bisher habe ich folgendes Konstrukt:
    PHP-Code:
    $sql "SELECT a.*, b.* FROM user_to_course b LEFT JOIN user a ON a.userID = b.userID ORDER BY b.userID DESC";
    $result mysql_query($sql);
    while(
    $row mysql_fetch_array($result)) {
      if(!isset(
    $data[$row['userID']])) {
        
    $data[$row['userID']] = $row//neuen User einfügen
        
    $data[$row['userID']]['courseID'] = array($row['courseID']); //courseID als eigenes Array anlegen
      
    } else {
        
    $data[$row['userID']]['courseID'][]=$row['courseID']; //falls der User schon existiert, nur die courseID hinzufügen
      
    }

    Dadurch bekomme ich folgendes Array:
    Code:
    Array
    (
        [2] => Array
            (
                [userID] => 2
                [username] => 13f1d1e67f
                [email] => 13f1d1e67f@example.com
                [registrationDate] => 1285463023
                [courseID] => Array
                    (
                        [0] => 1
                    )
    
                [valid] => 0
                [key] => 0
            )
    
        [1] => Array
            (
                [userID] => 1
                [username] => Admin
                [email] => rene-gessinger@web.de
                [registrationDate] => 1285449974
                [courseID] => Array
                    (
                        [0] => 3
                        [1] => 1
                        [2] => 2
                    )
    
                [valid] => 0
                [key] => 0
            )
    
    )
    Wie man sehen kann ist es ja nicht gerade gleich dem was ich gerne hätte.

    hoffe mir knann jmd sagen wie ich es hinbekomme.

    Eine Überprüfung ob der Kurse (in der Spalte course) in einer Kategorie steht die einer eigenen Gruppe zugeordnet ist findet noch nicht statt.

    lg

  • #2
    Also wenn ich mein Thema von gestern richtig umgesetzt habe, dann sollte das hier eigentlich funktionieren:

    SELECT
    u.userID,
    u.username,
    c.courseID,
    c.courseName,
    cat.categoryID,
    cat.categoryName
    FROM
    users u,
    LEFT JOIN user_to_course USING(userID)
    LEFT JOIN courses c USING(courseID)
    LEFT JOIN categories cat USING(categoryID)

    Du solltest so alle Kursteilnahmen der User erhalten. Ein Gruppenwechsel und schon hast du es in der gewünschten Form.

    (Statt in Arrays, könnte man das auch schön mit Objekten (und dann rekursiv) aufbauen. )
    ICH BIN ICH!!!

    Kommentar


    • #3
      Hallo,

      ersteinmal Danke. Leider funktioniert es nicht so ganz

      http://nurpech.madahost.de/wcf/checkuser.php

      muss ich an der Abfrage sonst noch was ändern?

      Hier mal der Aufbau der Tabelle:

      user:
      Code:
      CREATE TABLE `user` (
       `userID` int(10) unsigned NOT NULL auto_increment,
       `username` varchar(255) NOT NULL default '',
       `email` varchar(255) NOT NULL default '',
       `password` varchar(40) NOT NULL default '',
       `salt` varchar(40) NOT NULL default '',
       `languageID` int(10) unsigned NOT NULL default '0',
       `registrationDate` int(10) unsigned NOT NULL default '0',
       `styleID` int(10) NOT NULL default '0',
       `activationCode` int(10) unsigned NOT NULL default '0',
       `registrationIpAddress` varchar(15) NOT NULL default '',
       `lastLostPasswordRequest` int(10) unsigned NOT NULL default '0',
       `lostPasswordKey` varchar(40) NOT NULL default '',
       `newEmail` varchar(255) NOT NULL default '',
       `reactivationCode` int(10) unsigned NOT NULL default '0',
       `oldUsername` varchar(255) NOT NULL default '',
       `lastUsernameChange` int(10) unsigned NOT NULL default '0',
       `quitStarted` int(10) unsigned NOT NULL default '0',
       `banned` tinyint(1) unsigned NOT NULL default '0',
       `banReason` mediumtext,
       `rankID` int(10) unsigned NOT NULL default '0',
       `userTitle` varchar(255) NOT NULL default '',
       `activityPoints` int(10) unsigned NOT NULL default '0',
       `avatarID` int(10) unsigned NOT NULL default '0',
       `gravatar` varchar(255) NOT NULL default '',
       `disableAvatar` tinyint(1) NOT NULL default '0',
       `disableAvatarReason` text,
       `lastActivityTime` int(10) unsigned NOT NULL default '0',
       `profileHits` int(10) unsigned NOT NULL default '0',
       PRIMARY KEY  (`userID`),
       KEY `username` (`username`),
       KEY `registrationDate` (`registrationDate`),
       KEY `styleID` (`styleID`),
       KEY `activationCode` (`activationCode`),
       KEY `registrationIpAddress` (`registrationIpAddress`,`registrationDate`),
       KEY `activityPoints` (`activityPoints`)
      ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
      course:
      Code:
      CREATE TABLE `course` (
       `courseID` int(11) NOT NULL auto_increment,
       `courseNAME` text NOT NULL,
       `categoryID` int(11) NOT NULL,
       `teacherID` int(11) NOT NULL,
       `active` int(11) NOT NULL,
       `open` int(11) NOT NULL,
       PRIMARY KEY  (`courseID`)
      ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
      user_to_course:
      Code:
      CREATE TABLE `user_to_course` (
       `userID` int(11) NOT NULL,
       `courseID` int(11) NOT NULL,
       `valid` int(4) NOT NULL,
       `key` int(60) NOT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      categories:
      Code:
      CREATE TABLE `categories` (
       `categoryID` int(11) NOT NULL auto_increment,
       `categoryNAME` text NOT NULL,
       `groupID` int(11) NOT NULL,
       `open` int(11) NOT NULL,
       PRIMARY KEY  (`categoryID`)
      ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

      Kommentar


      • #4
        Was genau funktioniert noch nicht?

        Beschränk auch mal die Query auf das wesentliche! Durch den Wust an Informationen blickt ja keiner mehr durch...

        Und wie sieht denn die Query überhaupt aus?
        ICH BIN ICH!!!

        Kommentar


        • #5
          Code:
          Array
          (
              [1] => Array
                  (
                      [userID] => 1
                      [username] => Admin
                      [email] => rene-gessinger@web.de
                      [password] => effcfda1c7d2a85feebc4e5d224650193dc6e280
                      [salt] => 2de52416f34f908d4f7dace3f86ea1ee1dc03c47
                      [languageID] => 5
                      [registrationDate] => 1285449974
                      [styleID] => 0
                      [activationCode] => 0
                      [registrationIpAddress] => 
                      [lastLostPasswordRequest] => 0
                      [lostPasswordKey] => 
                      [newEmail] => 
                      [reactivationCode] => 0
                      [oldUsername] => 
                      [lastUsernameChange] => 0
                      [quitStarted] => 0
                      [banned] => 0
                      [banReason] => 
                      [rankID] => 0
                      [userTitle] => 
                      [activityPoints] => 0
                      [avatarID] => 0
                      [gravatar] => 
                      [disableAvatar] => 0
                      [disableAvatarReason] => 
                      [lastActivityTime] => 1286820164
                      [profileHits] => 0
                      [courseID] => Array
                          (
                              [0] => 3
                              [1] => 1
                              [2] => 2
                          )
          
                      [courseNAME] => TK3
                      [categoryID] => 2
                      [teacherID] => 1
                      [active] => 1
                      [open] => 1
                      [categoryNAME] => Test
                      [groupID] => 7
                  )
          (Quelle: Link von oben)

          Sieht doch gut aus, nur ordnest du die Kursnamen, etc. offensichtlich noch falsch zu. Das scheitert aber dann nicht an der Query, sondern an deinem PHP-Code. Also überprüf noch mal, ob die Query das richtige Ergebnis liefert, und dann poste noch mal deinen aktuellen PHP-Code.

          BTW: Du solltest nicht mit SELECT * arbeiten, dann wird ganz nebenbei auch das Ergebnis übersichtlicher.
          Zuletzt geändert von TobiaZ; 16.10.2010, 16:56.

          Kommentar


          • #6
            Nunja. Die Sache ist jetzt aber die das nicht jedem Nutzer ein Kurs zugeordnet ist. Es gibt auch Benutzer (user) die in der Tabelle user_to_course gar nicht erst vorkommen.

            Ich habe mir jetzt überlegt dass es vermutlöich einfacher ist, wenn man mehrer Abfragen hintereinander steckt und erst am Schluss das Array baut.

            Das ganze soll übrigens eine Anwendung für das Woltlab Community Framework werden. Habe aber aus Rücksicht auf die die das Teil nicht kennen alles rausgeholt was damit zu tun hat. Ändert aber nichts an der Technik. Das einzigste was anders sind sind die Tabellennamen (das setze ich allerdings um wenn ich es teste. und wenn ich es hier ins Forum stelle) und eben die Methoden um DB-Abfragen zu machen (WCF::getDB()->sendQuery($sql). Allerdings teste ich das hier jetzt ohne das Framework und baue es erst hinterher ein. von daher sollte das jetzt nichts zur sache tun.

            lg

            Kommentar


            • #7
              Nunja. Die Sache ist jetzt aber die das nicht jedem Nutzer ein Kurs zugeordnet ist. Es gibt auch Benutzer (user) die in der Tabelle user_to_course gar nicht erst vorkommen.
              Was soll denn mit diesen passieren? Anzeigen, oder nicht anzeigen. Beides sollte sich in de JOIN steuern lassen.

              ch habe mir jetzt überlegt dass es vermutlöich einfacher ist, wenn man mehrer Abfragen hintereinander steckt und erst am Schluss das Array baut.
              Sagen wir mal, weniger Aufwand. Aber schöner ist es sicher nicht.

              Warum gehst du eigentlich nicht auf unsere Fragen hier ein???

              Kommentar


              • #8
                Zitat von TobiaZ Beitrag anzeigen
                Warum gehst du eigentlich nicht auf unsere Fragen hier ein???
                Auf welche Fragen?^^

                Ich bin der Meinung das es zwar mit dem Code von IchBinIch funktioniert es aber nicht so rauskommt wie ich es gerne hätte.

                [courseNAME] => TK3
                [categoryID] => 2
                [teacherID] => 1
                [active] => 1
                [open] => 1
                [categoryNAME] => Test
                [groupID] => 7

                hat z.b. nichts da zu suchen wo es atm steht.

                Und wenn ein User gar nicht in der Tabelle user_to_course stejt soll er auch nicht aufgelistet werden (was z.T. nicht der Fall ist). wenn du oder sonst jmd noch weiter Fragen hast, kann ich gerne gefragt werden und beantworte diese auch.

                lg

                Kommentar


                • #9
                  Ich bin der Meinung das es zwar mit dem Code von IchBinIch funktioniert es aber nicht so rauskommt wie ich es gerne hätte.

                  [courseNAME] => TK3
                  [categoryID] => 2
                  [teacherID] => 1
                  [active] => 1
                  [open] => 1
                  [categoryNAME] => Test
                  [groupID] => 7

                  hat z.b. nichts da zu suchen wo es atm steht.
                  Schön, dann sind wir da schon mal einer Meinung. Nur dass ich da schon längst eine Vermutung angestellt habe, woran das liegen könnte, die du jedoch nicht überprüfst!

                  Und wenn ein User gar nicht in der Tabelle user_to_course stejt soll er auch nicht aufgelistet werden (was z.T. nicht der Fall ist).
                  Wenn es nicht der Fall ist, ist doch Prima. Falls doch, dann schließe eben die aus, die course_ID IS NULL haben.

                  nn du oder sonst jmd noch weiter Fragen hast, kann ich gerne gefragt werden und beantworte diese auch.
                  Ach nee, ist ja dein problem. Das solltest du mit den infos hier im Thread schon hinbekommen. Wenn nicht, dann solltest du konkrete Fragen stellen!

                  Kommentar


                  • #10
                    also nochmal von vorne?

                    Ich weiß dass es für dritte schwierig ist sich in die Datenbankstruktur einzuarbeiten. Von daher bin ich gerne bereit euch das zu liefern was ihr benötigt um mir zu helfen.

                    Weiterhin habe ich das Gefühl dass wir irgendwie aneinander vorbei reden. Ihr wollt etwas von mir von dem ich aber nicht weiß was es ist (so dämlich es sich jetzt auch anhört^^).

                    (Statt in Arrays, könnte man das auch schön mit Objekten (und dann rekursiv) aufbauen. )
                    <- Was damit gemeint ist weiß ich grade auch nicht

                    Gibt's Fragen, dann beantworte ich diese (wenn ich's kann).
                    Wenn ich mein Problem genauer erklären soll, kann ich's machen.

                    Wenn ich richtig gelesen habe, hättest du ganz gerne meinen PHP-Code:
                    PHP-Code:
                    $sql "SELECT
                      u.userID, u.username, u.email, u.registrationDate,
                      c.courseID, c.courseNAME,
                      cat.categoryID, cat.categoryNAME, cat.groupID
                    FROM
                      user u
                      LEFT JOIN user_to_course USING(userID)
                      LEFT JOIN course c USING(courseID)
                      LEFT JOIN categories cat USING(categoryID)
                    WHERE
                      c.courseID != ''
                      AND
                      cat.groupID IN ("
                    .implode(',',$owngroups).")
                    ORDER BY
                      u.userID ASC"
                    ;
                    $result mysql_query($sql);
                    while(
                    $row mysql_fetch_array($result)) {
                      if(!isset(
                    $data[$row['userID']])) {
                        
                    $data[$row['userID']] = $row//neuen User einfügen
                        
                    $data[$row['userID']]['course'] = array($row['courseID']); //courseID als eigenes Array anlegen
                      
                    } else {
                        
                    $data[$row['userID']]['course'][]=$row['courseID']; //falls der User schon existiert, nur die courseID hinzufügen
                      
                    }

                    Meine konkrete Frage: wie muss ich den php-code anpassen damit das Array rauskommt das ich gerne hätte?

                    In der Hoffnung dass ich jetzt das geschrieben habe was du gerne hättest:

                    lg
                    Zuletzt geändert von NurPech; 16.10.2010, 21:44.

                    Kommentar


                    • #11
                      Ich weiß dass es für dritte schwierig ist sich in die Datenbankstruktur einzuarbeiten. Von daher bin ich gerne bereit euch das zu liefern was ihr benötigt um mir zu helfen.
                      Nö, ganz und gar nicht. Hast du schließlich eingangs gepostet.
                      Weiterhin habe ich das Gefühl dass wir irgendwie aneinander vorbei reden. Ihr wollt etwas von mir von dem ich aber nicht weiß was es ist (so dämlich es sich jetzt auch anhört^^).
                      In Zukunft nachfragen, anstatt es zu ignorieren.
                      (Statt in Arrays, könnte man das auch schön mit Objekten (und dann rekursiv) aufbauen. )
                      <- Was damit gemeint ist weiß ich grade auch nich
                      "Könnte" heißt, dass es nur ein Verbesserungsvorschlag ist. Also zur Problemlösung erstmal unwichtig. Warum hast du nicht direkt nachgefragt?
                      Wenn ich richtig gelesen habe, hättest du ganz gerne meinen PHP-Code:
                      Ja, das hatte ich vor 5 Postings auch mal so geschrieben:
                      Also überprüf noch mal, ob die Query das richtige Ergebnis liefert, und dann poste noch mal deinen aktuellen PHP-Code.
                      --------
                      Also, ab hier machen wir jetzt vernünftig weiter:

                      PHP-Code:
                      if(!isset($data[$row['userID']])) {
                          
                      $data[$row['userID']] = $row//neuen User einfügen 
                      Und da wunderst du dich nicht, dass die kompletten Informationen (also auch die Kursinfo) im Hauptarray stehen??? Works as designed. Gib einfach konkret an, welche Infos rein sollen und gut ist.

                      PHP-Code:
                      $data[$row['userID']]['course'] = array($row['courseID']); //courseID als eigenes Array anlegen 
                      und
                      PHP-Code:
                      $data[$row['userID']]['course'][]=$row['courseID']; //falls der User schon existiert, nur die courseID hinzufügen 
                      Hier hingegen gibst du explizit nur die ID an, dann darfst du dich auch nicht wundern, dass der Rest fehlt.

                      Also anpassen und die Sache läuft!

                      Kommentar


                      • #12
                        Ich wunder mich NICHT dass das ganze im Hauptarray steht.

                        Ich hätte es nur ganz gerne in einem Unterarray^^ Von mir aus auch zusätzlich.

                        Das Query liefert das richtige Ergebnis. Habe ich aber bereits geschrieben.

                        Zu meinem grundsätzlichen problem: Ich benötige einen kleinen (oder doch einen etwas größeren^^) Denkanstoss wie ich das jetzt gebacken bekomme

                        Wenn du es gerne hättest, kann ich auch die ganzen daten aus dem hauptarray entfernen. Tut jetzt allerdings nichts zur Sache.

                        Mein größtes Problem ist dass ich jetzt schon seit längere Zeit auf dem Schlauch stehe. Normalerweise bin zwar fit in php und mysql aber hier hat irgendwie mein Wissen ausgesetzt

                        Edit: Wie es scheint funktioniert es jetzt. Vielen Dank euch beiden. jetzt muss ich nurnoch den Index 'own' auf 1 oder 0 setzen und dann passt alles.

                        thx
                        lg
                        Zuletzt geändert von NurPech; 16.10.2010, 22:43.

                        Kommentar


                        • #13
                          Ähm, du weißt, wie du die KursID in das Unterarray bekommst, aber nicht wie du z.B. den Kursnamen da rein kriegst???

                          Jetzt vielleicht?

                          Kommentar


                          • #14
                            es geht^^

                            PHP-Code:
                            while($row WCF::getDB()->fetchArray($result)) {
                            if(
                            in_array($row['groupID'], $owngroups))
                              {
                                
                            $own 1;
                              }
                            else
                              {
                                
                            $own 0;
                              }
                              if(!isset(
                            $data[$row['userID']])) {
                                
                            $data[$row['userID']]['userID'] = $row['userID']; //neuen User einfügen
                                
                            $data[$row['userID']]['username'] = $row['username'];
                                
                            $data[$row['userID']]['email'] = $row['email'];
                                
                            $data[$row['userID']]['registrationDate'] = $row['registrationDate'];
                                
                            $data[$row['userID']]['course'][] = array('courseID' => $row['courseID'], 'courseNAME' => $row['courseNAME'], 'own' => $own); //courseID als eigenes Array anlegen
                              
                            } else {
                                
                            $data[$row['userID']]['course'][]=array('courseID' => $row['courseID'], 'courseNAME' => $row['courseNAME'], 'own' => $own); //falls der User schon existiert, nur die courseID hinzufügen
                              
                            }

                            Noch eine kleine Frage: der Index des Hauptarrays ist hier ja die userID. Wie krig ich es hin dass ein Zähler (von 0 - x) der Index ist?

                            So geht es leider nicht:
                            PHP-Code:
                            $i=0;
                            while(
                            $row WCF::getDB()->fetchArray($result)) {
                            if(
                            in_array($row['groupID'], $owngroups))
                              {
                                
                            $own 1;
                              }
                            else
                              {
                                
                            $own 0;
                              }
                              if(!isset(
                            $data[$i])) {
                                
                            $data[$i]['userID'] = $row['userID']; //neuen User einfügen
                                
                            $data[$i]['username'] = $row['username'];
                                
                            $data[$i]['email'] = $row['email'];
                                
                            $data[$i]['registrationDate'] = $row['registrationDate'];
                                
                            $data[$i]['course'][] = array('courseID' => $row['courseID'], 'courseNAME' => $row['courseNAME'], 'own' => $own); //courseID als eigenes Array anlegen
                              
                            } else {
                                
                            $data[$i]['course'][]=array('courseID' => $row['courseID'], 'courseNAME' => $row['courseNAME'], 'own' => $own); //falls der User schon existiert, nur die courseID hinzufügen
                              
                            }
                              
                            $i++;

                            Kommentar


                            • #15
                              Noch eine kleine Frage: der Index des Hauptarrays ist hier ja die userID. Wie krig ich es hin dass ein Zähler (von 0 - x) der Index ist?
                              Im Moment brauchst du die ID ja, um die Kurse zuzuordnen.

                              Hast jetzt zwei möglichkeiten. Entweder sortierst du die Einträge nach der UserID, dann kannst du mit einem Gruppenwechsel arbeiten. Dabei speicherst du die kurse immer in den letzten eintrag des Hauptarrays. Immer wenn ein neuer User kommt, legst du einen neuen User im Hauptarray an.

                              Alternativ kannst du das array natürlich auch einfach neu durchzählen, wenn du fertig bist.

                              Kommentar

                              Lädt...
                              X