[MySQL - PHP] Riordinamento colonna progressiva

[Genymus]

Salve,
ho un problema con una tabella.

Devo fare uno script di inserimento foto ordinate dal cliente.
Lo script lo ho fatto, però mi sembra impossibile che debba riordinare ogni volta tutta la tabella quando sposto una foto su o giù.

In pratica la mia richiesta è:
Avendo una tabella composta così:
Nome, Ordine
a, 1
b, 2
c, 3
d, 4

Come posso fare per ottenere questo risultato:
Nome, Ordine
a, 1
c, 2
b, 3
d, 4

In poche parole, voglio che eliminando, aggiornando, od inserendo una riga nella tabella in una posizione specifica, mysql ricalcoli automaticamente il valore di Ordine in modo progressivo.

C'è un modo?
Grazie

[Genymus]

salve,
no, il numero di ordinamento è un normale int che però deve essere sempre progressivo, ma si può ripetere a seconda della categoria a cui appartiene la foto.
esempi:
cat 1 > 1234567
cat 2 > 1234

però vorrei che spostando la foto numero 5 alla prima posizione, questa diventi 1 e tutte le altre foto si shiftino in modo progressivo.
naturalmente posso fare tutto questo con query di update, ma se ho 50 foto, dovrei fare 50 query.
ovviamente potrei anche fare una procedura mysql ma volevo sapere se c'era un modo più semplice.

grazie

[Genymus]

Esatto, ma io vorrei eseguire solo una update invece che una decina.
potrei collegare un trigger alla modifica del campo per ricalcolare la progressione, ma volevo sapere se esisteva qualcosa di già fatto di mysql, o se esistevano alternative da un punto di vista logico sul problema.

comunque grazie

[Daniels118]

Prima di rispondere alla tua domanda, sappi che esiste un altro modo per mantenere un ordine tra gli elementi, ovvero quello delle linked list.
La tabella ha bisogno di due colonne, ovvero una chiave primaria e un campo "next" che punterà alla chiave primaria dell'elemento successivo, l'ultimo elemento ovviamente avrà null. Serve inoltre qualcosa per identificare il primo elemento, per esempio una riga fittizia che non verrà inserita nei risultati.
La query estrae gli elementi non ordinati, l'ordinamento avviene da codice seguendo i valori del campo next.
Questo metodo è ottimo quando devi estrarre sempre tutti gli elementi dalla tabella; risulta invece inefficiente se devi paginare, perché bisogna sempre scorrere gli elementi uno alla volta per ricostruire l'ordine.

Tornando alla tua domanda, l'operazione di aggiornamento potrebbe essere automatizzata con dei trigger, oppure gestita tramite opportune query. Non è vero che se hai 50 righe devi fare 50 query, puoi farne una sola che aggiorna tutte le righe in un colpo solo.
Conviene consentire i valori duplicati sulla colonna posizione, in questo modo si risparmiano parecchi update. Supponiamo di voler spostare l'elemento 5 in posizione 2, dovrai usare questo algoritmo:
1) recuperi la chiave primaria dell'elemento in posizione 5:
SELECT id FROM tabella WHERE posizione=5 LIMIT 1
2) crei uno spazio in posizione 2 spostando gli elementi in avanti:
UPDATE tabella SET posizione=posizione+1 WHERE posizione BETWEEN 2 AND (5-1)
3) sposti l'elemento desiderato in posizione 2:
UPDATE tabella SET posizione=2 WHERE id=:id
Come vedi si riesce a fare tutto con solo 3 query.

[Daniels118]

Ehhh, 1-2 minuti è un tempo enorme, per arrivare a tanto servono milioni di righe anche su hardware scarso.
Comunque non esiste un metodo migliore in assoluto, ma di sicuro ne esiste uno più indicato in base alle esigenze specifiche. Ad esempio un'attesa di 2 minuti potrebbe essere ragionevole se l'operazione viene fatta una sola volta all'anno, mentre è inaccettabile per operazioni frequenti. Di contro il metodo delle linked list offre un tempo di accesso costante in modifica, ma penalizza la consultazione.
E' una questione di equilibrio, ragionare sul problema ed analizzare i dati è fondamentale, senza di essi si possono fare solo ipotesi.

[Genymus]

Credo che la soluzione di Daniels118 sia tra le migliori: molto efficiente e senza problematiche... proprio quello che serve a me.
la funzione dovrei utilizzarla sulla parte amministrativa, che verrà usata regolarmente ma circa una volta alla settimana.
mentre il mio interesse era quello di rendere il recupero dei dati molto veloce e senza tanti fronzoli, per questo avevo pensato a fare una tabella strutturata in quel modo.
credo di aver risolto comunque.

vi ringrazio tutti per l'aiuto, siete stati tutti molto cortesi e disponibili.