I am trying to build a function that will allow me to display a set of pictures (already saved in a certain order in a database), and allow the user to drag each one into a new order, relative to the others, with instant (ajax?) save in the background whenever a picture is dropped into a new position.
My idea is to set up my database table like so:
tablename: picturetable
fields and sample values
[pictureset],[picture_order]
"Set1","Pic1A.jpg|Pic1B.jpg|Pic1C.jpg"
"Set2","Pic2C.jpg|Pic2A.jpg|Pic3B.jpg"
...and so on.
This way, if I call one record, using php I can:
$oldorder=explode("|", $row[pic_order]);
into an array I can use to display (foreach($oldorder), echo a draggable div within some kind of container div) to show the pictures in their current order. Every time a picture is dropped into a new position, I could then:
$neworder=implode ("|", [picture names in divs according to their new positions])
and in the background (ajax?) have the database record do:
UPDATE picturetable SET picture_order=$neworder WHERE pictureset="Set2"
I have found some scripts that have helped me to create the draggable pictures, and even one that supposedly does the ajax save...but I can't seem to get it to work beyond the dragging part (the ajax save thing does not actually seem to take place or if it does, the pictures don't get imploded into the new order.
The model I was following is located here,
http://www.gregphoto.net/sortable/ (last display on 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 the picture-drag above)
but I am wondering if anyone can help me strip the javascript (or comment on it) to the barest code so that I can be clear about what is supposed to be going on.
I feel like I am getting very close to being able to do this but the Javascript is confusing me: is there anyway to have what is actually happening in the script reflected verbosely in what I see on the page (ie. echo the variables or arrays that are changing or the $sql statements that are taking place in the background?
I hope this is not too fussy a question.
Okay, I made some significant modifications to the script I found at: http://www.webresourcesdepot.com/dynamic-dragn-drop-with-jquery-and-php
and came up with the following (two files are required, the main .php file and an updateDB.php file.
Note the structure of the database table/contents I described in the original question: I have a single record row for each SET of pictures, the primary key is an ID for the picture set, and the list of picture names, in the desired order, is placed in a single text field, with each picture name separated with the "pipe" ("|") character.
This same model could probable be modified pretty easily to handle other things like quotations, paragraphs, links, whatever.
Here is the first file model:
<?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 this will be of use to someone else.