News arrow Forum
Home    Contacts
Forum Dolibarr
Welcome, Guest
Please Login or Register.    Lost Password?
Forum Dolibarr Aide Bugs
Re:SQL err searching ass.products - FIX dupl. rows (1 viewing) (1) Guest
Go to bottom Post Reply Favoured: 0
TOPIC: Re:SQL err searching ass.products - FIX dupl. rows
#10677
jacopo (User)
Junior Boarder
Posts: 29
graphgraph
User Offline Click here to see the profile of this user
Gender: Male infodea Location: Rome Birthdate: 1976-05-21
SQL err searching ass.products in today snaps. ver 2008/10/23 22:22 Karma: 0  
Hi all,

I have found this error tryng to search associated products.
This error occours only when I search products by category and I have noticed that if you search without input anything the results are duplicated.

This is the error:

Code:
Dolibarr: 2.5-dev Data: 20081023220522 Livello di funzionalità: 10 Server: Apache/2.2.8 (Win32) DAV/2 mod_ssl/2.2.8 OpenSSL/0.9.8g mod_autoindex_color PHP/5.2.5 URL richiesto: /dolibarr_snapshot/htdocs/product/sousproduits/fiche.php?id=8 Referer: http://127.0.0.1/dolibarr_snapshot/htdocs/product/sousproduits/fiche.php?id=8 Tipo di database manager: mysql Richiesta di ultimo accesso a database in errore: SELECT p.rowid, p.ref, p.label, p.price FROM llx_product as p left join llx_categorie_product as cp on p.rowid=cp.fk_product WHERE 1=1AND cp.fk_categorie ='4' ORDER BY p.ref ASC Codice di ritorno per ultimo accesso al database per errore: DB_ERROR_SYNTAX Informazioni per ultimo accesso al database per errore: 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 'cp.fk_categorie ='4' ORDER BY p.ref ASC' at line 1
thank's for your support! Jacopo
 
Logged Logged  
 
Last Edit: 2008/10/23 22:23 By jacopo.
  The administrator has disabled public write access.
#10678
eldy (Admin)
Admin
Posts: 1910
graphgraph
User Online Now Click here to see the profile of this user
Gender: Male Site web personnel eldy10 ldestailleur@hotmail.com Location: Montigny-le-Bretonneux (St-Quentin en Yvelines) Birthdate: 1972-10-10
Re:SQL err searching ass.products in today snaps. ver 2008/10/24 01:42 Karma: 44  
I made a fix. Tell me if it's ok.
 
Logged Logged  
  The administrator has disabled public write access.
#10684
jacopo (User)
Junior Boarder
Posts: 29
graphgraph
User Offline Click here to see the profile of this user
Gender: Male infodea Location: Rome Birthdate: 1976-05-21
Re:SQL err searching ass.products - FIX dupl. rows 2008/10/24 11:40 Karma: 0  
Hi eldy,

thank's! Now it work but as I said I have also noticed the search without category return duplicated rows due to the join.
I have fixed it adding a distinct on the select into the file:

htdocs/products/sousproduits/fiche.php

at row 101

Code:
$sql = 'SELECT distinct p.rowid, p.ref, p.label, p.price'; $sql.= ' FROM '.MAIN_DB_PREFIX.'product as p'; $sql.= ' left join '.MAIN_DB_PREFIX.'categorie_product as cp on p.rowid=cp.fk_product'; $sql.= " WHERE 1=1";
Jacopo
 
Logged Logged  
  The administrator has disabled public write access.
Go to top Post Reply
Powered by FireBoardget the latest posts directly to your desktop
 
Dolibarr © 2007 - Gestion d'entreprise sous licence GNU GPL