Post by LeorLindel » Wed Sep 23, 2015 3:13 pm

Hello everyone,

I have an online music store v1.5.6.4 and I need additional fields (artist and label) in the product page.
So I duplicated all files "manufacturer" I renamed (artist and label), I also duplicated the table "manufacturer and manufacturer_to_store" in the database that I heard renamed too.
I created a vqmod for the necessary changes to files (header and product)
but I have a little problem to change this request (original) and do a join with these two new tables (artist and label) knowing which are identical to table "manufacturer".
Initial request line 14 located the file "catalog/model/catalog/product.php":

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
Additional tables in the database:

Code: Select all

CREATE TABLE IF NOT EXISTS `oc_artist` (
  `artist_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_artist_to_store` (
  `artist_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_label` (
  `label_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_label_to_store` (
  `label_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I would like to join a "artist_id and label_id" to this request.

If a specialist could help me.

sorry for my bad English
Last edited by LeorLindel on Fri Sep 25, 2015 4:23 pm, edited 1 time in total.

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by straightlight » Wed Sep 23, 2015 9:33 pm

Since the artist_id and the label_id don't have the same significance as the original product_id, I would first assign a new product_id field on the artist and label table and assign each reliable products from either PHPMyAdmin or from a module before joining the tables so that at least one value would be found in the query.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by LeorLindel » Thu Sep 24, 2015 7:51 am

Bonsoir,

Je te remercie de ta réponse.
Les champs "artist_id et label_id" ne sont pas similaires à "product_id" puisqu'ils sont identiques à "manufacturer_id".
Dans la table "product" j'ai bien ajouté les deux champs ci-dessus (artist_id et label_id) juste après le champ "manufacturer_id" et j'aimerais donc faire une jointure à la requête initiale comme cela est fait avec le champs "manufacturer_id"

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by straightlight » Thu Sep 24, 2015 8:28 am

Maintenant que la demande est un peu plus éclairci, remplacez:

Code: Select all

WHERE p.product_id = '" . (int)$product_id . "'
par:

Code: Select all

LEFT JOIN `" . DB_PREFIX . "artist` `a1` ON (`a1`.`artist_id` = `p`.`artist_id`) LEFT JOIN `" . DB_PREFIX . "artist_to_store` `a2s` ON (`a2s`.`store_id` = `p2s`.`store_id`) LEFT JOIN  `" . DB_PREFIX . "label` `l1` ON (`l1`.`label_id` = `p`.`label_id`) LEFT JOIN `" . DB_PREFIX . "label_to_store` `l2s` ON (`l2s`.`store_id` = `a2s`.`store_id`) WHERE `p`.`product_id` = '" . (int)$product_id . "'
Cette modification devrait fonctionner.
Last edited by straightlight on Thu Sep 24, 2015 9:04 am, edited 1 time in total.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by LeorLindel » Thu Sep 24, 2015 8:59 am

Malheureusement cela m'entraine une page blanche et voici l'erreur qui m'apparaît dans le journal des erreurs :

Code: Select all

2015-09-24 2:57:27 - PHP Notice:  Error: Not unique table/alias: 'a'<br />Error No: 1066<br />SELECT DISTINCT *, pd.name AS name, p.image, a.name AS artist, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_artist a ON (p.artist_id = a.artist_id) LEFT JOIN `oc_artist` `a` ON (`a`.`artist_id` = `p`.`artist_id`) LEFT JOIN `oc_artist_to_store` `a2s` ON (`a2s`.`store_id` = `p2s`.`store_id`) LEFT JOIN  `oc_label` `l1` ON (`l1`.`label_id` = `p`.`label_id`) LEFT JOIN `oc_label_to_store` `l2s` ON (`l2s`.`store_id` = `a2s`.`store_id`) WHERE `p`.`product_id` = '43' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in /public_html/1564/system/database/mysql.php on line 50

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by straightlight » Thu Sep 24, 2015 9:06 am

1 possibilité; sois que vous avez laissé votre ancienne requête là où elle était et vous n'aviez pas réversé le changement. Par contre, j'ai fait les ajustements à mon code. Je vous recommande par contre de complètement réverser le code avec l'original. Ensuite, d'essayer mon intégration.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by LeorLindel » Thu Sep 24, 2015 9:15 am

Je viens donc d'isoler la requête du vqmod et ai mis votre modification dans le fichier initial et j'obtiens le même résultat (une page blanche) et cette erreur :

Code: Select all

2015-09-24 3:13:26 - PHP Notice:  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND p.label_id = '1'' at line 1<br />Error No: 1064<br />SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p.label_id = '1' GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC LIMIT 0,15 AND p.label_id = '1' in /public_html/1564/system/database/mysql.php on line 50

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by straightlight » Thu Sep 24, 2015 10:29 am

'AND p.label_id = '1'' at line 1
D'après ma requête précédente, je n'ai pas fait mention de cette ligne.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by LeorLindel » Thu Sep 24, 2015 10:35 am

C'est exact mais c'est le résultat qui est affiché dans l'erreur suite au test sur le label portant l'ID n° 1 et non la requête que j'ai injecté dans le fichier.

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by straightlight » Thu Sep 24, 2015 10:45 am

Ne sachant aucunement la façon dont vous l'aviez inséré, il faudra que vous postiez la requête entière afin de repérer l'erreur.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by LeorLindel » Thu Sep 24, 2015 10:51 am

Requête initiale :

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
Requête modifiée :

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN `" . DB_PREFIX . "artist a  ON ( p.artist_id = a.artist_id) LEFT JOIN " . DB_PREFIX . "artist_to_store a2s ON ( p2s.store_id = a2s.store_id) LEFT JOIN " . DB_PREFIX . "label l ON (p.label_id = l.label_id) LEFT JOIN " . DB_PREFIX . "label_to_store l2s ON (p2s.store_id = l2s.store_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by straightlight » Thu Sep 24, 2015 11:03 am

Je ne vois toujours pas p.label = 1 dans la requête modifiée.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by LeorLindel » Thu Sep 24, 2015 11:10 am

straightlight wrote:Je ne vois toujours pas p.label = 1 dans la requête modifiée.
Cela ne figure pas non plus dans la modification que vous m'avez demandé d'effectuer, donc c'est pour cela que ça ne figure pas dans la requête modifiée.

Code: Select all

LEFT JOIN `" . DB_PREFIX . "artist` `a1` ON (`a1`.`artist_id` = `p`.`artist_id`) LEFT JOIN `" . DB_PREFIX . "artist_to_store` `a2s` ON (`a2s`.`store_id` = `p2s`.`store_id`) LEFT JOIN  `" . DB_PREFIX . "label` `l1` ON (`l1`.`label_id` = `p`.`label_id`) LEFT JOIN `" . DB_PREFIX . "label_to_store` `l2s` ON (`l2s`.`store_id` = `a2s`.`store_id`) WHERE `p`.`product_id` = '" . (int)$product_id . "'

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Thu Sep 24, 2015 11:39 am

Dans la modification cela ne devrait pas être cela :

Code: Select all

(l2s.store_id = p2s.store_id)
plutôt que cela :

Code: Select all

(l2s.store_id = a2s.store_id)
avant le "WHERE"

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Thu Sep 24, 2015 3:59 pm

Deleted
Last edited by LeorLindel on Fri Sep 25, 2015 4:29 pm, edited 1 time in total.

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by villagedefrance » Thu Sep 24, 2015 6:26 pm

Salut Marco,

Je crois qu'il te faut ajouter une table supplementaire dans ta BDD:

Code: Select all

CREATE TABLE IF NOT EXISTS `oc_product_to_artist` (
  `product_id` int(11) NOT NULL,
  `artist_id` int(11) NOT NULL,
  `label_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`, `artist_id`, `label_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Sans cette table tu ne peux pas comparer les `product_id` et donc tu ne peux pas faire un LEFT JOIN dans la requete du produit.

Aussi, tes nouvelles tables ne contiennent pas de PRIMARY KEYS!

Ajouter la table `product_to_artist` va bien entendu entrainer des modifications supplementaires dans les `model`. Je devine que tu as deja ajoute les champs necessaires dans tes fiches produits, donc il te faudra seulement mettre a jour les requetes `INSERT`, `UPDATE`, `COPY` et `DELETE` dans tes fichiers `model`.

N'hesite pas a me contacter directement si tu veux un coup de main.

OpenCart custom solutions @ https://villagedefrance.net


User avatar
Active Member

Posts

Joined
Wed Oct 13, 2010 10:35 pm
Location - UK

Post by LeorLindel » Thu Sep 24, 2015 6:37 pm

Salut Philippe,

Pourtant je me suis basé sur Manufacturer et il n'existe pas de table "oc_product_to_manufacturer" mais tu n'as peut être pas tort.

Mes nouvelles tables contiennent bien une PRIMARY KEYS sur les "_id" mais j'ai surement omis de le mettre dans mon sujet.

Si tu veux jeter un oeil, j'ai mis en PJ, tous mes fichiers.

[Hors Sujet] Je t'ai écrit via MP du forum Français pour un autre sujet [/Hors Sujet]

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by villagedefrance » Thu Sep 24, 2015 7:47 pm

Ok, essaye ca:

Au debut de la requete, apres `md.name AS manufacturer,` ajoute `a.name AS artist, l.name AS label,`.

Ensuite, comme tu l'avais deja fait dans ta requete modifiee ajoute le code suivant, mais sans ajoindre les tables `store`.

Code: Select all

LEFT JOIN `" . DB_PREFIX . "artist a  ON ( p.artist_id = a.artist_id) LEFT JOIN " . DB_PREFIX . "label l ON (p.label_id = l.label_id)
Dans l'array produit ajoute ces 2 lignes:

Code: Select all

'artist' => $query->row['artist'],
'label' => $query->row['label'],
Aussi, comme tu as mis `seo_url_keyword` dans ta fiche Artist, soit bien sur de mettre a jour le fichier `seo_url.php` sous Catalog > common, sinon tu risques d'avoir des problemes. Personnellement je retirerai cette option dans ta fiche Artist.

Bon courage.

P.S: j'ai rien trouve dans mes PMs sur le forum Francais.

OpenCart custom solutions @ https://villagedefrance.net


User avatar
Active Member

Posts

Joined
Wed Oct 13, 2010 10:35 pm
Location - UK

Post by LeorLindel » Thu Sep 24, 2015 8:02 pm

Voici donc la requête modifiée :

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, a.name AS artist, l.name AS label, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "artist a  ON ( p.artist_id = a.artist_id) LEFT JOIN " . DB_PREFIX . "label l ON (p.label_id = l.label_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
Cela m'affiche une page blanche avec cette erreur dans le journal après que j'ai testé sur l'artiste (id = 1) :

Code: Select all

2015-09-24 13:59:37 - PHP Notice:  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND p.artist_id = '1'' at line 1<br />Error No: 1064<br />SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p.artist_id = '1' GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC LIMIT 0,15 AND p.artist_id = '1' in /public_html/1564/system/database/mysql.php on line 50

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

Image


User avatar
Active Member

Posts

Joined
Mon Feb 22, 2010 8:05 pm

Post by villagedefrance » Thu Sep 24, 2015 10:39 pm

Ok, je crois que c'est du au positionnement des nouveaux `LEFT JOIN` dans la requete.
Essaye de les deplacer progressivement vers le debut de la requete, avant `manufacturer` dabord, puis plus haut si possible.

OpenCart custom solutions @ https://villagedefrance.net


User avatar
Active Member

Posts

Joined
Wed Oct 13, 2010 10:35 pm
Location - UK
Who is online

Users browsing this forum: No registered users and 6 guests