Compare one query with multiple results in PHP

Question

I have two text input. Like This:

So, I have some kind of dynamic ajax search. I pass the input data and make two different mysql select. Something like this:

Table finalmap - SELECT 1

id -------- latitud-----longitud---

1        |  6.2523915 | -75.5737028 |
2        |  6.2640349 | -75.5990783 |
3        |  6.2642411 | -75.5999791 |
4        |  6.2638461 | -75.5982590 |
-------------------------------------

Table finalmap - SELECT 2

id -------- latitud-----longitud---

6        |  6.262669 | -75.596799 |
7        |  6.258019 | -75.598001 |
8        |  6.253668 | -75.599374 |
9        |  6.250724 | -75.602335 |
-------------------------------------

So, I want to compare every single "latitud and longitud field" with all the "latitud" and "longitud" fields of the SELECT2:

I have this Php, I have to make some improvements but can say that it worked:

<?php
$buscar = $_POST['b'];
$buscarcarrera = $_POST['c'];
$whatIWant = substr($buscar, strpos($buscar, "Calle") + 5);
$whatIWant2 = substr($buscarcarrera, strpos($buscarcarrera, "Carrera") + 5);
$vacio = "Calle50A";
$vacioc = "Carrera50A";

if (preg_match('/[A-Za-z]/', $whatIWant))
    {
    buscar($buscar, "", $buscarcarrera, "");
    }
  else
    {
    buscar($buscar, $vacio, $buscarcarrera, $vacioc);
    }

function buscar($b, $exclusion, $buscarcarrera, $exclusion2)
    {
    $con = mysql_connect('localhost', 'root', '');
    mysql_select_db('map', $con);
    $sql = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $b . "%' AND calle not in ('$exclusion')", $con);
    $contar = mysql_num_rows($sql);
    if ($contar == 0)
        {
        echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
        }
      else
        {
        while ($row = mysql_fetch_array($sql))
            {
            $nombre = $row['calle'];
            $id = $row['id'];
            $lat = $row['latitud'];
            $lon = $row['longitud'];
            }
        }

    $sql2 = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $buscarcarrera . "%' AND calle not in ('$exclusion2')", $con);
    $contar2 = mysql_num_rows($sql2);
    if ($contar2 == 0)
        {
        echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
        }
      else
        {
        while ($row2 = mysql_fetch_array($sql2))
            {
            $nombre2 = $row2['calle'];
            $id2 = $row2['id'];
            $lat2 = $row2['latitud'];
            $lon2 = $row2['longitud'];
            }
        }
    }

function distance($lat1, $lon1, $lat2, $lon2, $unit)
    {
    $theta = $lon1 - $lon2;
    $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
    $dist = acos($dist);
    $dist = rad2deg($dist);
    $miles = $dist * 60 * 1.1515;
    $unit = strtoupper($unit);
    if ($unit == "K")
        {
        return ($miles * 1.609344);
        }
      else
    if ($unit == "N")
        {
        return ($miles * 0.8684);
        }
      else
        {
        return $miles;
        }
    }

echo distance(32.9697, -96.80322, 29.46786, -98.53506, "M") . " Miles<br />";
echo distance(32.9697, -96.80322, 29.46786, -98.53506, "K") . " Kilometers<br />";
echo distance(32.9697, -96.80322, 29.46786, -98.53506, "N") . " Nautical Miles<br />";
?>

Then: how could compare each of the values ​​using the function to determine the proximity between the coordinates (using my distance() function)?. My problem is I don't know how to compare distance between each point of the 1st query with each point of the 2nd query, with every possible combinations.

I want to have something like this function compare (lat1,lon1,lat2,lon2); (lat1,lon1,lat3,lon3),(lat1,lon1,lat4,lon4),(lat1,lon1,lat5,lon5),(lat1,lon1,lat6,lon6) and so on.

Thank you very much in advance for any help given

Answer 1

Your logic is completely wrong and you are way complexifying what you are looking for. It s much simpler than you think.

First: in order to drastically speed up your script and queries as well as protecting it from any attacks, use PDO prepared statements and security protocols. Mysql none prepared statements are deprecated for more than a decade !!!

My experience (25 years of coding) shows me to never ever trust what s coming into your server so I am a little phobic about security.... This tutorial explains the first basic steps you need to do and explains you this script as it should be (copy past all codes as they are).

Note, Using a local IP address instead of Localhost will speed up mysql connections by 2 to 5 depending on dev platform (lamp,wamp etc...)

set execution parameters

ini_set('memory_limit', '64M'); // memory limit used by script
set_time_limit(0);              // no time limit

Establish Your secured mysql connection

try {
    $con = new PDO('mysql:host=127.0.0.1;dbname=map;charset=UTF8','root','password');
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Establish a second level security protocol to check if it s really an ajax call

$isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
    strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
if (!$isAjax) {
    $user_error = 'Access denied - not an AJAX request from allowed computer...';
    trigger_error($user_error, E_USER_ERROR);
    exit;
}

Second: Use the distance function INSIDE your mysql query in order to properly sort the results you are looking for. Please note the mysql distance used here is different from the one you are currently using (faster and more precise). It will gives you the distance between reference points from table 1 and all others from Table 2 in km, ordered by distance (closest as first row).

We suppose you get your results from your ajax like this:

if($isAjax){
    $param4 = '%' . $_POST['b']. '%';  // MYSQL LIKE bind param buscar 
    $param5 = '(' . $_POST['c'] . ')'; //MYSQL NOT IN bind param  buscarcarrera
}

We suppose you get your results from first query as this (simplified):

$sql1_array = array(
    array('id' => 1, 'Lati' => 6.2523915, 'Longi' => -75.5737028),
    array('id' => 2, 'Lati' => 6.2640349, 'Longi' => -75.5990783)
);

(note: always use "lati" and "longi" to avoid collision with PHP function like "long")

Your prepared statement:

$sql2 = "SELECT *, ( 6371 * acos( cos(radians(?)) 
* cos(radians(Latitude)) * cos(radians(Longitude) - radians(?)) + 
sin( radians(?) ) * sin(radians(Latitude))))  AS distance 
FROM finalmap WHERE calle LIKE ? AND calle NOT IN ? 
ORDER BY distance LIMIT 10";

$stmt = $con->prepare($sql2);

Third: Execute a loop with each query1 result to get the distances compared with table 2. In the loop, you bind your parameters to your prepared stmt. This will drastically speed up your queries as connection with database is persistent and the query is prepared with PDO (compiled and in memory).

foreach ($sql1_array as $key => $value) {
    $stmt->bindParam(1, $value['Lati'], PDO::PARAM_INT);
    $stmt->bindParam(2, $value['Longi'], PDO::PARAM_INT);
    $stmt->bindParam(3, $value['Lati'], PDO::PARAM_INT);
    $stmt->bindParam(4, $param4, PDO::PARAM_STR);
    $stmt->bindParam(5, $param5, PDO::PARAM_STR);
    $stmt->execute();
    $count = $stmt->rowCount();
    if ($count >= 1) {
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
      }
}


/*    $result ordered by distance     */

|**************************************|
|field 0 | field 1 | field 2 | distance|
|**************************************|
| 1      | a1      | a2      | 0.00123 | 1 meters
| 2      | b1      | b2      | 0.01202 | 12 meters
| 3      | c1      | c2      | 0.23453 | 234 meters
| 4      | d1      | d2      | 1.58741 | 1km and 587 meters
|**************************************|  

I think you are all set ! Enjoy.