Keywords: CodeIgniter | LIKE queries | wildcard handling | database queries | PHP framework
Abstract: This technical article provides an in-depth analysis of proper wildcard handling in CodeIgniter's LIKE queries. By examining common error patterns, it explains the automatic escaping mechanism and wildcard addition rules of the $this->db->like() method, while demonstrating multiple query patterns. The discussion also covers the fundamental differences between HTML tags like <br> and character sequences like \n, along with proper escaping techniques for special characters in code examples.
Understanding CodeIgniter's LIKE Query Mechanism
CodeIgniter's database query builder offers robust LIKE query functionality, yet many developers encounter issues when handling wildcards. The core challenge lies in understanding the operational principles of the $this->db->like() method.
Analysis of Common Error Patterns
A typical incorrect implementation appears as:
$this->db->like('film.title', "%$query%");
$this->db->escape_like_str($query);
$res = $this->db->get('film');This approach presents two primary issues: manual addition of % wildcards disrupts query conditions, and improper timing/placement of escape_like_str() calls.
Correct Implementation Methodology
According to CodeIgniter's official documentation, the $this->db->like() method automatically handles wildcard placement and string escaping. The proper implementation is remarkably straightforward:
$this->db->like('title', $query);
$res = $this->db->get('film');By default, this generates SQL equivalent to WHERE title LIKE '%query%', automatically adding % wildcards before and after the search string while performing necessary escaping operations.
Advanced Query Patterns
CodeIgniter supports three distinct wildcard placement modes:
// Prefix matching: Find records ending with specific string
$this->db->like('title', 'match', 'before');
// Generates: WHERE title LIKE '%match'
// Suffix matching: Find records beginning with specific string
$this->db->like('title', 'match', 'after');
// Generates: WHERE title LIKE 'match%'
// Full matching: Find records containing specific string (default mode)
$this->db->like('title', 'match', 'both');
// Generates: WHERE title LIKE '%match%'Security Considerations
When implementing LIKE queries, SQL injection prevention is paramount. The $this->db->like() method incorporates built-in safe string escaping mechanisms, making manual escape_like_str() calls unnecessary. For user input processing, data validation and sanitization are strongly recommended.
Version Compatibility Notes
The techniques described apply to CodeIgniter 2.x, 3.x, and 4.x, though implementation details may vary slightly between versions. Developers should consult version-specific documentation:
- CodeIgniter 2: Database Active Record Documentation
- CodeIgniter 3: Query Builder Documentation
- CodeIgniter 4: Query Builder Documentation
Practical Implementation Recommendations
In production environments, encapsulating search functionality within dedicated model methods enhances maintainability and reusability. For LIKE queries on large datasets, appropriate indexing strategies should be implemented to optimize query performance.