CodeIgniter Forums
Calculate position based on a value - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Model-View-Controller (https://forum.codeigniter.com/forumdisplay.php?fid=10)
+--- Thread: Calculate position based on a value (/showthread.php?tid=63842)

Calculate position based on a value - Violette - 12-14-2015

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

RE: Calculate position based on a value - keulu - 12-14-2015

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

RE: Calculate position based on a value - Violette - 12-15-2015

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 ?

RE: Calculate position based on a value - ozzy mandiaz - 12-15-2015

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);

RE: Calculate position based on a value - Violette - 12-17-2015

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

RE: Calculate position based on a value - davicotico - 12-17-2015

(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