paulman888888 Posted June 27, 2010 Share Posted June 27, 2010 Hello Hello, I'm trying to get a rank from a voteing table. My table holds the vote in a coloum which is 1 for a like and 2 for a dislike. What i want to do is get rank of an id. I know what i need to do but i dont no how. I need to; Count the amount of 1's in the column vote Count the amount of 2's in the colomn vote Take the amount of 2's away from the amount of 1's then order it by the amount left. Can some please help me? Paul P.S Am sorry for the (really) bad words but i couldn't get it out of my head Quote Link to comment Share on other sites More sharing options...
Mchl Posted June 27, 2010 Share Posted June 27, 2010 Hello Hello, I'm trying to get a rank from a voteing table. My table holds the vote in a coloum which is 1 for a like and 2 for a dislike. Why not 0 and 1? Would make such thing a bit easier... Anyway SELECT id, SUM(IF(voteColumn =1,1,0)) AS like, SUM(IF(voteColumn = 2,1,0)) AS dontLike FROM table GROUP BY id ORDER BY like - dontLike DESC Quote Link to comment Share on other sites More sharing options...
paulman888888 Posted June 27, 2010 Author Share Posted June 27, 2010 Thank-you Mchl, I've edited it and its still not working :: Hmm... $result=mysql_query(" SELECT table_id, table_name, SUM(IF(vote = 1,1,0)) AS like, SUM(IF(vote = 2,1,0)) AS dontLike FROM votes WHERE table_name = \"lyrics\" GROUP BY table_id ORDER BY (like - dontLike) DESC")or die(mysql_error()); ////// And below is the creation code for the table CREATE TABLE IF NOT EXISTS `votes` ( `id` int(100) NOT NULL auto_increment, `table_name` varchar(100) NOT NULL, `user_id` int(100) NOT NULL, `table_id` int(100) NOT NULL, `vote` int(1) NOT NULL default '0', `date_voted` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1453 ; Quote Link to comment Share on other sites More sharing options...
Mchl Posted June 27, 2010 Share Posted June 27, 2010 'Not working' as in? Do you get any errors? BTW: There is no difference between INT(100) and INT(1). Quote Link to comment Share on other sites More sharing options...
paulman888888 Posted June 27, 2010 Author Share Posted June 27, 2010 I always thought the number in the brackets for INT(n) is the length. With INT(1) i can have 1-9 and with INT(2) i can have from 1-99. I might be wrong and if i am i would like to be corrected. I also think that INT(-1) is infinity. Anyways... The error; I get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'like, SUM(IF(vote = 2,1,0)) AS dontLike FROM votes WHERE table_name = "lyrics' at line 3 I just don't no what's up with it. I can normally sort this error when it happens. Please help Thank-you all again Paul Quote Link to comment Share on other sites More sharing options...
Mchl Posted June 27, 2010 Share Posted June 27, 2010 Oh... I know what's up. You cant use 'like' as a column alias, because it's MySQL's reserved word. Unless you backtick it. This should work: SELECT id, SUM(IF(voteColumn =1,1,0)) AS sumLike, SUM(IF(voteColumn = 2,1,0)) AS sumDontLike FROM table GROUP BY id ORDER BY sumLike - sumDontLike DESC The number in parentheses has nothing to do with what numbers you can store in a field. It just tells MySQL how to display these numbers. See here: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html Quote Link to comment Share on other sites More sharing options...
paulman888888 Posted June 27, 2010 Author Share Posted June 27, 2010 You learn something new everyday. I try yours and i get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table GROUP BY id ORDER BY sumLike - sumDontLike DESC' at line 1. When i try my moddifyed version of yours i get, // the code used " SELECT table_id, table_name, SUM(IF(vote=1,1,0)) AS dodlike, SUM(IF(vote=2,1,0)) AS dontLike FROM votes WHERE table_name = \"lyrics\" GROUP BY table_id ORDER BY (dodlike - dontLike) DESC" // //The error; Reference 'dodlike' not supported (reference to group function) Quote Link to comment Share on other sites More sharing options...
Mchl Posted June 27, 2010 Share Posted June 27, 2010 The first one is obviously because you should put your actualtable name in place of 'table' but you figured this out. The second one I almost forgot about. You have to work around it like this SELECT * FROM ( SELECT table_id, table_name, SUM(IF(vote=1,1,0)) AS dodlike, SUM(IF(vote=2,1,0)) AS dontLike, SUM(IF(vote=1,1,0)) - SUM(IF(vote=2,1,0)) AS score FROM votes WHERE table_name = 'lyrics' GROUP BY table_id ) AS sq ORDER BY score DESC Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.