Up/down arrows to sort database results, problem with Delete
Posted: Thu Jul 21, 2011 7:29 pm
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
(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)
database.class.php (used for my database queries, nothing special)
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();
?>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;
}
}
}
?>