MySQL and CodeIgniter

You have some $ which make the latitude and longitudes variables which are empty > $nearbycities = $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 citiesextended HAVING distance < 25 ORDER BY distance LIMIT 0 , 3;");

@obs:

You have some $ which make the latitude and longitudes variables which are empty
> $nearbycities = $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 citiesextended HAVING distance < 25 ORDER BY distance LIMIT 0 , 3;");

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.

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

@hoopycat:

Also, obligatory meme.
I was expecting this…