Trying to make a complex search in PHP

Question

Scenario

I currently have a database hooked up to my PHP pages running from localhost, one of those PHP pages is a shop that's items are populated from the database.

I've allowed the user to search for particular items, for instance:

"List every item in the shop that is under < $5.00"

And I have achieved that. I currently have four simple searches to search for items in the shop, these searches are by: "Category","Name","Availability" and "Price"

So if the user entered a Category of "Gift" then all of the "Gift"s should show up, and that already works.

---

And although these single-Criteria searches give results? I'd like the user to be able to select from ALL four, or three or two criteria options at the same time.

My attempt at constructing the PHP looks a little like this, and this doesn't work.

    <?php 
    //check to make sure the form has been submitted and retrieve the contents of 
    //both things and make variables.
    if(isset($_POST['search'])){
    $get_price=$_POST['price'];
    $get_function=$_POST['Operation'];
    $get_name=$_POST['UserEnteredText'];

$get_Availability=$_POST['AvailabilityOperation'];
$get_Category=$_POST['CategoryOperation'];
//create the query to extract the data from the table and store
//the SQL query in a variable called $query
/*$query="SELECT * FROM friends WHERE name = '$get_name'";*/

if ($get_function == 'Higher')
{
$queryPrice="SELECT * FROM product WHERE price < $get_price";
}
else
{
$queryPrice="SELECT * FROM product WHERE price > $get_price";
}

if ($get_name = $get_name)
{
$queryName="SELECT * FROM `product` WHERE `Product Name` like '%$get_name%'";
}
else
{
//doesn't work  
}

if ($get_Availability == 'Now')
{
$queryAvailability="SELECT * FROM product WHERE availability LIKE '%Now%'";
}
elseif ($get_Availability == 'Out Of Stock')
{
$queryAvailability="SELECT * FROM product WHERE availability LIKE '%Out%'";
}
else
{
$queryAvailability="SELECT * FROM product WHERE availability";
}

if ($get_Category == 'Garden')
{
$query="SELECT * FROM product WHERE category LIKE '%Garden%'";
}
elseif ($get_Category == 'Gift')
{
$query="SELECT * FROM product WHERE category LIKE '%Gift%'";
}
elseif ($get_Category == 'Test')
{
$query="SELECT * FROM product WHERE category LIKE '%Test%'";
}

//the result set of the mysql_query function is then stored in
//an array called $result
$result=mysqli_query($connection, $queryPrice, $queryName, $queryAvailability, $get_Category);
//create while loop and loop through result set, reading each one 
//and displaying it through the use of an echo command

while ($row = mysqli_fetch_array($result)) {

    $itemname=$row['Product Name'];
    $itemPrice=$row['Price'];
     $itemavailability=$row['Availability'];
?>
    <div class="item">
        <h3><?php echo $itemname. " " ?></h3>
        <a href="<?php echo $row["Product Image Path"] ?>" data-lightbox="Perfecto">
            <img src="<?php echo $row["Product Image Path"] ?>" width="70" height="70" alt="Sculpture1"> </a>
        <br />
        Price: £<?php echo $itemPrice. " " ?>
        <form action="" method="post">
            <input type="submit" value="Buy" />
        </form>
    </div>


<?php
}

}
?>

Question

Wondering if any experienced PHP programmers could help me understand how to construct an algorithm that takes in all of these values and produces he same results as my individual searches do.

If you're wondering how I got to this conclusion I tried to combine all four of my other searches (That all work) into a more complex search.

Would really appreciate it, thanks.

Answer 1

Rather than using an IF/ELSE to return a specific query you could try building up your query programatically based on params passed in. eg.

$sql = "select * from `product` where `product name` like '%$get_name%'";
$sql .= (isset($get_price) ? " and `price` < $get_price" : "");
$sql .= (isset($get_Category) ? " and `category` LIKE '$GET_Category'" : "");

You will end up writing far less code that way and it will be far easier to add new criteria in as your search application grows.

Cheers Ash

Answer 2

The conditions in your SQL query need to be ANDed together to a form a single query with several conditions.

Generally, if you want all objects matching

SELECT fruits FROM market WHERE color='green'

and

SELECT fruits FROM market WHERE price<5,

the solution is

SELECT fruits FROM market WHERE color='green' AND price<5.