Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

CodeIgniter query builder: Column 'id' in order clause is ambiguous

ErrorERROR 1052 (23000): Column 'id' in order clause is ambiguous

Short answer

MySQL error 1052 means the column name exists in more than one joined table, so MySQL cannot pick one. CodeIgniter only passes your text through. Qualify the column with its table name, for example order_by('users.id'), or give the column an alias in select() and sort by the alias.

  • Applies to CodeIgniter 3.1, CodeIgniter 4.5, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the SQL on CodeIgniter's database error page, then see which joined tables both have the column. The words after 'in' in the message (order clause, where clause, on clause) show where to qualify it.

    // CodeIgniter 3: print the last query before it fails or after it runs
    echo $this->db->last_query();
  2. 2Match what you see, then jump to its fix

    1. 1Column 'id' in order clause is ambiguousorder_by() uses a bare column name that two joined tables shareFix
    2. 2Column 'id' in where clause is ambiguous, or in on clause is ambiguouswhere() or the join condition uses an unqualified columnFix
    3. 3The select list holds several id columns and order_by() names only idTwo selected columns with the same name and a sort on that nameFix

What does the error look like?

  • ERROR 1052 (23000): Column 'id' in order clause is ambiguous
  • Column 'id' in order clause is ambiguous
  • Column 'id' in where clause is ambiguous
  • Column 'id' in on clause is ambiguous

Cause 1 of 3

order_by() uses a bare column name that two joined tables share

1Check

MySQL's error reference says the cause is a column used without proper qualification. A join of users and orders both with an id column makes ORDER BY id unclear.

$this->db->select('users.*, orders.total');
$this->db->from('users');
$this->db->join('orders', 'orders.user_id = users.id');
$this->db->order_by('id', 'DESC'); // fails: which id?

2Fix

Prefix the column with the table name in order_by().

$this->db->order_by('users.id', 'DESC');
$query = $this->db->get();

3Verify

Run the page again; the query returns rows ordered by that table's column.

echo $this->db->last_query();

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

where() or the join condition uses an unqualified column

1Check

The same rule applies in every clause. The MySQL reference gives an ON clause example, ON i = i, that fails because both sides are the same bare name.

$this->db->join('orders', 'user_id = id');   // ambiguous in on clause
$this->db->where('id', $id);                  // ambiguous in where clause

2Fix

Qualify each column with its table in the ON condition and in where().

$this->db->join('orders', 'orders.user_id = users.id');
$this->db->where('users.id', $id);

3Verify

Run the query again. No error 1052 appears.

echo $this->db->last_query();

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Two selected columns with the same name and a sort on that name

1Check

A column alias can be used in ORDER BY, according to the MySQL manual. Giving each duplicate column its own name removes the doubt.

$this->db->select('users.id AS user_id, orders.id AS order_id, orders.total');

2Fix

Alias the columns in select() and order by the alias.

$this->db->select('users.id AS user_id, orders.id AS order_id, orders.total');
$this->db->from('users');
$this->db->join('orders', 'orders.user_id = users.id');
$this->db->order_by('order_id', 'DESC');

3Verify

Inspect the first row: it holds the aliased keys user_id and order_id.

var_dump($this->db->get()->row_array());

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Always write table.column in any query that has a join, including where() and order_by().
  • Give joined tables short aliases (users AS u) and qualify with the alias to keep lines short.
  • Print last_query() while developing so you read the real SQL.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.