查询mysql其中一个简单的显示特定记录

Question

I have a simple dB of real estate property listings. I am trying to do a very simple search to show records. Eg property location, type and price eventually. I am having trouble with the select statement especially if users submit blank fields. See code. Is there a better way to do this? I am OK on the bit that displays the records using a while loop. I am a newbie as I am sure you can tell.

<form action="search.php" method="post">

<table border="0" cellpadding="10" cellspacing="0">
<tr>
<td width="300"><b>Reference No</b></td>
<td><input type="text" name="Reference_No" maxlength="20" id="Reference_No" /></td>
</tr>

<tr>
<td><b>Property Name</b></td>
<td><input type="text" name="Property_Name" maxlength="30" id="Property_Name" /></td>
</tr>

<tr>
<td><b>Property Area</b></td>
<td>
<select name="Property_Area" id="Property_Area">
<option value="Ortaca">Ortaca</option>
<option value="Sarigerme">Sarigerme</option>
<option value="Other">Other</option>
</select>
</td>
</tr>

<tr>
<td><b>Property Type</b></td>
<td><select name="Property_Type" id="Property_Type">
<option value="Apartment">Apartment</option>
<option value="Land">Land</option>
<option value="Commercial Property">Commercial Property</option>
</select></td>
</tr>

<tr>
<td></td>
<td>
<input class="formsubmit" type="submit" value="Submit Enquiry" name="Submit" />
</td>
</tr>
</table>

</form>

$Reference_No = $_POST['Reference_No'];
$Property_Name = $_POST['Property_Name'];
$Property_Area = $_POST['Property_Area'];
$Property_Type = $_POST['Property_Type'];



$query = "SELECT * FROM properties WHERE (Live = 'yes' AND Property_Type = '$Property_Type') AND (Reference_No = '$Reference_No' OR Property_Name = '$Property_Name' OR Property_Area = '$Property_Area') ORDER BY Property_Area, Property_Price";

Answer 1

If i understand you correctly you want to get all properties if nothing is chosen, and limit selection by user choices .

On SQL Server you could use intersect in the following way:

$filters[] = "SELECT * FROM properties"
if ($Property_Type)
    $filters[] = "SELECT * FROM properties WHERE Property_Type = '$Property_Type'";
if ($Reference_No)
    $filters[] = "SELECT * FROM properties WHERE Reference_No = '$Reference_No'";
if ($Property_Name)
    $filters[] = "SELECT * FROM properties WHERE Property_Name = '$Property_Name'";
if ($Property_Area)
    $filters[] = "SELECT * FROM properties WHERE Property_Area = '$Property_Area'";

$final_query = implode(' INTERSECT ', $filters)

But MySQL does not support INTERSECT. In this case you could wrap filter over filter over filter ... etc

$sql = 'SELECT * FROM properties';
if ($Property_Type) 
    $sql = "SELECT * FROM ({$sql}) AS Q WHERE Property_Type = '$Property_Type'";
if ($Reference_No) 
    $sql = "SELECT * FROM ({$sql}) AS Q WHERE Reference_No = '$Reference_No'";
if ($Property_Name) 
    $sql = "SELECT * FROM ({$sql}) AS Q WHERE Property_Name = '$Property_Name'";
if ($Property_Area) 
    $sql = "SELECT * FROM ({$sql}) AS Q WHERE Property_Area = '$Property_Area'";

And yes, don't forget to either escape/sanitize all values prior inserting them into the query string or use parameterized query PDO or MySqli style.