Welcome Guest, Not a member yet? Register   Sign In
SQRT function does not exist - using it for postalcode proximity search
#1

[eluser]coldfire82[/eluser]
Hi,

I am not sure what caused this problem because on localhost its working.

and even if I do
Code:
select SQRT(16);
on my server phpmuadmin SQL, it does work. But, the query that I execute from CI says,

Code:
A Database Error Occurred

Error Number: 1305

FUNCTION <mydatabase>.SQRT does not exist

SELECT DISTINCT geodb.postalcode FROM geodb WHERE ( 6367.41*SQRT ( 2* ( 1-cos (RADIANS(geodb.latitude)) *cos('1.0368209942311') *( sin (RADIANS(geodb.longitude)) *sin('0.3132031478148') +cos(RADIANS(geodb.longitude)) *cos('0.3132031478148') ) -sin(RADIANS(geodb.latitude)) *sin('1.0368209942311') ) ) <= '10' )

Instead of selecting <mydatabase>.SQRT it should select only SQRT because otherwise it gives error if I do

Code:
select <mydatabase>.SQRT(16);

Any hints on what might be causing it?

Thanks
#2

[eluser]pickupman[/eluser]
It maybe how CI escapes select strings. You haven't posted your exact code in CI, but something you could try is after you execute the query, on the next line add:
Code:
echo $this->db->last_query();

You maybe able to tell what query it is trying to execute exactly how CI is forming it. CI doesn't sometimes play nice when you have parenthesis in a query. If you notice it is adding backticks in weird spots try using
Code:
$this->db->query("complex query string");
#3

[eluser]Burak Guzel[/eluser]
Probably caused by escaping.

Set the second parameter to false. Example:

$this->db->select('SQRT(81)',false)




Theme © iAndrew 2016 - Forum software by © MyBB