MySQLl one or multiple parameters in where clause

Question

I have a form that user can enter following

Shape = Round, Triangle, Square, Rectangle, Rhombus, Kite, Decagon

Color = Red, Black, Green, Yellow, Blue, White

Size = 1, 2, 3 … 11

And in the databases, there are multiple items with above characteristics
Example red color round with size 5 and another round with black color size 1 so on.

User can select just one or all 3 characteristics and submit the form and I want to show founded results.
Example: if user selected only color results should display all items from selected color no matter the shape or size. And if user select shape and color all items that have selected shape and color.
My question is how can create a query to do this?

Code that I try

if (!empty($_POST["shape"])):

    $shape = trim($_POST["shape"]);

else:  

    $shape = "";

endif;



if (!empty($_POST["color"])):

    $color = strtolower(trim($_POST["color"]));

else:  

    $color = "";

endif;

if (!empty($_POST["size"])):

    $size = trim($_POST["size"]);

else:  

    $size = "";

endif;



SQL = SELECT * FROM items WHERE item_shape = $shape &&  item_color = $color && item_size = $size

Results always 0 unless I use only one WHERE clause it works like only shape and remove others from the command.

Also, I try changing like this

if (!empty($_POST["shape"])):

    $shape = trim($_POST["shape"]);

else:  

    $shape = " Round, Triangle, Square, Rectangle, Rhombus, Kite, Decagon";

endif;



// changed all post parameters sane wat u did with shape



SQL = SELECT * FROM items WHERE item_shape in ($shape) &&  item_color in ($color) && item_size = ($size)

How can i achieve this? Appreciate your time.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You can try something like this:

// add as many features as you like to filter in this array
$features = array();

if (!empty($_POST["shape"])):
    $features['shape'] = trim($_POST["shape"]);
endif;

if (!empty($_POST["color"])):
    $features['color'] = strtolower(trim($_POST["color"]));
endif;

if (!empty($_POST["size"])):
    $features['size'] = trim($_POST["size"]);
endif;

$sql = "SELECT * FROM items";

// if there is any feature in the array
if ( ! is_null($features))
{
    $i = 0;
    $len = count($features);

    // check each one of the features 
    foreach ($features as $feature => $feature_value) {
        if ($i == 0)
        {
            // if the first item, use WHERE
            $sql .= ' WHERE ';
        } else
        {
            // else, use &&
            $sql .= ' && ';
        }

        $sql .= 'item_' . $feature . ' = ' . $feature_item;
        $i++;
    }
}

MySQLl one or multiple parameters in where clause

Sign up