php - Mysql 查询从值比某个值少 X% 或多 X% 的位置进行选择

Question

我想要完成的是选择所有比 $influencer_account['followed_by'] 多 20% 或少 20% 的用户。

我尝试使用下面的代码，但它不起作用。

$matchquery = mysql_query("SELECT * FROM `publishers_instagram_accounts` WHERE `pid` != '$publisher_id' AND ((".$influencer_account['followed_by']."+(".$influencer_account['followed_by']."*0.20)) <= followed_by) OR ((".$influencer_account['followed_by']."+(".$influencer_account['followed_by']."*0.20)) > followed_by) ORDER BY `id` DESC");

Answer 1

我认为您在代码中没有正确地进行数学计算。我还推荐你使用 mysqli 函数。请参阅这段代码，我没有测试它，但我认为它会起作用:

<?php

        $mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");

        $query = "SELECT * FROM `publishers_instagram_accounts` WHERE `pid` != :pub_id
            AND (followed_by >= :start ) AND (followed_by <= :end) ORDER BY `id` DESC";

        //You need users with followed_by in the range:
        //      [start,end]
        // where:
        //      start = $influencer_account['followed_by'] - $influencer_account['followed_by'] * 0.2
        //      end = $influencer_account['followed_by'] + $influencer_account['followed_by'] * 0.2
        $percent = 0.2;
        $start = $influencer_account['followed_by'] * (1 - $percent); //20% less
        $end = $influencer_account['followed_by'] * (1 + $percent);//20% more       
        $stmt = $mysqli->prepare($query);
        $stmt->bind_param('idd',$publisher_id,$start,$end);
        $stmt->execute();

        $result = $stmt->get_result();
      while ($row = $result->fetch_array(MYSQLI_ASSOC))
      {
            var_dump($row);
      }

?>

编辑:如果您想使用 mysql_query，请尝试:

$percent = 0.2;
$start = $influencer_account['followed_by'] * (1 - $percent); //20% less
$end = $influencer_account['followed_by'] * (1 + $percent);//20% more       
$query = "SELECT * FROM `publishers_instagram_accounts` WHERE `pid` !=  $publisher_id AND (followed_by >= $start ) AND (followed_by <= $end ) ORDER BY `id` DESC";
mysql_query($query);