function getCityId($mysqli, $city_name, $flag)
{
if (empty($city_name) == false) {
$query_string = "SELECT city_id FROM CITIES WHERE city_name = ?";
if (!($stmt = $mysqli->prepare($query_string))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$stmt->bind_param('s', $city_name);
// Execute the prepared query.
if (!$stmt->execute()) {
echo "Execute failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
//return false;
$city_id = null;
/* bind result variables */
if (!$stmt->bind_result($city_id)) {
echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
/* fetch values */
//while ($stmt->fetch()) {
if ($stmt->fetch()) {
/* close statement */
$stmt->close();
//printf("%s\nlatitude = %s (%s), longitude = %s (%s)\n",$city_name, $lat, gettype($lat), $long, gettype($long));
return $city_id;
} else {
if ($flag) {
return null;
}
// City does not exist in DB, insert it
if (insertCity($mysqli, $city_name)) {
echo "Inserted: " . $city_name;
getCityId($mysqli, $city_name, true);
}
}
/* close statement */
$stmt->close();
}
return null;
}
function read_excel($target_file)
{
global $locations;
global $duplicateLoc;
global $city;
global $cityId;
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($target_file);
$sheets = $data->_ole->sheets;
//get active sheets 0 - first sheet
$city = $sheets[0]['cells'][0][1];
//echo "City = " . $city;
if ($city == "-- Select --") {
die("Select an appropriate city and upload the excel again !!");
} else {
$cityId = getCityId($city);
if ($cityId == -1) {
unlink($target_file);
die("City \"" . $city . "\" not found in the database !");
}
}
$j = 0;
for ($i = 2; $i < $sheets[0]['numRows'] && sizeof($sheets[0]['cells'][$i]) != 0; $i++) {
if (!isset($sheets[0]['cells'][$i][0]) || $sheets[0]['cells'][$i][0] == "" || $sheets[0]['cells'][$i][0] == null) {
return;
}
$locations[$j] = array();
$locations[$j][0] = $sheets[0]['cells'][$i][0];
$locations[$j][1] = locationExist($cityId, $locations[$j][0]);
if ($locations[$j][1]) {
$duplicateLoc = true;
}
$j++;
}
}
