Changing ranking position of database item with Python and AJAX

[drumdogg]

Hi Guys, i have a python application which uses the youtube import to search youtube videos and create a playlist.

When a video is added to the playlist the details are stored in a database file such as, id, title, duration and position. Position being the position it is in the playlist.

I need to add a function that moves the position of the video up or down the rank(position) it is in the playlist.

There is a Up|Down|Delete buttons on each video that when clicked on activates a AJAX function. I have the delete function working but cant get the up and down to work.

So basically what needs to happen is check the position of the video and change the position in the database either up a position or down one.

Here is the necessary code:

@route('/deleteFromPlaylist')
def deleteFromPlaylist():
	connect = sqlite3.connect('ytPlaylist_V3.db')
	cursor = connect.cursor()
	cursor.execute("DELETE FROM playlist WHERE id=?", (request.query.id,))
	connect.commit()
	cursor.close()
	connect.close()
	return fetchPlaylist()

@route('/moveUpPlaylist')
def moveUpPlaylist():
	connect = sqlite3.connect('ytPlaylist_V3.db')
	cursor = connect.cursor()
	cursor.execute("SELECT id,position FROM playlist WHERE id=?", (request.query.id,))
	connect.commit()
	cursor.execute("UPDATE playlist SET position WHERE position=?", (request.query.position,))
	connect.commit()
	cursor.close()
	connect.close()
	return fetchPlaylist()

@route('/moveDownPlaylist')
def moveDownPlaylist():
	connect = sqlite3.connect('ytPlaylist_V3.db')
	cursor = connect.cursor()
	cursor.execute("SELECT FROM playlist WHERE id=?", (request.query.id,))
	connect.commit()
	cursor.close()
	connect.close()
	return fetchPlaylist()

function moveUp(id,position) {
	xmlhttp.open("GET", "/moveUpPlaylist?id="+id,?position="+position, true);
	var nextPos == position ++;
	var currentPos = position;
	var currentPos = nextPos;
	xmlhttp.send();
	xmlhttp.onreadystatechange=showPlaylist;
}

function moveDown(id) {
	xmlhttp.open("GET", "/moveDownPlaylist?id="+id, true);
	xmlhttp.send();
	xmlhttp.onreadystatechange=showPlaylist;
}

function deleteEntry(id) {
	xmlhttp.open("GET", "/deleteFromPlaylist?id="+id, true);
	xmlhttp.send();
	xmlhttp.onreadystatechange=showPlaylist;
}

function showPlaylist() {
  if (xmlhttp.readyState==4 && xmlhttp.status==200) {
    var output = xmlhttp.responseText;
    document.getElementById("playlistPane").innerHTML = output;
  } 
  else document.getElementById("playlistPane").innerHTML="Playlist information not available";
}

In theory its easy to do but just cant my head around it!

Any help guys??

Thanks

 

[sb89]

Just had a quick look through (so I may be wrong), but it looks like you need to update two records when moving up or down. For example:

1 - Item1
2 - Item2

If Item2 is moving up the play list then the index of Item2 will need to be incremented and the index of Item1 will need to be decremented. I guess another way of looking at it is that the indexes of the two items will need to be swapped.

Your moveDownPlaylist method doesn't seem to have an UPDATE statement.

The update statement in the moveUpPlaylist method isn't updating anything. Surely it should be something like:

"UPDATE playlist SET position =<some_value_here> WHERE position=?"

Also why are you doing a "SELECT" in the moveUpPlayList and committing after it?