MySQL Returning rows based on additional parameters PDO

Question

I am trying to write a products page based on criteria from the user. The following fields are passed by default:

• qString (to match product name / description)
• maxPrice / minPrice (to do a between of prices in the table)
• minDate / maxDate (to search for products between dates availability)

The above gives me returned values.

The next couple of fields are optional and added in via jQuery based on the category:

• Category: category_id which matches back to the deals.deals_category_id (this still works)

Based on the Category, there may be additional attributes (like mobile deals has service provider, manufacturer) which will dynamically become visible within the front end. From here on the client can via checkboxes select the manufacturs they are interested in and service provider.

My Tables contain:

• Deals (Contains the products with a deal_category_id)
• Categories (All deals are linked to a category)
• Category_attributes ( Additional attributes per category. If a product is loaded within the category they will also be provided with additional attribute selectors
• Deal_attribute_options (Contains relationship linked options based on the attribute (where the attribute could be service providers and the attribute options may have AT&T, t-Mobile etc).
• Deal_attribute_values (Contains the values of the selected attribute options when the product is created. Say I created a new mobile phone and select a category mobile phones, the product will be required to give values for the attribute_options. They will receive a list of say mobile networks or manufacturers and link the product manufacturer to that specific manufactuer.

So by now, I have in the database a couple of products which each is linked to a category and based on the attributes linked to that category, each product would have selected a value for each attribute option. If there was 2 attributes (Manufacturer, Mobile Network), each product would have a value in deal_attribute_values linking the deal_id, attribute_id and then the selected value (so the products could be Samsung manufacturer and Vodafone as Service Provider).

What I am now trying to do is based on a search query, return the associated rows based on the front end filtering. Once the form on the frontend changes, it will serialize the values of the form and via ajax return the correct products based on those queries.

I am getting it to work perfectly except for the attribute_values. I suspect it has something to do with my IN() clause in the query, but hopefully someone can assist me here with what I'm doing wrong.

Here is the model method:

 public function getDeals() {

    $select = array();
    $tables = array();
    $where = array();
    $values = array();

    // Default to get all deal rows:
    $select[] = "`deals`.*";
    $tables[] = "`deals`";

    // Get the category Name AND ID
    $select[] = "`deal_categories`.`name` AS `catName`";
    $tables[] = "`deal_categories`";
    $where[] = "`deals`.`deal_category_id`=`deal_categories`.`id`";

    // Look if a category was selected:
    $cat = $_POST['category'];
    if ($cat != "") {
        $where[] = "`deals`.`deal_category_id`=?";
        $values[] = $cat;
    }

    // Assign a query of the deal by string:
    if ($_POST['searchDeals'] != "") {
        $where[] = "CONCAT_WS(' ',`deals`.`name`,`deals`.`description`) LIKE ?";
        $values[] = "%" . str_replace(" ", "%", htmlspecialchars($_POST['searchDeals'])) . "%";
    }

    // Process Min / Max PRicing:
    if (isset($_POST['minPrice'])) {
        $minPrice = intval($_POST['minPrice']);
        $where[] = "`deals`.`price` >= ?";
        $values[] = $minPrice;
    }

    if (isset($_POST['maxPrice'])) {
        $maxPrice = intval($_POST['maxPrice']);
        $where[] = "`deals`.`price` <= ?";
        $values[] = $maxPrice;
    }

    // PRocess the min/max dates:
    if (isset($_POST['minDate'])) {
        $minDate = $_POST['minDate'];
        $where[] = "`deals`.`start_date` >= ?";
        $values[] = $minDate;
    }

    if (isset($_POST['maxDate'])) {
        $maxDate = $_POST['maxDate'];
        $where[] = "`deals`.`end_date` <= ?";
        $values[] = $maxDate;
    }

    if (isset($_POST['attr']) && valid_array($_POST['attr'])) {
        $tables[] = "`deal_attribute_values`";
        foreach ($_POST['attr'] AS $attrID => $checked) {
            $values[] = $attrID;
            $where_condition = "`deals`.`id`=`deal_attribute_values`.`deal_id` 
        AND `deal_attribute_values`.`deal_attribute_id`=? 
        AND `deal_attribute_values`.`value` IN (";
            $bind_placeholder = array();
            foreach ($checked as $val) {
                $bind_placeholder[] = "?";
                $values[] = $val;
            }
            $where_condition .= implode(',', $bind_placeholder) . ")";
            $where[] = $where_condition;
        }
    }

    $sql = "SELECT " . implode(", ", $select) . " FROM " . implode(",", $tables) . " WHERE " . implode(" AND ", $where);

    return $this->get($sql, $values);
}

Here is a sample POST including the attributes (note that the key 3 and 4) represents the attribute collection (manufacturer and service provider). The values wihin them is the checked options through which we want the IN() to operate.

Array
 (
[searchDeals] => 
[category] => 2
[attr] => Array
    (
        [3] => Array
            (
                [0] => 8
                [1] => 9
                [2] => 12
                [3] => 10
            )

        [4] => Array
            (
                [0] => 4
                [1] => 7
                [2] => 5
            )

    )

[minPrice] => 100000
[maxPrice] => 9500
[minDate] => 2014-10-26 00:00:00
[maxDate] => 2014-11-30 00:00:00
 )

Here is outputted SQL based on the above (updated based on the code update):

SELECT `deals`.*, `deal_categories`.`name` AS `catName` FROM `deals`,`deal_categories`,`deal_attribute_values` WHERE `deals`.`deal_category_id`=`deal_categories`.`id` AND `deals`.`deal_category_id`=? AND `deals`.`price` >= ? AND `deals`.`price` <= ? AND `deals`.`start_date` >= ? AND `deals`.`end_date` <= ? AND `deals`.`id`=`deal_attribute_values`.`deal_id` AND `deal_attribute_values`.`deal_attribute_id`=? AND `deal_attribute_values`.`value` IN (?,?,?,?) AND `deals`.`id`=`deal_attribute_values`.`deal_id` AND `deal_attribute_values`.`deal_attribute_id`=? AND `deal_attribute_values`.`value` IN (?,?,?)

And here is the values for PDO:

 Array
(
[0] => 2
[1] => 100000
[2] => 9500
[3] => 2014-10-26 00:00:00
[4] => 2014-11-30 00:00:00
[5] => 3
[6] => 8
[7] => 9
[8] => 12
[9] => 10
[10] => 4
[11] => 4
[12] => 7
[13] => 5
 )

I can confirm that i'm getting results until the attributes are introduced. I am still able to get a result if I untick all atttributes (for both manufactuer and service provider) and only select 1 attribute say for example "Apple" which then returns a single product, but if I select the associated Service Provider there is no results.

Essentially with the attributes, I want to select a row if the value of that product within the attribute_values table is within the attributes in the IN() clause. As there will be multiple attributes based on a category and multiple products should be returned if I keep Apple, Samsung and Vodacom / MTN as SP. If the product is a samsung and either Vodacom / MTN it should return or if no manufucturer is selected and we only select Vodacom / MTN as the SP, it should return all handsets who is either MTN/Vodacom. Even if there is no Vodacom, it should at least return all the MTN ones.

Please let me know if you need any additional assistance / info. I tried to have as much detail here so hope everyone understands :)

** ANSWER UPDATE **

I have made 2 changes to the code posted by Alex which works on my end! Thank you Alex :)

 if (isset($_POST['attr']) && valid_array($_POST['attr'])) {
        foreach ($_POST['attr'] AS $attrID => $checked) {
            $current_table = "`deal_attribute_values` AS `dav" . $attrID."`";
            $asName = "`dav".$attrID."`";
            $values[] = $attrID;
            $where_condition = "`deals`.`id`=".$asName.".`deal_id` 
        AND " . $asName . ".`deal_attribute_id`=? 
        AND " . $asName . ".`value` IN (";
            $bind_placeholder = array();
            foreach ($checked as $val) {
                $bind_placeholder[] = "?";
                $values[] = $val;
            }
            $where_condition .= implode(',', $bind_placeholder) . ")";
            $where[] = $where_condition;
            $tables[] = $current_table;
        }
    }

Answer 1

I believe you're right about the fact that the problem is in the IN clause. There is an issue with prepared statements and providing values for the IN clause. You can't pass the entire IN clause condition as one parameter as it will automatically escape it as a whole not as individual values.

So you have:

if (isset($_POST['attr']) && valid_array($_POST['attr'])) {
    $tables[] = "`deal_attribute_values`";
    foreach ($_POST['attr'] AS $attrID => $checked) {
        $str = implode(", ", $checked);
        $where[] = "`deals`.`id`=`deal_attribute_values`.`deal_id` 
        AND `deal_attribute_values`.`deal_attribute_id`=? 
        AND `deal_attribute_values`.`value` IN (?)";
        $values[] = $attrID;
        $values[] = $str;
    }
}

When you bind the $str parameter to the statement it will encapsulate the entire $str in quotes thus IN having only one value instead of the values separated by comma.

You have 2 options:

1. You either append the $str to the query without binding it
2. You add enough ? to the IN clause for each of the IN values you desire like this:

    foreach ($_POST['attr'] AS $attrID => $checked) {
            $values[] = $attrID;
            $where_condition = "`deals`.`id`=`deal_attribute_values`.`deal_id` 
            AND `deal_attribute_values`.`deal_attribute_id`=? 
            AND `deal_attribute_values`.`value` IN (";
            $bind_placeholder = array();
            foreach($checked as $val) {
                  $bind_placeholder[] = "?";
                  $values[] = $val;
            }
            $where_condition .= implode(',', $bind_placeholder).")";
            $where[] = $where_condition;
        }

Follow up answer: You have a logic error in your query. When you introduce more then 1 attribute category, the following condition deal_attribute_values.deal_attribute_id=? appears twice. This means that deal_attribute_id must equal 2 values at the same time for the same row which is not possible.

You will have to modify the query so that for each attribute category/set you are adding to the query you will have to add deal_attribute_values to the join list. So for your last update the query should look something like this:

SELECT deals.*, deal_categories.name AS catName FROM deals,deal_categories,deal_attribute_values, deal_attribute_values AS dav2 WHERE deals.deal_category_id=deal_categories.id AND deals.deal_category_id=? AND deals.price >= ? AND deals.price <= ? AND deals.start_date >= ? AND deals.end_date <= ? AND deals.id=deal_attribute_values.deal_id AND deal_ attribute_values.deal_attribute_id=? AND deal_attribute_values.value IN (?,?,?,?) AND deals.id=dav2.deal_id AND dav2.deal_attribute_id=? AND dav2.value IN (?,?,?)

Notice that i have added deal_attribute_values as dav2 this is in order to match the 2nd group of attributes too.

And for the code change required for this it whould be like (see the changes to the $tables[] part):

    if (isset($_POST['attr']) && valid_array($_POST['attr'])) {
        foreach ($_POST['attr'] AS $attrID => $checked) {
            current_table = "`deal_attribute_values` AS dav".$attrID;
            $values[] = $attrID;
            $where_condition = "`deals`.`id`=`".$current_table."`.`deal_id` 
            AND `".$current_table."`.`deal_attribute_id`=? 
            AND `".$current_table."`.`value` IN (";
            $bind_placeholder = array();
            foreach($checked as $val) {
                  $bind_placeholder[] = "?";
                  $values[] = $val;
            }
            $where_condition .= implode(',', $bind_placeholder).")";
            $where[] = $where_condition;
            $tables[] = $current_table;
        }
    }