Drag the list item to a new relative position and save it to the database.
I am trying to create a function that will allow me to display a set of images (already saved in a specific order in the database) and allow the user to drag each one to a new order in relation to the others, with instant (ajax?) Saving in the background whenever the image falls to a new position.
My idea is to set up a database table like this:
tablename: picturetable
and sample values
[pictureset], [picture_order]
"Set1", "Pic1A.jpg | Pic1B.jpg | Pic1C.jpg"
"Set2", "Pic2C.jpg | Pic2A.jpg | Pic3B.jpg"
... etc.
This way, if I call one record using php, I can:
$oldorder=explode("|", $row[pic_order]);
into an array that I can use to display (foreach ($ oldorder), echo the dragged div in some div container of some sort) to show the photos in their current order. Every time the image falls to a new position, I could:
$neworder=implode ("|", [picture names in divs according to their new positions])
and in the background (ajax?) there is an entry in the database:
UPDATE picturetable SET picture_order=$neworder WHERE pictureset="Set2"
I found several scripts that helped me create drag-and-drop images, and even one that supposedly does the ajax rescue ... but I can't seem to get it to work outside of the drag part (the ajax save thing doesn't really seem to happen or if it does , the images do not fall into the new order.
Below is model I,
http://www.gregphoto.net/sortable/ (last screen at the bottom of the page)
http://www.gregphoto.net/index.php/2007/01/16/scriptaculous-sortables-with-ajax-callback/ (detailed code ... but not exactly the same as dragging the image above)
but I'm wondering if anyone can help me break the javascript (or comment it out) into the strictest code so I can figure out what should happen.
It seems to me that I am very close to the possibility of this, but Javascript is confusing me: is there anything at all that actually happens in the script, graphically reflected in what I see on the page (i.e. echo the variables or arrays that change, or $ sql statements that happen in the background?
I hope this is not too fussy a question.
source to share
Ok, I made some significant changes to the script, I found: http://www.webresourcesdepot.com/dynamic-dragn-drop-with-jquery-and-php
and came up with the following (two files are needed, the main .php file and the updateDB.php file.
Notice the structure of the database table / content that I described in the original question: I have one row of record for each SET of images, the primary key is the id for the set of images, and the list of image names in the desired order is placed in one text box, with each image name is delimited by a pipe (|) character.
The same model could be modified quite easily to handle other things like quotes, paragraphs, links, whatever.
Here is the first model of the file:
<?php
$conn=mysqli_connect("localhost", "username", "password", "database_name") or die ("Could not connect:" . mysqli_error($conn));
$_POST[setID]="Set1"; //sample value
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>jQuery Dynamic Drag'n Drop</title>
<script type="text/javascript" src="../jquery/jquery-1.9.1.min.js"></script>
<script type="text/javascript" src="../jquery/jquery-ui-1.10.2.custom.min.js"></script>
<style>
ul {
margin: 0;
}
#contentLeft {
float: left;
width: auto;
height: auto;
border: black solid 1px;
}
#contentLeft li {/* I want the pictures to look like floated tiles rather than a vertical top-bottom list. The jquery code seems to require that the items be presented as li rather than just images*/
white-space: nowrap;
float: left;
list-style: none;
margin: 0 0 4px 0;
padding: 10px;
background-color:#00CCCC;
border: #CCCCCC solid 1px;
color:#fff;
}
#contentRight {/* just a green box over on the right that shows what happened in the background after an item is moved */
float: right;
width: 260px;
padding:10px;
background-color:#336600;
color:#FFFFFF;
}
</style>
<script type="text/javascript">
$(document).ready(function(){
$(function() {
$("#contentLeft ul").sortable({ opacity: 0.6, cursor: 'move', update: function() {
var order = $(this).sortable("serialize") + '&action=updateRecordsListings&setID=<?php echo $setID;?>';
$.post("updateDB.php", order, function(theResponse){
$("#contentRight").html(theResponse);
});
}
});
});
});
</script>
</head>
<body>
<div id="contentLeft">
<ul>
<?php //go get a set of pictures from the database
$query = "SELECT * FROM picset_table where setID={$_POST[setID]}";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_array($result, MYSQL_ASSOC))
{$currentOrder=explode("|",$row[pics_ordered]);}
foreach($currentOrder as $pic) {//cycle through picture names and present each in a li (floated)
//$picfix is needed here because MY naming convention is "setid_n.jpg", and javascript seems to break all stings on the "_" character, which messes this up
//so,after the data passes into the updateDB.php process, the "~" gets re-replaced with the "_" to construct the SQL Update string.
$picfix=str_replace("_","~",$pic); //you may not need this if you don't have underscores in your picture names.
echo "<li id=\"recordsArray_$picfix\"><img src=\"photos/$pic\" height=\"100px\" /></li>";
}
?>
</ul>
</div>
<div id="contentRight">
<p>Array will be displayed in this box when any pictures are moved.</p>
<p> </p>
</div>
</body>
</html>
and here is the updateDB.php file
<?php
$conn=mysqli_connect("localhost", "username", "password", "database_name") or die ("Could not connect:" . mysqli_error($conn));
$action = mysqli_real_escape_string($conn, $_POST['action']);
$updateRecordsArray = $_POST['recordsArray'];
if ($action == "updateRecordsListings") {
$neworder=implode("|", $updateRecordsArray);
$picUnfix=str_replace("~","_",$neworder); // puts the underscore back where it belongs
$query = "UPDATE picset_table SET pics_ordered='".$picUnfix."'
WHERE setID=$setID";
mysqli_query($conn, $query);
//echo "<b>$query</b><br />";
echo '<pre>';
print_r($updateRecordsArray); //thisappears in the green box
echo '</pre>';
}
?>
Perhaps it will be helpful for someone else.
source to share