so making highscore system, unity game, using mysql, php.
for highscore info stored in db, wrote like, (db has 'score', 'name' column, not 'rank' column name)
$sql = "select id, score, name, case\n" . " when @prev_value = score @rank_count\n" . " when @prev_value := score @rank_count := @rank_count + 1\n" . "end rank\n" . "from bbr\n" . "order score desc limit $min, $max"; $result = mysql_query($sql) or die('query failed: ' . mysql_error()); $info = ""; while($found = mysql_fetch_array($result)){ $info = $info .'@'. $found['name'] .':'. $found['score'] .':'. $found['rank']; } echo $info; and in unity, receive info , split each string.
string[] score = serverhighscores[x].split(':');
but work @ score[0] (name), score[1] (score) well, not work @ score[2] (rank).
why? how should fix? thanks.
this query is
set @prev_value = null; set @rank_count = 0; select id, score, name, case when @prev_value = score @rank_count when @prev_value := score @rank_count := @rank_count + 1 end rank bbr order score desc and works in phpmyadmin sql.
i'd write query this:
select b.id , b.name , @rank_cnt := if(@prev_score = b.score,@rank_cnt,@rank_cnt+1) rank , @prev_score := b.score score bbr b cross join ( select @rank_cnt := 0, @prev_score := null) order b.score desc, b.id desc note: behavior observe query not guaranteed. mysql reference manual warns against using user defined variables in manner. behavior observe (at least mysql 5.1 , 5.5) consistent.
it's important assignment of @prev_score done after comparison, that's why columns ordered in select list way are.
i don't think assignments within case expression (the way in query in question) "work" way we'd expect them to. think has order of operations mysql performs. i've had result of expression assigned user defined variable in select list, shown above.
the expression if(@prev_score = b.score,@rank_cnt,@rank_cnt+1) replaced equivalent case expression:
case when @prev_score = b.score @rank_cnt else @rank_cnt+1 end note expression returning value, it's not attempting assignment.
i prefer have user defined variables initialized within statement, not dependent on separate set statements. in case, we're not concerned what's returned inline view i, except want return 1 row (because of join operation)... we're more interested inline view query gets materialized before outer query runs, variables initialized when outer query runs.
also, added expression order by, results more deterministic.
... (add link applicable section of mysql reference manual regarding warnings user defined variables)
the mysql_ interface functions deprecated. use mysqli or pdo interface instead.
