How to use sql database value in php query

Question

So I am trying to do a radius type search with multiple variables, I have the Lat/Long stored for each city in my database, but I have noticed that most solutions to what I want to do require me to query the whole database (which could eventually be 50,000+ rows) to use the value of the stored lat/long in a function.

Is there anyway to use this stored database value without getting it from the query first and just use it during my query.

This is how I do it right now, but it requires so much I think, specially once the database reaches 100,000 + rows it might take some time to go through each row, it would be better if I could check the distance during the query I think so I could filter results better as well without having to check every single row.

function getLocation($address) {
    if (!empty($address)) {

        //Formatted address
        $formattedAddr = str_replace(' ', '+', $address);

        //Send request and receive json data by address
        $geocodeFromAddr = file_get_contents('http://maps.googleapis.com/maps/api/geocode/json?address=' . $formattedAddr . '&sensor=true_or_false');
        $output1 = json_decode($geocodeFromAddr);

        //Get latitude and longitute from json data
        $latitude = $output1->results[0]->geometry->location->lat;
        $longitude = $output1->results[0]->geometry->location->lng;

        return "$latitude,$longitude";
    } 
    else {
        return false;
    }
}

function GetDrivingDistance($lat1, $lat2, $long1, $long2) {
    $url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" . $lat1 . "," . $long1 . "&destinations=" . $lat2 . "," . $long2 . "&mode=driving&units=imperial";
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_PROXYPORT, 3128);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
    $response = curl_exec($ch);
    curl_close($ch);
    $response_a = json_decode($response, true);
    $dist = $response_a['rows'][0]['elements'][0]['distance']['text'];
    $time = $response_a['rows'][0]['elements'][0]['duration']['text'];

    return $dist;

    //return array('distance' => $dist, 'time' => $time);
}

$sql = "SELECT * FROM mytable WHERE `date` >= '$selected_date'";

if ($length && !empty($length)) {
    $sql.= " AND `length`<='$length'";
}

$sql.= " ORDER BY date_created DESC";

$CITY = "DALLAS, TX";
 // FOR TESTING
$SLAT = getLocation($CITY);
$oparr = split("\,", $SLAT);
$FLAT = $oparr[0];
$FLONG = $oparr[1];

while ($row = $result->fetch_assoc()) {
    $id = $row['id'];
    $city = $row['city'];
    $state = $row['state'];
    $lat = $row['lats'];
    $long = $row['longs'];
    $distance = GetDrivingDistance($FLAT, $lat, $FLONG, $long);

    if ($distance <= 200) {

        // SHOW TABLE RESULTS

    } 
    else {
        echo "No results";
    }
}

Answer 1

Based on the queries from Fastest Way to Find Distance Between Two Lat/Long Points I have found that you can calculate the distances in MySQL, reducing the need to read records from the database before calling the Google Maps API. See the code sample below.

Take a look at this SQLFiddle which has 3 records in the table, two of which are within 200 miles of Dallas, TX (the other is near Houston).

//get location for Dallas
//getLocation('Dallas, TX') //
$lat = 32.7856717;
$lng = -96.77326000000001;
    $query = 'SELECT city, ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( mytable.lats ) ) 
    * cos( radians(mytable.longs) - radians('.$lng.')) + sin(radians('.$lat.')) 
    * sin( radians(mytable.lats)))) AS distance  FROM mytable having distance < 200';

//$connection initialized earlier via mysqli_connect()
$results = $connection->query($query);
while($row = $results->fetch_object()) {
    //show table results
    echo 'record: '.$row->city.' - distance: '.$row->distance.'<br />';
}

You might also consider adding this plugin to your build so you can utilize the User-defined function (UDF) haversine_distance() - then your query could be simplified to something like:

$query = 'SELECT city, haversine_distance('.$lat.','.$lng.',mytable.lats,mytable.longs) AS distance  FROM mytable having distance < 200';

Answer 2

$sql = "SELECT caid, months, visa, tickets, package, income, expenses FROM cash group by date(cash.months) "; $result = $conn-> query($sql);

  $result=mysqli_query($conn, $sql);

 $rows=mysqli_fetch_array($result);
//      $d = $data["expenses"];
  $res=mysqli_query($conn, $sql);
   $data=mysqli_fetch_array($res);



  if ($result-> num_rows >0){
      while($row = $result-> fetch_assoc()){
          //$expenses =  $row["expenses"];
         // echo $expenses;




     echo  $row["visa"]."</br>" ;

      }
     }