Insert And Update To Same Table On Same Page

[artsyL]

Hello again,

 

I'm wondering if it is possible to insert to a table and then update to that table in the same php script.

 

What I want to do is insert a location address, then geocode it with a location update immediately after the insert with the form action as the same page. However, I suspect that I will need to do this through a hidden submit input that takes me to another page because I have been scratching my head over it for a few hours now.

 

Here is the location insert

	    $q = 'INSERT INTO location (loc_address, loc_address2, loc_city, loc_state, loc_zip, loc_country, coll_id, country_id) VALUES
            (?, ?, ?, ?, ?, ?, ?, ?)';
            $stmt = mysqli_prepare($dbc, $q);
            mysqli_stmt_bind_param($stmt, 'ssssssii', $addr1, $addr2, $city, $state, $zip, $sn, $c, $coun);
            mysqli_stmt_execute($stmt);
            
            //Check the results
            if (mysqli_stmt_affected_rows($stmt)== 1){
                echo '<p>The location has been added</p>';
                $locid = mysqli_stmt_insert_id($stmt);//Get the location ID
                }else{//Error!
                    $errors[] = 'the location could not be added to the database.';
                }
                
            //Close this prepared statement
            mysqli_stmt_close($stmt);

and here is the geocoding with the update query

//geocode it
$geoloc="$addr1, $city, $state, $zip, $sn";
$georesult=file_get_contents("http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=" . urlencode($location) );
$geocodedinfo=json_decode($result);

print_r($geocodedinfo);

$lat = $geocodedinfo->results[0]->geometry->location->lat;
$lng = $geocodedinfo->results[0]->geometry->location->lng;


	$query = "UPDATE location SET loc_address='$addr1', loc_address2='$addr2', loc_city='$city', loc_state='$state',loc_zip='$zip', loc_country='$sn', lat='$lat',lng='$lng' WHERE loc_id='$locid'"; 
	$result = mysqli_query($query); 
	if ($result){
		echo "<h2><b><strong>OMG it worked!</strong></b></h2>"; 
		}else {
		echo "<p>No geocode (mope)" . mysqli_error() . "</p>"; 
		
	}

can I force the insert submit to go first and then run the update? if so, what do you suggest? I came across one article that said something about making each query into a function, but it wasn't explained very well. And do I need to create a separate pre-populated hidden form somehow for the second submit?

[Antonio Conte]

How about doing the calculations on the input before you insert to the DB? Use the same variables as from your insert statement in your geolocation code, and insert the both data and the calculations in one step.

 

Btw, I created a super simple class for handling Google maps geo data for my own site. Feel free to use it:

- https://gist.github.com/thomaslarsson/5938143

 

You basically use it like this:

include 'maps.php'; // Or what you want to call it

$address = "Somestreet 7, 1000 postal, Country"; // As you would use Google maps

// Use the class
$map = new SimpleGoogleMaps();
$map->find($address);

if( $map->resultExists() )
{
   echo $map->getAddress();
   echo $map->getLatitude();
   echo $map->getLongitude();
   // You can simply do the insert here...
}
else 
{
   echo "Address not found";
}

[HartleySan]

It looks like all of the info is going into the same table, so why not make the Google Maps API call first, and upon getting back the geocoded data, you then insert everything into the DB with one INSERT query?

[artsyL]

Thank you both! That worked really well. I was over-thinking it again.

 

Antonio, I used my code just because I know it works (though you wouldn't think so - I was in the middle of changing some variables when I posted), but I will definitely be taking yours for a whirl. Very nicely done.

 

Here is the final result:

//add the location to the database.
			//geocode it first
$geoloc="$addr1, $city, $state";
$result=file_get_contents("http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=" . urlencode($geoloc) );
$geocodedinfo=json_decode($result);

//print_r($geocodedinfo);

$lat = $geocodedinfo->results[0]->geometry->location->lat;
$lng = $geocodedinfo->results[0]->geometry->location->lng;

//var_dump($lat, $lng);
			
			$q = 'INSERT INTO location (loc_address, loc_address2, loc_city, loc_state, loc_zip, loc_country, lat, lng, coll_id, country_id) VALUES
			(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
			$stmt = mysqli_prepare($dbc, $q);
			mysqli_stmt_bind_param($stmt, 'ssssssddii', $addr1, $addr2, $city, $state, $zip, $sn, $lat, $lng, $c, $coun);
			mysqli_stmt_execute($stmt);
			
			//Check the results
			if (mysqli_stmt_affected_rows($stmt)== 1){
				echo '<p>The location has been added</p>';
				$locid = mysqli_stmt_insert_id($stmt);//Get the location ID
				}else{//Error!
					$errors[] = 'the location could not be added to the database.';
				}
				
			//Close this prepared statement
			mysqli_stmt_close($stmt);