All geek questions in one place

MySQL String Returns Based on Additional PDO Parameters

I am trying to write a product page based on criteria from a user. By default, the following fields are passed:

  • qString (to match product name / description)
  • maxPrice / minPrice (to make between prices in a table)
  • minDate / maxDate (product search between dates)

The above gives me return values.

The following pair of fields is optional and is added via jQuery based on the category:

  • Category: category_id, which matches the deal.deals_category_id request (this still works)

Depending on the category, there may be additional attributes (for example, mobile transactions have a service provider, manufacturer) that will dynamically become visible in the interface. Here, on the client, using the checkboxes, select the manufacturers they are interested in and the service provider.

In my tables:

  • Transactions (Contains products with deal_category_id)
  • Categories (All transactions are associated with a category)
  • Category_attributes (additional attributes for each category. If a product is loaded into a category, they will also be provided with additional attribute selectors
  • Deal_attribute_options (contains related attribute-based binding parameters (where service providers may be the attribute, and attribute parameters may have AT & T, t-Mobile, etc.).
  • Deal_attribute_values ​​(Contains the values ​​of the selected attribute parameters when creating the product. Suppose I created a new mobile phone and select mobile phones of the category, the product will have to provide values ​​for the sweat attribute. They will receive a list of, for example, mobile networks or manufacturers, and associate the product manufacturer with this particular manufacturer.

So, by now I have several products in the database, each of which is associated with a category and based on the attributes associated with this category, each product would select a value for each attribute parameter. If there were two attributes (Manufacturer, mobile network), each product would have a value in deal_attribute_values, linking deal_id, attribute_id, and then the selected value (so the products can be the manufacturer of Samsung and Vodafone as a service provider).

What I'm trying to do now is based on a search query, returning related strings based on front-end filtering. After changing the form in the external interface, it will serialize the form values ​​and return the correct products through ajax based on these requests.

I make it work fine except for value_attributes. I suspect this has something to do with my IN () clause in the request, but hopefully someone can help me here with what I'm doing wrong.

Here is a 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 POST example that includes attributes (note that key 3 and 4) represents a collection of attributes (manufacturer and service provider). The values ​​in them are the checked options by which we want IN () to work.

 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 SQL is derived based on the above (updated based on 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 are 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 get the results until the attributes are entered. I can still get the result if I turn off all atttributes (both for the manufactuer and the service provider), and only select 1 attribute, say, for example, "Apple", which then returns one product, but if I select the appropriate supplier Services, Results.

Essentially with attributes, I want to select a row if the value of this product in the attribute_values ​​table is in the attributes in the IN () clause. Since there will be a lot of attributes based on the category, and several products should be returned if I keep Apple, Samsung and Vodacom / MTN as SP. If the product is samsung and Vodacom / MTN, it must be returned or if manufucturer is not selected and we select only Vodacom / MTN as SP, it must return all phones that are MTN / Vodacom. Even if there is no Vodacom, it should at least return all MTNs.

Please let me know if you need further help / information. I tried to get so many details here, so I hope everyone understands :)

** ANSWER UPDATE **

I made 2 changes to the code posted by Alex, which works on my end! Thanks, 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; } }
+5
source share
1 answer

I believe that you are right that the problem is the IN clause. There is a problem with prepared statements and providing values ​​for the IN clause. You cannot pass the whole IN condition as a single parameter, since it will automatically delete it as a whole, not as separate 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 an operator, it encapsulates the entire $str in quotation marks, so IN has only one value instead of comma separated values.

You have 2 options:

  • You either add $str to the request without binding it
  • Are you adding enough ? in the IN clause for each of the IN values ​​you want:
  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 logical error in your request. When you enter more than 1 attribute category, the following condition appears deal_attribute_values.deal_attribute_id=? . This means that deal_attribute_id must equal 2 values ​​simultaneously for the same row, which is not possible.

You will need to modify the request so that for each category / set of attributes added to the request, you need to add deal_attribute_values to the list of connections. Therefore, 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 (?,?,?)

Note that I added deal_attribute_values as dav2 so that it matches the second attribute group.

And for the code change required for this, it will be similar (see the changes in the $tables[] ):

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

Source: https://habr.com/ru/post/1205669/

More articles:


All Articles