Keskustelu: Nettisivujen teko: MySQL: rivien numerointi ORDER BY:llä

Seuraava pitäisi saada tehtyä. Valitaan tietokannasta tietyt rivit ja järjestetään ne. Rivien kenttä nimeltä sijoitus pitäisi sitten saada juoksevasti numeroitua siihen järjestykseen, jonka kysely antaa.

Silmukkahan siihen tarvitaan, mutta mikä. Vähän apua kiitos.

Vaikka tämä kuulostaa aika tyhmältä, tämä on tosiaan pakko tehdä näin, sillä järjestysnumeroa tarvitaan toisessa vaiheessa, jossa sitä ei voi luoda.

Itse käyttäisin foria. Snadisti riippuu siitäkin mitä kieltä käytetään.

Jaa juu, PHP on siis käytössä. Foria minä jo kokeilin, mutta siitä ei tullut oikein mitään.

Heitä se for-silmukalla tehty koodi tänne, niin katotaan.

Tarvitaanko järjestysnumeroa vain PHP:n puolella, vai pitääkö numerointi tallentaa tietokantaan?

Joka tapauksessa voit käyttää seuraavanlaista silmukkaa:

$rivit = sql_hae("SELECT * FROM taulu ORDER BY ...");
$sijoitus = 0;
foreach ($rivit as $i => $rivi) {
	$sijoitus += 1;
	$rivit[$i]["sijoitus"] = $sijoitus;
	sql_aja("UPDATE taulu SET sijoitus = {$sijoitus} WHERE id = {$rivi["id"]}");
}

Jos rivejä on hyvin paljon, niiden kaikkien muuttaminen erillisellä UPDATE-kyselyllä voi olla hidasta. Silloin päivityksen voi tehdä suoraan tietokantaan MySQL:n muuttujien avulla jotenkin suunnilleen näin:

SET @sijoitus = 0

UPDATE taulu SET sijoitus = (@sijoitus := (@sijoitus + 1)) ORDER BY ...

Nyt näyttää tältä. Tulos on, että parhaan sijoitus on 6 ja kaikkien muiden 2, myös niillä, joilla sarja ei ole m.

$kysely = $yhteys->prepare("SELECT * FROM taulu WHERE sarja = 'm' ORDER BY 'ky', 'uy', 'parempi' DESC");
$kysely->execute(array());
$rivit = $kysely->fetch();
$sija = 0;
foreach ($rivit as $i => $rivi) {
    $sija += 1;
    $rivit[$i]["sija"] = $sija;
	$nimi = $rivi["nimi"];
	$kysely = $yhteys->prepare("UPDATE taulu SET sija = '$sija' WHERE nimi = '$nimi'");
	$kysely->execute(array());
}

Ihmeellistä purkkaa tuo koodi.

$result = $pdo->query('SELECT a, b, c FROM table WHERE foo="bar" ORDER BY first, second DESC');
$smt = $pdo->prepare('UPDATE table SET pos=? WHERE name=?');

foreach ($result as $i => &$row) {
    $smt->execute([$i, $row['name']]);
}

Ainakin tuo sun alkuperäinen kyselysi on väärin, koska sorttaat kiinteiden merkkijonojen mukaan -> järjestys ei muutu. Muuta selkeästi viallista tuossa snippetissä ei ole, joten joko kirjoitit tuon koodin tänne väärin tai sitten jätit sen viallisen osan kokonaan esittämättä. Mene ja tiedä.

The Alchemist kirjoitti:

Muuta selkeästi viallista tuossa snippetissä ei ole,

Onhan siinä sellainen virhe, että ennen silmukkaa haetaan vain yksi rivi (fetch) vaikka pitäisi hakea kaikki (fetchAll). Koko silmukassa ei ole silloin järkeä, ja jos kysyjällä olisivat varoitukset esillä, niitä tulisi.

Lisäksi on vaarallista laittaa muuttujia suoraan kyselyyn, vaan pitää antaa ne execute-metodille, kuten The Alchemist näyttää. Hänen koodiinsa pitäisi varmaan lisätä sijoitukseen +1, ja &-merkki foreach-silmukassa on turha.

No hemmetti. Mietin itsekin tuota fetch()-kutsua mutta tulin apia tarkistamatta siihen tulokseen, että se on oikein ja että fetchRow() olisi hakenut yhden rivin.

Foreach-silmukkaan laitoin &-merkin $row-muuttujan eteen siksi, että alkuperäisessä koodissa oli vähän vaikeasti tehty taulukkoon sijoittaminen ja viitteen käyttö olisi selkeyttänyt sitä. Heti seuraavaksi totesinkin koko sijoittelun turhaksi, joten jätin lopun koodin kirjoittamatta ja unohdin &-merkin sinne.

Ja vaikka itse asiassa tuolloin tuo viitteen käyttö vaikutti hyvältä idealta, niin nyt taas muistan, miksi päätin joskus aikoinaan lopettaa sen käytön kokonaan (foreach-silmukan lausekkeessa): koska php:n scopessa viite säilyy foreachin ulkopuolellekin ja siten sillä on helppo aiheuttaa mahdottomalta vaikuttavia bugeja.

FetchAll kun laittoi, niin johan alkoi toimimaan. Tiedän, ettei koodini ole mallikelpoista, mutta sovellus ei tule nettiin vaan tulospalveluksi lähiverkkoon, jossa sitä käyttää pari henkilöä. Näinollen olen ottanut tärkeämmäksi saada koodin toimimaan. Tietysti se tosin voi opettaa pahoille tavoille.