hi all , I have this SQL Query :

SELECT *, ( 3959 * acos( cos( radians(41.832100) ) * cos( radians( latitude ) ) * cos( radians( longitude) - radians(-87.789597) ) + sin( radians(41.832100) ) * sin( radians( latitude ) ) ) ) AS distance FROM cities_extended HAVING distance < 10 ORDER BY distance LIMIT 0 , 5;

when I run it through phpMyAdmin or n the command line it works.
However I tried to implement it with codeigniter via the $this->db->query(); function.

like so :

$nearby_cities = $this->db->query("SELECT city,state,zip, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM cities_extended HAVING distance < 25 ORDER BY distance LIMIT 0 , 3;");

and I get this :

Error Number: 1582

Incorrect parameter count in the call to native function 'radians'

SELECT city,state,zip, ( 3959 * acos( cos( radians() ) * cos( radians( latitude ) ) * cos( radians( longitude) - radians() ) + sin( radians() ) * sin( radians( latitude ) ) ) ) AS distance FROM cities_extended HAVING distance < 25 ORDER BY distance LIMIT 0 , 3;

it does not make sense that when I run the exact same query on phpmyadmin and through the command line on mysql it works , but with php and codeigniter, mysql gives me an error. can any one help ?

You have some $ which make the latitude and longitudes variables which are empty

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

Latitude and Longitude are columns in my table. I managed to fix the problem by doing this.

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM cities_extended HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
           mysql_real_escape_string('41.832100'),
            mysql_real_escape_string('-87.789597'),
            mysql_real_escape_string('41.832100'),
            mysql_real_escape_string('25'));
            
         $nearby_cities = $this->db->query($query);

that was quite strange!

FYI, try to avoid being in the habit of handling queries like that. Obviously this particular case is safe, but read this before you pass user-provided data into your app.

Also, obligatory meme.

_________________

/* TODO: need to add signature to posts */

In CodeIgniter, what hoopycat recommends is called "Query Bindings".

http://codeigniter.com/user_guide/database/queries.html

To agree with the above, "bind variables" are very good practice. Not only do they help with avoiding SQL injection attacks, they can actually have performance benefits in some databases!

eg
if I do "select * from table where column1=$myvar" then run it twice with myvar=1 and myvar=2 then the SQL parser will see two separate SQL strings and parse them.
However, if I do "select * from table where column1=?" and run it twice with different bind values then the SQL is identical and the SQL parser may be able to use the cached parse results second time around (Oracle definitely works this way). So we are more secure _and_ more efficient.

Neat, huh?

_________________
Rgds
Stephen
(Linux user since kernel version 0.11)

I was expecting this...