时间戳

首页 > 解决方案 > 尝试添加额外语句来处理查询时，我从 MariaDb 收到错误

php - 尝试添加额外语句来处理查询时，我从 MariaDb 收到错误

问题描述

我有一张有很多记录的表。我需要检查重复项。我有以下查询：

public function findduplicates(){
        $query =  $this->db->query(
            'SELECT book_id, auth_last , ebooks.ebook_title, storage_sys, ebook_path, AZ FROM ebooks
            INNER JOIN( SELECT ebook_title FROM ebooks  GROUP BY ebook_title HAVING COUNT(ebook_title) >1 ) temp ON ebooks.ebook_title = temp.ebook_title'
        );

        if($query->num_rows() > 0){
            return $query->result();
        }else{
            return FALSE;
        }
    }

该查询在我看来会产生以下结果，这就是我要寻找的结果：

Array
(
    [0] => stdClass Object
        (
            [book_id] => 31
            [auth_last] => Aardenburg
            [ebook_title] => 06 Bas Banning en de Tour de France.epub
            [storage_sys] => 1
            [ebook_path] => Aardenburg, A van - 06 Bas Banning en de Tour de France.epub
            [AZ] => A
        )

    [1] => stdClass Object
        (
            [book_id] => 46
            [auth_last] => Van
            [ebook_title] => 06 Bas Banning en de Tour de France.epub
            [storage_sys] => 1
            [ebook_path] => Aardenburg A van - 06 Bas Banning en de Tour de France.epub
            [AZ] => B
        )

)

因为表格非常大，我想限制我的选择，在我的方法中添加两个参数：

public function findduplicates($sys_code,$folder_name){
        $query =  $this->db->query(
            'SELECT book_id, auth_last , ebooks.ebook_title, storage_sys, ebook_path, AZ FROM ebooks WHERE storage_sys = $sys_code AND AZ = $folder_name
            INNER JOIN( SELECT ebook_title FROM ebooks  GROUP BY ebook_title HAVING COUNT(ebook_title) >1 ) temp ON ebooks.ebook_title = temp.ebook_title'
        );
        if($query->num_rows() > 0){
            return $query->result();
        }else{
            return FALSE;
        }
    }

near 'INNER JOIN...' 这给了我尝试过的 MariaDb 的错误：

WHERE storage_sys = '.$sys_code.' AND AZ = '.$folder_name.'

结果相同，并且：

WHERE (storage_sys = `'.$sys_code.'` AND AZ = `'.$folder_name.'`

也没有运气。

标签： phpmariadbcodeigniter-3codeigniter-4

解决方案

放在JOIN任何WHERE子句之前。PHP变量不应该直接放在SQL中，需要单独绑定。用占位符替换变量，?然后使用bind_param().

如果您使用的是 CodeIgniter，那么应该是这样的：

public function findduplicates():array {
    $sql = 'SELECT book_id, auth_last, ebooks.ebook_title, storage_sys, ebook_path, AZ 
        FROM ebooks 
        INNER JOIN( SELECT ebook_title FROM ebooks  GROUP BY ebook_title HAVING COUNT(ebook_title) >1 ) temp ON ebooks.ebook_title = temp.ebook_title
        WHERE storage_sys = ? AND AZ = ?';
    $db->query($sql, [$sys_code, $folder_name]);
    return $query->result();
}

推荐阅读