Vlastní pořadí řádků v tabulce

Školení, která pořádám

Některé objekty není možné řadit podle nějakého přirozeného kritéria (např. podle abecedy), ale je nutné jejich pořadí určit ručně. Jde třeba o stránky webové prezentace, kdy na prvním místě chceme stránku „O nás“ a na posledním „Kontakt“. V takovém případě nezbývá nic jiného, než do tabulky přidat sloupec, který bude toto pořadí uchovávat.

Jak obsah tohoto sloupce udržovat? Nejjednodušší je nechat to na uživateli - pokud ale v řádcích bude uloženo pořadí 1, 2, 3 a uživatel bude chtít nový záznam umístit na druhé místo, tak bude muset stávající záznamy ručně přečíslovat (samozřejmě pokud pro uložení pořadí nepoužijeme desetinná čísla). Lepší tedy bude obsah tohoto sloupce udržovat v konzistentní podobě (číslováno spojitě od jedničky) programově. Kromě pohodlnější práce pro uživatele to navíc přináší další výhody – např. pro získání prvního záznamu nemusíme pracně zjišťovat minimum, ale stačí vzít záznam s poradi = 1.

Prohazování prvků

Nově vkládané záznamy můžeme přidávat na konec a pořadí stávajících záznamů určovat jejich prohazováním:

<?php
// vložení na konec
mysql_query("INSERT INTO tabulka (poradi, nadpis) SELECT IFNULL(MAX(poradi), 0) + 1, '" . mysql_real_escape_string($_POST["nadpis"]) . "' FROM tabulka");

// kód pro prohození dvou prvků
if ($_GET["dolu"]) {
    mysql_query("UPDATE tabulka SET poradi = " . (2 * $_GET["dolu"] + 1) . " - poradi WHERE poradi IN (" . intval($_GET["dolu"]) . ", " . ($_GET["dolu"] + 1) . ")");
}

// odkazy pro prohazování
$result = mysql_query("SELECT * FROM tabulka ORDER BY poradi");
for ($i=0; $row = mysql_fetch_assoc($result); $i++) {
    echo ($i ? "<a href='?dolu=" . ($row["poradi"] - 1) . "'>nahoru</a>" : "nahoru") . " ";
    echo ($i+1 < mysql_num_rows($result) ? "<a href='?dolu=$row[poradi]'>dolů</a>" : "dolů") . " ";
    echo htmlspecialchars($row["nadpis"]) . "<br />\n";
}
mysql_free_result($result);
?>

Kód pro prohození prvků vezme prohazovaný a za ním následující prvek a do jeho pořadí uloží pořadí toho druhého (např. pro dolu=3 se zpracují prvky 3 a 4 a do jejich pořadí se uloží 7-3=4, resp. 7-4=3).

Variantou tohoto řešení by bylo vytvořit rozhraní na straně klienta a posílat vždy celou posloupnost všech prvků – pořadí všech záznamů bychom potom mohli nastavit funkcí FIELD.

Zvolení pořadí

U krátkých seznamů určení pořadí prohazováním prvků není problém, ale přesouvat záznam třeba z 28. pozice na 2. by se mi opravdu nechtělo. Proto můžeme uživateli dát možnost určit pořadí ručně. Do formuláře přidáme políčko poradi (může to být běžné textové políčko nebo <select> umožňující vybrat, na místo které položky se daný záznam přesune) a zpracujeme ho takto:

<?php
mysql_query("LOCK TABLES tabulka WRITE");

if (!$_GET["select"]) { // vložení nového záznamu
    $max_poradi = "IFNULL(MAX(poradi), 0) + 1";
    $poradi = (strlen($_POST["poradi"]) ? "LEAST($max_poradi, " . max($_POST["poradi"], 1) . ")" : $max_poradi);
    if (mysql_query("INSERT INTO tabulka (poradi, nadpis) SELECT $poradi, '" . mysql_real_escape_string($_POST["nadpis"]) . "' FROM tabulka") && strlen($_POST["poradi"])) {
        mysql_query("UPDATE tabulka SET poradi = poradi + 1 WHERE poradi >= " . intval($_POST["poradi"]) . " AND id != " . mysql_insert_id());
    }
} else { // aktualizace existujícího záznamu
    $row = mysql_fetch_assoc(mysql_query("SELECT poradi FROM tabulka WHERE id = " . intval($_GET["select"])));
    if ($row["poradi"] != $_POST["poradi"]) {
        $max_poradi = mysql_result(mysql_query("SELECT MAX(poradi) FROM tabulka"), 0);
        $poradi = (strlen($_POST["poradi"]) ? min($max_poradi, max(1, $_POST["poradi"])) : $max_poradi);
        if (mysql_query("UPDATE tabulka SET poradi = $poradi, nadpis = '" . mysql_real_escape_string($_POST["nadpis"]) . "' WHERE id = " . intval($_GET["select"]))) {
            $where = "id != " . intval($_GET["select"]) . " AND poradi BETWEEN " . min($row["poradi"], $poradi) . " AND " . max($row["poradi"], $poradi);
            mysql_query("UPDATE tabulka SET poradi = poradi " . ($row["poradi"] < $poradi ? "-" : "+") . " 1 WHERE $where");
        }
    }
}

mysql_query("UNLOCK TABLES");
?>

Pokud uživatel při vložení nového záznamu určil pořadí, tak se omezí na interval 1 až první-volné-pořadí a prvky, které jsou za ním, se posunou. Pokud pořadí neurčil, tak se použije stejně jako v předchozím řešení první volné místo. Při aktualizace záznamu se nejprve posunou záznamy mezi starým a novým pořadím (buď o jedničku dozadu nebo dopředu) a následně se pořadí (opět omezené na platný interval) nastaví u aktualizovaného záznamu.

Index

Protože podle daného sloupce budeme řadit, tak je určitě vhodné nad ním vytvořit index. Přestože budou ve výsledku prvky jednoznačné, tak při provádění operací budou chvilku kolidovat, proto nad sloupcem nemůžeme vytvořit unikátní klíč, ale jen obyčejný.

Skupiny

Situace se trochu zkomplikujeme v případě, že pořadí v rámci tabulky nebude absolutní, ale bude samostatné v každé skupině. Vložení nového prvku o nic složitější nebude, ale při aktualizaci musíme záznamy ve staré skupině posunout dozadu a v nové dopředu:

<?php
$row = mysql_fetch_assoc(mysql_query("SELECT skupina, poradi FROM tabulka WHERE id = " . intval($_GET["select"])));
if ($row["poradi"] != $_POST["poradi"] || $row["skupina"] != $_POST["skupina"]) {
    $max_poradi = mysql_result(mysql_query("SELECT IFNULL(MAX(poradi), 0)" . ($row["skupina"] != $_POST["skupina"] ? " + 1" : "") . " FROM tabulka WHERE skupina = " . intval($_POST["skupina"])), 0);
    $poradi = (strlen($_POST["poradi"]) ? min($max_poradi, max(1, $_POST["poradi"])) : $max_poradi);
    if (mysql_query("UPDATE tabulka SET skupina = " . intval($_POST["skupina"]) . ", poradi = $poradi, nadpis = '" . mysql_real_escape_string($_POST["nadpis"]) . "' WHERE id = " . intval($_GET["select"]))) {
        mysql_query("UPDATE tabulka SET poradi = poradi - 1 WHERE id != " . intval($_GET["select"]) . " AND skupina = $row[skupina] AND poradi > $row[poradi]");
        mysql_query("UPDATE tabulka SET poradi = poradi + 1 WHERE id != " . intval($_GET["select"]) . " AND skupina = " . intval($_POST["skupina"]) . " AND poradi >= $poradi");
    }
}
?>

Přečíslování

Pokud číslování potřebujeme doplnit do existující tabulky nebo pokud ho třeba kvůli přímému zásahu do databáze potřebujeme vygenerovat znovu, můžeme ve většině databází použít SQL příkaz UPDATE tabulka SET poradi = (SELECT COUNT(*) FROM tabulka t WHERE skupina = tabulka.skupina AND id <= tabulka.id). V MySQL ale tento příkaz skončí chybou č. 1093, protože při aktualizaci nelze pokládat poddotazy do téže tabulky. Vyřešit to můžeme spuštěním posloupnosti těchto příkazů: SET @skupina = 0; SET @poradi = 0; UPDATE tabulka SET poradi = (@poradi:= IF(skupina = @skupina, @poradi, (@skupina:= skupina) - skupina) + 1) ORDER BY skupina, id;. Příkaz využívá schopnosti MySQL určovat pořadí, ve kterém se budou záznamy aktualizovat. U každého záznamu si kromě nastavení jeho pořadí toto pořadí uložíme také do proměnné, která se použije při aktualizaci dalšího záznamu ze stejné skupiny. Bez zdánlivě zbytečného příkazu SET @poradi = 0 si MySQL pořadí nezapamatuje.

Jakub Vrána, Výuka, 26.2.2007, diskuse: 11 (nové: 0)

Diskuse

Vesta:

Řešil jsem stejný problém následujícím způsobem: v případě vložení jsem to rozdělil na dva kroky, nejdříve jsem posunul číslování pomocí SET poradi = poradi + 1 WHERE poradi >= $n a potom jsem pomoci INSERTU vložil řádek do tabulky kde pořadí bylo $n. U mazání byl postup opačný tzn. smazat řádek  a potom SET poradi = poradi - 1 WHERE poradi >= $n.

ikona Jakub Vrána OpenID:

Ano, přesně toto je popsáno v části Zvolení pořadí. Kromě toho je potřeba taky ošetřit okrajový případ, kdy uživatel zadá číslo mimo 1..max.

Hds:

Já uživatelské řazení řeším tak, že v tabulce kterou chci takto řadit mám sloupec "item_order" (INT), ve kterém je číslo jako pořadí určité položky. Čísla nejsou po jedničce (1,2,3,4), ale např. po pěti (5,10,15,20), aby uživatel měl dostatečný prostor např. pro vkládání více položek mezi ostatní dvě položky najednou. Ve výpisu položek jsou inputy, do kterých si vypíše pořadí jaké chce a tlačítkem to odešle k seřazení. Po seřazení se čísla opět seřadí vzestupně např. po těch pěti.

Honza Odvárko:

10 PRINT "Tohle znali ";
20 PRINT "uz Kemeny a Kurtz";
25 PRINT "(chytry chlapici) ";
30 PRINT "v dobach BASICu";
40 GOTO 10

ikona Jakub Vrána OpenID:

Problém tohoto řešení se ukáže tehdy, když dojde k zaplněnosti čísel 15,16,17,18,19,20 a je potřeba něco vložit na pozici 18. Navíc když vím, že chci mít třeba fotku na třetím místě, tak se musím koukat do seznamu čísel a přemýšlet, které zvolím.

Programátorsky jednoduché, uživatelsky složité.

Martin Ševčík:

Myslím, že "Hds" to používá špatně a byl správně opraven Honzou, ale ten zase neupozornil na důležitost přepočítání pořadí u všech záznamů.

Používám zmíněné číslování po desítkách (tedy 10, 20, 30).
Když chci uložit něco na 18. místo, zjistím si původní pořadí abych věděl, jestli nová hodnota bude 175 nebo 185, a pak načtu všechny záznamy podle pořadí a po desítkách jim přidám hodnotu pořadí krát deset.
V tomto případě bude vypadat pole třeba takto: ... 160 => 16, 170 => 17, 175 => 18, 180 => 19 ...

Možnou nevýhodu vidím v nutnosti přepočítání při velkém počtu řádků, funguje vše ale spolehlivě a jednoduše.

Smajchl:

Ja to porad nechapu a co kdyz budu chtit mezi 18 a 19 presunout 20 polozek?

ikona D1ce:

Já na tohle mám akorát sloupec priority, který má téměř tatáž funkci, jen řazení nezávisí čistě na něm, jako ten Váš, ale netušil jsem jak může být přenastavování složité při větším počtu položek, když uživatel nastavuje jen to číslo manuálně. Děkuji pěkně za inspiraci. :)

Non_E:

Problém s unikátním klíčem se afair dá řešit dočasným nastavením hodnoty na NULL.

Johan:

Jakým nejoptimálnějším způsobem řešíte přečíslování při DELETE záznamu? Napadl mě trigger, ale ten bohužel nepodporuje změnu dat v tabulce nad kterou je prováděná operace.

ikona Václav Černík:

Zkuste toto. Hromadně to přečísluje pořadí.

SET @ordering_inc = 1;SET @new_ordering = -1;UPDATE categories SET `order` = (@new_ordering := @new_ordering + @ordering_inc) ORDER BY `order` ASC;

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2017 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.