PHP/MySQL Swap places in database + JavaScript (jQuery)

Question

I'm currently developing a website which stores bookmarks in a MySQL database using PHP and jQuery.

The MySQL for bookmarks looks like this (CSV format):

id,userid,link_count,url,title,description,tags,shareid,fav,date
"1";"1";"0";"img/test/google.png";"Google";"Best. Search Engine. Ever.";"google, search, engine";"7nbsp";"0";"1267578934"
"2";"1";"1";"img/test/james-brooks.png";"jTutorials";"Best. jQuery Tutorials. Ever.";"jquery, jtutorials, tutorials";"8nbsp";"0";"1267578934"
"3";"1";"2";"img/test/benokshosting.png";"Benoks Hosting";"Cheap website hosting";"Benoks, Hosting, server, linux, cpanel";"9nbsp;";"0";"1267578934"
"4";"1";"3";"img/test/jbrooks.png";"James Brooks";"Personal website FTW!";"james, brooks, jbrooksuk, blog, personal, portfolio";"1nbsp";"0";"1267578934"
"6";"1";"4";"img/test/linkbase.png";"LinkBase";"Store and organise your bookmarks and access them from anywhere!";"linkbase, bookmarks, organisation";"3nbsp";"0";"1267578934"
"5";"1";"5";"img/test/jtutorials.png";"jTutorials";"jQuery tutorials, videos and examples!";"jquery, jtutorials, tutorials";"2nbsp";"0";"1267578934"

I'm using jQuery Sortable to move the bookmarks around (similar to how Google Chrome does). Here is the JavaScript code I use to format the bookmarks and post the data to the PHP page:

$(".bookmarks").sortable({scroll: false, update: function(event, ui){
        // Update bookmark position in the database when the bookmark is dropped
        var newItems = $("ul.bookmarks").sortable('toArray');
        console.log(newItems);
        var oldItems = "";
        for(var imgI=0;imgI < newItems.length;imgI++) {
            oldItems += $("ul.bookmarks li#" + imgI + " img").attr("id") + ",";
        }
        oldItems = oldItems.slice(0, oldItems.length-1);
        console.log("New position: " + newItems);
        console.log("Old position: " + oldItems);

        // Post the data 
        $.post('inc/updateBookmarks.php', 'update=true&olditems=' + oldItems + "&newitems=" + newItems, function(r) {
            console.log(r);
        });
    }
});

The PHP page then goes about splitting the posted arrays using explode, like so:

if(isset($pstUpdate)) {
    // Get the current and new positions
    $arrOldItems = $_POST['olditems'];
    $arrOldItems = explode(",", $arrOldItems);

    $arrNewItems = $_POST['newitems'];
    $arrNewItems = explode(",", $arrNewItems);

    // Get the user id
    $usrID = $U->user_field('id');

    // Update the old place to the new one
    for($anID=0;$anID<count($arrOldItems);$anID++) {
        //echo "UPDATE linkz SET link_count='" . $arrNewItems[$anID] . "' WHERE userid='" . $usrID . "' AND link_count='" . $arrOldItems[$anID] . "'\n";
        //echo "SELECT id FROM linkz WHERE link_id='".$arrOldItems[$anID]."' AND userid='".$usrID."'";

        $curLinkID = mysql_fetch_array(mysql_query("SELECT id FROM linkz WHERE link_count='".$arrOldItems[$anID]."' AND userid='".$usrID."'")) or die(mysql_error());
        echo $arrOldItems[$anID] . " => " . $arrNewItems[$anID] . " => " . $curLinkID['id'] . "\n";

        //mysql_query("UPDATE linkz SET link_count='" . $arrNewItems[$anID] . "' WHERE userid='" . $usrID . "' AND link_count='" . $curLinkID['id'] . "'") or die(mysql_error());

        // Join a string with the new positions
        $outPos .= $arrNewItems[$anID] . "|";
    }

    echo substr($outPos, 0, strlen($outPost) - 1);
}

So, each bookmark is given it's own link_count id (which starts from 0 for each user). Every time a bookmark is changed, I need the link_count to be changed as needed. If we take this array output as the starting places:

Array
(
    [0] => 0
    [1] => 1
    [2] => 2
    [3] => 3
    [4] => 4
    [5] => 5
)

Each index equalling the link_count position, the resulting update would become:

Array
(
    [0] => 1
    [1] => 0
    [2] => 3
    [3] => 4
    [4] => 5
    [5] => 2
)

I have tried many ways but none are successful.

Thanks in advance.

Answer 1

So far I've managed to find out that I was setting the img ID attribute of each li.droplet to the wrong value, they're now set the matching link_count (from the database).

I've also realized that if, for example, we have the bookmarks beginning in order: 0,1,2,3,4,5

And we drag bookmark 0 to place 1, so the new output is, 1,0,2,3,4,5

The output from the (new) PHP:

// Update the old place to the new one
for($anID=0;$anID<count($arrOldItems);$anID++) {            
    echo $arrNewItems[$anID] . " = " . $arrOldItems[$anID] . "\n";
    echo $arrOldItems[$anID] . " = " . $arrNewItems[$anID] . "\n\n";

    //mysql_query("UPDATE linkz SET link_count='" . $arrNewItems[$anID] . "' WHERE userid='" . $usrID . "' AND link_count='" . $arrOldItems[$anID] . "'") or die(mysql_error());

    //mysql_query("UPDATE linkz SET link_count='" . $arrOldItems[$anID] . "' WHERE userid='" . $usrID . "' AND link_count='" . $arrNewItems[$anID] . "'") or die(mysql_error());
}

becomes, 1 = 0, 0 = 1 0 = 1, 1 = 0

2 = 2, 2 = 2

3 = 3, 3 = 3

4 = 4, 4 = 4

5 = 5, 5 = 5 If we take a look at the bits in bold, we can see that the data is being over written again so how can I go about stopping that from happening?

Answer 2

After some long hours of Googling, searching, and testing, I've come up with something that is practical, but not 100%.

// Update the old place to the new one
for($anID=0;$anID<count($arrOldItems);$anID++) {
    if($oldCycle != $arrNewItems[$anID]) {
        if($arrOldItems[$anID] != $arrNewItems[$anID]) {
            $sQuery = "UPDATE linkz AS rule1 JOIN linkz AS rule2 ON (rule1.link_count = $arrOldItems[$anID] AND rule2.link_count = $arrNewItems[$anID]) OR ( rule1.link_count = $arrNewItems[$anID] AND rule2.link_count = $arrOldItems[$anID]) SET rule1.link_count = rule2.link_count, rule2.link_count = rule1.link_count";
            mysql_query($sQuery) or die(mysql_error());
            // Now set a temporary variable which we'll check later
            $oldCycle = $anID;
        }
    }else{
        $oldCycle = NULL;
    }
}

It kind of works, however it does not allow you to swap the same bookmarks twice. I'll figure something out.

Answer 3

Thanks for the this, it’s extremely extensive and useful!,do check out the link below

http://www.tutorials99.com where all tutorials have a Higher page rank and professional.Very helpful for beginners…