PHP/MySQL Swap places in database + JavaScript (jQuery)
- by James Brooks
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.
