Hello. Could someone help me write the right query to select the right products?
Introduction: - The store has item numbers, I use the "model" field - There are replacement items, I use the "sku" field
The replacement item is an item of a similar product. (fields are filled in manually in the admin)
Example: Item No. 1 Article - xxxxxx Replacement Article - yyyyyy
Item No. 2 Article - yyyyyy Replacement Article - xxxxxx
Replacement articles on one product can be up to 3 pieces.
What I did: in the product controller
$this->data['productsArtZam'] = array(); $results = $this->model_catalog_product->getProductArtZam($this->request->get['product_id'],$this->data['sku'], $this->data['model']); foreach ($results as $result) { $skus = explode(',', $result['model']); foreach ($skus as $sku) { $this->data['productsArtZam'][] = array( 'sku' => trim($sku), 'href' => $this->url->link('product/product', 'product_id=' . $result['product_id']) ); } } In the product model ...
public function getProductArtZam($product_id, $sku, $model) { $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE (sku LIKE '%" . $model . "%' AND quantity > 0 AND status = '1' ) LIMIT 1,6 "); } In tpl product I display the article of those products that match the request.
It works for me like this: I fill in the field of the article (model) and, separated by a comma, the replacement article (sku). I split the string into an array, compare and display. To these art. deputy were deduced they need to be registered in both goods
It seems that everything works out for me, I get the right products, up to 3 pcs. with active, correct links.
BUT
because of LIKE% ...%, I can't do anything. There are two different products with different part numbers: article ART-38 and ART-3. The replacement item applies to ART-38 and ART-3, but only to the first.
Instead of LIKE, I do "=" everything works as it should, but no more than 1 replacement item is displayed, i.e. if in ...
Item No. 2 Article - yyyyyy Replacement Article - xxxxxx and zzzzzz
... that request, as if not satisfied.
Tell me, please, what am I doing wrong?
