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++; } }