Help with a 'position' field

[rondog]

Hi I have built an app in flash that is talking to PHP. It is just a textual list of items and the user has the ability to move items up or down.

 

Every time they move them up or down, I send an array to PHP of the data with their new position number.

 

The only solution I have found is to select all their previous list data, remove it and then re-enter it. The auto_increment number starts getting high quickly and I think removing the previous entries and adding new ones will make the table slow..just in testing, I got the auto id to about 500 and it already showed me a warning in phpMyAdmin to optimize the table. I haven't got it to pop up again and maybe just a fluke, but I think their might be a better way to do this.

 

Any ideas?

 

Here is my function to update the playlist:

function updatePlaylist($playlistID,$data)
{
	$query = mysql_query("SELECT * FROM playlistData WHERE playlist_id = '$playlistID'");

	$num = mysql_num_rows($query);

	if ($num > 0)
	{

		$query = mysql_query("DELETE FROM playlistData WHERE playlist_id = '$playlistID'");

	}

	for ($i = 0; $i < count($data); $i++)
	{

		if ($data[$i]["type"] == "Video")
		{

			$query = mysql_query("INSERT INTO playlistData (playlist_id,type,title,tcIn,tcOut,position) VALUES ('$playlistID','".$data[$i]["type"]."','".$data[$i]["title"]."','".$data[$i]["tcIn"]."','".$data[$i]["tcOut"]."','".$data[$i]["position"]."')");

		}
		else
		{

			$query = mysql_query("INSERT INTO playlistData (playlist_id,type,title,position) VALUES ('$playlistID','".$data[$i]["type"]."','".$data[$i]["title"]."','".$data[$i]["position"]."')");

		}

	}	

}	

[rondog]

What if I ran "OPTIMIZE TABLE playlistData" after the delete query?