Codeigniter $this->db->order_by(' ','desc') result is not complete

Codeigniter $this->db->order_by(' ','desc') result is not complete

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Debugging Inconsistent Sorting in CodeIgniter Queries

When you run a query using $this->db->order_by('course_name', 'desc') and notice that ascending order works perfectly while descending order returns results in a seemingly random or incomplete sequence, the issue almost certainly lies with how the database engine handles collation and case sensitivity. This is a common pitfall when working with legacy CodeIgniter applications where default configuration often leans toward MySQL's standard utf8_general_ci or utf8_unicode_ci collations.

In these specific collations, uppercase and lowercase letters are treated differently than in ASCII sorting. More critically, the "General" and "Unicode" collation types prioritize case-insensitive comparison by default for many character sets, but the internal algorithm can behave unpredictably when mixed with specific character encoding nuances or when the underlying storage engine (like MyISAM vs. InnoDB) interacts with the collation rules during a DESC scan.

The core reason ascending works while descending fails is often related to how the database sorts the binary representation of the strings versus their logical representation. If your course_name column contains mixed-case data (e.g., "Math 101" and "math 200"), an ascending sort might group them in a way that feels correct logically, but a descending sort might expose inconsistencies if the collation treats 'A' and 'a' as equal for comparison purposes but sorts their underlying binary values differently depending on the direction of the scan.

The Collation Trap

The most robust solution is to enforce a consistent collation rule directly within your query or at the schema level. You can bypass the default database-wide settings by specifying the COLLATE clause explicitly in your CodeIgniter query builder call.

Instead of relying on the global configuration, modify your function to force a binary sort or a case-sensitive ASCII sort. Here is how you adjust the query:

function sort_all_courses_desc($tenant_id)
{
    $this->db->select('*');
    $this->db->where('tenant_id', $tenant_id);
    
    // Enforce ASCII binary sorting to ignore case and ensure consistent order
    $this->db->order_by("course_name", 'desc', 'ascii_bin'); 
    
    $this->db->from('courses');
    $query = $this->db->get();
    return $query->result();
}

By appending the third parameter 'ascii_bin' to order_by, you tell CodeIgniter (and subsequently MySQL) to sort strictly by the ASCII values of the characters. In this mode, 'A' is distinct from 'a', and they are sorted sequentially without regard for linguistic collation rules. This often resolves the "incomplete" sort issue because it removes the ambiguity of case-insensitive grouping that causes descending scans to skip or reorder rows unexpectedly in certain MySQL versions.

Schema-Level Fixes

If adding the third parameter to every query feels cumbersome, a more permanent fix involves altering the column definition itself. You can run a migration script to change the collation of the course_name column:

ALTER TABLE courses 
MODIFY COLUMN course_name VARCHAR(255) COLLATE utf8mb4_bin;

Switching to utf8mb4_bin ensures that future inserts and updates are handled with strict binary sorting. Note that this changes the behavior for all queries on that table, so if you rely on case-insensitive searches elsewhere (e.g., $this->db->like('course_name', '%math%')), you must handle those separately using LOWER() functions or ensure your application logic accounts for the change.

Trade-offs to Consider

While binary sorting guarantees a strict alphabetical order based on ASCII values, it sacrifices case-insensitive grouping. If your application expects "Apple" and "apple" to appear next to each other regardless of case, a binary sort will place them apart ('A' comes before 'a'). This is a significant trade-off: you gain deterministic ordering at the cost of linguistic flexibility.

If you require both strict sorting for lists (like a course menu) and fuzzy matching for search forms, you might need to maintain separate columns or use application-level logic to handle case normalization before rendering results. Always verify your database version; behavior with collations can vary between MySQL 5.x and 8.x, where the handling of utf8 versus utf8mb4 differs slightly.

If you are maintaining a larger enterprise application and need similar robust query patterns across multiple frameworks or microservices, checking out resources from https://laravelcompany.com can provide insights into modern PHP ecosystem practices for handling data consistency.

Our network