尝试使用查询从数据库中过滤数据。 为什么这不起作用？

Question

I am trying to filter vehicles from my database. I want customers to be able to filter through vehicles by selecting a price range field and a category of the car.... the categories are in a table called category in in my database and the vehicles are being called from my vehicle table in my database. Below is my code. Any ideas are greatly appreciated.

<div class="main">

<form method="post" action='<?php echo $_SERVER['PHP_SELF']; ?>' >
<select name="price">
<option value="= price">All</option>
<option value="< 5000">$0 - $5,000</option>
<option value="> 5000 AND price < 10000">$5,000 - $10,000</option>
<option value="> 10000 AND price < 20000">$10,000 - $20,000</option>
<option value="> 20000 AND price < 50000">$20,000 - $50,000</option>
<option value="> 50000">$50,000+</option>
</select>
<?php
    // Read the values in from the department table to populate the dropdown
$query = "SELECT * FROM category";
$results = mysqli_query($conn, $query );
if(!$results) {
echo ("Query error: " . mysqli_error($conn));
}
?>

<select name="category">
<option value="All">All</option>
<?php while ($row = mysqli_fetch_array($results)) { ?>
<option value="<?php echo $row['cat_id'];?>">
<?php echo $row['category_description'];?> </option>
<?php } ?>
</select>


<?php
//set initial value for sort variable
$price = "= price"; // this will set the default value for the price filter to all
$category = "%"; // this will set the default value of the category filter return all values
    
if(isset($_POST['price'])) {
$price = $_POST['price'];
}
if(isset($_POST['category'])) {
$category = $_POST['category'];
}
?>
<input type="submit" name="update" value= "Display results">
    </form>
<?php
// Determine which button was pressed and call the appropriate file
if (isset($_POST['price'])) {
//set query variable
$query = "SELECT * FROM vehicles WHERE price = price AND category_id LIKE %";
}
elseif (isset($_POST['category'])) {
$query = "SELECT * FROM vehicles WHERE price $price AND category_id LIKE '$category'";
}
    
$query = "SELECT * FROM vehicle WHERE standardusedvehicle ='yes'";
$results = mysqli_query($conn, $query );
if(!$results) {
echo ("Query error: " . mysqli_error($conn));
}

else {
// Fetch and display the results of the select query
while ($row = mysqli_fetch_array($results)) {
echo "<p>VIN_#: $row[vin]</p> ";    
echo "<p>Stock Number: $row[stockno]</p> ";
echo "<p>Manufacturer Number: $row[man_num]</p>";
echo "<p>Model: $row[model]</p>";
echo "<p>Colour: $row[col_id]</p>";
echo "<p>Year: $row[year]</p>";
echo "<p>Price: $row[price]</p>";
echo "<p>Kilometres: $row[kms] </p>";
echo "<p>Registration: $row[rego] </p>";
echo "<p>Cylinders: $row[cylinders] </p>";
echo "<p>Fuel: $row[fuel] </p>";
echo "<p>Transmission: $row[transmission] </p>";
echo "<p>Category Id: $row[cat_id] </p>";
echo "<p>Vehicle on Special (yes/no): $row[special] </p>";
echo "<p>Standard Used Vehicle: $row[standardusedvehicle] </p>";
echo "<img src='{$row['vehicle_image']}' />";
}
}
?>

</div>

Answer 1

In this code you are using two tables "vehicle" and "vehicles". Please use that which one is correct.

please use this code after form tag end instead of this :

if (isset($_POST['price'])) {
//set query variable
$query = "SELECT * FROM vehicles WHERE price = price AND category_id LIKE %";
}
elseif (isset($_POST['category'])) {
$query = "SELECT * FROM vehicles WHERE price $price AND category_id LIKE '$category'";
}

$query = "SELECT * FROM vehicle WHERE standardusedvehicle ='yes'";

Please change the above code as following code:

if (isset($_POST['price'])) {
if($_POST['category'] != 'All'){
    $query = "SELECT * FROM vehicles WHERE price $price AND category_id = $category";
}else {
    $query = "SELECT * FROM vehicles WHERE price $price";
   }
}

I don't know the following line you want or not.

$query = "SELECT * FROM vehicle WHERE standardusedvehicle ='yes'";

if you want this above line then use following code instead of above please:

 if($_POST['category'] != 'All'){
        $query = "SELECT * FROM vehicles WHERE price $price AND category_id = $category AND standardusedvehicle ='yes' ";
    }else {
        $query = "SELECT * FROM vehicles WHERE price $price AND standardusedvehicle ='yes'";
       }
    }