Using PHP while loop to retrieve specific data and post it on a google map

Question

Hi im relatively new to PHP and very new to google maps API but I would like to retrieve data from my mysql database and post it onto my google map, the problem is that I need to search database A for relevant data, pull an 'id' from each entry then search database B for all the ids pulled and finally use the data pulled from database B to place a marker on a google map (database B holds the 'id' to correspond with database A a latitude field and a longitude field). I hope all this makes sense after reading my code:

<?PHP
    $min = $_POST['min'];
    $result = mysqli_query($con,"SELECT * FROM A WHERE x>".$min);
    while($row = mysqli_fetch_array($result))
    {
        $host=$row['host_id'];
        $result1 = mysqli_query($con,"SELECT * FROM B WHERE host_id=".$host);
        while($row1 = mysqli_fetch_array($result1));
        {
            $lat2=$row1['latitude'];
            $lon2=$row1['longitude'];
            echo  'var myLatLng = new google.maps.LatLng('.$lat2.','.$lon2.');';
            echo  'var aMarker = new google.maps.Marker({';
            echo      'position: myLatLng,';
            echo      'map: map,';
            echo  '});';
        }
    } 
?>

As I said im pretty new to PHP so any help as to where im going wrong here would be greatly appreciated Thanks :)

Answer 1

You can connect to two different databases at the same time. With the mysqli_ extension you are using:

<?php

$db1 = mysqli_connect('localhost',$username1,$password1);
mysqli_select_db($db1, $database1) or die( "Unable to select database 1");

$db2 = mysqli_connect('localhost',$username2,$password2);
mysqli_select_db($db2, $database2) or die( "Unable to select database 2");

?>

Then you can choose either $db1 or $db2 as needed.

<?php
$min = (int) $_POST['min'];
$result = mysqli_query($db1,"SELECT * FROM A WHERE x>".$min);

while($row = mysqli_fetch_array($result))
{
    $host=$row['host_id'];
    $result1 = mysqli_query($db2,"SELECT * FROM B WHERE host_id=".$host);

    while($row1 = mysqli_fetch_array($result1));
    {
        //...
    }
}
?>

I have added an (int) to sanitise your $_POST['min'] variable - this is important to guard against SQL injection. For strings, you should investigate using mysqli_real_escape_string(), or even better, go for parameterised queries: How can I prevent SQL injection in PHP?

Update For tables in the same database, I would recommend reconfiguring your code to use a single SQL statement, and JOIN the tables together. Something like:

<?php

$sql = "
SELECT B.latitude, B.longitude
FROM B
INNER JOIN A ON (A.host_id = B.host_id)
WHERE A.x > {$min}
";

?>

This gets all records from B which have a host_id which matches any host_id in A with a value of x greater than $min. You will get duplicates if you have multiple records in A with the same host_id, so you could add a GROUP BY clause to reduce these, either on host_id, or on the primary key of table B, if this is not host_id:

...
WHERE A.x > {$min}
GROUP BY B.host_id

Also, it's probably worth checking the SQL for errors and displaying a warning:

<?php

$result1 = mysqli_query($con,$sql);
if($result1===FALSE)
{
    $sql_error = mysqli_error($con);
    $js_safe_sql_error = json_encode(utf8_encode($sql_error));
    echo "alert('Database Error: ' + " . $js_safe_sql_error . ");\n";
}
else
{
    while($row1 = mysqli_fetch_array($result1));
    {
        //...
    }
}

?>

I notice in your comment you say that the map completely disappears though. This would indicate an error in the generated javascript. Hopefully your browser's javascript console will display this error. When I run it I get: ReferenceError: map is not defined, but this is probably being defined elsewhere in your code?