根据用户输入编写包含变量WHERE的查询 [英] Writing a query that contains variable WHERE based on user input
问题描述
我在查询时遇到问题.我想要做的是检查每个变量是否存在,如果不存在,请忽略它们.我也想在表中显示结果.任何帮助将不胜感激!
I'm having trouble with a query. What I would like it to do is check that each variable exists, and ignore them if they don't. I also want to display the results in a table. Any help would be greatly appreciated!
到目前为止,我所拥有的:这是我的代码.目前,它从数据库返回一个包含所有结果的数组,但是如果我将WHERE子句中的OR更改为AND,则需要填写所有字段.我希望用户能够输入他们所知道的尽可能多的信息,以便显示所有可能的结果.
What I have so far: This is my code. At the moment, it returns an array with all results from the database, but if I change the ORs in the WHERE clause to AND, all fields need to be filled. I want the user to be able to input as much information as they know, in order to display all possible results.
<?php require("common.php");?>
<?php
if(!empty($_POST))
{
$sth = $db->prepare("SELECT * FROM customer WHERE First_name LIKE '%$First_name%' OR Surname LIKE '%$Surname%' OR DOB LIKE '$DOB' OR Street LIKE '%$Street%' OR Suburb LIKE '$Suburb' OR State LIKE '$State' OR Postcode LIKE '$Postcode' OR Phone LIKE '$Phone'");
$sth->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
$First_name = $_POST['First_name'];
$Surname = $_POST['Surname'];
$DOB = $_POST['DOB'];
$Street = $_POST['Street'];
$Suburb = $_POST['Suburb'];
$State = $_POST['State'];
$Postcode = $_POST['Postcode'];
$Phone = $_POST['Phone'];
}
?>
<fieldset><legend>Find a customer</legend>
<form name="querycustomerform" method="post" action="qcustomer.php">
<table class="five">
<tr>
<td colspan="4">
<h3>Please fill out as many details as possible</h3>
</td>
</tr>
<tr>
<td>
<input type="text" name="First_name" maxlength="30" size="30" placeholder="First name">
</td>
<td>
<input type="text" name="Surname" maxlength="30" size="30" placeholder="Surname">
</td>
<td style="text-align: right">Date of Birth:</td>
<td>
<input type="date" name="DOB">
</td>
</tr>
<tr>
<td>
<input type="text" name="Street" maxlength="40" size="30" placeholder="Street Address">
</td>
<td>
<input type="text" name="Suburb" maxlength="15" size="30" placeholder="Suburb">
</td>
<td>
<select name="State">
<option value="">State</option>
<option value="ACT">Australian Capital Territory</option>
<option value="NSW">New South Wales</option>
<option value="NT">Northern Territory</option>
<option value="QLD">Queensland</option>
<option value="SA">South Australia</option>
<option value="TAS">Tasmania</option>
<option value="VIC">Victoria</option>
<option value="WA">Western Australia</option>
</select>
</td>
<td>
<input type="text" name="Postcode" maxlength="4" size="30" placeholder="Postcode">
</td>
</tr>
<tr>
<td>
<input type="text" name="Phone" maxlength="15" size="30" placeholder="Phone Number">
</td>
<td>
</td>
<td>
</td>
<td>
<input class="button" type="submit" value="Search">
</td>
</tr>
</table>
</form>
</fieldset>
推荐答案
OR 条件要求条件中的任何个条件(即:condition1,condition2,condition_n)必须满足条件才能将记录包含在结果集中. AND 条件要求必须满足以下条件的所有(即:condition1,condition2,condition_n)遇见.根据您的要求,必须满足 OR 条件.
The OR condition requires that any of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.Whereas the AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) be must be met. For your requirement the OR condition is required.
您需要构建一个动态查询来执行此操作.从基本存根开始
You need to build a dynamic query to perform this. Start with a basic stub
$sql = "SELECT * FROM customer";
然后,您需要将初始子句设置为WHERE.
Then you need to set the initial clause to WHERE.
$clause = " WHERE ";//Initial clause
您需要一个数组来存储参数
You need an array to store parameters
$paramArray =array();
开始构建查询.注意,我已从POST更改为GET,因为它更易于测试 另请参阅 PDO WIKI ,以在占位符中使用%.即占位符不能代表占位符的任意部分.查询,但仅是完整的数据文字.
Start building the query.Note I have changed from POST to GET as it is easier to test Also see PDO WIKI for use % in placeholders.ie placeholders cannot represent an arbitrary part of the query, but a complete data literal only.
if(isset($_GET['First_name'])){
$First_name = $_GET['First_name'];
$sql .= "$clause First_name LIKE ?";
$clause = " OR ";//Change clause
array_push($paramArray,"%$First_name%");
}
继续下一个子句
if(isset($_GET['Surname'])){
$Surname = $_GET['Surname'];
$sql .= "$clause Surname LIKE ?";
$clause = " OR ";
array_push($paramArray,"%$Surname%");
}
添加上述子句的其余部分
Add remainder of clauses as above
测试结果,测试后删除&将GET更改为POST
Test result, Remove after testing & change GET to POST
echo $sql ;
echo "<br>";
print_r($paramArray);
准备并执行查询
$sth = $db->prepare($sql);
$sth->execute($paramArray);
test.php?First_name=dave&Surname=smith
SELECT * FROM customer WHERE First_name LIKE ? OR Surname LIKE ?
Array ( [0] => %dave% [1] => %smith% )
来自test.php?Surname=smith
SELECT * FROM customer WHERE Surname LIKE ?
Array ( [0] => %smith% )
这篇关于根据用户输入编写包含变量WHERE的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!