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