我正在与我的Comp.Sci课程中的一个小组合作,使用PHP,mySQL和HTML进行这个网站项目,我们在搜索功能方面遇到了一些麻烦。
以下是完整的代码:
<html>
<head>
<title>US Cities Database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
<link rel="stylesheet" href="index.css" type="text/css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
</head>
<script>
function doWhenReady () {
$('a').click(showMap);
};
function showMap(event) {
var element = $(event.currentTarget);
var lat = element.attr('lat');
var long = element.attr('long');
var url = "https://maps.google.com/maps?q="+lat+","+long+"&h1=es&z=12&t=m&output=embed";
$('iframe').attr('src', url);
}
$(document).ready(doWhenReady);
</script>
<body class="container">
<h4>US Cities Zip Code and Latitude / Longitude Locator</h4>
<p><strong>Make sure to enter the smaller Zip Code in the first box, and the larger Zip Code in the second box.</strong></p>
<form action="index.php" method="POST" class="form-inline">
<div class="form-group">
<label for="city">City</label>
<input type="text" class="form-control" name="city" />
</div>
<div class="form-group">
<label for="state">State</label>
<input type="text" class="form-control" name="state" />
</div>
<div class="form-group">
<label for="zipFrom">Zipcode from:</label>
<input type="text" class="form-control" name="zipFrom" />
</div>
<div class="form-group">
<label for="zipTo">to:</label>
<input type="text" class="form-control" name="zipTo" />
</div>
<button type="submit" class="btn btn-default btn-primary">Search</button>
</form>
<hr>
<div class="row">
<div class="col-md-5 scrollable">
<div class="search-results">
<h4>Search results:</h4>
<?php
$host = 'localhost';
$username = 'caleyhalpern';
$password = '';
$dbName = 'project224';
$db = mysqli_connect ($host, $username, $password, $dbName);
if (!$db) {
die("Failed to connect to MySQL: (" . mysqli_connect_errno() . ") " . $mysqli->connect_error);
}
$searchCity = $_POST['city'];
$searchState = $_POST['state'];
$searchZipCode = $_POST['zipcode'];
if ((isset($searchCity) || isset($searchState)) && (($searchCity != '') || ($searchState != '')))
$query = 'SELECT * FROM zips WHERE city LIKE "%'.$searchCity.'%" AND state LIKE "%'.$searchState.'%"';
$results = mysqli_query($db, $query);
$resultArray = mysqli_fetch_all($results, MYSQLI_ASSOC);
?>
<table class="table table-striped table-condensed small">
<thead>
<tr>
<th>Show</th>
<th>Zip</th>
<th>State</th>
<th>City</th>
<th>Lat</th>
<th>Long</th>
</tr>
</thead>
<tbody>
<?php
foreach($resultArray as $city) {
echo "<tr>";
echo '<td><a href="#" lat="'.$city['lat'].'" long="'.$city['lng'].'">Show</a></td>';
echo "<td>".$city['zip']."</td>";
echo "<td>".$city['state']."</td>";
echo "<td>".$city['city']."</td>";
echo "<td>".$city['lat']."</td>";
echo "<td>".$city['lng']."</td>";
echo "</tr>";
}
?>
</tbody>
</table>
<?php
}
?>
</div>
</div>
<div class="col-md-7">
<div class="map">
<h4>Map:</h4>
<iframe src=""></iframe>
</div>
</div>
</div>
</body>
</html>
抱歉,它有点长,但是如果您向下滚动到PHP部分,您将看到" if((isset(...",这就是我们遇到麻烦的地方。 我们有城市和州的搜索功能,但我们在使用邮政编码搜索功能时遇到了问题。 我们试图让用户输入两个邮政编码,较小的邮政编码进入第一个搜索框,较大的邮政编码进入第二个搜索框。(例如:1st:82190,2nd:92120)。 它将搜索用户输入的这两个邮政编码之间的每个邮政编码。 我知道搜索功能将类似于城市/州的搜索功能 - 我们只是PHP和MySQL的新手,并且有点挣扎。
首先,您需要检查两个邮政编码字段是否为数字,如果不是,则设置默认值,例如00000
表示$zipFrom
,99999
表示$zipTo
。
$zipFrom = $_POST['zipFrom'];
if (!is_numeric($zipFrom))
$zipFrom = 0;
以类似方式处理$zipTo
的值。您还应该检查该$zipFrom <= $zipTo
,如果没有,则交换它们。 (总是,总是,总是防御性地编程!之后,查询本身很简单。以下是使用预准备语句执行此操作的方法:
$query = 'SELECT * FROM zips
WHERE zipcode >= ?
AND zipCode <= ?';
$stmt = $db->prepare($query);
$stmt->bind_param('ii', $zipFrom, $zipTo);
$stmt->execute();
$rslt = $stmt->get_result();
$resultArray = $rslt->fetch_all(MYSQLI_ASSOC);
对 bind_param
的调用将两个变量映射到查询中的两个问号占位符。第一个参数('ii'
)指定值的数据类型。
我将错误处理留给读者作为练习。
你可以尝试这样的事情...
$query = "SELECT * FROM zips WHERE city LIKE '%".$searchCity."%')";
$result = mysqli_query($db, $query);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo $row['zip'];
// echo any others you need
}
}