在问任何问题之前,我想说的是,我对php非常陌生,几乎没有任何经验。我制作了一个数据库表,其中包括关于在船上完成的工作的信息/细节。
我的代码:
<?php
error_reporting(0);
require 'db/connect.php';
include 'header.php';
require 'functions/safety.php';
$records = array();
$user = trim($_POST['user']);
$customer = trim($_POST['customer']);
$vessel = trim($_POST['vessel']);
$country = trim($_POST['country']);
$port = trim($_POST['port']);
$eta = trim($_POST['eta']);
$service_station = trim($_POST['service_station']);
$type_of_service = trim($_POST['type_of_service']);
$case_reference = trim($_POST['case_reference']);
$status = trim($_POST['status']);
if(isset($_POST['search'])) {
if($results = $db->query("SELECT * FROM pelates
WHERE user = '{$user}'
OR customer = '{$customer}'
OR vessel = '{$vessel}'
OR country = '{$country}'
OR port = '{$port}'
OR eta = '{$eta}'
OR service_station = '{$service_station}'
OR type_of_service = '{$type_of_service}'
OR case_reference = '{$case_reference}'
OR status = '{$status}'")) {
if($results->num_rows) {
while($row = $results->fetch_object()) {
$records[] = $row;
}
$results->free();
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Search jobs</title>
<link rel="stylesheet" type="text/css" href="syles.css">
<meta charset="utf-8" />
</head>
<body>
<table>
<thead>
<tr>
<th>User</th>
<th>Customer</th>
<th>Vessel</th>
<th>Country</th>
<th>Port</th>
<th>Eta</th>
<th>Service station</th>
<th>Type of service</th>
<th>Case reference</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php
foreach($records as $r) {
?>
<tr>
<td class="green"><?php echo escape($r->user); ?></td>
<td class="blue"><?php echo escape($r->customer); ?></td>
<td class="green"><?php echo escape($r->vessel); ?></td>
<td class="blue"><?php echo escape($r->country); ?></td>
<td class="green"><?php echo escape($r->port); ?></td>
<td class="blue"><?php echo escape($r->eta); ?></td>
<td class="green"><?php echo escape($r->service_station); ?></td>
<td class="blue"><?php echo escape($r->type_of_service); ?></td>
<td class="green"><?php echo escape($r->case_reference); ?></td>
<td class="blue"><?php echo escape($r->status); ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</br><a href="http://prinseapals-marine.com/filing/search.php" id="clear_button" class="button">Clear all</a>
<hr>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
<div class="field">
<label for="user">User</label>
<select name="user" id="user">
<option value="">Any</option>
<option value="VAK">VAK</option>
<option value="DTS">DTS</option>
<option value="SVAK">SVAK</option>
</select>
</div>
<div class="field">
<label for="customer">Customer</label>
<input type="text" name="customer" id="customer" autocomplete="off">
</div>
<div class="field">
<label for="vessel">Vessel</label>
<input type="text" name="vessel" id="vessel" autocomplete="off">
</div>
<div class="field">
<label for="country">Country</label>
<input type="text" name="country" id="country" autocomplete="off"></input>
</div>
<div class="field">
<label for="port">Port</label>
<input type="text" name="port" id="port" autocomplete="off">
</div>
<div class="field">
<label for="eta">ETA</label>
<input type="text" name="eta" id="eta" autocomplete="off">
</div>
<div class="field">
<label for="service_station">Service station</label>
<input type="text" name="service_station" id="service_station" autocomplete="off">
</div>
<div class="field">
<label for="type_of_service">Type of service</label>
<input type="text" name="type_of_service" id="type_of_service" autocomplete="off">
</div>
<div class="field">
<label for="case_reference">Case reference</label>
<input type="text" name="case_reference" id="case_reference" autocomplete="off">
</div>
<div class="field" id="radio">
<label>Pending</label>
<input type="radio" name="status" value="pending"/></br>
<label>Offer</label>
<input type="radio" name="status" value="offer"/></br>
<label>Order</label>
<input type="radio" name="status" value="order"/></br>
<label>Lost</label>
<input type="radio" name="status" value="lost"/></br>
<label>Postponed</label>
<input type="radio" name="status" value="postponed"/></br>
<label>Declined</label>
<input type="radio" name="status" value="declined"/>
</div>
<input type="submit" value="Search database" class="button" name="search">
</form>
</body>
</html>
我的问题是,因为在$db->query("SELECT...")
中,当我尝试搜索user=Steven和status=Pending时,我在变量之间使用了OR,例如,它会得到所有将Steven作为用户或Pending作为状态的结果,而不是将Steven and Pending作为用户和状态的结果。
所以我试着在变量之间使用AND,然后我遇到了另一个问题。假设我再次搜索user=Steven和status=Pending。问题是,因为我只填写了10个可用搜索输入中的2个,并且因为在我的数据库中的任何字段中都没有NOTHING值,所以我没有得到结果,因为该程序正在搜索一份以Steven为用户的工作,以Pending为状态,以国家为国家,以船只为船只,然后继续…
我知道我的问题可能是愚蠢的或太容易回答,但请原谅我,因为正如我在一开始提到的,我真的是php的新手,就像4天新手一样。如果有人能帮我对付这些家伙,我将不胜感激。
提前谢谢。
我建议您有条件地从$_POST
数据中填充变量,这样,如果每个变量为空,则会将其设置为false
。然后,从SQL查询中排除假值。
这样,您的查询将只包括表单中已填充的值。留空的输入不会影响查询。
类似这样的东西:
// build array of field names
$fields=array('user','customer','vessel','country',
'port','eta','service_station','type_of_service',
'case_reference','status');
// initialize empty array for WHERE clauses
$wheres=array();
// loop through field names, get POSTed values,
// and build array of WHERE clauses, excluding false values
foreach ($fields as $field) {
// get existing field value from POST, mark missing or empty value as FALSE
${$field} = isset($_POST[$field]) && trim($_POST[$field])!=''
? trim($_POST[$field]) : false;
// add to array of WHERE clauses only if value is not FALSE
if (${$field}) { $wheres[]="$field='".${$field}."'"; }
}
// build SELECT statement from WHERE clauses
$sql="SELECT * FROM pelates WHERE ".
(!empty($wheres) ? implode(" AND ",$wheres) : '1=1').
";";
// preview your query on screen
echo "<p>$sql</p>";