Welcome Guest, Not a member yet? Register   Sign In
Stopping Active Record JOIN from escaping characters
#1

[eluser]johnnytoobad[/eluser]
I'm trying to execute the following query using active record:
Code:
SELECT geoname.geonameid, geoname.name, geoname.country, admin1codes.name
FROM (geoname)
LEFT JOIN admin1codes
ON CONCAT(geoname.country, ".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE geoname.name LIKE '%pre%'
ORDER BY population desc LIMIT 10

Here is the CI code I have:
Code:
$this->db->select('geoname.geonameid, geoname.name, geoname.country, admin1codes.name');
$this->db->like('geoname.name', $location_name);
$this->db->orderby('population', 'desc');
$this->db->join('admin1codes', 'CONCAT(geoname.country, ".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code', 'left');
$query = $this->db->get('geoname', $num_results);

This however fails because of the "." in the CONCAT on the JOIN code. At least I think thats the problem. I tried passing FALSE as the third paramer on the JOIN but this had no effect. I'm still a beginner, so if anyone has any suggestions that would be great. Thanks.
#2

[eluser]Đaяк Đaηтє[/eluser]
I'm not really surehow importat is the order , why you don't try to do like this:

Code:
$this->db->select('geoname.geonameid, geoname.name, geoname.country, admin1codes.name');
$this->db->join('admin1codes', 'CONCAT(geoname.country, ".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code', 'left');
$this->db->like('geoname.name', $location_name);
$this->db->orderby('population', 'desc');
$query = $this->db->get('geoname', $num_results);
#3

[eluser]johnnytoobad[/eluser]
I don't think its the order thats causing a problem the error i get is:
Quote:A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code WHERE `geoname`.`name` L' at line 3
#4

[eluser]Đaяк Đaηтє[/eluser]
Ok Maybe yo need to include geoname.admin1 field in your select clause

Code:
$this->db->select('geoname.admin1,geoname.geonameid, geoname.name, geoname.country, admin1codes.name');
$this->db->join('admin1codes', 'CONCAT(geoname.country, ".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code', 'left');
$this->db->like('geoname.name', $location_name);
$this->db->orderby('population', 'desc');
$query = $this->db->get('geoname', $num_results);
#5

[eluser]Hakkam[/eluser]
Please print out last_query() .. and then try execute that sql in sql command.
#6

[eluser]johnnytoobad[/eluser]
Last query gives me:
Code:
A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE `geoname`.`name` L' at line 3

SELECT `geoname`.`admin1`, `geoname`.`geonameid`, `geoname`.`name`, `geoname`.`country`,
`admin1codes`.`name` FROM (`geoname`) LEFT JOIN `admin1codes`
ON `CONCAT`(`geoname`.`country,` ".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE `geoname`.`name` LIKE '%lon%' ORDER BY `population` desc LIMIT 1

The SQL that runs ok on SQL command is:
Code:
SELECT geoname.admin1, geoname.geonameid, geoname.name, geoname.country, admin1codes.name
FROM geoname LEFT JOIN admin1codes
ON CONCAT(geoname.country, '.', SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE geoname.name LIKE '%lon%' ORDER BY population desc LIMIT 10
#7

[eluser]Đaяк Đaηтє[/eluser]
Try with:

Code:
$this->db->select('geoname.admin1,geoname.geonameid, geoname.name, geoname.country, admin1codes.name');
$this->db->join('admin1codes', 'CONCAT_WS(".",geoname.country, SUBSTR(geoname.admin1,1,2)) = admin1codes.code', 'left');
$this->db->like('geoname.name', $location_name);
$this->db->orderby('population', 'desc');
$query = $this->db->get('geoname', $num_results);

OR

Code:
$consulta = $this->db->query('SELECT geoname.admin1, geoname.geonameid, geoname.name, geoname.country, admin1codes.name
FROM geoname LEFT JOIN admin1codes
ON CONCAT(geoname.country, '.', SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE geoname.name LIKE '%lon%' ORDER BY population desc LIMIT 10');

I don't understand why the dot character is the problem, the warning tells you that:
Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE `geoname`.`name` L' at line 3

I see the last query printed and I don't undestand why ` character is after comma character that's is a mistake
Code:
ON `CONCAT`(`geoname`.`country,` ".",....
instead
Code:
ON `CONCAT`(`geoname`.`country`, ".",....
and probably this is the error, can you see the difference?.

Code:
SELECT `geoname`.`admin1`, `geoname`.`geonameid`, `geoname`.`name`, `geoname`.`country`,
`admin1codes`.`name` FROM (`geoname`) LEFT JOIN `admin1codes`
ON `CONCAT`(`geoname`.`country`, ".", SUBSTR(geoname.admin1, 1, 2)) = admin1codes.code
WHERE `geoname`.`name` LIKE '%lon%' ORDER BY `population` desc LIMIT 1




Theme © iAndrew 2016 - Forum software by © MyBB