php - 向mysql查询添加条件并再次获取

Question

我运行了 3 个几乎相同的查询，后两个查询具有 AND 条件。

主要查询:

$mess = $mysqli->prepare("SELECT * from ( SELECT cm.id ,cm.userid,cm.message,cm.voteup,cm.votedown,cm.date
                        FROM chat_messages cm 
                        INNER JOIN members m  ON m.id =cm.userid
                        INNER JOIN chat_settings cs ON cs.id = cm.room_id 
                        WHERE cm.setting_id = ?          
                        ORDER BY cm.date DESC LIMIT 30 ) ddd
                        ORDER BY date ASC ");
$mess->bind_param("i", $room);                              
$mess->execute();
$mess->store_result();

$mess->bind_result($chatid,$chat_userid,$message,$voteup,$votedown,$date);
while($row = $mess->fetch()){
    //im fetching here in my <div class='div1' >
}

然后，在第二个 div 中，我必须添加一个 AND 条件:

$mess2 = $mysqli->prepare("SELECT * from ( SELECT cm.id ,cm.userid,cm.message,cm.voteup,cm.votedown,cm.date
                        FROM chat_messages cm 
                        INNER JOIN members m  ON m.id =cm.userid
                        INNER JOIN chat_settings cs ON cs.id = cm.room_id
                        WHERE cm.setting_id = ?    AND voteup - votedown >= 5      
                        ORDER BY cm.date DESC LIMIT 30 ) ddd
                            ORDER BY date ASC ");
$mess2->bind_param("i", $room);                              
$mess2->execute();
$mess2->store_result();

$mess2->bind_result($chatid,$chat_userid,$message,$voteup,$votedown,$date);
while($row2 = $mess2->fetch()){
    //im fetching here in my <div class='div2' >
}

最后，在第三个 div 中，我有一个稍微不同的 AND 条件:

$mess3 = $mysqli->prepare("SELECT * from ( SELECT cm.id ,cm.userid,cm.message,cm.voteup,cm.votedown,cm.date
                        FROM chat_messages cm 
                        INNER JOIN members m  ON m.id =cm.userid
                        INNER JOIN chat_settings cs ON cs.id = cm.room_id 
                        WHERE cm.setting_id = ?    AND votedown - voteup >= 5      
                        ORDER BY cm.date DESC LIMIT 30 ) ddd
                            ORDER BY date ASC ");
$mess3->bind_param("i", $room);                              
$mess3->execute();
$mess3->store_result();

$mess3->bind_result($chatid,$chat_userid,$message,$voteup,$votedown,$date);
while($row3 = $mess3->fetch()){
    //im fetching here in my <div class='div3' >
}

一切正常但是执行这个几乎相同的查询似乎很笨拙。是否可以只用一个查询来构建相同的东西？我使用了 $mess->data_seek(0); 但它没有帮助，因为我没有将我的条件添加到查询中。

Answer 1

只需使用 PhP 来过滤您的数据，而不是三次查询您的数据库。在这种情况下，您可以考虑使用此解决方案，因为您使用相同的参数调用了 3 次查询:

    $mess3  =   $mysqli->prepare("  SELECT    * 
                                    FROM      ( SELECT  cm.id ,
                                                        cm.userid,
                                                        cm.message,
                                                        cm.voteup,
                                                        cm.votedown,
                                                        cm.date
                                                FROM        chat_messages cm 
                                                INNER JOIN  members m  ON m.id =cm.userid
                                                INNER JOIN  chat_settings cs ON cs.id = cm.room_id 
                                                WHERE       cm.setting_id = ?    
                                                AND         votedown - voteup >= 5      
                                                ORDER BY    cm.date DESC LIMIT 30 ) ddd
                                    ORDER BY   date ASC ");
$mess3->bind_param("i", $room);                              
$mess3->execute();
$mess3->store_result();
$mess3->bind_result($chatid,$chat_userid ,$message,$voteup,$votedown ,$date);

while($row = $mess3->fetch()){
  $voteup     =   $row['voteup'];
  $votedown   =   $row['votedown'];

  addToDiv1($row);

  if( $voteup - $votedown >= 5 ) {
    addToDiv2($row);
  }

  if( $votedown - $voteup >= 5 ) {
    addToDiv3($row);
  } 
}