PDA

View Full Version : Sortable list with MySql Query update on drop


iBrightDev
November 27th, 2007, 19:18
you will need to get a couple things for this to work properly. thanks goes to Joe for helping me get this working 100%.

download all the js files here.
http://www.mct-hosting.com/downloads/js_Sortable_List_Files.zip

in the example below, we will use the sortable list to sort email addresses.

make a file, for argument sake, call it index.php


<?
require('db.php');
$demo = new SortableExample();
$list = $demo->getList();
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Sortables Ajax Example</title>
<link rel="stylesheet" type="text/css" href="style.css">
<script src="js/prototype.js"></script>
<script src="js/scriptaculous.js"></script>
<script>
Event.observe(window,'load',init,false);
function init() {
Sortable.create('listContainer',{tag:'div',onUpdate:updateLi st});
}
function updateList(container)
{
var url = 'ajax.php';
var params = Sortable.serialize(container.id);
var ajax = new Ajax.Request(url,{
method: 'post',
parameters: params,
onLoading: function(){$('workingMsg').show()},
onLoaded: function(){$('workingMsg').hide()}
});
}
function handleResponse(req) {
// this function will fire after the ajax request is complete...but we have nothing to do here
}
</script>
</head>
<body>

<div id="listContainer">
<?
$break = "\r\n";
foreach($list as $item) {
if ($item['display'] <= 7) {
echo '<div id="item_' . $item['id'] . '">' . $item['email'] . '</div>' . $break;
} else {
echo '<div id="item_' . $item['id'] . '">' . $item['email'] . '</div>' . $break;
}
}
?>
</div>
<div id="workingMsg" style="display:none;">Updating database...</div>

</body>
</html>


now make a second file, and call it db.php. Make sure to configure everything properly


<?

class SortableExample {

var $conn;
var $user = 'DB USER NAME GOES HERE';
var $pass = 'DB PASSWORD GOES HERE';
var $dbname = 'DB NAME GOES HERE';
var $host = 'DB HOST HERE'; //ususally localhost

function SortableExample() {
$this->conn = mysql_connect($this->host, $this->user, $this->pass);
mysql_select_db($this->dbname,$this->conn);
}

function getList() {
$sql = "SELECT * FROM TABLE_NAME_HERE ORDER BY display, id";
$recordSet = mysql_query($sql,$this->conn);
$results = array();
while($row = mysql_fetch_assoc($recordSet)) {
$results[] = $row;

}

return $results;
}

function updateList($orderArray) {
$id = 1;
foreach($orderArray as $catid)
{

$catid = (int) $catid;
$sql = "UPDATE `TABLE_NAME_HERE` SET display={$id} WHERE id={$catid}";
$recordSet = mysql_query($sql,$this->conn) OR DIE(mysql_error());
$id++;

}
}
}

?>


make another file, and call it ajax.php


<?
session_start();
require('db.php');
$demo = new SortableExample();
$demo->updateList($_POST['listContainer']);
?>


last, but not least, make a css file called style.css


body {
font-family: Arial, Verdana, sans-serif;
font-size: 12px;
background: #FFFFFF;
}

div#listContainer {
width: 400px;
border: 2px solid #336699;
background: #EFF7FF;
}

div#listContainer div {
border: 1px solid #336699;
margin: 5px;
padding: 3px 5px;
background: #DFEFFF;
font-weight: bold;
cursor: move;
}

.menuItem {
border: 1px solid #1a8918;
margin: 5px;
padding: 3px 5px;
background: #cefbcd;
font-weight: bold;
cursor: move;
}


after you have all four files made, make sure you have downloaded the js files that i provided a link to at the top of this posting, and place them in a file named js. this should automatically happen if you unzip them properly, but, if you dont, make sure you fix whatever error you made.

next, you will need a mysql file to use. so, if you have mysql knowledge, continue, otherwise, you may not want to go on with this. so, create a database, db user/password, and then configure the below sql code for your settings, and upload it through phpmyadmin



SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `DB_NAME_HERE`
--

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

--
-- Table structure for table `TABLE_NAME_HERE`
--

CREATE TABLE `TABLE_NAME_HERE` (
`id` int(25) NOT NULL auto_increment,
`display` int(255) NOT NULL default '0',
`email` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `TABLE_NAME_HERE`
--

INSERT INTO `TABLE_NAME_HERE` VALUES (1, 6, 'fakeEmail_1@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (2, 13, 'fakeEmail_2@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (3, 14, 'fakeEmail_3@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (4, 4, 'fakeEmail_4@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (5, 2, 'fakeEmail_5@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (6, 9, 'fakeEmail_6@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (7, 1, 'fakeEmail_7@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (8, 7, 'fakeEmail_8@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (9, 12, 'fakeEmail_9@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (10, 8, 'fakeEmail_10@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (11, 5, 'fakeEmail_11@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (12, 10, 'fakeEmail_12@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (13, 3, 'fakeEmail_13@fakeEmail.com');
INSERT INTO `TABLE_NAME_HERE` VALUES (14, 11, 'fakeEmail_14@fakeEmail.com');


finally, upload all files to your server, and you will have a sortable list that will automatically update any time you drag and drop.
BananaMaster
November 28th, 2007, 12:48
Hmm.... I uploaded the script and that to my localhost but all it does is show the entries in the database and if you go over one it changes cursor but what else is it meant to do?



EDIT: Never mind I didn't put the javascript files inside a folder called js. Also it would be great to have a function to delete them
iBrightDev
November 28th, 2007, 13:33
there is a way to delete them if you add in that option to the script, but, i dont delete them from where i sort them, so, not in this script. :D
AMC
November 28th, 2007, 13:53
Having seen this in action, i gotta hand it to you justin, thats a pretty cool little script you've got there ;) nice!
iBrightDev
November 28th, 2007, 14:03
Thanks Hugo. It was something i found when searching scriptaculous stuff for sortable lists, and then i found one that was supposed to work with writing a query to the database, but, it wouldnt work properly, so, KrakJoe fixed it to function properly after I had made every adjustment i could think of. i was missing one stupid little thing in the end, but, thanks to Joe, it works great. and now i have it so that when you submit a new menu item to the database, it will count the rows, and make the display id from the whatever number it finds +1, then it will write the id and the display_id so that it will show up in the right order in the sortable list. :D
Darknight
November 28th, 2007, 14:17
Sorry I may just be over tired, But Im having trouble understanding what this really does.. Could somoene please explain it in n00b :P thanks
It looks like you put alot of work in to it.. SO I guess it does something good, Just trying to work out if its the kinda thing id be able to make use of or not
BananaMaster
November 28th, 2007, 15:29
yeah I know you can but I know nothing about javascript so I can't add it so you can like drag it into a bin or something and it gets deleted.
iBrightDev
November 28th, 2007, 15:56
can see a sample here...
http://www.lissadoll.com/list2

i am currently using it on my CMS to sort my pages. i can sort my menu with this script. then i have another script to make new pages and delete pages, and iff i add a page and want to put it on the menu, and take something else off, i just drag and drop. :D
Darknight
November 29th, 2007, 10:53
Oh I see, That could be very usefull
thanks!
iBrightDev
November 29th, 2007, 12:49
no problem bro. it is very useful in my cms. :D