php - 执行排名查询时mysql语法错误

Question

我想执行排名查询，但我不确定正确的语法是什么这是我的查询:

  static public function sortranks(){
    global $db;
    $sql ="TRUNCATE TABLE `ranking`";
    $db->query($sql);

    $sql = "INSERT INTO `ranking` (`user_id`) VALUES 
    ( SELECT `employe_id` FROM `rates_employe` WHERE `status` = '0' ORDER BY rawpoint DESC ) ";
    $db->query($sql);

    $sql = "UPDATE  rates_employe , ranking  SET rates_employe.rank = ranking.rank WHERE 
    rates_employe.employe_id = ranking.user_id ";
    $db->query($sql);
    echo 'ok';
    exit;
  }

当我运行此查询时，我不断收到语法错误

Database query failed: 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 'SELECT employe_id FROM rates_employe WHERE status = '0' ORDER BY rawpoint ' at line 2

Answer 1

您应该一项一项地执行查询，而不是将它们连接在一起。

通过简单的赋值更改 .= 字符串连接，并在分配每个查询后执行它。如:

 $sql = "TRUNCATE TABLE ranking";
 $db->query($sql);
 $sql = "INSERT INTO `ranking`(`user_id`) VALUES ..."; 
 $db->query($sql);

同时从查询中删除 VALUES:

INSERT INTO ranking (user_id) ( SELECT employe_id FROM rates_employe WHERE status = '0' ORDER BY rawpoint DESC )

抱歉，如果我听起来很迂腐，但在英语中，Employee 的拼写是在单词末尾有两个 e。