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: Reklamní výdaje rostou. Nejméně tisk

Reklamní výdaje rostou. Nejméně tisk

Root.cz: Legendární hra Quake slaví 20. narozeniny

Legendární hra Quake slaví 20. narozeniny

Lupa.cz: Elektronika tajemství zbavená. Jak s ní začít?

Elektronika tajemství zbavená. Jak s ní začít?

Vitalia.cz: „Sjíždět“ porno není bez rizika

„Sjíždět“ porno není bez rizika

Podnikatel.cz: OSA zdraží, ale taky přidá nový poplatek

OSA zdraží, ale taky přidá nový poplatek

DigiZone.cz: Prima a vznik slovenského kanálu

Prima a vznik slovenského kanálu

120na80.cz: Víte, co je svobodná menstruace?

Víte, co je svobodná menstruace?

DigiZone.cz: Vláda schválila digitální vysílání ČRo

Vláda schválila digitální vysílání ČRo

Měšec.cz: Co když na dovolené přijdete o kartu?

Co když na dovolené přijdete o kartu?

Měšec.cz: Investiční pasti. Děláte to, co ostatní, ale proděláváte

Investiční pasti. Děláte to, co ostatní, ale proděláváte

DigiZone.cz: AXN u FreeSatu měsíc zdarma

AXN u FreeSatu měsíc zdarma

Vitalia.cz: Kedlubna, neobyčejná zelenina

Kedlubna, neobyčejná zelenina

Lupa.cz: Milý deníčku, teď mi tě bude psát aplikace

Milý deníčku, teď mi tě bude psát aplikace

Root.cz: Xiaomi má vlastní notebook podobný Macu

Xiaomi má vlastní notebook podobný Macu

Vitalia.cz: Galerie: Strouhanka ze starých rohlíků? Kdepak

Galerie: Strouhanka ze starých rohlíků? Kdepak

DigiZone.cz: Elektrická Formule E. Práva má Arena Sport

Elektrická Formule E. Práva má Arena Sport

Lupa.cz: Kdo vykrádá LinkedIn? Zjistit to má soud

Kdo vykrádá LinkedIn? Zjistit to má soud

Lupa.cz: Hackujete? Můžete mít problém sehnat práci

Hackujete? Můžete mít problém sehnat práci

Vitalia.cz: Vakcína Cervarix je oficiálně i pro chlapce

Vakcína Cervarix je oficiálně i pro chlapce

Vitalia.cz: Syfilis: To není „nemoc z lásky“

Syfilis: To není „nemoc z lásky“