Page 1 of 1

how to implement dynamic ranking of database entries

Posted: Tue Dec 12, 2006 9:13 am
by konstandinos
hello folk

i'm using php/mysql to insert/update/delete rows (entities) to a table.

i need to be able to dynamically rank each entity, so i figured i'd add a "rank" column. nothing fancy, just an int. starting at 1 (highest) and moving down the ranks (1++) etc. so an item ranked 3 would be after an item ranked 2 etc.

now when a new item is added it gets a rank. default rank is the next available (hence highest number (ie: 4)) rank. should the user specify a rank, that already exists (ie: 2) then the current entities (ranked from 2, to 3 to 4 etc) get incremented.

thus there are never two separate entities with identical ranks.

what's a clever way to do this in php?

is it a matter of going and updating all entities where " rank > x " one by one, by incrementing their rank. and what if an entity gets deleted? then there will be gaps in the ranking etc.

is there a better way? what's the industry standard way of ranking items?

any suggestions would be appreciated. thanks

Ranks

Posted: Tue Dec 12, 2006 10:31 am
by timclaason
You could do something like this:

Code: Select all

for($rank=1; $rank <= $class->getMaxRank(); $rank++) {
   $query = "SELECT * FROM table WHERE rank=".$rank;
   $SQL = mysql_query($query, $dblink);
   if(@mysql_num_rows($SQL) > 1) {
       //This is an arbitrary way to do it, but accomplishes what you're looking to do
      $query2 = "UPDATE table SET rank=rank+1 WHERE rank=".$rank." LIMIT 1";
   }
}

Posted: Tue Dec 12, 2006 6:12 pm
by feyd
There's no need for a select or a loop. Just update with results from say implode() and using the IN() SQL construct.

Not sure I follow

Posted: Tue Dec 12, 2006 7:50 pm
by timclaason
Feyd, Not sure I follow. Could you give a code example?

Posted: Tue Dec 12, 2006 8:11 pm
by Sloth

Code: Select all

$query = "SELECT * FROM table WHERE rank =>".$rank; 
   $SQL = mysql_query($query, $dblink); 
   //something to retrieve the the ranks into an array, lets just call it array
   $imploded = implode(",", $array);
   $query2 = "UPDATE table SET rank=rank+1 WHERE rank in ($imploded);

Posted: Tue Dec 12, 2006 11:16 pm
by boo_lolly
this is completely untested. and rather noobish. but it's there.

Code: Select all

<!- addentry.php -->
<html>
<head><title>Add Entry</title></head>
<body>
<?php
	$rank = $_POST['rank'];

	/* if rank is not specified, append an entry */
	if($rank == NULL){
		$db = mysql_connect("localhost", "server", "pass");
		$sql = "SELECT rank FROM your_table ORDER BY rank DESC";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);

		$sql = "INSERT INTO your_table (rank) VALUES (". $row['rank']++ .")";
		mysql_query($sql);
	}

	/* replace specified rank, and increment all below */
	else{
		$db = mysql_connect("localhost", "server", "pass");
		$sql = "SELECT rank FROM your_table ORDER BY rank";
		$result = mysql_query($sql);

		while($row = mysql_fetch_array($result)){
			if($rank == $row['rank']){
				$bool = true;
				$sql = "INSERT INTO your_table (rank) ".
				       "VALUES (". $row['rank']++ .") ".
				       "WHERE ". $row['rank'] ." = ". $rank ."";
				mysql_query($sql);
			}
			while($bool = true;){
				$row['rank']++;
				$sql = "UPDATE your_table ".
				       "SET rank = ". $row['rank'] ." ".
				       "WHERE ". $row['rank'] ." = ". $row['rank']-- ."";
				mysql_query($sql);
			}
		}
	}
?>


<form method="post" action="addentry.php">
<b>Add Entry Number:</b><input name="rank" type="text" size="3">
</form>
</body>
</html>
or to do it backwards, you can replace the contents of your 'else' condition with this:

Code: Select all

$db = mysql_connect("localhost", "server", "pass");
	$sql = "SELECT rank FROM your_table ORDER BY rank DESC";
	$result = mysql_query($sql);

	while($row = mysql_fetch_array($result)){
		if($row['rank'] = $rank){
			$sql = "INSERT INTO your_table (rank) ".
			       "VALUES (". $row['rank']-- .") ".
			       "WHERE ". $row['rank'] ." = ". $row['rank'] ."";
			mysql_query($sql);
		}else{
			$row['rank']++;
			$sql = "UPDATE your_table ".
			       "SET rank = ". $row['rank'] ." ".
			       "WHERE ". $row['rank'] ." = ". $row['rank']-- ."";
			mysql_query($sql);
		}
	}
again, untested.

Posted: Wed Dec 13, 2006 9:17 am
by boo_lolly
feyd wrote:There's no need for a select or a loop. Just update with results from say implode() and using the IN() SQL construct.
what's the IN() sql construct, and how do you use it?

Posted: Wed Dec 13, 2006 9:26 am
by feyd
boo_lolly wrote:what's the IN() sql construct, and how do you use it?
http://dev.mysql.com/doc/refman/5.0/en/ ... #id3094194