Select and insert in one query SQL

Question

I have a web page where a user clicks on a map, the latitute and longitude are passed to text fields, and along with some other details the data is submitted to the database. I have a formula for calculating the nearest town, and I have a little sample table of towns with their latitudes and logitudes.

At the moment I can display the nearest town and distance, but I can't get it to send to the database. I think I need to put the two queries together but I don't know how. Please be kind, I know my code is terrible and I have lots of security issues which I'm going to try to address later. Thank you!

<?php

 $conn    = Connect();

/**
 * Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon
 * Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius.
 */

$origLat = $conn->real_escape_string($_POST['lat']);
$origLon = $conn->real_escape_string($_POST['lng']);

// This is the maximum distance (in miles) away from $origLat, $origLon in which to search
$dist = 50; 

$query = "SELECT Townland, lat, lng, 3956 * 2 * 
          ASIN(SQRT( POWER(SIN(($origLat - lat)*pi()/180/2),2)
          +COS($origLat*pi()/180 )*COS(lat*pi()/180)
          *POWER(SIN(($origLon-lng)*pi()/180/2),2))) 
          as distance FROM townland WHERE 
          lng between ($origLon-$dist/cos(radians($origLat))*69) 
          and ($origLon+$dist/cos(radians($origLat))*69) 
          and lat between ($origLat-($dist/69)) 
          and ($origLat+($dist/69)) 
          having distance < $dist ORDER BY distance limit 1"; 

$result = mysqli_query($conn, $query) or die(mysql_error());
while($row = mysqli_fetch_assoc($result)) {
    echo $row['Townland']." > ".$row['distance']."<BR>";
}


$User_ID = $conn->real_escape_string($_POST['User_ID']);
$Species_Name = $conn->real_escape_string($_POST['Species_Name']);
$No_Of_Birds = $conn->real_escape_string($_POST['No_Of_Birds']);
$newdate = $conn->real_escape_string($_POST['Sighting_Date']);
//Converts date to 'yyyy-mm-dd' acceptable to mysql
$newdate=date('Y-m-d',strtotime($newdate));    
$Sighting_Details = $conn->real_escape_string($_POST['Sighting_Details']);
$lat = $conn->real_escape_string($_POST['lat']);
$lng = $conn->real_escape_string($_POST['lng']);
$townland = $row['Townland'];

$query = "INSERT into sighting 
                (User_ID, Species_Name, No_Of_Birds, 
                 Sighting_Date, Sighting_Details, 
                 lat, lng, Townland) 
           VALUES('" . $User_ID . "','" . $Species_Name . "','" . $No_Of_Birds . "','"
             . $newdate . "','" . $Sighting_Details . "','" 
             . $lat . "','" . $lng . "','" . $townland . "')";

$success = $conn->query($query);

if (!$success) {
    die("Couldn't enter data: ".$conn->error);
} 

header("Location: ../View/thankYouSubmitSighting.php");

$conn->close();
exit();

while($row = mysqli_fetch_assoc($result)) {
    echo $row['Townland']." > ".$row['distance']."<BR>";
}
mysqli_close($conn); 

?>

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Nov 30 '16 at 21:23
Sorry, yes the error is 'Cannot add or update a child row: a foreign key constraint fails ' on 'Townland', but is it because it's not pulling townland into the second query? The way I have the queries set up, they aren't just going to run one after the other like that are they? I just don't know how to put them together into one. Do the distance calculation, then use the townland discovered from that to fill the database using the second query. — champion_bake, Nov 30 '16 at 21:29
SIMPLFIED CONCATENATION: `VALUES('$User_ID','$Species_Name','$No_Of_Birds','$newdate','$Sighting_Details','$lat','$lng','$townland')";` _You dont need all that start/stop open/close double quotes as `$vars` are expaned in a double quoted string — RiggsFolly, Nov 30 '16 at 21:31
Your first query has a `LIMIT 1` so you dont need the while loop because there is only ever going to be one row returned — RiggsFolly, Nov 30 '16 at 21:33
Q: Does your echo of `$row['Townland']` actually show the town — RiggsFolly, Nov 30 '16 at 21:35
Yeah the echo does show the town. Thank you for your help so far! — champion_bake, Nov 30 '16 at 21:37

score 0 · Answer 1 · answered Nov 30 '16 at 21:20

If you need insert the result from query in a table eg: my_table could be you need an insert select query ( a single sql command composed by two part )

 "INSERT  into my_table (Townland, lat, lng, distance)
 SELECT Townland, lat, lng, 3956 * 2 * 
        ASIN(SQRT( POWER(SIN(($origLat - lat)*pi()/180/2),2)
        +COS($origLat*pi()/180 )*COS(lat*pi()/180)
        *POWER(SIN(($origLon-lng)*pi()/180/2),2))) 
        as distance FROM townland WHERE 
        lng between ($origLon-$dist/cos(radians($origLat))*69) 
        and ($origLon+$dist/cos(radians($origLat))*69) 
        and lat between ($origLat-($dist/69)) 
        and ($origLat+($dist/69)) 
        having distance < $dist ORDER BY distance limit 1";

Select and insert in one query SQL

1 Answers1