• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Join on Table Named with Dash Produces 1054 Error

#1
[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?

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

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

#3
[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

#4
[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.

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.