+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: mysql query mess help (/thread-40665.html)
mysql query mess help - El Forum - 04-15-2011
[eluser]quasiperfect[/eluser]
can anyone sugest a more elegant method of doing this
Code:
$this->db->query("CREATE TEMPORARY TABLE ranks (rank int(10) NOT NULL,voted VARCHAR(15) NOT NULL) ENGINE=MEMORY");
$this->db->query("INSERT INTO ranks (rank, voted) select @rownum:[email protected]+1 as rank,voted from (select @rownum:=0,votes.voted, (sum(votes.vote)) as score,count(votes.id) as nr_votes from votes,users where votes.date like '".date("Y-m")."%' and votes.contest = ".$this->db->escape($contest)." and votes.sex = ".$this->db->escape($sex)." and users.deleted = 'no' and users.rights = 'normal' and users.username = votes.voted GROUP BY votes.voted ORDER BY score desc,nr_votes desc,users.subscription desc) as r");
$query = $this->db->query("select rank from ranks where voted = " . $this->db->escape($username));
$this->db->query("DROP TABLE ranks");?
the idea is to select the rank of a user
mysql query mess help - El Forum - 04-15-2011
[eluser]carbona[/eluser]
Can you shows us your tables structure ?