Join on Table Named with Dash Produces 1054 Error

Join on Table Named with Dash Produces 1054 Error - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Join on Table Named with Dash Produces 1054 Error (/showthread.php?tid=29257)

Join on Table Named with Dash Produces 1054 Error - El Forum - 04-04-2010

[eluser]oppenheimer[/eluser]
Active record join doesn't work when a table name includes a dash "-". For example:

Code:
$this->db->select('*');

$this->db->from('12-registration');

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

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

return ($query->row_array());

Produces the following error:

Code:
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?

Join on Table Named with Dash Produces 1054 Error - El Forum - 04-04-2010

[eluser]oppenheimer[/eluser]
Looks like a bug report is already filed:

http://codeigniter.com/bug_tracker/bug/7692/

Join on Table Named with Dash Produces 1054 Error - El Forum - 09-22-2014

[eluser]wallace[/eluser]
Hi,

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

Code:
$this->db

                    ->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)

                    ->get()->result();

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?

Thanks

Join on Table Named with Dash Produces 1054 Error - El Forum - 09-22-2014

[eluser]CroNiX[/eluser]
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.

Join on Table Named with Dash Produces 1054 Error - El Forum - 09-24-2014

[eluser]wallace[/eluser]
ok thanks I will replace dash by underscore