PHP okénko: Spojování tabulek

Jakub Vrána 11. 4. 2005

Dnešní PHP okénko ukazuje možnosti využití spojování databázových tabulek na příkladě několika jednoduchých úloh.

Důležitým obratem při práci s databází je spojování tabulek. Jak už název napovídá, spojování tabulek slouží ke spojení několika tabulek, které jsou v nějakém vztahu (např. master-detail), podle zadaného kritéria.

Dejme tomu, že máme tabulku s výrobky a tabulku se skupinami, do kterých tyto výrobky patří. A dejme tomu, že chceme vypsat přehled všech výrobků spolu se skupinou, do které patří (např. jako výsledek vyhledávání). Na vyřešení tohoto úkolu se ideálně hodí spojování tabulek.

<?php
// odstrašující řešení
$result = mysql_query("SELECT id, skupina, nazev FROM vyrobky WHERE podminka");
while ($row = mysql_fetch_assoc($result)) {
    $skupina = mysql_result(mysql_query("SELECT nazev FROM skupiny WHERE id = $row[skupina]"), 0);
    echo "<a href='?id=$row[id]'>$row[nazev]</a> ($skupina)<br />\n";
}
mysql_free_result($result);

// za určitých okolností přijatelné řešení
$skupiny = array(); // array(id => nazev)
$result = mysql_query("SELECT id, nazev FROM skupiny");
while ($row = mysql_fetch_assoc($result)) {
    $skupiny[$row["id"]] = $row["nazev"];
}
mysql_free_result($result);
$result = mysql_query("SELECT id, skupina, nazev FROM vyrobky WHERE podminka");
while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='?id=$row[id]'>$row[nazev]</a> (" . $skupiny[$row["skupina"]] . ")<br />\n";
}
mysql_free_result($result);

// správné řešení
$result = mysql_query("
    SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky
    INNER JOIN skupiny ON vyrobky.skupina = skupiny.id
    WHERE podminka
");
while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='?id=$row[id]'>$row[nazev]</a> ($row[skupina_nazev])<br />\n";
}
mysql_free_result($result);
?> 

První řešení je odstrašující proto, že v každém průchodu cyklem se znovu klade dotaz do databáze, což je drahá (pomalá) operace. Druhé řešení je ideální v případě, kdy se proměnná $skupiny využije i k jinému účelu (např. k výpisu všech skupin v navigaci).

Při spojování tabulek se vyplatí dodržovat několik zásad:

  1. Všechny sloupce by měly být pomocí tečkové notace uvozeny tabulkou, ke které se vztahují. Předejde se tím problémům při přidání stejnojmenného sloupce do druhé tabulky.
  2. Ze stejného důvodu je lepší se vyhnout zápisu SELECT *. Pokud chcete získat většinu sloupců z jedné tabulky, můžete použít  SELECT tabulka.*.
  3. Pro spojení tabulek se dá používat i zápis FROM vyrobky, skupiny WHERE vyrobky.skupina = skupiny.id. Tento zápis osobně nemám rád, protože dochází ke smíchání spojovacích a ostatních podmínek, což je nepřehledné obzvláště při větším množství spojovaných tabulek.
  4. Existuje i typ spojení NATURAL JOIN, který spojí tabulky podle shodných hodnot stejnojmenných sloupců, ten ale vyžaduje volit pojmenování sloupců tak, aby vzájemně nekolidovaly (tedy např. vyrobky(id_vyrobek, id_skupina, v_nazev), skupiny(id_skupina, s_nazev)), což názvy sloupců zbytečně komplikuje.

Kromě INNER JOIN existuje ještě typ spojení LEFT JOIN, které se liší tím, že pokud v pravé tabulce není nalezen žádný odpovídající řádek, dosadí se místo něj hodnoty NULL. Tento typ spojení se dá využít v situaci, kdy nemáme jistotu, že v pravé tabulce bude existovat alespoň jeden odpovídající záznam, tedy např. pokud výrobek nemusí mít nutně přiřazenou nějakou skupinu.

<?php
$result = mysql_query("
    SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky
    LEFT JOIN skupiny ON vyrobky.skupina = skupiny.id
    WHERE podminka
");
while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='?id=$row[id]'>$row[nazev]</a> (" . (isset($row["skupina_nazev"]) ? $row["skupina_nazev"] : "bez skupiny") . ")<br />\n";
}
mysql_free_result($result);
?> 

Spojování tabulek se dá využít i ve složitějších případech. Řekněme, že chceme vypsat postupně všechny skupiny se všemi jejich výrobky.

<?php
// přehledné, ale neefektivní
$result = mysql_query("SELECT * FROM skupiny ORDER BY nazev");
while ($row = mysql_fetch_assoc($result)) {
    echo "<h3>$row[nazev]</h3>\n";
    $result1 = mysql_query("SELECT * FROM vyrobky WHERE skupina = '$row[id]'");
    while ($row1 = mysql_fetch_assoc($result1)) {
        echo "<a href='?id=$row1[id]'>$row1[nazev]</a><br />\n";
    }
    mysql_free_result($result1);
}
mysql_free_result($result);

// náročné na paměť
$vyrobky = array(); // array(skupina => array($row, ...), ...)
$result = mysql_query("SELECT id, skupina, nazev FROM vyrobky ORDER BY skupina");
while ($row = mysql_fetch_assoc($result)) {
    $vyrobky[$row["skupina"]][] = $row;
}
mysql_free_result($result);
$result = mysql_query("SELECT * FROM skupiny ORDER BY nazev");
while ($row = mysql_fetch_assoc($result)) {
    echo "<h3>$row[nazev]</h3>\n";
    if (isset($vyrobky[$row["id"]])) {
        foreach ($vyrobky[$row["id"]] as $row1) {
            echo "<a href='?id=$row1[id]'>$row1[nazev]</a><br />\n";
        }
    }
}
mysql_free_result($result);

// méně přehledné, ale efektivnější
$skupina_prev = 0;
$result = mysql_query("
    SELECT skupiny.*, vyrobky.id AS vyrobky_id, vyrobky.nazev AS vyrobky_nazev
    FROM skupiny
    LEFT JOIN vyrobky ON skupiny.id = vyrobky.skupina
    ORDER BY skupiny.nazev, skupiny.id
");
while ($row = mysql_fetch_assoc($result)) {
    if ($skupina_prev != $row["id"]) { // začíná nová skupina, vypíšeme nadpis
        echo "<h3>$row[nazev]</h3>\n";
        $skupina_prev = $row["id"];
    }
    if (isset($row["vyrobky_id"])) { // byl nalezen odpovídající výrobek
        echo "<a href='?id=$row[vyrobky_id]'>$row[vyrobky_nazev]</a><br />\n";
    }
}
mysql_free_result($result);
?> 

Vzhledem k tomu, že u třetího způsobu není na první pohled vůbec patrné, co kód dělá, zvolil bych ve většině případů spíše první nebo druhý způsob (první, pokud je málo skupin a hodně výrobků, druhý v opačném případě). Třetí případ bych tedy zvolil asi jen v situaci, kdy databázový server běží na jiném stroji než webový server a cena každého dotazu do databáze je opravdu vysoká.


Podobně laděné texty můžete najít i na autorově weblogu PHP triky.

Našli jste v článku chybu?
DigiZone.cz: Digi Sport 4 HD na Digi TV a Magio Sat

Digi Sport 4 HD na Digi TV a Magio Sat

DigiZone.cz: Kauza technik: oficiální vyjádření Novy

Kauza technik: oficiální vyjádření Novy

120na80.cz: Bonbon si schovejte na přistání

Bonbon si schovejte na přistání

Měšec.cz: Banky umí platby na kartu, jen to neříkají

Banky umí platby na kartu, jen to neříkají

Měšec.cz: Udali ho na nelegální software a přišla Policie

Udali ho na nelegální software a přišla Policie

Vitalia.cz: Za zánět močových cest mohou plavky

Za zánět močových cest mohou plavky

Podnikatel.cz: Kanceláře jako kóje? Špatný vtip

Kanceláře jako kóje? Špatný vtip

Měšec.cz: TEST: Vyzkoušeli jsme pražské taxikáře

TEST: Vyzkoušeli jsme pražské taxikáře

Vitalia.cz: Sobotní masakr žrádla, chlastu a zábavy

Sobotní masakr žrádla, chlastu a zábavy

Měšec.cz: Cool karta: recenze předplacenky

Cool karta: recenze předplacenky

Vitalia.cz: Ahold a Billa prodávaly falšované sýry

Ahold a Billa prodávaly falšované sýry

Měšec.cz: Platíme NFC mobilem. Konečně to funguje!

Platíme NFC mobilem. Konečně to funguje!

DigiZone.cz: Sat novinky: pátý kanál maďarské televize

Sat novinky: pátý kanál maďarské televize

DigiZone.cz: Loewe Subwoofer 300 pro televizory

Loewe Subwoofer 300 pro televizory

Měšec.cz: Co s reklamací, když e-shop krachuje?

Co s reklamací, když e-shop krachuje?

Vitalia.cz: Petr Koukal: Až rakovina mi zkvalitnila život

Petr Koukal: Až rakovina mi zkvalitnila život

Lupa.cz: IT scéna po brexitu: přijde exodus vývojářů?

IT scéna po brexitu: přijde exodus vývojářů?

DigiZone.cz: Přechod na DVB-T2? Kolem miliardy...

Přechod na DVB-T2? Kolem miliardy...

Podnikatel.cz: Fotogalerie: Jesenka už má skoro 50 let

Fotogalerie: Jesenka už má skoro 50 let

Podnikatel.cz: Český zákazník nakupuje v čínských e-shopech

Český zákazník nakupuje v čínských e-shopech