Hlavní navigace

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?

11. 4. 2005 8:10

Petr Bravenec (neregistrovaný)

U takto jednoduchého příkladu se to dá omluvit, ale pokud to má být "ukázkový" příklad, pak je to naprostá trotlovina. Velké množství dotazů se používá na několika místech - jednou se ze stejných dat generuje třeba ceník pro internetového uživatele, podruhé se ze stejných dat generuje ceník pro prodejce, doplněný o možnosti změn. Pro podobné příklady bych použil zásadně VIEW. To je předem připravený dotaz, uložený přímo v databázi, a na všech místech programu poskytující pokaždé stejná data. P…

12. 4. 2005 11:59

V první řadě vaší pozornosti doporučuji článek http://www.root.cz/clanky/stromy/ nebo http://interval.cz/clanek.asp?article=3801. Asi by bylo lepší přizpůsobit strukturu databáze.
Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

DigiZone.cz: ČRo rozšiřuje DAB do Berouna

ČRo rozšiřuje DAB do Berouna

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá

120na80.cz: Pánové, pečujte o svoje přirození a prostatu

Pánové, pečujte o svoje přirození a prostatu

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Měšec.cz: Zdravotní a sociální pojištění 2017: Připlatíte

Zdravotní a sociální pojištění 2017: Připlatíte

Lupa.cz: Teletext je „internetem hipsterů“

Teletext je „internetem hipsterů“

DigiZone.cz: Sony KD-55XD8005 s Android 6.0

Sony KD-55XD8005 s Android 6.0

Podnikatel.cz: Babiše přesvědčila 89letá podnikatelka?!

Babiše přesvědčila 89letá podnikatelka?!

Vitalia.cz: Chtějí si léčit kvasinky. Lék je jen v Německu

Chtějí si léčit kvasinky. Lék je jen v Německu

Podnikatel.cz: EET: Totálně nezvládli metodologii projektu

EET: Totálně nezvládli metodologii projektu

Lupa.cz: Avast po spojení s AVG propustí 700 lidí

Avast po spojení s AVG propustí 700 lidí

DigiZone.cz: NG natáčí v Praze seriál o Einsteinovi

NG natáčí v Praze seriál o Einsteinovi

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Podnikatel.cz: Prodává přes internet. Kdy platí zdravotko?

Prodává přes internet. Kdy platí zdravotko?

Vitalia.cz: Tesco: Chudá rodina si koupí levné polské kuře

Tesco: Chudá rodina si koupí levné polské kuře

Lupa.cz: Co se dá měřit přes Internet věcí

Co se dá měřit přes Internet věcí

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Podnikatel.cz: Víme první výsledky doby odezvy #EET

Víme první výsledky doby odezvy #EET

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte