[php/mysql] koppeltabel / joins

[Roa]

Ello,

In mijn verveling ben ik weer eens begonnen aan een site, maar ik loop vast op iets en ik kom er echt even niet meer uit. Allereerst het idee:

Wat ik nu heb is een site die boven de pagina een aantal tabbladen heeft, deze tabbladen zijn dynamisch en veranderen als de pagina veranderd. De bedoeling is dat je pagina's aan elkaar kunt koppellen en zodoende tabbladen krijgt van relevante pagina's en natuurlijk van de huidige pagina.

Voorbeeld:
Ik heb 3 pagina's, pagina A, B & C. Ik koppel pagina A aan pagina B en pagina B koppel ik aan pagina C. Als ik nu op pagina A ben, wil ik tabbladen zien van pagina A & B. Als ik op pagina B ben wil ik tabbladen van pagina A, B & C (pagina B is immers aan A en C gekoppeld) en op pagina C wil ik tabbladen van pagina C & B.

Ik hoop dat dat een beetje duidelijk is. Om dit te verwezenlijken heb ik 2 tabellen. 1 Tabel voor de pagina's, en 1 tabel die het zaakje moet koppellen:

pages
- id
- tab
- title
- content

page_link
- link_key
- page_id

Wat ik nu dus wil is dat als ik pagina A wil laten zien met id 1, ik kijk welke link_key(s) er bij page_id = 1 horen en vervolgens wil ik de page_id(s) weten die link_key(s) horen zodat ik de 'tab' kan uitlezen en een linkje kan weergeven. Dit heb ik nu alsvolgt gedaan:

PHP:

}elseif(is_numeric($_GET['pgid'])){
    //haal tabbladen en links op als er een pagina uit de database gehaald wordt
    $query_tabs = "SELECT 
                        link_key
                   FROM page_link  
                   WHERE page_id = '" . $_GET['pgid'] . "'";
    $result_tabs = mysql_query($query_tabs) or die(mysql_error());
    echo '<div id="tabbladen">';
    while($obj = mysql_fetch_object($result_tabs)){
        $query_obj = "SELECT
                        pages.tab, page_link.page_id 
                      FROM page_link 
                        INNER JOIN pages ON (pages.id = page_link.page_id) 
                      WHERE page_link.link_key = '" . $obj->link_key . "'";
        $result_obj = mysql_query($query_obj) or die(mysql_error());
        while($tab_obj = mysql_fetch_object($result_obj)){
            if($tab_obj->page_id == $_GET['pgid']){
                echo '<li><a href="index.php?pgid=' . $tab_obj->page_id . '" class="selected">' . $tab_obj->tab . '</a></li>';
            }else{
                echo '<li><a href="index.php?pgid=' . $tab_obj->page_id . '">' . $tab_obj->tab . '</a></li>';
            }
        }
    }
    echo '</div>';
}

Dit werkt opzich, maar bij pagina B krijg ik problemen. Dan krijg ik 2 keer de tab van pagina B omdat pagina B 2 link_keys heeft. Daarnaast heb ik het idee dat ik erg in-efficient bezig ben.

Ik staar me er al een tijdje op blind en ik weet het even niet meer, iemand suggesties?

[BasieP]

SQL:

SELECT *
FROM pages p
INNER JOIN page_link pl ON pl.page_id == p.id
INNER JOIN pages p2 ON pl.link_key == p2.id
WHERE p2.id == '$jerarehuidigepaginaid'

of begrijp ik je nu verkeerd?

[ Voor 18% gewijzigd door BasieP op 10-05-2006 00:07 ]

[Roa]

BasieP schreef op woensdag 10 mei 2006 @ 00:06:

SQL:

SELECT *
FROM pages p
INNER JOIN page_link pl ON pl.page_id == p.id
INNER JOIN pages p2 ON pl.link_key == p2.id
WHERE p2.id == '$jerarehuidigepaginaid'

of begrijp ik je nu verkeerd?

Hehehe, ik moest in ieder geval glimlachen om '$jerarehuidigepaginaid'

Wat jij zegt kan niet, want de link_key staat niet in de pages tabel. Dat is bewust niet zo, want anders zou je pagina's maar aan 1 link_key kunnen koppellen (het kan wel, met scheidingstekens e.d., maar dat vind ik een wat lelijke oplossing, het moet imo met queries te doen zijn...)

Sowieso vraag ik me af of het met 1 query kan, omdat je eerst met de

PHP:

$_GET['pgid']

de link_key(s) op moet halen, om vervolgens juist alle page_id(s) op te halen die die link_key(s) hebben. Met die page_id(s) wil ik dan weer in de tabel pages de 'tab' waarde krijgen, zodat ik een lijstje krijg:

PHP:

page_id: 1
tab:        FAQ
page_id: 2
tab:        About
page_id: 3
tab:        Form

Naja, ik wil geen lijstje, maar jullie snappen wat ik bedoel (als ik namelijk die dingen heb kan ik er tabbladen van maken...)

[BasieP]

Roa schreef op woensdag 10 mei 2006 @ 00:13:
[...]


Hehehe, ik moest in ieder geval glimlachen om '$jerarehuidigepaginaid'

Wat jij zegt kan niet, want de link_key staat niet in de pages tabel. Dat is bewust niet zo, want anders zou je pagina's maar aan 1 link_key kunnen koppellen (het kan wel, met scheidingstekens e.d., maar dat vind ik een wat lelijke oplossing, het moet imo met queries te doen zijn...)

je moet je toch eens verdiepen in 1 op meer relaties..

page -< pagelink >- page

dat is de hele structuuur van een koppeltabel. En dan kan je prima 100 pages aan 10000 andere pages linken..


even uitgetekent volgens jouw voorbeeld

hier je pages tabel data:
id:       name
1         A
2         B
3         C

en hier die van je page_link
link1:    link2
1         2
2         1
2         3
3         2

Sowieso vraag ik me af of het met 1 query kan, omdat je eerst met de

PHP:

$_GET['pgid']

de link_key(s) op moet halen, om vervolgens juist alle page_id(s) op te halen die die link_key(s) hebben. Met die page_id(s) wil ik dan weer in de tabel pages de 'tab' waarde krijgen, zodat ik een lijstje

dan dus wel

[ Voor 14% gewijzigd door BasieP op 10-05-2006 00:20 ]

[NMe]

BasieP schreef op woensdag 10 mei 2006 @ 00:16:
je moet je toch eens verdiepen in 1 op meer relaties..

page -< pagelink >- page

dat is de hele structuuur van een koppeltabel. En dan kan je prima 100 pages aan 10000 andere pages linken..

Niet als je, zoals TS, pagina A aan pagina B linkt, en pagina B aan pagina C. Je krijgt dan een join PER extra table; dus pagina's - 1 joins per query.

Dat dat niet optimaal is hebben we geloof ik allemaal door. Je kan beter een container-table maken, die je pagegroup noemt ofzo. Hierin sla je in elk geval een uniek ID op, en alle meta-data die geldig is voor alle pagina's die in die groep horen. Vervolgens link je pagina's niet aan elkaar, maar aan die groep. Op die manier heb je genoeg aan twee joins in elke situatie.

dan dus wel

Klopt, maar niet zoals de TS het aanpakte.

[BasieP]

-NMe- schreef op woensdag 10 mei 2006 @ 00:22:
[...]
knip
[..]

oke we stellen nu allebij een wijziging voor aan zijn DB structuur, ik heb het idee dat mijn methode het meest gebruikt word, maar is die van jouw sneller ofzo? want hij lijkt me niet makkelijker te onderhouden (qua code)

[ Voor 47% gewijzigd door BasieP op 10-05-2006 00:24 ]

[NMe]

Op jouw manier heb je nog steeds hetzelfde probleem dat de TS nu heeft?

Bovendien doe je nogal rare dingen...

en hier die van je page_link
link1:    link2
1         2
2         1
2         3
3         2

Je slaat nu elke verbinding dubbel op? Nogal wiedes dat pagina 1 bij pagina 2 hoort als pagina 2 ook bij pagina 1 hoort.

Trouwens, met mijn oplossing kun je één pagina ook onder meerdere groepen laten vallen. Dat kan handig zijn als je (bijvoorbeeld) een FAQ-pagina toegankelijk wil maken vanuit meerdere invalshoeken. Daarnaast heb ik het gevoel dat mijn suggestie beter genormaliseerd is vanwege de metadata die je in de pagegroups-tabel opslaat.

[ACM]

Aangezien ik niet helemaal snap wat er volgens jullie mis is met BasieP's eerste idee, behalve dan dat de query letterlijk fout is, nog zo'n variant:

SQL:

SELECT
    p.tab, p.page_id 
  FROM page_link p1
        JOIN page_link p2 ON p2.link_key = p1.link_key
    JOIN pages p ON p.id = p2.page_id
  WHERE p1.page_id = $_GET['pgid']

Oftewel, je eerste en tweede query gecombineerd dus. En waarschijnlijk krijg je dan weer je B dubbel, maar dat kan je natuurlijk oplossen door er DISTINCT in je SELECT bij te zetten.

[NMe]

Ik zie net dat ik het probleem van de topicstarter verkeerd begrepen heb; blijkbaar wil ie alleen de volgende pagina ophalen. Dan voldoet inderdaad die eerste query van BasieP na verbetering van die syntaxfoutjes.

[Roa]

ACM schreef op woensdag 10 mei 2006 @ 07:33:
Aangezien ik niet helemaal snap wat er volgens jullie mis is met BasieP's eerste idee, behalve dan dat de query letterlijk fout is, nog zo'n variant:

SQL:

SELECT
    p.tab, p.page_id 
  FROM page_link p1
        JOIN page_link p2 ON p2.link_key = p1.link_key
    JOIN pages p ON p.id = p2.page_id
  WHERE p1.page_id = $_GET['pgid']

Oftewel, je eerste en tweede query gecombineerd dus. En waarschijnlijk krijg je dan weer je B dubbel, maar dat kan je natuurlijk oplossen door er DISTINCT in je SELECT bij te zetten.

Hmm, deze query volg in inderdaad wel en is inderdaad waar ik naar zocht. Of dit is wat BasieP bedoelde weet ik niet, maar volgens mij begrepen we elkaar niet helemaal dan

Overigens, ik heb nu deze query, maar het wil nog niet:

SQL:

SELECT pages.tab, pages.id 
  FROM page_link p1 
      JOIN page_link p2 ON (p2.link_key = p1.link_key) 
    JOIN pages p ON (p.id = p2.page_id) 
  WHERE p1.page_id = '2'

_{(die 2 is een voorbeeld, daarin komt $_GET['pgid'])}

Waarmee ik de volgende error krijg:

code:

You have an error in your SQL syntax near 'ON (p2.link_key = p1.link_key) JOIN pages p ON (p.id = p2.page_id) ' at line 4

-NMe- schreef op woensdag 10 mei 2006 @ 09:23:
Ik zie net dat ik het probleem van de topicstarter verkeerd begrepen heb; blijkbaar wil ie alleen de volgende pagina ophalen. Dan voldoet inderdaad die eerste query van BasieP na verbetering van die syntaxfoutjes.

Nee, je begrijpt me wel prima. Ik wil alle pagina's die eenzelfde link_key hebben als de opgegeven pagina. Ik ben alleen nog niet echt een ster in JOINS e.d.....

-NMe- schreef op woensdag 10 mei 2006 @ 00:22:
Dat dat niet optimaal is hebben we geloof ik allemaal door. Je kan beter een container-table maken, die je pagegroup noemt ofzo. Hierin sla je in elk geval een uniek ID op, en alle meta-data die geldig is voor alle pagina's die in die groep horen. Vervolgens link je pagina's niet aan elkaar, maar aan die groep. Op die manier heb je genoeg aan twee joins in elke situatie.

Volgens mij is dat precies wat ik nu doe. Alleen noem ik de container-table 'page_link' en is de unieke-id de combinatie van de link_key en page_id. Maar misschien volg ik je niet helemaal

Anyhoe, ik ga maar even stoeien met bovenstaande query...

[ Voor 7% gewijzigd door Roa op 10-05-2006 15:13 ]

[NMe]

Roa schreef op woensdag 10 mei 2006 @ 15:11:
Waarmee ik de volgende error krijg:

code:

You have an error in your SQL syntax near 'ON (p2.link_key = p1.link_key) JOIN pages p ON (p.id = p2.page_id) ' at line 4

Je mag geen haakjes gebruiken bij je ON-gedeelte geloof ik. Trouwens, is die ID een string? Zo nee: haal dan de quotes weg zodat er 2 komt te staan in plaats van '2'.

Nee, je begrijpt me wel prima. Ik wil alle pagina's die eenzelfde link_key hebben als de opgegeven pagina. Ik ben alleen nog niet echt een ster in JOINS e.d.....

Je wil dus in één keer alle pagina's terug kunnen krijgen die bij elkaar horen? In dat geval...:

Volgens mij is dat precies wat ik nu doe. Alleen noem ik de container-table 'page_link' en is de unieke-id de combinatie van de link_key en page_id. Maar misschien volg ik je niet helemaal

Nee, jij hebt 2 tabellen, ik stel er 3 voor.

page
id
..
text

page_link
container_id
page_id

page_container
id
header
..
enz

Op die manier kun je pagina's makkelijk bij elkaar groeperen in een niet-fysieke container. Vervolgens doe je een dubbele join die deze tabellen linkt, en dan ben je er. Overigens hou ik hier geen rekening met volgorde, maar dat moet relatief makkelijk erbij te zetten zijn.

[Roa]

pffff, ik kom er nog steeds niet uit

Ik heb nu drie tabellen, zoals voorgesteld:

pages
id

page_link
container_id
page_id

page_container
id

Met de volgende query benader ik het:

SQL:

SELECT pages.tab, pages.id, page_link.container_id
FROM page_link
LEFT JOIN page_container ON page_container.id = page_link.container_id
RIGHT JOIN pages ON pages.id = page_link.page_id
WHERE page_link.page_id = $_GET['page_id']

Maar daar kijg ik hetvolgende resultaat mee:

code:

tab - pages.id - page_link.container_id
A        1             2 
B        2             2 
C        3             2

En dat is onmogelijk, want in page_link is helemaal geen record met container_id = 2 & page_id = 1, dus ik snap echt niet hoe dat kan.

Ik heb al verschillende varianten geprobeerd van de JOINS, maar of ik krijg alleen de record waarvan ik de page_id heb, of ik krijg alles op de 1 of andere manier. Ik ben echt

Sorry als de post wat gaar is, ik had net een hele mooie, maar die verdween in een zwart gat

[Roa]

Ik wilde toch nog even reageren omdat ik de oplossing heb na veel klooien. Heb er een paar dagen niet meer naar omgekeken, maar het is net gelukt, dus hierbij

Anyhoe, ik had dus geen derde tabel nodig (hoewel ik die nu al heb en maar houd, wie weet is het nog handig voor de toekomst), maar een constructie zoals die hieronder staat:

ACM schreef op woensdag 10 mei 2006 @ 07:33:
Aangezien ik niet helemaal snap wat er volgens jullie mis is met BasieP's eerste idee, behalve dan dat de query letterlijk fout is, nog zo'n variant:

SQL:

SELECT
    p.tab, p.page_id 
  FROM page_link p1
        JOIN page_link p2 ON p2.link_key = p1.link_key
    JOIN pages p ON p.id = p2.page_id
  WHERE p1.page_id = $_GET['pgid']

Oftewel, je eerste en tweede query gecombineerd dus. En waarschijnlijk krijg je dan weer je B dubbel, maar dat kan je natuurlijk oplossen door er DISTINCT in je SELECT bij te zetten.

Het probleem was dus dat ik 2 keer naar 1 tabel moet kijken, 1 keer met een page_id die je krijgt, de tweede keer met het resultaat van de eerste query (de container_id's die bij het gegeven page_id horen) en met het resultaat daarvan kan je dan de tab ophalen (met de page_id's die je krijg van query nummer 2).

Ik heb nu devolgende tabellen:

pages
- id
- tab

page_link
- id
- container_id
- page_id

page_container
- id
- name
- num_pages

En de volgende query:

SQL:

SELECT 
  DISTINCT (p2.page_id), pages.tab
    FROM page_link p1
      INNER JOIN page_link p2 ON ( p2.container_id = p1.container_id ) 
        INNER JOIN pages ON ( pages.id = p2.page_id ) 
    WHERE p1.page_id = $_GET['id'];

Zoals je ziet gebruik ik de tabel page_container dus eigenlijk niet, maar wellicht dat ik in de toekomst nog wel namen wil gaan hangen aan gelinkte pagina's ofzo, dus ik laat het nu maar zoals het is. Niet dat het moeilijk is in de toekomst een nieuwe tabel te maken alla page_container, maar goed, als hij er nu toch staat

Anyhoe, bedankt voor de hulp, ik ben blij dat ik het heb en ik ben een stuk wijzer geworden met JOINS

[ Voor 5% gewijzigd door Roa op 16-05-2006 01:49 ]