Up/down arrows to sort database results, problem with Delete

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Up/down arrows to sort database results, problem with Delete

Post by Sindarin »

I have created a simple sorting system for database results. The user can click on the Up/Down links and move an entry up or down to sort it to his liking.
It seems it works fine, the entries swap their `order` along with each other. Problem is that when an entry is deleted, a `gap` is left between the rows which results in bugs like the user has to click 2 or 3 times for the sorting to work.

I am posting my code,

index.php

Code: Select all

<?php

error_reporting(E_ALL);

include('sql.manager.class.php');
$sql = new sqlManager();
$sql->connect('localhost','sort_sample','root','');

//SORTING
if (isset($_REQUEST['action'])){

	if ($_REQUEST['action'] == 'sort'){

		$id = $sql->escapeString($_REQUEST['id']);
		$order = $sql->escapeString($_REQUEST['order']);
	
		if ($_REQUEST['dir']=='up')
		{
			$sql->query("UPDATE `items` SET `order` = '$order' WHERE `order` < '$order' AND `order` = '$order'-1 LIMIT 1"); //other row+1
			$sql->query("UPDATE `items` SET `order` = '$order'-1 WHERE `id` = '$id' AND `order` = '$order' LIMIT 1"); //this row-1
		}


		if ($_REQUEST['dir']=='down')
		{
			
			$sql->query("UPDATE `items` SET `order` = '$order' WHERE `order` > '$order' AND `order` = '$order'+1 LIMIT 1"); //other row-1
			$sql->query("UPDATE `items` SET `order` = '$order'+1 WHERE `id` = '$id' AND `order` = '$order' LIMIT 1"); //this row+1
		}

	}
	
	if ($_REQUEST['action'] == 'delete'){
		$id = $sql->escapeString($_REQUEST['id']);
		
		$sql->query("DELETE FROM `items` WHERE `id`=$id LIMIT 1");
		//nice but `order` database row has gaps...
	}

}
//endof SORTING

$sqlResult1 = $sql->query("SELECT * FROM `items` ORDER BY `order` ASC");
$numOfRows = $sql->numRows($sqlResult1);

while ($row = $sql->fetchArray($sqlResult1))
{
	echo $row['title'].' | ';
	if ($row['order']!=1)
	{echo '<a href="?action=sort&id='.$row['id'].'&dir=up&order='.$row['order'].'">Up</a> - ';}
	else{echo 'Up - ';}
	
	if ($row['order'] != $numOfRows)
	{echo '<a href="?action=sort&id='.$row['id'].'&dir=down&order='.$row['order'].'">Down</a> | ';}
	else{echo 'Down | ';}
	
	echo '<a href="?action=delete&id='.$row['id'].'">Delete</a>
	<br />';
	
	
}

 $sql->disconnect();

?>
(below is a couple files you will need if you wish to test the code in your environment, you will need to import the sort_sample.sql in your databases)

sort_sample.sql (the mySQL database file)

Code: Select all

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 22, 2011 at 03:16 
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sort_sample`
--

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `order` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `title`, `order`) VALUES
(1, 'item1', 1),
(2, 'item2', 2),
(3, 'item3', 3),
(4, 'item4', 4),
(5, 'item5', 5),
(6, 'item6', 6),
(7, 'item7', 7),
(8, 'item8', 8 );

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


database.class.php
(used for my database queries, nothing special)

Code: Select all

<?php

/* 

	SQL Manager Class

	description	 : connects to a database and executes SQL queries
	version		 : 1.1
	last update	 : 05-Feb-2011
	author		 : Alexander Vourtsis
	
*/

class sqlManager
{

	public function __construct( $db_engine = 'mysql', $db_charset = 'utf8' )
	{
		$this->db_engine = $db_engine;
		$this->db_charset = $db_charset;
	}

	public function connect( $db_host = 'localhost', $db_database = 'test', $db_username = 'root', $db_password = '' )
	{
		$this->db_host = $db_host;
		$this->db_database = $db_database;
		$this->db_username = $db_username;
		$this->db_password = $db_password;

		switch($this->db_engine)
		{
			case 'mysql':
				//start database connection for mysql
				$this->db_connection = @mysql_connect($this->db_host, $this->db_username, $this->db_password);
				if (!$this->db_connection){return false;}
				//set the connection charset for mysql
				if (!@mysql_query("SET NAMES '".$this->db_charset."'")){return false;}
				//connect to the database for mysql
				if (!@mysql_select_db($this->db_database, $this->db_connection)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//start database connection for mysqli
				$this->db_connection = @mysqli_connect($this->db_host, $this->db_username, $this->db_password);
				if (!$this->db_connection){return false;}
				//set the connection charset for mysqli
				if(!@mysqli_query($this->db_connection, "SET NAMES '".$this->db_charset."'")){return false;}
				//connect to the database for mysqli
				if(!@mysqli_select_db($this->db_connection, $this->db_database)){return false;} 
				else{return true;}
			break;
			default:
				return false;
		}
	}
	
	public function escapeString($string)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//escape string for query
				
				if (ini_get('magic_quotes_gpc')) {
					$string = stripslashes($string);
				}
				
				if (!is_numeric($string)) {
					$string = @mysql_real_escape_string($string, $this->db_connection);
				}
				
				return $string;
			break;
			case 'mysqli':
				//escape string for query
				
				if (ini_get('magic_quotes_gpc')) {
					$string = stripslashes($string);
				}
				
				if (!is_numeric($string)) {
					$string = @mysqli_real_escape_string($this->db_connection, $string);
				}
				
				return $string;
			break;
			default:
				return false;
		}
	}
	
	public function query($db_query)
	{
		$this->db_query = $db_query;
		
		switch($this->db_engine)
		{
			case 'mysql':
				//execute query in mysql
				return @mysql_query($this->db_query, $this->db_connection);
			break;
			case 'mysqli':
				//execute query in mysqli
				return @mysqli_query($this->db_connection, $this->db_query);
			break;
			default:
				return false;
		}
	}
	
	
	public function fetchArray($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//fetch array in mysql
				return @mysql_fetch_array($db_result);
			break;
			case 'mysqli':
				//fetch array in mysqli
				return @mysqli_fetch_array($db_result);
			break;
			default:
				return false;
		}
	}

	public function fetchRow($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//fetch row in mysql
				return @mysql_fetch_row($db_result);
			break;
			case 'mysqli':
				//fetch row in mysqli
				return @mysqli_fetch_row($db_result);
			break;
			default:
				return false;
		}
	}
	
	public function numRows($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//num rows in mysql
				return @mysql_num_rows($db_result);
			break;
			case 'mysqli':
				//num rows in mysqli
				return @mysqli_num_rows($db_result);
			break;
			default:
				return false;
		}
	}
	
	public function affectedRows($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//affected rows in mysql
				return @mysql_affected_rows($db_result);
			break;
			case 'mysqli':
				//affected rows in mysqli
				return @mysqli_affected_rows($db_result);
			break;
			default:
				return false;
		}
	}
	
	public function numFields($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//num fields in mysql
				return @mysql_num_fields($db_result);
			break;
			case 'mysqli':
				//num fields in mysqli
				return @mysqli_field_count($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function getFieldName($db_result, $field_number)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//field name in mysql
				return @mysql_field_name($db_result, $field_number);
			break;
			case 'mysqli':
				//field name in mysqli
				@mysqli_field_seek($db_result, $field_number);
				$finfo = @mysqli_fetch_field($db_result);
				return $finfo->name;
			break;
			default:
				return false;
		}
	}
	
	public function getFieldType($db_result, $field_number)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//field type in mysql
				return @mysql_field_type($db_result, $field_number);
			break;
			case 'mysqli':
				//field type in mysqli
				@mysqli_field_seek($db_result, $field_number);
				$finfo = @mysqli_fetch_field($db_result);
				return $finfo->type;
			break;
			default:
				return false;
		}
	}
	
	public function getFieldTable($db_result, $field_number)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//field table in mysql
				return @mysql_field_table($db_result, $field_number);
			break;
			case 'mysqli':
				//field table in mysqli
				@mysqli_field_seek($db_result, $field_number);
				$finfo = @mysqli_fetch_field($db_result);
				return $finfo->table;
			break;
			default:
				return false;
		}
	}
	
	public function getInsertID()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//insert ID in mysql
				return @mysql_insert_id();
			break;
			case 'mysqli':
				//insert ID in mysqli
				return @mysqli_insert_id();
			break;
			default:
				return false;
		}
	}
	
	public function getTableName()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//table name in mysql
				return @mysql_tablename();
			break;
			case 'mysqli':
				//table name in mysqli
				return @mysql_tablename();
			break;
			default:
				return false;
		}
	}
	
	public function getEncoding()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//get encoding in mysql
				return @mysql_client_encoding($this->db_connection);
			break;
			case 'mysqli':
				//get encoding in mysqli
				return @mysqli_client_encoding($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function setEncoding($encoding)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//set encoding in mysql
				if(!@mysql_set_charset($encoding, $this->db_connection)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//set encoding in mysqli
				if(!@mysqli_set_charset($this->db_connection, $encoding)){return false;}
				else{return true;}
			break;
			default:
				return false;
		}
	}
	
	public function freeResult($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//free result in mysql
				if(!@mysql_free_result($db_result)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//free result in mysqli
				if(!@mysqli_free_result($db_result)){return false;}
				else{return true;}
			break;
			default:
				return false;
		}
	}
	
	public function getVersion()
	{
		switch($this->db_engine)
		{
			case 'mysql':
			//get mysql version number
				return @mysql_get_server_info();
			break;
			case 'mysqli':
			//get mysqli version number
				return @mysqli_get_server_info($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function ping()
	{
	 	switch($this->db_engine)
		{
			case 'mysql':
			//ping in mysql
				return @mysql_ping($this->db_connection);
			break;
			case 'mysqli':
			//ping in mysqli
				return @mysqli_ping($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function getUptime()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//uptime in mysql
				$stats = explode('  ', @mysql_stat($this->db_connection));
				$stats[0] = ltrim($stats[0],'Uptime: ');
				return $stats[0];
			break;
			case 'mysqli':
			//uptime in mysqli
				$stats = explode('  ', @mysqli_stat($this->db_connection));
				$stats[0] = ltrim($stats[0],'Uptime: ');
				return $stats[0];
			break;
			default:
				return false;
		}
	}
	
	public function getThreads()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//threads in mysql
				$stats = explode('  ', @mysql_stat($this->db_connection));
				$stats[1] = ltrim($stats[1],'Threads: ');
				return $stats[1];
			break;
			case 'mysqli':
			//threads in mysqli
				$stats = explode('  ', @mysqli_stat($this->db_connection));
				$stats[1] = ltrim($stats[1],'Threads: ');
				return $stats[1];
			break;
			default:
				return false;
		}
	}
	
	public function getQuestions()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//questions in mysql
				$stats = explode('  ', @mysql_stat($this->db_connection));
				$stats[2] = ltrim($stats[2],'Questions: ');
				return $stats[2];
			break;
			case 'mysqli':
			//questions in mysqli
				$stats = explode('  ', @mysqli_stat($this->db_connection));
				$stats[2] = ltrim($stats[2],'Questions: ');
				return $stats[2];
			break;
			default:
				return false;
		}
	}
	
	public function getSlowQueries()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//slow queries in mysql
				$stats = explode('  ', @mysql_stat($this->db_connection));
				$stats[3] = ltrim($stats[3],'Slow queries: ');
				return $stats[3];
			break;
			case 'mysqli':
			//slow queries in mysqli
				$stats = explode('  ', @mysqli_stat($this->db_connection));
				$stats[3] = ltrim($stats[3],'Slow queries: ');
				return $stats[3];
			break;
			default:
				return false;
		}
	}
	
	public function getOpenTables()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//get open tables in mysql
				$stats = explode('  ', @mysql_stat($this->db_connection));
				$stats[6] = ltrim($stats[6],'Open tables: ');
				return $stats[6];
			break;
			case 'mysqli':
			//get open tables in mysqli
				$stats = explode('  ', @mysqli_stat($this->db_connection));
				$stats[6] = ltrim($stats[6],'Open tables: ');
				return $stats[6];
			break;
			default:
				return false;
		}
	}
	
	public function getQueriesPerSecond()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//queries per second in mysql
				$stats = explode('  ', @mysql_stat($this->db_connection));
				$stats[7] = ltrim($stats[7],'Queries per second avg: ');
				return $stats[7];
			break;
			case 'mysqli':
			//queries per second in mysqli
				$stats = explode('  ', @mysqli_stat($this->db_connection));
				$stats[7] = ltrim($stats[7],'Queries per second avg: ');
				return $stats[7];
			break;
			default:
				return false;
		}
	}
	
	public function getError()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//display error in mysql
				return mysql_error($this->db_connection);
			break;
			case 'mysqli':
				//display error in mysqli
				return mysqli_error($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function getErrorCode()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//display error in mysql
				return mysql_errno($this->db_connection);
			break;
			case 'mysqli':
				//display error in mysqli
				return mysqli_errno($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function disconnect()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//close the connection for mysql
				if (!@mysql_close($this->db_connection)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//close the connection for mysqli
				if(!@mysqli_close($this->db_connection)){return false;}
				else{return true;}
			break;
			default:
				return false;
		}
	}
}
?>
Last edited by Sindarin on Sun Jul 24, 2011 8:59 pm, edited 1 time in total.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Up/down arrows to sort database results, problem with De

Post by McInfo »

Thank you for providing your schema, sample data, and all the code! :D

Before you delete a row, you need to run an UPDATE query to decrement the order of all the rows that have an order greater than the row you will delete. It may involve a separate SELECT query to get the order of the row to be deleted.

Consider switching to the InnoDB storage engine and using transactions.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Up/down arrows to sort database results, problem with De

Post by Sindarin »

Thanks a lot, I was able to do this with just an UPDATE query,

so, my final index.php is:

Code: Select all

<?php

error_reporting(E_ALL);

include('sql.manager.class.php');
$sql = new sqlManager();
$sql->connect('localhost','sort_sample','root','');

//SORTING
if (isset($_REQUEST['action'])){

	if ($_REQUEST['action'] == 'sort'){

		$id = $sql->escapeString($_REQUEST['id']);
		$order = $sql->escapeString($_REQUEST['order']);
	
		if ($_REQUEST['dir']=='up')
		{
			$sql->query("UPDATE `items` SET `order` = '$order' WHERE `order` < '$order' AND `order` = '$order'-1 LIMIT 1"); //other row+1
			$sql->query("UPDATE `items` SET `order` = '$order'-1 WHERE `id` = '$id' AND `order` = '$order' LIMIT 1"); //this row-1
		}


		if ($_REQUEST['dir']=='down')
		{
			
			$sql->query("UPDATE `items` SET `order` = '$order' WHERE `order` > '$order' AND `order` = '$order'+1 LIMIT 1"); //other row-1
			$sql->query("UPDATE `items` SET `order` = '$order'+1 WHERE `id` = '$id' AND `order` = '$order' LIMIT 1"); //this row+1
		}

	}
	
	if ($_REQUEST['action'] == 'delete'){
		$id = $sql->escapeString($_REQUEST['id']);
		$order = $sql->escapeString($_REQUEST['order']);
		
		$sql->query("UPDATE `items` SET `order` = `order`-1 WHERE `order` > '$order'"); //all rows larger than the one we want to delete-1
		$sql->query("DELETE FROM `items` WHERE `id`='$id' LIMIT 1"); //delete current entry
	}

}
//endof SORTING

$sqlResult1 = $sql->query("SELECT * FROM `items` ORDER BY `order` ASC");
$numOfRows = $sql->numRows($sqlResult1);

while ($row = $sql->fetchArray($sqlResult1))
{
	echo $row['title'].' | ';
	if ($row['order']!=1)
	{echo '<a href="?action=sort&id='.$row['id'].'&dir=up&order='.$row['order'].'">Up</a> - ';}
	else{echo 'Up - ';}
	
	if ($row['order'] != $numOfRows)
	{echo '<a href="?action=sort&id='.$row['id'].'&dir=down&order='.$row['order'].'">Down</a> | ';}
	else{echo 'Down | ';}
	
	echo '<a href="?action=delete&order='.$row['order'].'&id='.$row['id'].'">Delete</a>
	<br />';
	
	
}

?>
Post Reply