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.

widgety

<?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: Světový pohár v přímém přenosu na ČT

Světový pohár v přímém přenosu na ČT

DigiZone.cz: Banaxi: videa kdekoli na světě

Banaxi: videa kdekoli na světě

120na80.cz: Ochablé svaly mohou značit vážnou nemoc

Ochablé svaly mohou značit vážnou nemoc

Lupa.cz: Blíží se konec Wi-Fi sítí bez hesla?

Blíží se konec Wi-Fi sítí bez hesla?

Vitalia.cz: Tohle jsou nejlepší česká piva podle odborníků

Tohle jsou nejlepší česká piva podle odborníků

Lupa.cz: Patička e-mailu závazná jako vlastnoruční podpis?

Patička e-mailu závazná jako vlastnoruční podpis?

Lupa.cz: Další Češi si nechali vložit do těla čip

Další Češi si nechali vložit do těla čip

Vitalia.cz: Voda z Vltavy před a po úpravě na pitnou

Voda z Vltavy před a po úpravě na pitnou

Lupa.cz: Jak se prodává firma za miliardu?

Jak se prodává firma za miliardu?

120na80.cz: Galerie: Čínští policisté testují českou minerálku

Galerie: Čínští policisté testují českou minerálku

Vitalia.cz: Tradiční čínská medicína a rakovina

Tradiční čínská medicína a rakovina

DigiZone.cz: Technisat připravuje trojici DAB

Technisat připravuje trojici DAB

Lupa.cz: Jak levné procesory změnily svět?

Jak levné procesory změnily svět?

Vitalia.cz: Muž, který miluje příliš. Ženám neimponuje

Muž, který miluje příliš. Ženám neimponuje

Vitalia.cz: Jsou vegani a vyrábějí nemléko

Jsou vegani a vyrábějí nemléko

Podnikatel.cz: Instalatér, malíř a elektrikář. "Vymřou"?

Instalatér, malíř a elektrikář. "Vymřou"?

DigiZone.cz: Digi Slovakia zařazuje stanice SPI

Digi Slovakia zařazuje stanice SPI

Vitalia.cz: 5 chyb, které děláme při skladování potravin

5 chyb, které děláme při skladování potravin

DigiZone.cz: Rapl: seriál, který vás smíří s ČT

Rapl: seriál, který vás smíří s ČT

Podnikatel.cz: Udělali jsme velkou chybu, napsal Čupr

Udělali jsme velkou chybu, napsal Čupr