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.

Answer 1

You could build a dynamic query, mapping the columns names of database with the names sent by form. After use a foreach to check if every element has or not a value if positive create a new element in $new. At last make the WHERE clause with implode() it will glue which element of array with AND key-word.

$fakePost = array('Shape' => '', 'Color' => '', 'Size' =>'2');


$fieldsName = ['Shape' => 'shape_list', 'Round' => 'round_list', 'Size' => 'size_list', 'Color' => 'color_list'];
$new = [];
foreach($fakePost as $k => $v){
    if(!empty($v)){
        $new[] = $fieldsName[$k] . " = '$v'";
    }   
}

if(!empty($new)){
    echo ' WHERE '. implode(' AND ', $new);
}else{
    echo 'please select some value';
}

Output:

WHERE size_list = '2'

or:

WHERE shape_list = 'green' AND size_list = '2'

Answer 2

Here would be my version:

• getItems.php: Fetch the posted values and call a function for fetching the items based on them (getItems()).
• itemsFunctions.php: All functions for handling items could be found in a separate file for reuse.

---

getItems.php

<?php

require_once 'itemsFunctions.php';

$shape = isset($_POST['shape']) ? trim($_POST['shape']) : '';
$color = isset($_POST['color']) ? strtolower(trim($_POST['color'])) : '';
$size = isset($_POST['size']) ? trim($_POST['size']) : 0;

// Get items.
$items = getItems($shape, $color, $size);

// Do something with the fetched items
// (like display them in a table/grid).

itemsFunctions.php

/**
 * Fetch items from items table.
 * 
 * @param string $shape [optional] Item shape.
 * @param string $color [optional] Item color.
 * @param integer $size [optional] Item size.
 * @return array Fetched items.
 */
function getItems($shape = '', $color = '', $size = 0) {
    // Array containing the WHERE conditions.
    $where = array();

    // Add WHERE conditions to array based on the given values.
    if (!empty($shape)) {
        $where[] = 'item_shape = ' . $shape;
    }

    if (!empty($color)) {
        $where[] = 'item_color = ' . $color;
    }

    if ($size > 0) {
        $where[] = 'item_size = ' . $size;
    }

    // Build the sql statement.
    $sql = sprintf('SELECT * FROM items %s'
            , count($where) > 0 ? ' WHERE ' . implode(' AND ', $where) : ''
    );

    // Run query for fetching items by using the sql statement...
    // Fetched items as array.
    $items = array(
        array(
            'item_shape' => 'round',
            'item_color' => 'red',
            'item_size' => '45',
        ),
        array(
            'item_shape' => 'square',
            'item_color' => 'blue',
            'item_size' => '1258',
        ),
    );

    return $items;
}

/**
 * Add item to items table.
 * 
 * @param string $shape [optional] Item shape.
 * @param string $color [optional] Item color.
 * @param integer $size [optional] Item size.
 * @return integer Last insert id.
 */
function addItem($shape = '', $color = '', $size = 0) {
    //...
}

/**
 * Update item in items table.
 * 
 * @param integer $id Item ID.
 * @param string $shape [optional] Item shape.
 * @param string $color [optional] Item color.
 * @param integer $size [optional] Item size.
 * @return bool TRUE if updated, FALSE otherwise.
 */
function updateItem($id, $shape = '', $color = '', $size = 0) {
    // Validate item ID...
    //...
}

/**
 * Remove item from items table.
 * 
 * @param integer $id Item ID.
 * @return bool TRUE if deleted, FALSE otherwise.
 */
function deleteItem($id) {
    // Validate item ID...
    //...
}

---

Notes:

• Size should be integer.
• I used the function sprintf(). It prints as echo() but it allows the use of placeholders (%s) as well. I use it a lot in building complex sql statements.

Recommendation: Use prepared statements in your PHP codes in order to avoid MySQL injection.

Good luck!

Answer 3

As i understand your problem below query will give the exact result what you want

    $shape = $_POST['shape'])??trim($_POST['shape']) ;
    $color = $_POST['color'])??trim($_POST['color']) ;
    $size  = $_POST['color'])??trim($_POST['color']) ;

    $where = array();

    if (!empty($shape)) {

        $shape_arr = explode(',', $shape);

        $temp_shape = '';

        foreach ($shape_arr as $key => $value) {
          $temp_shape .= "'".$value."',";
        }

        $temp_shape = rtrim($temp_shape,',');

        $where[] = " item_shape IN(".$temp_shape.") ";
    }

    if (!empty($color)) {

        $color_arr = explode(',', $color);

        $temp_color = '';

        foreach ($color_arr as $key => $value) {
          $temp_color .= "'".$value."',";
        }

        $temp_color = rtrim($temp_color,',');

        $where[] = " item_color IN(".$temp_color.") ";
    }


    if (!empty($size)) {
        $where[] = " item_size IN(".$size.") ";
    }

    $sql = "SELECT * FROM items ";

    if(count($where)>0){
      $sql .= " WHERE ". implode(" AND ", $where) ;
    }

Answer 4

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++;
    }
}