为何这段查询代码无法在CodeIgniter框架中运行？

Hey there! Let’s break down why your query isn’t running as expected and fix it step by step. I’ve spotted a couple of key issues that are likely causing the problem, plus some best practices to make your code safer and more reliable.

1. Critical SQL Injection Risk & Quoting Error #

Your current code directly concatenates the $id variable into the SQL string, which is a huge security risk (it opens the door to SQL injection attacks). On top of that, if $id is a string value, this will cause a syntax error because the quotes will clash.

Fix: Use Query Bindings #

CodeIgniter has built-in query binding that handles quoting automatically and protects against injection. Here’s how to rewrite your query:

$sql = 'SELECT course.*, AVG(course_review.`rating`) AS `avg_rating` 
        FROM course 
        LEFT JOIN course_review ON `course`.`id` = `course_review`.`course_id` 
        WHERE `course`.`course_category` = ? 
          AND `course`.`approved` = 3 
          AND `course`.`delete_course` != 1 
          AND `course`.`unpublish_course` != 1 
        GROUP BY `course`.`id` 
        ORDER BY avg_rating DESC';
$query = $this->db->query($sql, array($id));
return $query->result();

The ? acts as a placeholder for $id, and CodeIgniter takes care of escaping and quoting it properly.

2. Potential Conflict with MySQL’s ONLY_FULL_GROUP_BY Mode #

Most modern MySQL setups enable ONLY_FULL_GROUP_BY by default, which enforces strict SQL standards for GROUP BY clauses. Your query uses SELECT course.* but only groups by course.id—this will throw an error in strict mode because not all selected columns are part of the GROUP BY or an aggregate function.

Fix: Use CodeIgniter’s Active Record (Query Builder) #

Query Builder not only avoids injection risks but also helps you write cleaner, standards-compliant SQL. Instead of selecting all columns with *, list only the ones you need:

$this->db->select('course.id, course.title, course.description, AVG(course_review.rating) AS avg_rating');
$this->db->from('course');
$this->db->join('course_review', 'course.id = course_review.course_id', 'left');
$this->db->where('course.course_category', $id);
$this->db->where('course.approved', 3);
$this->db->where('course.delete_course !=', 1);
$this->db->where('course.unpublish_course !=', 1);
$this->db->group_by('course.id');
$this->db->order_by('avg_rating', 'DESC');

$query = $this->db->get();
return $query->result();

This approach is easier to maintain and avoids the GROUP BY conflict.

3. Debugging Trick to Pinpoint Exact Errors #

If you’re still stuck, use CodeIgniter’s last_query() method to print the exact SQL that’s being sent to the database. You can run this SQL directly in your database client (like phpMyAdmin or MySQL Workbench) to see the specific error message:

$query = $this->db->query(...);
echo $this->db->last_query(); // Outputs the generated SQL

This will help you catch any remaining syntax issues or logical errors in your query.

Give these fixes a shot—switching to query bindings or Query Builder should resolve most of your problems, and the debugging tip will help you iron out any remaining kinks.

内容的提问来源于stack exchange，提问作者CHAN