[MySQL 4.1] Datenbanksuche

[Timo Trallala]

Hallo Ihr Mysql-Gurus

Bsp: folgendes dynamisch generiertes statement

select  (0
         +20*(concat(' ',prod_name,' ') like '% playa %')*length('playa')
         +6*(concat(' ',prod_description_search,' ') like '% playa %')*length('playa')
         +10*(prod_name like '%playa%')*length('playa')
         +3*(prod_description_search like '%playa%')*length('playa')

         +20*(concat(' ',prod_name,' ') like '% de %')*length('de')
         +6*(concat(' ',prod_description_search,' ') like '% de %')*length('de')
         +10*(prod_name like '%de%')*length('de')
         +3*(prod_description_search like '%de%')*length('de')

         +20*(concat(' ',prod_name,' ') like '% palma %')*length('palma')
         +6*(concat(' ',prod_description_search,' ') like '% palma %')*length('palma')
         +10*(prod_name like '%palma%')*length('palma')
         +3*(prod_description_search like '%palma%')*length('palma')
          ) result_value,
        (0
         +20*(1)*length('playa')
         +6*(1)*length('playa')
         +10*(1)*length('playa')
         +3*(1)*length('playa')
         
         +20*(1)*length('de')
         +6*(1)*length('de')
         +10*(1)*length('de')
         +3*(1)*length('de')

         +20*(1)*length('palma')
         +6*(1)*length('palma')
         +10*(1)*length('palma')
         +3*(1)*length('palma')
          ) result_max,cat_parent_id,
         prod_code,left(prod_name,50)
  from products LEFT JOIN productcatalogs ON prodcat_prod_id=prod_id LEFT JOIN catalogs ON prodcat_cat_id=cat_id
 where ((0
         +20*(concat(' ',prod_name,' ') like '% playa %')*length('playa')
         +6*(concat(' ',prod_description_search,' ') like '% playa %')*length('playa')
         +10*(prod_name like '%playa%')*length('playa')
         +3*(prod_description_search like '%playa%')*length('playa')

         +20*(concat(' ',prod_name,' ') like '% de %')*length('de')
         +6*(concat(' ',prod_description_search,' ') like '% de %')*length('de')
         +10*(prod_name like '%de%')*length('de')
         +3*(prod_description_search like '%de%')*length('de')

         +20*(concat(' ',prod_name,' ') like '% palma %')*length('palma')
         +6*(concat(' ',prod_description_search,' ') like '% palma %')*length('palma')
         +10*(prod_name like '%palma%')*length('palma')
         +3*(prod_description_search like '%palma%')*length('palma')

          ) 
       >0
 order by 1 desc; 

gibt es eine Möglichkeit, den left join nur zu berücksichtigen, wenn eine cat_parent_id übergeben wird, wenn nicht lediglich im produktkatalog suchen.

ein Fuchs sagte mal sowas gine durch eine schlaue or Verknüpfung in der where-klausel.

Thanx für eure hilfe
der Trallala