Drag and drop with php/Mysql Working example

Drag and drop with php/Mysql Working example

Hi everyone!

I have managed, with some help, to get a working example of a drag and drop with multiple columns that stores everything with php/mysql.

However, since I've made sort of a ugly solution, when I drag a lot of items in a number of different directions and hit refresh not every items has been updated. 

My solution was to make a loop, which probably is where the problem lies. It has to loop through everything so many times that it takes a lot of time, and everything isn't sent before I hit refresh.

Is there a cleaner way to do it? A more efficient way perhaps, so that everything doesn't have get looped over and over and over again?



Index.php

<? // DATABASE CONNECTION

$dbhost = "xxx";
$dbuser = "xxx";
$dbpass = "xxx";
$dbname = "xxx";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname); ?>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.1/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.min.js"></script>


<? // START OF LOOP

$t = '0';
$i = '1';
while($t <= '4') { // JUST FOR EXAMPLE, THE REAL CONDITION WILL COME FROM E.G HOM MANY CATAGORYS THERE'S IN THE DATABASE

$query = "SELECT * FROM images WHERE ranking_column = '$t'";
$result = mysql_query($query) or die('Error, query failed'); ?>


<style type="text/css">

#sortable<? echo $i; ?> {
width:250px; 
min-height:400px; 
max-height:400px; 
overflow-y:auto; 
border:1px solid #ccc; 
background:#f3f3f3;
list-style-type: none; 
margin: 10px; 
padding: 0; 
float: left; 
}
#sortable<? echo $i; ?> img { 
width:80px; 
margin: 10px; 
float:left; 
}
#sortable<? echo $i; ?> li { 
display:block; 
float:left; 
background:#e3e3e3; 
text-align:center; 
cursor:move;
margin: 5px 5px 5px 5px; 
padding: 5px; 
font-size: 1.2em;
}

</style>


<ul id="sortable<? echo $i;?>" class="connectedSortable">
<? while ($row = mysql_fetch_array($result)) {
echo '<li id="entry_' . $row['entry_id'] . '" class="ui-state-default">
<img src="bilder/'.$row['image_name'].'" />
 </li>';
} ?>
</ul> 


<script type="text/javascript">
$(function() 
{
    $("#sortable<? echo $i; ?>").sortable(
    {
        connectWith: '.connectedSortable',
        update : function () 
        { 
            $.ajax(
            {
                type: "POST",
                url: "draggable.php",
                data: 
                {
                    sort<? echo $i;?>:$("#sortable<? echo $i; ?>").sortable('serialize')
                },
                success: function(html)
                {
                    $('.success').fadeIn(500);
                    $('.success').fadeOut(500);
                }
            });
        } 
    }).disableSelection();
});
</script>


<? $t = $t + 1;
$i = $i + 1;

} // WHILE-LOOP ENDS ?>


<p class="success" style="display:none;">Success</p>

</body>
</html>


draggable.php

<?
if (!($_POST)) { echo 'This has to be a post jackass!'; }
else {
$dbhost = "xxx";
$dbuser = "xxx";
$dbpass = "xxx";
$dbname = "xxx";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
$t = '0';
$i = '1';
while($t >= '0') {
// PARSES THE POSTS FROM EACH COLUMN INTO AN ARRAY
parse_str($_POST['sort'.$i], $sort);
foreach($sort['entry'] as $key=>$value) {
$updatequery = "UPDATE `images` SET ranking_column = '$t' WHERE entry_id = '$value'";
mysql_query($updatequery) or die('Error, UPDATE failed!');
} 
$i = $i + 1;
$t = $t + 1;
} // WHILE-LOOP ENDS
    }
    
?>