3 commenti my script, mysql, php

Nei casi in cui si voglia personalizzare l'ordine di visulizzazione dei record estratti occorrerà prevedere all'interno della tabella in oggetto un apposito campo di tipo INT adibito allo scopo, che possiamo chiamare, ad esempio, 'my_order'. La query SELECT pertanto sarà:

SELECT * FROM tabella ORDER BY my_order

 

Fin qui tutto facile.

 

Aspetto complesso, invece, starà nel sviluppare le query per creare il classico meccanismo di "sposta su" e "sposta giù" (up and down) dei vari record così da poter gestire l'ordine di visulizzazione di questi.

 

La classe OrderTable che oggi vi presento ci servirà proprio a tale scopo ma richiede alcune importanti accortezze.

In particolare, il campo che gestirà l'ordine di estrazione, che nella tabella utilizzata per gli esempi è 'my_order', dovrà essere assolutamente senza "buchi" e senza "doppioni":

  • se abbiamo all'interno della tabella 10 records il campo my_order dovrà assumere valori da 1 ad 10 univoci per ogni record; tale rilievo assumerà rilevanza in caso di una INSERT in tale tabella.
  • in caso di eliminazione si dovrà colmare il buco che si andrà a creare.

 

Sono prorpio questi aspetti che occorre tener presente per implementare un meccanismo di gestione della posizione di ogni record.

 

La classe OrderTable

<?php
Class OrderTable{
	protected $table;
	protected $orderfield;
	protected $old_position;
	protected $new_position;
	protected $max_position;
	
	public function __construct($tbl, $field){
		$this->table = $tbl;
		$this->orderfield = $field;
		}
	
	public function get_maxorder(){
		$query = "SELECT MAX(".$this->orderfield.") as maxorder FROM ".$this->table."";
		$result = mysql_query($query) or die(mysql_error());
		$row = mysql_fetch_array($result);
		$this->max_position = (!$row['maxorder']) ? 0 : $row['maxorder'];	
		return $this->max_position;
		}
	
	public function up($start_position, $moved = 1){
		return $this->moved($start_position, -$moved);
		}
	
	public function down($start_position, $moved = 1){
		return $this->moved($start_position, $moved);
		}
		
	public function moved($start_position, $moved){
		$this->old_position = intval($start_position);
		$this->new_position = $this->old_position + intval($moved);
		$this->get_maxorder();
		if($this->new_position>0 AND $this->new_position<=$this->max_position){
			$result_move = $this->query_moved();
			return $result_move;
			}
		return FALSE;
		}
		
	protected function query_moved(){
		$query = "
				UPDATE ".$this->table." 
				SET ".$this->orderfield." = CASE ".$this->orderfield." 
				WHEN ".$this->old_position." THEN ".$this->new_position." 
				ELSE ".$this->orderfield." + SIGN(".$this->old_position."-".$this->new_position.")
				END
				WHERE ".$this->orderfield." 
					BETWEEN 
						LEAST( ".$this->new_position.", ".$this->old_position." ) AND 
						GREATEST( ".$this->new_position.", ".$this->old_position." )";
						
		mysql_query($query) or die(mysql_error());
		if(mysql_affected_rows()>0){
			return TRUE;
			}
		return FALSE;
		}
	
	public function get_insert_position(){
		return ($this->get_maxorder() + 1);
		}
	
	public function delete_position($delete){
		// eliminiamo
		$query = "DELETE FROM ".$this->table." WHERE ".$this->orderfield." = ".intval($delete)." LIMIT 1";
		mysql_query($query) or die(mysql_error());
		// colmiamo il buco
		$query_2 = "UPDATE ".$this->table." SET ".$this->orderfield." = ".$this->orderfield." -1 WHERE ".$this->orderfield.">".intval($delete)."";
		mysql_query($query_2) or die(mysql_error());
		}
	}
?>

 

Tabella di esempio

A puro fine esemplificativo gli esempi riportati in questa pagina fanno riferimento alla tabella mysql qui di seguito riportata e popolata da alcuni record.

CREATE TABLE IF NOT EXISTS `prodotti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL,
  `my_order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


INSERT INTO `prodotti` (`id`, `nome`, `my_order`) VALUES
(1, 'righello', 1),
(2, 'evidenziatore', 2),
(3, 'matita', 3),
(4, 'gomma', 4),
(5, 'temperamatite', 5),
(6, 'colla', 6),
(7, 'pastello', 7),
(8, 'penna', 8),
(9, 'quaderno', 9),
(10, 'diario', 10);

 

Per estrarre i recod all'interno della nostra pagina web in cui faremo i test avremo molto banalmente:

<html>
<head>
<title>Test OrderTable</title>
</head>
<body>
<?php
$conn = mysql_connect('localhost', 'root', '') or die(mysql_error());  
$selezione_db = mysql_select_db('test', $conn) or die(mysql_error()); 

$query = "SELECT * FROM prodotti ORDER BY my_order";
$result = mysql_query($query) or die(mysql_error());
echo '<table cellspacing="5" cellpadding="5" border="1">';
echo '<tr>
		<td><p><strong>Prodotto</strong></p></td>
		<td><p><strong>Sposta sù</strong></p></td>
		<td><p><strong>Sposta giù</strong></p></td>
		<td><p><strong>Elimina</strong></p></td>';

while($row = mysql_fetch_array($result)){
	echo '
		<tr>
			<td><p>'.$row['nome'].'</p></td> 
			<td><a href="move.php?up='.$row['my_order'].'">UP</a></td>
			<td><a href="move.php?down='.$row['my_order'].'">DOWN</a></td> 
			<td><a href="move.php?delete='.$row['my_order'].'">DELETE</a></td>
		</tr>';
	}
	
echo '</table>';
?>
</body>
</html>

 

Occorre prestare attenzione alla costruzione dei link per l'up, il down e l'eliminazione. 

 

La pagina che materialmente eseguirà lo spostamento dei record e l'eliminazione dei record sarà move.php la quale sarà così strutturata:

<?php
require_once('OrderTable.class.php');

$conn = mysql_connect('localhost', 'root', '') or die(mysql_error());  
$selezione_db = mysql_select_db('test', $conn) or die(mysql_error()); 

$update_order = New OrderTable('prodotti', 'my_order');

if(isset($_GET['up'])){
	$update_order->up($_GET['up']);
	}
	
else if(isset($_GET['down'])){
	$update_order->down($_GET['down']);
	}
	
else if(isset($_GET['delete'])){
	$update_order->delete_position($_GET['delete']);
	}

header('location: pagina.php');	
?>

 

Occorre notare che nell'istanziare la classe occorrerà indicare come due parametri il nome della tabella su cui si andrà ad agire e il nome del campo che gestisce l'ordine di visualizzazione dei record: nel nostro esempio la tabella è 'prodotti' e il campo in oggetto è 'my_order'.

Poi a seconda della variabile get ricevuta andremo ad eseguire il metodo up(), down(), o delete_position(). Tutti questi metodi ricevono come parametro unicamente la posizione attuale del record.

 

I metodi up() e down() possono, inoltre ricevere come parametro opzionale anche il numero di posizione da incrementare/decrememntare (di default pari a 1). Di seguito un esempio di spostamento di 4 posizioni verso il basso:

$update_order->down($_GET['up'], 4);

 

Vediamo cosa c'è dietro questa classe e, in particolare la query che esegue la modifica dell'ordine dei record.

 

La query per eseguire la modifica dell'ordine

Poniamo di voler spostare 'gomma' con my_order pari 4 in posizione 8. Nella seguente immagine è illustrato gli efetti che dovrà avere la query di UPDATE che andremo ad eseguire.

effetti della query di update position

 

La posizione 4 (nella classe indicata con $this->old_posizion) dovrà diventare 8 ($this->new_position).

Inoltre, i record che hanno una posizione compresa fra 5 ed 8 (estremi inclusi) dovranno essere diminuiti di 1.

 

Quindi, i record coinvolti nella query di UPDATE saranno tutti quelli che avranno un valore di 'my_order' compreso fra la posizione iniziale (4) e quella finale (8) del record di cui vogliamo modificare.

 

Per eseguire tale operazione la classe prevede il metodo query_moved() che contiene una query solo apparentemente complessa.

protected function query_moved(){
	$query = "
			UPDATE ".$this->table." 
			SET ".$this->orderfield." = CASE ".$this->orderfield." 
			WHEN ".$this->old_position." THEN ".$this->new_position." 
			ELSE ".$this->orderfield." + SIGN(".$this->old_position."-".$this->new_position.")
			END
			WHERE ".$this->orderfield." 
				BETWEEN 
					LEAST( ".$this->new_position.", ".$this->old_position." ) AND 
					GREATEST( ".$this->new_position.", ".$this->old_position." )";
	mysql_query($query) or die(mysql_error());
	if(mysql_affected_rows()>0){
		return TRUE;
		}
	return FALSE;
	}

 

Nel caso dello spostamento preso come esempio (da posizione 4 a posizione 8) questa diventerà:

UPDATE 
	prodotti 
SET my_order = CASE my_order 
WHEN 4 THEN 8 
ELSE my_order + SIGN(4-8) 
END 
WHERE my_order 
	BETWEEN 
	LEAST( 8, 4 ) AND 
	GREATEST( 8, 4 )

 

Cerchiamo di "spogliarla" di alcune complessità:

  • la funzione di mysql SIGN ci restituisce il -1 se il suo parametro è negativo, +1 se il parametro è positivo, 0 se il suo parametro è 0; all'interno della query il parametro è costituito dalla differenza fra vecchia e la nuova posizone; pertanto nello spostamento verso il basso da 4 a 8 (4-8 = -4) essendo il parametro negativo la funzione SIGN(-4) ci darà -1 (con analoga logica nel caso dello spostamento verso l'alto ci darà 1).
  • le funzioni LEAST e GREATEST di mysql ci daranno rispettivamente il valore più basso e più alto rispetto ai parametri indicati pertanto LEAST(4, 8) ci darà 4, mentre GREATEST(4, 8) ci darà 8.

Quindi possiamo riscrivere la query che eseguiremo in questo modo:

UPDATE 
	prodotti 
SET my_order = CASE my_order 
WHEN 4 THEN 8 
ELSE my_order -1 
END 
WHERE my_order BETWEEN 4 AND 8

 

Quindi, verranno modificati solo i record compresi fra 4 e 8 ('WHERE my_order BETWEEN 4 AND 8') e tale modifica sarà: nel caso in cui il campo my_order è pari a 4 questo diventerà 8 ('WHEN 4 THEN 8'); mentre negli altri casi si diminuirà il valore di 1 ('ELSE my_order - 1').

 

Come è possibile notare questo è la modifica che avevamo rappresentato nella precedente immagine.

 

Eliminazione dei record

Nel caso in cui si debba eliminare un record dovremo evitare il venirsi a formare del "buco" all'interno del campo 'my_order' il quale, ricordo, deve essere assolutamente univoco per ogni record e senza buchi.

 

La classe prevede al suo interno un apposito metodo per l'eliminazione, delete_position(), che richiede come parametro la posizione da eliminare.

public function delete_position($delete){
	// eliminiamo
	$query = "DELETE FROM ".$this->table." WHERE ".$this->orderfield." = ".intval($delete)." LIMIT 1";
	mysql_query($query) or die(mysql_error());
	// colmiamo il buco
	$query_2 = "UPDATE ".$this->table." SET ".$this->orderfield." = ".$this->orderfield." -1 WHERE ".$this->orderfield.">".intval($delete)."";
	mysql_query($query_2) or die(mysql_error());
	}

 

L'eliminazione del record con una determinata posizione (ricordo che questa è univoca)  è seguita da una successiva query di modifica delle posizioni successive rispetto a quella oggetto dell'eliminazione decrementando di 1 il loro valore.

 

Inserimento di un nuovo record

In fase di inserimento il nuovo record dovrà essere posizionato necessariamente nella posizione successiva all'ultima: se, ad esempio, abbiamo 10 record il successivo inserimento dovrà avere come valore di my_order 11.

Per ottenere tale valore utilizzeremo il metodo get_insert_position():

public function get_insert_position(){
	return ($this->get_maxorder() + 1);
	}

 

Pertanto nel formulare una query di inserimento faremo:

require_once('OrderTable.class.php');

$conn = mysql_connect('localhost', 'root', '') or die(mysql_error());  
$selezione_db = mysql_select_db('test', $conn) or die(mysql_error()); 

$update_order = New OrderTable('prodotti', 'my_order');

$my_order_insert = $update_order->get_insert_position();
$query = "INSERT INTO prodotti SET
			nome='taglierino',
			my_order='" .$my_order_insert. "'";
mysql_query($query) or die(mysql_error());

 

Conclusioni

Questo è il metodo che io seguo per gestire l'ordine dei record all'interno delle tabelle.

Per particolari e specifiche necessità alle volte può essere utile realizzare delle classi (eventualmente extends) con cui gestire tabelle specifiche che richiedono particolari operazioni.

 

Conosci qualche altro metodo per gestire l'ordine delle tabelle? Hai qualche altro suggerimento? Hai provato la classe e ti ha dato dei problemi? Lasciami un commento e sarò felice di poterti aiutare o di darti qualche suggerimento.

Olimpio Romanella

Sono un appassionato di Web Developing con un particolare debole per php. Mi dedico principalmente dello sviluppo back-end ed in particolare programmazione lato server con php, sviluppo di database relazionali MySql e progettazione di CMS di piccole e medie dimensioni.

Mi avvalgo del framework javascript Jquery, utilizzando molti dei suoi plugin e nei dei miei progetti utilizzo spesso il framework MVC Codeigniter.

3 Commenti presenti

avatar oly1982

oly1982

11 July 2012 ore 20:38

Devi eseguire delle modifiche alla classe.
In base a quanto mi dici io farei così:
- andrei a creare nella classe un'ulteriore proprietà che chiamerei
protected $addCondition;
- modificherei il costruttore permettendo di settare un ulteriore parametro opzionale, che utilizzerei per gestire le condizioni di cui parli;
- in base al valore di tale proprietà andrei a modificare le query presenti nei vai metodi.

avatar Francesco

Francesco

11 July 2012 ore 14:58

Scusa, mi spiego meglio e vado direttamente al pratico:
come posso inserire un ulteriore "WHERE" all'interno della funzione query_moved() in modo da limitare i cambiamenti di ordinamento solo per gli elementi che anno lo stesso codice di sottoarea che ho già passato all'interno dei link UP, DOWN, DELETE.
Ora dovrebbe essere molto più chiaro di prima :)

thx

avatar Francesco

Francesco

11 July 2012 ore 14:38

grazie mille per questo articolo, era quasi quello che cercavo.
volevo chiederti se fosse possibile utilizzare lo stesso principio con un ordinamento caratterizzato da un codice non modificabile, e all'interno di questa sottoarea effettuare l'ordinamento.
Non so se son stato chiaro...cmq grazie ancora