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.
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.
Jakub Vrána 
:
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.
Jakub Vrána : 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
Jakub Vrána :
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é.
Jakub Vrána : 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?
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. :)
D1ce: 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.
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;
Václav Černík: Diskuse je zrušena z důvodu spamu.
© 2005-2024 