CodeIgniter Forums

Full Version: Join on Table Named with Dash Produces 1054 Error
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

Active record join doesn't work when a table name includes a dash "-". For example:

$this->db->join('12-students', '12-students.studentid = 12-registration.studentid');
$query = $this->db->get();
return ($query->row_array());

Produces the following error:

A Database Error Occurred

Error Number: 1054

Unknown column '12' in 'on clause'

SELECT * FROM (`12-registration`) JOIN `12-students` ON `12`-`students`.`studentid` = 12-registration.studentid

Notices the ON clause is unusually quoted. The active record class works well on those tables in all other respects.

Any ideas?

El Forum

Looks like a bug report is already filed:

El Forum


I have the same problem with the latest version of CI but for the columns names.

                    ->from('fqdn_has_ipaddress AS r')
                    ->join('fqdn AS f', 'f.fqdn-id = r.fqdn-id', 'inner')
                    ->join('ipaddress AS i', 'i.ipaddress-id = r.ipaddress-id', 'inner')
                    ->where('f.fqdn-id', $fqdnid)

SELECT * FROM (`fqdn_has_ipaddress` AS r) INNER JOIN `fqdn` AS f ON `f`.`fqdn`-`id` = r.fqdn-id INNER JOIN `ipaddress` AS i ON `i`.`ipaddress`-`id` = r.ipaddress-id WHERE `f`.`fqdn-id` = '2'

Left condition is sql quoted arround dash and in right condition there is no sql quotes.
It's a bug or I don't make good syntax query?


El Forum

It's generally not a good idea to use dashes in db/table/field names as mysql thinks you're doing subtraction, just like it's not a good idea to use MySQL reserved words as names for identifiers. The escaping logic for your edge case obvious doesn't exist in the Active Record, so you'll probably need to use db::query() and write your own query, escaping your dashed column names. I'd use underscores instead of dashes so it's clear that subtraction operations are totally separate from names.

El Forum

ok thanks I will replace dash by underscore