INNER JOIN drie tabellen

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Als PHP programmeur werken aan IT oplossingen voor

Vacature Omschrijving Deze fraaie partij ondersteunt bedrijven bij hun technische vraagstukken door middel van software, trainingen, de inzet van specialisten en state of the art apparatuur. Hun missie is: “Het leveren van technische producten, diensten en systemen die van meerwaarde zijn voor de opdrachtgevers.” Zij beantwoorden dan ook de meest uiteenlopende technische vraagstukken, waarbij zij zich richten op vijf marktsegmenten: Bovengrondse Infrastructuur, Ondergrondse Infrastructuur, Bouw & Installatietechniek, Geo & Data en Industrie & Energie. Als technisch specialist ontwerpen zij niet alleen producten en oplossingen voor deze marktsegmenten, maar nemen zij ook het beheer en onderhoud op zich. Deze groeiende partij

Bekijk vacature »

Dirk Huizinga

Dirk Huizinga

04/02/2019 22:21:51
Quote Anchor link
Hoi jongens.. ik wederom experts nodig. Het kan nog gekker. Ik leer op deze manier steeds een beetje bij (dat is dan de positieve kant).. Andere kant is het een beetje vervelend om jullie te moeten lastig valllen.. Maar ik heb weer een avondje zitten te sleutelen aan een nieuwe functie.. Dit keer data uit twee tabellen en een koppeltabel. Ben de hele avond zitten te proberen om dit voor elkaar te krijgen.. Oom Goegel brengt met me ook niet veel veder..

Hieronder de wat ik nu heb. (uiteraard niet werkend).. de HAM vraag is hoe gaat het me lukken dat ik het wel werkend krijg? Kan iemand mij helpen hiermee aub?

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
function person2role (){
        global $connection;
        
        $where = NULL;
        if (isset($_GET['asn'])){
            $where  = 'MSKEYVALUE_MEDEWERKER = "'.mysqli_real_escape_string($connection, $_GET['asn']).'"';
        }
        
        $sqlUitlezen = mysqli_query($connection, $sql = "
        SELECT
            person.*,
            role.*,
            person2role.*
        FROM
            person
        INNER JOIN
            person2role
        ON
            person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER
        INNER JOIN
            role
        ON
            person2role.MSKEYVALUE_ROL = role.MSKEYVALUE_ROL
        WHERE
        ".$where."
        ");
        
        echo '<b>$where:</b> '.$where;
        echo '<br>';
        echo '<b>$sql :</b> '.$sql;
        
        $sqlAantal = mysqli_num_rows($sqlUitlezen);
        
        if ($sqlAantal > 0){
            echo '<article>';
            echo '<table id="customers">';
            echo '    <tr>';
            echo '    <td colspan="4" align="right"><h3>Aantal medewerkers binnen deze OU ('.$sqlAantal.') ';
            echo '    </tr>';
            echo '  <tr>';
            echo '    <th><h4>ASN</h4></th>';
            echo '    <th><h4>Rol</h4></th>';
            echo '    <th colspan="2" nowrap><h4>Geldig tot</h4></th>';
            echo '  </tr>';
            
            while ($sqlData = mysqli_fetch_assoc($sqlUitlezen)){    
            
            echo '  <tr>';
            echo '    <td><h6>'.$sqlData['MSKEYVALUE_MEDEWERKER'].'</h6></td>';
            echo '    <td><h6>x</h6></td>';
            echo '    <td><h6>x</h6></td>';
            echo '    <td align="center">
                        <table border="0" cellpadding="0" cellspacing="0">
                            <tr>
                            <!--
                            <td><img src="../img/icon-rollen.png" height="20" title="Rollen"></td>
                            -->
                            <td><A HREF="javascript:javascript:history.go(-1)"><img src="../img/icon-pagina-terug.png" height="12" title="Vorige pagina"></a></td>
                            
                            </tr>
                        </table>
                    </td>';
            echo '  </tr>';
            }
            echo '</table>';
            echo '</article>';
        }else{
            echo '<h4>Sorry, ik kan geen medewerker-rolkoppeling vinden!</h4>';
        }
    }


Alvast bedankt voor het meedenken.
 
PHP hulp

PHP hulp

20/08/2019 21:40:49
 
- Ariën -
Beheerder

- Ariën -

04/02/2019 22:28:28
Quote Anchor link
Wat krijg je er nu dan uit? En wat verwacht je?
Laat anders even een uitvoer van MySQL zien.
 
Thomas van den Heuvel

Thomas van den Heuvel

04/02/2019 22:50:09
Quote Anchor link
Indien $_GET['asn'] niet bestaat heb je in ieder geval een WHERE-clause zonder iets erachter, dus dat gaat uberhaupt niet werken.

En inderdaad, wat verwacht je dat er zou moeten gebeuren en wat gebeurt er daadwerkelijk en hoe verschilt dit van elkaar.
 
Dirk Huizinga

Dirk Huizinga

05/02/2019 18:35:45
Quote Anchor link
Hoi mannen,

Ik zal een poging ondernemen ;-)

Wat ik als gewenste resultaat zo willen dat ik van persoon (willekeurig geselecteerd uit een lijst op basis van ASN (dit is een uniek nummer... je kan het vergelijk met een BSN nr die we allemaal hebben).
Wanneer ik de op die persoon klik dan ziet de link er als volgt uit:

http://000.000.000.00/bp/members/index.php?actie=person2role&asn=100.005.813

Ik roep dan de functie person2role (die hierboven staat) aan waarbij criteria dat asn is.

Ik zou verwachten dat ik dan een overzicht krijgt van die persoon (tabel person) met welke rollen (tabel role) via het koppeltabel (person2role) in bezit heeft.

de velden waarop gematched kan worden is:

tabel person:
MSKEYVALUE_MEDEWERKER

tabel person2role:
MSKEYVALUE_MEDEWERKER en
MSKEYVALUE_ROL

tabel role:
MSKEYVALUE_ROL

Naar mijn idee zou het moeten lukken om een overzicht te genereren. Maar dit is voor nog ietwat aan de hoge kant blijkt.. In ieder geval ik kom er na 2 avondjes puzzelen nog niet tot het gewenste resultaat.

Ik denk dat ik het redelijk heb omschreven... misschien ook niet niet maar dat verneem ik dan graag.

Wederom een bedankje op zijn plaats voor de harde meedenkers

Nog ter aanvulling... wanneer ik echo $Where; an en echo $sql; uitvoer is krijgt ik het volgende:

$where: MSKEYVALUE_MEDEWERKER = "100.005.813"

$sql : SELECT person.MSKEYVALUE_MEDEWERKER FROM person INNER JOIN person2role ON person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER WHERE MSKEYVALUE_MEDEWERKER = "100.005.813"
Gewijzigd op 05/02/2019 19:59:25 door Dirk Huizinga
 
Ward van der Put
Moderator

Ward van der Put

05/02/2019 18:55:46
Quote Anchor link
Als je de persoon al weet en je alleen zijn/haar rollen wilt weten, moet je de query uitvoeren vanuit de tabel person2role.

Je functie doet daarnaast veel te veel: person2role() zou person2role($asn) met een parameter voor de ID moeten zijn, om te beginnen.
 
Dirk Huizinga

Dirk Huizinga

05/02/2019 19:14:55
Quote Anchor link
Ward van der Put op 05/02/2019 18:55:46:
Als je de persoon al weet en je alleen zijn/haar rollen wilt weten, moet je de query uitvoeren vanuit de tabel person2role.

Je functie doet daarnaast veel te veel: person2role() zou person2role($asn) met een parameter voor de ID moeten zijn, om te beginnen.


Hoi Ward... klopt... uiteraard wil ik nog wel aanvullende gegevens uit de tabel person trekken wanneer eenmaal de verbinding ligt.
 
Thomas van den Heuvel

Thomas van den Heuvel

05/02/2019 21:45:29
Quote Anchor link
Indien een persoon meerdere rollen kan vervullen zou ik een LEFT JOIN verwachten op person2role. En dan zijn er wellicht nog wat randgevallen zoals personen die geen rollen hebben? Of dat deze niet actief ("verwijderd") zijn uit het systeem (WHERE ... person.deleted = 0, is wellicht nog een handige toevoeging in de functionaliteit). En dan zul je dus nog de kolomwaarden moeten opvragen in de SELECT om deze weer te geven.

Waar strandt het schip precies?

En wat @Ward zegt, indien je hier een functie van bakt zorg dan dat deze herbruikbaar is (gebruik parameters). Mogelijk zou deze functie eigenlijk alleen een array met data moeten teruggeven, het weergeven hiervan is een aparte taak die ergens anders -met behulp van deze functie- uitgevoerd kan worden.
 
Dirk Huizinga

Dirk Huizinga

05/02/2019 22:51:53
Quote Anchor link
tuut tuut tuut... bovenstaande is voor mij nog niet begrijpbaar sorry..

Met onderstaande code krijg ik een overzicht welke persoon welke rollen heeft.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function person2role (){
        global $connection;
        
        $where = NULL;
        if (isset($_GET['asn'])){
            $where  = 'WHERE person.MSKEYVALUE_MEDEWERKER = "'.mysqli_real_escape_string($connection, $_GET['asn']).'"';
        }
        $sqlUitlezen = mysqli_query($connection, $sql = "
            SELECT
                person.*,
                person2role.*
            FROM
                person
            LEFT JOIN
                person2role
            ON
                person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER
            ".$where."                
            ORDER BY
                person.MSKEYVALUE_MEDEWERKER ASC            
            ");


maar hiermee kan ik dus geen aanvullende informatie halen want de 3e tabel `role` ontbreekt. Het lukt me niet om een 2 JOIN toe te voegen want dan krijg ik de foutmelding die hierboven al zijn beschreven.

Thomas tipt daar nog een goed punt aan. Er zullen personen zijn die geen rol hebben gekoppeld. Maar hoe krijg ik die inzichtelijk. Is dat een andere query of kan/moet dat in deze mee worden genomen?
 
Thomas van den Heuvel

Thomas van den Heuvel

06/02/2019 00:42:49
Quote Anchor link
Hm, oke. Dus even een aantal zaken op een rij.

Een persoon heeft 0 of meer rollen. Klopt dit?

Je gebruikt het ASN ook als intern gebruikers-id, ik hoop dat je je database ook echt relationeel hebt opgezet?
Normaal gesproken wordt het personeelsnummer (ASN) vaak nog vertaald naar een soort van intern gebruikers-id, wat gewoon een auto-increment veldje (oplopend nummer) is. Dit lijkt mij voor indexering (levert je snellere queries op) en interne consistentie beter. Maar goed. Als je overal dat ASN gebruikt dan kun je in principe person2role als uitgangspunt nemen... ware het niet dat het ook voor kan komen dat iemand geen rol heeft. Dus voor nu maar person als uitgangspunt.

Snap je het verschil tussen een LEFT JOIN en een INNER JOIN?
person2role bevat in principe geen nieuwe informatie, dit is slechts een koppeltabel, dus daar hoef je geen informatie uit op te halen lijkt mij? Wat heb je nodig uit de role-tabel? Zijn dit enkel de labels van de concrete rollen? Volgens mij kom je een eind als je zoiets doet:

SELECT <informatie die je wilt hebben>
FROM person
LEFT JOIN person2role ON person2role.MSKEYVALUE_MEDEWERKER = person.MSKEYVALUE_MEDEWERKER
LEFT JOIN role ON role.MSKEYVALUE_ROL = person2role.MSKEYVALUE_ROL
WHERE person.MSKEYVALUE_MEDEWERKER = <ASN>

Een LEFT JOIN op person2role en role omdat het niet gegarandeerd is dat iedereen een rol heeft.

Vervolgens zal je in een loopje de resultaten moeten uitlezen waarbij je moet controleren of informatie in person2role (en role) verschilt van NULL - het kan namelijk voorkomen dat iemand geen rol toegewezen heeft gekregen. De LEFT JOIN zorgt er dan voor dat wel informatie over de desbetreffende persoon wordt opgehaald, maar de ontbrekende informatie (in person2role en role) wordt bij afwezigheid opgevuld met NULL-waarden. Zou je een INNER JOIN gebruiken krijg je de person niet als resultaat retour.

Je zou ook nog iets met GROUP_CONCAT kunnen doen als je dat leuker vindt.

Bijbehorende testjes:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# gebruikers tabel
CREATE TABLE test_a (
a_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
a_text VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# koppeltabel
CREATE TABLE test_a_b (
a_id INT(10) UNSIGNED NOT NULL,
b_id INT(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# rechten tabel
CREATE TABLE test_b (
b_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
b_text VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# relaties tussen de tabellen, verwijder corresponderende gebruiker of recht als tegenhanger verdwijnt
ALTER TABLE test_a_b ADD FOREIGN KEY (a_id) REFERENCES test_a(a_id) ON DELETE CASCADE;
ALTER TABLE test_a_b ADD FOREIGN KEY (b_id) REFERENCES test_b(b_id) ON DELETE CASCADE;

# voeg gebruiker 1 toe
INSERT INTO test_a (a_text) VALUES ('user 1');

# voeg rechten toe
INSERT INTO test_b (b_text) VALUES ('role 1'), ('role 2');

# gebruiker 1 heeft twee rechten
INSERT INTO test_a_b (a_id, b_id) VALUES (1, 1), (1, 2);

# vraag alles op van gebruiker 1
SELECT a.a_id, a.a_text, b.b_id, b.b_text
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 1;

# gebruiker zonder rechten
INSERT INTO test_a (a_text) VALUES ('user 2');

# vraag alles op van gebruiker 2
SELECT a.a_id, a.a_text, b.b_id, b.b_text
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 2;

# alle rollen in 1 rij gescheiden door komma's
SELECT a.*, GROUP_CONCAT(b.b_text) AS rollen
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 2
GROUP BY a.a_id;
Gewijzigd op 06/02/2019 00:51:32 door Thomas van den Heuvel
 
Dirk Huizinga

Dirk Huizinga

06/02/2019 19:24:28
Quote Anchor link
Hoi Thomas,

Het kan inderdaad voorkomen dat een persoon geen rollen heeft gekoppeld. Dit zou betekenen dat hier actie op gezet moet worden. Dus het is goed dat het inzichtelijk wordt.

ASN is inderdaad de sleutelveld als het gaat om personen. Dit is gegeven en wijzigt verder niet.
Het verschil tussen LEFT en INNER JOIN wordt me nu wel stukken duidelijker. Nooit zo bij stil gestaan. Maar de LEFT variant is idd beter voor gebruik.

Met SELECT voorbeeld is het me gelukt om de koppelingen te leggen en de informatie uit zowel person en role tabel. Als ik het zo zie simpel :-) maar als ik het bedenken moet kom er na 2 avonden niet uit.. TOP MAN. THANKS.

Ik snap niet wat je bedoeld met GROUP_CONCAT hoor... Eens oom Goegel eens vragen.
Moet ik nog wat met die testjes doen dan?
 
Thomas van den Heuvel

Thomas van den Heuvel

06/02/2019 19:46:38
Quote Anchor link
Met de normale select haal je sommige data meerdere keren op in meerdere records (de person data wordt herhaald). Indien je group_concat() gebruikt kun je het aantal opgehaalde records reduceren tot één.

Je hoeft in principe niets te doen met de testjes, maar deze illustreren goed wat er allemaal gebeurt in termen van queryresultaten.
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.