how to implement dynamic ranking of database entries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
konstandinos
Forum Commoner
Posts: 68
Joined: Wed Oct 04, 2006 4:20 am

how to implement dynamic ranking of database entries

Post 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
timclaason
Forum Commoner
Posts: 77
Joined: Tue Dec 16, 2003 9:06 am
Location: WI

Ranks

Post 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";
   }
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timclaason
Forum Commoner
Posts: 77
Joined: Tue Dec 16, 2003 9:06 am
Location: WI

Not sure I follow

Post by timclaason »

Feyd, Not sure I follow. Could you give a code example?
Sloth
Forum Newbie
Posts: 18
Joined: Thu Dec 07, 2006 7:29 pm

Post 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);
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post 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.
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Post Reply