• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Get special record position in records

#1
Hi
I execute this query in database
SELECT * FROM tbl_user ORDER BY sScore
and result is
Username sScore
1 jack 100
2 Sara 59
3 frank 20
4 safar 10
5 omid 5
6 asghar 2

I need to know that what is grade of Sara between in all user
Also i need to know what is position of Sara in list (Sara position is 2
Reply

#2
It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause.

Convert the result to an array. Then do an array_search for "Sara". It will give you the array key. Because arrays are zero-based, add 1 to the key to determine Sara's position.
Reply

#3
@omid_student

If you need for some special reason the returned records to be enumerated by the database server, see this article: http://www.fromdual.com/ranking-mysql-results

On a random table of mine I tested the suggestion, it works:

Code:
$this->db->query("SET @rank = 0;");
        $result = $this->db->query(
"
    SELECT @rank := @rank + 1 AS rank, name, id
    FROM social_networks
    ORDER BY id DESC;
"
            )->result_array();
        var_dump($result);
Reply

#4
(02-24-2018, 12:21 PM)Wouter60 Wrote: It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause.

Convert the result to an array. Then do an array_search for "Sara". It will give you the array key. Because arrays are zero-based, add 1 to the key to determine Sara's position.

I used this way but i thought it is wrong way
Thanks
Reply

#5
(02-24-2018, 12:21 PM)Wouter60 Wrote: It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause.

Convert the result to an array. Then do an array_search for "Sara". It will give you the array key. Because arrays are zero-based, add 1 to the key to determine Sara's position.

Hey do you can help about below result?
I need define level for each user with his score
Example if user's score is 25 so level is 6
Thanks
Level 1: 0
Level 2: 5
Level 3: 10
Level 4: 15
Level 5: 20
Level 6: 25
Level 7: 30
Level 8: 35
Level 9: 40
Level 10: 45
Reply

#6
I would store the ranking in the table while saving the record. Otherwise, you need to add the ranking attribute to the records afterwards (after getting the results).
In both cases, I would use  a function like this (in the controller):

PHP Code:
private function get_rank($score
{
 
 return floor($score 5) + 1;


To get a ranking:
PHP Code:
$r $this->get_rank($score); 

I don't know if this can be done in MySQL directly.
Reply

#7
(02-25-2018, 12:59 AM)Wouter60 Wrote: I would store the ranking in the table while saving the record. Otherwise, you need to add the ranking attribute to the records afterwards (after getting the results).
In both cases, I would use  a function like this (in the controller):

PHP Code:
private function get_rank($score
{
 
 return floor($score 5) + 1;


To get a ranking:
PHP Code:
$r $this->get_rank($score); 

I don't know if this can be done in MySQL directly.
Yes it is best solution Thank you
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.