PHP & Laravel: ConvertingVARCHAR value to FLOAT in query

Question

I have stored in my table two fields lat and long, these columns have the datatype of VARCHAR

Now in Laravel I have the following variables:

$ne_lat = 21.405122657695813;
$ne_lng = -102.32061363281252;
$sw_lat = 19.984311565790197;
$sw_lng = -104.19652916015627;

Wich I want to use in my query to compare it against my table data like this:

$agencies = DB::table('users')
        ->whereRaw('lat < ? AND lat > ? AND long < ? AND long > ?',[$ne_lat,$sw_lat,$ne_lng,$sw_lng])

I've tried to CAST it like this:

$agencies = DB::table('users')
        ->whereRaw('CAST(lat AS FLOAT) < ? AND CAST(lat AS FLOAT) > ? AND CAST(long AS FLOAT) < ? AND CAST(long AS FLOAT) > ?',[$ne_lat,$sw_lat,$ne_lng,$sw_lng])

But it doesn't display any results (I'm not getting any error messages since the query shows up in json) it just doesn't show up. What am I doing wrong with my query?

Thanks in advance

Why have you defined these columns as varchar in the first place? — Mark Baker, Jun 13 '15 at 19:34
@mark baker - That's actually a Good question... Originally I was saving latitud and longitud as 20.3534535643,-113.23534535 (notice the comma) but now for testing purposes I splitted the column into two different columns.. Now just tested converting them to float directly and my original problem is still not solved :( Anyway thank you for your time and makign me think — Daniel, Jun 13 '15 at 19:44
Have you tried echoing the actual query and running it on the database directly ? By doing that after your query : `$query = DB::getQueryLog(); echo end($query);` — β.εηοιτ.βε, Jun 13 '15 at 20:15
@b.enoit.be Apparently the output is not how I expected it to be look: **string(113) "select * from `users` where `active` = ? and lat < ? AND lat > ? AND lng < ? AND lng > ?"** — Daniel, Jun 13 '15 at 20:30
http://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string ? — β.εηοιτ.βε, Jun 13 '15 at 20:33
@b.enoit.be I already read it, but that's the ouput I'm getting after doing **dd($agencies->toSql());** — Daniel, Jun 13 '15 at 20:38
Because it should. The bindings part of the debugging output show what those `?` should be replaced with. — β.εηοιτ.βε, Jun 13 '15 at 20:43
@b.enoit.be It's curious because if I enter the query with the direct values it works **->whereRaw('lat < 21.405122657695813 AND lat > 19.984311565790197 AND lng < -102.32061363281252 AND lng > -104.19652916015627')** not when I use the **?** and the array as in my original question — Daniel, Jun 13 '15 at 20:46

score -1 · Answer 1 · answered Jun 13 '15 at 19:45

(int)(your value);

Or you can use:

intval(string)

here is some sample:

<?php
echo intval(42);                      // 42
echo intval(4.2);                     // 4
echo intval('42');                    // 42
echo intval('+42');                   // 42
echo intval('-42');                   // -42
echo intval(042);                     // 34
echo intval('042');                   // 42
echo intval(1e10);                    // 1410065408
echo intval('1e10');                  // 1
echo intval(0x1A);                    // 26
echo intval(42000000);                // 42000000
echo intval(420000000000000000000);   // 0
echo intval('420000000000000000000'); // 2147483647
echo intval(42, 8);                   // 42
echo intval('42', 8);                 // 34
echo intval(array());                 // 0
echo intval(array('foo', 'bar'));     // 1
?>

This isn't useful for his question. He's trying to get the database to convert a character value to a number so that he can compare it inside an SQL statement. He's doesn't need PHP to convert the value. — orrd, Jun 14 '15 at 05:49

PHP & Laravel: ConvertingVARCHAR value to FLOAT in query

1 Answers1