Jordyn Jordyn - 3 years ago 47
MySQL Question

MySQLl one or multiple parameters in where clause

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.

Answer Source

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++;
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download