计算上下投票插件的数量

问题描述：

Currently I have a jQuery upvote Plugin in my project. So, with every I click upvote it saves the value in the db as a true, with every vote taken back it save the value as false in the database and saves the value in the other column as 0. All I was trying to do is to count the total number of votes just like stack overflow does. Even now I am so confuse to on how to solve this issue. I end up making this code in php which is also make my whole program slow:

$sample1 = $this->db->prepare("SELECT * from Ratings WHERE TopicID = :current");
$sample1->bindParam(':current', $id);
$sample1->execute();
$RES1 = $sample1->fetchAll(PDO::FETCH_ASSOC); 
$upVote = 0;

foreach ($RES1 as $mk){
     if(($mk['Upvote'] === 'true') && ($mk['Downvote'] ==='false')){
             $upVote++;
                                    }
     else if(( $mk['Upvote'] ==='false') && ($mk['Downvote'] === 'true')){
             $upVote--;
                                }
     else if(($mk['Upvote'] === 'false') && ($mk['Downvote'] === 'false')){
            $upVote--;
                                }
     else if(($mk['Downvote'] === 'false')){
             $upVote++;
                                }
     else if(($mk['Downvote'] === 'true') && ($mk['Upvote'] ==='0') || ($mk['Upvote'] === 'false')){
             $upVote--;
     }

 }

My table structure :

For the above example my counter should be be 0

目前我的项目中有一个jQuery upvote插件。因此，每次我点击upvote它都会将db中的值保存为true，每次投票都将它保存在数据库中，并将值保存为false，并将另一列中的值保存为0.我所要做的就是计算总票数就像堆栈溢出一样。即使是现在，我对如何解决这个问题感到困惑。我最终在php中创建了这个代码，这也使我的整个程序变慢： p>

  $ sample1 = $ this-＆gt; db-＆gt; prepare（“SELECT * from Ratings”  WHERE TopicID =：current“）; 
 $ sample1-＆gt; bindParam（'：current'，$ id）; 
 $ sample1-＆gt; execute（）; 
 $ RES1 = $ sample1-＆gt; fetchAll（PDO  :: FETCH_ASSOC）;  
 $ upVote = 0; 
 
foreach（$ RES1 as $ mk）{
 if（（$ mk ['Upvote'] ==='true'）＆amp;＆amp;（$ mk ['Downvote']  ==='false'））{
 $ upVote ++; 
} 
 if if（（$ mk ['Upvote'] ==='false'）＆amp;＆amp;（$ mk ['Downvote'] =  =='true'））{
 $ upVote  - ; 
} 
 if if（（$ mk ['Upvote'] ==='false'）＆amp;＆amp;（$ mk ['Downvote']  ==='false'））{
 $ upVote  - ; 
} 
 if if（（$ mk ['Downvote'] ==='false'））{
 $ upVote ++; 
} \ 否则if（（$ mk ['Downvote'] ==='true'）＆amp;＆amp;（$ mk ['Upvote'] ==='0'）||（$ mk ['Upvote'] ==  ='false'））{
 $ upVote  - ; 
} 
 
} 
  code>  pre> 
 
 我的表结构：
    p> 
 
 
对于上面的示例，我的计数器应为0  p> 
  div>

答

I think with a little more study, the conditions you show to decide up or down count could be simplified quite a bit. But, here's a SQL statement that implements your conditions:

SELECT
    SUM(
        IF(Upvote='true' AND Downvote='false',1,
            IF(upvote='false' AND Downvote='true',-1,
                IF(Upvote='false' AND Downvote='false',-1,
                    IF(Downvote='false',1,
                        IF(Downvote='true' AND (Upvote='0' OR Upvote=false),-1,0)
                    )
                )
            )
        )) as `votes`
FROM Ratings
WHERE TopicD = :current

If only 'true' values count, this might do the trick:

SELECT
    SUM(IF(Upvote='true',1,0)) - SUM(IF(Downvote='true',1,0)) as `votes`
FROM Ratings
WHERE TopicD = :current

计算上下投票插件的数量

相关推荐