problema con scrittura valori in database

[serino84]

Salve,

ho uno script che mi fa la conversione da indirizzi a coordinate e poi mi dovrebbe scrivere tali coordinate in

---------------------------------------------------------
id | address | city | state | zip | lat | lng|
---------------------------------------------------------
2 | via toledo | napoli | IT | 80121 | | |

ed ho il seguente codice che mi dovrebbe mettere i miei valori in lat e lng:

// save latitude and longitude to the database
function saveLatLng($latLngArray) {

include 'config.php';
connectToDatabase();

$geocodedToSave = explode(",",$latLngArray);

$latitudeToSave = $geocodedToSave[2];
$longitudeToSave = $geocodedToSave[3];
$indexToSaveTo = $geocodedToSave[0];
$addressToSaveTo = $geocodedToSave[1];

$result = mysql_query('UPDATE ' . $table .
' SET `' . $latitude . '`=' . $latitudeToSave . ', `' . $longitude . '`=' . $longitudeToSave .
' WHERE `' . $index . '`=' . $indexToSaveTo . ';');

return 'ROWID: ' . $indexToSaveTo .
', ADDRESS: ' . $addressToSaveTo .
', LATITUDE: ' . $latitudeToSave .
', LONGITUDE: ' . $longitudeToSave;

}


?>

il config.php è questo:

<?php


/************************************************** ********
* mysql-table-geocode config file
* THIS IS THE ONLY FILE YOU SHOULD EDIT
* EDITING OTHER FILES MAY LEAD TO DANGERRRRR
************************************************** ********/


/* update info to connect to database */
$host = 'localhost';
$username = 'root';
$password = 'password';
$database = 'prova';
$table = 'table';


/**
* enter the field names from $table
* which correspond to the table and address info
*/
$index = 'ID';
$street = 'address';
$city = 'city';
$state = 'state';
$zip = 'zip';


/**
* if you have one field for the full address, then
* uncomment the following line and replace the address
* with the name of that column in your table
*/
//$fullAddress = '123 Mockingbird Lane New York, NY 90210';


/** if you do not have latitude or longitude
* columns in your table, create them duh
*/
$latitude = 'lat';
$longitude = 'lng';


/**
* Enter your own Google Maps API here
* for more info, go here: https://developers.google.com/maps/
*/
$googleMapsAPI = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';


?>


Il mio problema è che non riesco a scrivere quei valori all'interno del database.

[satifal]

Stampa la query prima di eseguirla.

[serino84]

fatto, ho provato a inserire con query da mysql e mi fa l'inserimento... ma con lo script no..

[satifal]

Aggiungi mysql_error() e vedi che errore ti da:

$result = mysql_query('UPDATE '.$table.' SET `'.$latitude.'`='.$latitudeToSave.', `'.$longitude.             '`='.$longitudeToSave.' WHERE `'.$index.'`='.$indexToSaveTo.';') or die(mysql_error()); 

[serino84]

nessun errore

[satifal]

Se non ti fa l'update qualche errore deve esserci! Metti degli echo e vedi se arriva ad eseguire l'update inoltre metti mysql_error() anche quando stabilisci la connessione al DB.

[serino84]

Questo è:

function.php

<?php

/**
* HERE IS WHERE SOME OF THE PHP MAGIC HAPPENS. PAWS OFF, PALS.
**/


// check ajax call for geocoded data
if ( isset($GLOBALS['HTTP_RAW_POST_DATA'] ))
{
$result = saveLatLng($GLOBALS['HTTP_RAW_POST_DATA']);
print $result;
}


// get table of addresses
function connectToDatabase() {


include 'config.php';
$link = mysql_connect($host, $username, $password);
if (!$link) {
die('Looks like you didn\'t enter your database table credentials in /config.php properly!<p><em>' . mysql_error() . '</em></p>');
}
$locationDatabase = mysql_select_db($database, $link);
if (!$locationDatabase) {
die('Looks like you didn\'t enter your database table credentials in /config.php properly!<p><em>' . mysql_error() . '</em></p>');
}
}




// save latitude and longitude to the database
function saveLatLng($latLngArray) {

include 'config.php';
connectToDatabase();

$geocodedToSave = explode(",",$latLngArray);

$latitudeToSave = $geocodedToSave[2];
$longitudeToSave = $geocodedToSave[3];
$indexToSaveTo = $geocodedToSave[0];
$addressToSaveTo = $geocodedToSave[1];

/*$result = mysql_query("UPDATE PROVA.TABLE SET TABLE.lat='. $latitudeToSave .', table.lng='. $longitudeToSave .' where table.indx=' . $indexToSaveTo .'");
*/
/*$result = mysql_query('UPDATE ' . $table .
' SET `' . $latitude . '`=' . $latitudeToSave . ', `' . $longitude . '`=' . $longitudeToSave .
' WHERE `' . $index . '`=' . $indexToSaveTo . ';');
*/


$result = mysql_query('insert into '.$table.' SET `'.$latitude.'`='.$latitudeToSave.', `'.$longitude. '`='.$longitudeToSave.' WHERE `'.$index.'`='.$indexToSaveTo.';') or die(mysql_error());
return 'ROWID: ' . $indexToSaveTo .
', ADDRESS: ' . $addressToSaveTo .
', LATITUDE: ' . $latitudeToSave .
', LONGITUDE: ' . $longitudeToSave;

}


?>

[serino84]

questo è:
index.php



<!doctype html>
<html>
<head>
<title>GEOKERD! geocoding mysql tables of addresses with ease</title>
<style type="text/css">
html { background: #9cc; }
body { background: #fff; padding: 20px; font-family: Georgia, serif; color: #333; width: 600px; margin: 20px auto; box-shadow: 0 0 5px #000; }
h1, h2 { margin: 0; }
h2 { font-size: 1.2em; font-style: italic; letter-spacing: .1em; }
#alert { background: #cc9; padding: 10px; margin: 20px 0; font-weight: bold; font-style: italic; text-align: center; }
#alert ol { background: #eed; text-align: left; padding: 10px; }
#alert ol li { font-weight: normal; font-style: normal; font-size: .8em; list-style-position: inside; margin: 20px 0 0; }
#alert ol li:first-child { margin: 0;}
ol li { font-weight: bold; margin-top: 30px; }
form { text-align: center; }
#submit { background: #ccc; padding: 10px; margin: 30px; color: #333; font-size: 2em; }
#submit:hover { -webkit-box-shadow: 2px 2px 5px #666; -webkit-transition: all ease-out 200ms; }
#credit { font-style: italic; letter-spacing: .1em; text-align: center; }
a { color: #c66; text-decoration: none; }
</style>


<?php
// START ON-SUBMIT
if ( isset($_POST['source']) && $_POST['source'] == 'submit-table' ) {
include 'config.php';
include 'functions.php';
connectToDatabase();
?>


<script type="text/javascript" src="https://maps.googleapis.com/maps/api/js?key=<?php echo $googleMapsAPI; ?>&sensor=true"></script>
<script type="text/javascript">

function codeAddress(addressArray) {
var geocoder = new google.maps.Geocoder();
var geocodedAddress = new Array();
var addressID = addressArray['id'];
var address = addressArray['address'];

geocoder.geocode( { 'address': address }, function(results, status) {

if (status == google.maps.GeocoderStatus.OK) {
latitude = results[0].geometry.location.lat();
longitude = results[0].geometry.location.lng();
geocodedAddress = [addressID, address.replace(/,/g,''), latitude, longitude];

var ajax = new XMLHttpRequest();
ajax.open('POST','functions.php',true);
ajax.setRequestHeader('Content-Type','application/json');
ajax.onreadystatechange = function() {
if ( ajax.readyState == 4 && ajax.status == 200 ) {
var response = ajax.responseText;
var responseItem = document.createElement("li");
var responseText = document.createTextNode(response);
responseItem.appendChild(responseText);
document.getElementById('address-success').appendChild(responseItem);
}
}
ajax.send(geocodedAddress);
ajax.close;
success = true;
}
else {
console.log(status);
window.setTimeout(function(){ codeAddress(addressArray); }, 2000);
}
});
}

<?php

$addresses = mysql_query(sprintf("SELECT * FROM prova.table"))or die(mysql_error());
$count = mysql_num_rows($addresses);
$alert = "Something didn't work.";

if ( $count > 0 ) {
while ( $row = mysql_fetch_array($addresses) ) {
$addressID = $row[$index];

if ( $index ) {
$address = $row[$street] . ' ' . $row[$city] . ', ' . $row[$state] . ' ' . $row[$zip];
}
else {
$address = $row[$street] . ' ' . $row[$city] . ', ' . $row[$state] . ' ' . $row[$zip];
}
$addressArray = Array( 'id' => $addressID, 'address' => $address );

echo 'var addressToGeocode = ' . json_encode($addressArray) . ';' .
'var ajaxAddress = codeAddress( addressToGeocode );';
}
$alert = "The latitude and longitude values of the following addresses have been updated in your table.";
}
else {
$alert = 'There are no records in this table. Do you even blend, brah?';
}


} // END ON-SUBMIT
?>
</script>
</head>


<body>


<h1>GEOKERD!</h1>
<h2>geocoding mysql tables of addresses with ease</h2>

<?php
if ( isset($alert) ) {
echo '<div id="alert">' . $alert . '<ol id="address-success"></ol></div>';
}
?>

<ol id="faq">
<li>What is this?</li>
<p>This is a script you can use to generate latitude and longitude values for addresses you already have in a database, using the Google Maps API.
</p>

<li>What do I need?</li>
<p>You need a mysql database table of addresses, which has a field for latitude and a field for longitude. You need the credentials to modify this database table, and you also need a Google Maps API key. <em>Note: As this is powered by Google's API, you are limited to 2500 requests per day. <a href="https://developers.google.com/maps/documentation/geocoding/" target="_blank">See the Geocoder API docs here.</a></p>

<li>How do I make it work?</li>
<p><a href="https://github.com/jennschiffer/geokerd">Get the app from Github</a> and add the directory to a local or remote server. Update the config.php document with all the necessary info (database creds, field names, Google Maps API key). Then go to the app in your browser and click the button!</p>

<li>Ok then what?</li>
<p>Then you'll have a table of addresses *with* their latitudes and longitudes. You can use this table to do fun stuff with Google Maps or any other APIs that require geocoded data to work. Get going, press the button!</p>
</ol>

<form id="submit-table" method="post" action="">
<input type="hidden" name="source" value="submit-table" />
<input type="hidden" name="rowID" value="" />
<input type="submit" id="submit" value="CLICK THIS TO GEOCODE" />
</form>

<p id="credit">made with &hearts; by <a href="http://jennschiffer.com">jenn schiffer</a></p>


<a href="https://github.com/jennschiffer/geokerd"><img style="position: absolute; top: 0; left: 0; border: 0;" src="https://s3.amazonaws.com/github/ribbons/forkme_left_gray_6d6d6d.png" alt="Fork me on GitHub"></a>
</body>
</html>

[serino84]

e questo è
config.php


<?php


/************************************************** ********
* mysql-table-geocode config file
* THIS IS THE ONLY FILE YOU SHOULD EDIT
* EDITING OTHER FILES MAY LEAD TO DANGERRRRR
************************************************** ********/


/* update info to connect to database */
$host = 'localhost';
$username = 'root';
$password = 'xxxxx';
$database = 'prova';
$table = 'table';


/**
* enter the field names from $table
* which correspond to the table and address info
*/
$index = 'ID';
$street = 'address';
$city = 'city';
$state = 'state';
$zip = 'zip';


/**
* if you have one field for the full address, then
* uncomment the following line and replace the address
* with the name of that column in your table
*/
//$fullAddress = '123 Mockingbird Lane New York, NY 90210';


/** if you do not have latitude or longitude
* columns in your table, create them duh
*/
$latitude = 'lat';
$longitude = 'lng';


/**
* Enter your own Google Maps API here
* for more info, go here: https://developers.google.com/maps/
*/
$googleMapsAPI = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';


?>