Welcome Guest, Not a member yet? Register   Sign In
Calculate position based on a value
#1
Rainbow 

Hello,
I need counseling to help me complete a function codeigniter.

Let me explain, with this sql function, I get a ranking of "total_stats".
Code:
SELECT a1.from_user_id, a1.total_stats, COUNT(a2.total_stats) total_stats_Rank
FROM score a1, score a2
WHERE a1.total_stats <= a2.total_stats OR (a1.total_stats = a2.total_stats AND a1.from_user_id = a2.from_user_id)
GROUP BY a1.from_user_id, a1.total_stats
ORDER BY a1.total_stats DESC, a1.from_user_id DESC

Result

[Image: Capture.PNG]

With this sql function, I get the ranking "total_stats" with "from_user_id"

Code:
SELECT a1.from_user_id, a1.total_stats, COUNT(a2.total_stats) position
FROM score a1, score a2
WHERE a1.total_stats <= a2.total_stats AND a1.from_user_id=25030
GROUP BY a1.from_user_id, a1.total_stats
ORDER BY a1.total_stats DESC, a1.from_user_id DESC

Result :

[Image: Capture2.PNG]

I use this function to calculate the position of a user based on the total of "total_stats". If "from_user_id" exists then update position. If "from_ser_id" does not exist create it. Then display the result in my view.

PHP Code:
public function position($posit_score// Position user
{
 
   $this->db->select('a1.from_user_id, a1.total_stats, COUNT(a2.total_stats) position');
 
   $this->db->from('score'); 
 
   $this->db->where('a1.total_stats <= a2.total_stats AND a1.from_user_id=?');
    
$this->db->group_by('a1.from_user_id, a1.total_stats');
    
$this->db->order_by('a1.total_stats DESC, a1.from_user_id DESC');
 
   
    $result 
$this->db->get()->row_array();
 
   print_r($result);
 
   
    $total_posit 
$result['position'];
 
   
    if 
($this->ttl_position($user_id) > 0// Check si exists dans 'score' 
 
   {
 
       $data = array( 
 
           'position' => $total_posit
        
);
 
       $this->db->where('from_user_id'$user_id);
 
       $this->db->update('score'$data);
 
   }
 
   else
    
{
 
       $data = array( 
 
           'from_user_id' => $user_id,
 
           'position' => $total_posit
        
);
 
       $this->db->insert('score'$data);  
    


 
   return $total_posit;
}

public function 
ttl_position($user_id)
{
 
   $this->db->where('from_user_id'$user_id);
 
   $this->db->from('score');
 
   
    return $this
->db->count_all_results();



But this returns me an error message :

Error Number: 1054

Unknown column 'a1.from_user_id' in 'field list'


Someone would have an idea of where I was wrong ?

Thank you in advance for your precious help

Violette
Reply
#2

(This post was last modified: 12-14-2015, 09:36 AM by keulu.)

hi guy girl,

watch your error Smile

SELECT a1.from_user_id, a1.total_stats, COUNT(a2.total_stats) position
FROM score a1, score a2
VS
$this->db->select('a1.from_user_id, a1.total_stats, COUNT(a2.total_stats) position');
$this->db->from('score');

try to do this

$this->db->from('score a1, score a2');

but i didn't understand why you used 2 aliases for the same Table ?

can't you do this ?

SELECT from_user_id, total_stats, COUNT(total_stats) AS position
FROM score
WHERE total_stats <= position AND from_user_id=25030
GROUP BY from_user_id, total_stats
ORDER BY total_stats DESC, from_user_id DESC
Reply
#3

(This post was last modified: 12-15-2015, 12:22 AM by Violette.)

Thank you for your answer.

After correction, I have a new error:

Unknown column '?' in 'where clause'


Why can not I do it?

Code:
SELECT a1.from_user_id, a1.total_stats, COUNT(a2.total_stats) position
FROM score a1, score a2
WHERE a1.total_stats <= a2.total_stats AND a1.from_user_id=?
GROUP BY a1.from_user_id, a1.total_stats
ORDER BY a1.total_stats DESC, a1.from_user_id DESC


because this function does not return any results me:
Code:
SELECT from_user_id, total_stats, COUNT( total_stats ) AS position
FROM score
WHERE total_stats <= position
AND from_user_id =25030
GROUP BY from_user_id, total_stats
ORDER BY total_stats DESC , from_user_id DESC

Does there another solution to create a ranking?

Could you help me please ?
Reply
#4

just a casual observer... however..

the only place in your query where there is a ? would be "AND a1.from_user_id=?".

have you included the parameter substitution, like you did before (i.e., $this->db->where('from_user_id', $user_id);
OM...

-------------------------
And on the pedestal these words appear:
'My name is Ozymandias, king of kings:
Look on my works, ye Mighty, and despair!'
Nothing beside remains. Round the decay
Of that colossal wreck, boundless and bare
The lone and level sands stretch far away.
Reply
#5

Thank you but I had already made the correction.
I have trouble writing my fuction.
Someone an idea to move me please
Reply
#6

(This post was last modified: 12-17-2015, 06:31 AM by davicotico. Edit Reason: fix )

(12-17-2015, 12:22 AM)Violette Wrote: Thank you but I had already made the correction.
I have trouble writing my fuction.
Someone an idea to move me please

With this fields: from_user_id, total_stats

You can order by total_stats and get the rank position without update another table:

Code:
SELECT from_user_id, total_stats, position FROM
    (
    SELECT t.from_user_id, total_stats,
        @rownum := @rownum + 1 AS position
    FROM score t, (SELECT @rownum := 0) r
    ORDER BY total_stats DESC
    ) as table_position

And for a single user too:

Code:
SELECT from_user_id, total_stats, position FROM
    (
    SELECT t.from_user_id, total_stats,
        @rownum := @rownum + 1 AS position
    FROM score t, (SELECT @rownum := 0) r
    ORDER BY total_stats DESC
    ) as table_position
WHERE from_user_id = 25032

Reply




Theme © iAndrew 2016 - Forum software by © MyBB