[MySQL 4.1] statement dynamisch generieren

[Timo Trallala]

es geht darum, ein sql-statement dynamisch zu generieren. das
statement selber und faktoren sollen über eine config einstellbar sein.

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,
prod_code,left(prod_name,50)
from products
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;

es geht hier um DB-Suche: übergeben wird per post:

array("playa","de","palma") 

hab es dann mal folgendermaßen gelöst, was mir allerdings nicht sonderlich elegant erscheint.

in der config hab ich folgende vars

 $vars["search_statement"]="SELECT (0 [[RESULT_VALUE]]) result_value,
                                    (0 [[RESULT_MAX]]) result_max,prod_code,left(prod_name,50)
                                     from products WHERE (0 [[WHERE]]) >0  order by 1 desc";
 $vars["sql_search"]["result_value"]="
         +20*(concat(' ',prod_name,' ') like '% [[SEARCH_WORD]] %')*length('[[SEARCH_WORD]]')
         +6*(concat(' ',prod_description_search,' ') like '% [[SEARCH_WORD]] %')*length('[[SEARCH_WORD]]')
         +10*(prod_name like '%[[SEARCH_WORD]]%')*length('[[SEARCH_WORD]]')
         +3*(prod_description_search like '%[[SEARCH_WORD]]%')*length('[[SEARCH_WORD]]')";
 $vars["sql_search"]["result_max"]="
          +20*(1)*length('[[SEARCH_WORD]]')
         +6*(1)*length('[[SEARCH_WORD]]')
         +10*(1)*length('[[SEARCH_WORD]]')
         +3*(1)*length('[[SEARCH_WORD]]')";
 define("search_word_length", "2");
 define("search_result_prosite", "5"); 

die werden dann folgendermaßen verarbeitet

    // Suchergebnisse?
    function SearchResults($post, $parent_id, $search_sql,$vars)
        {
        $db1 = new dbconn(host, user, pass, name);

        $post=explode(" ",$post);

        //Result-Value dynamisch generieren
        $sql_result_value="";
        foreach ($post AS $key => $value)
            $sql_result_value.=str_replace("[[SEARCH_WORD]]",$value,$vars["sql_search"]["result_value"]);


        //Result-Max dynamisch generieren
        $sql_result_max="";
        foreach ($post AS $key => $value)
            $sql_result_max.=str_replace("[[SEARCH_WORD]]",$value,$vars["sql_search"]["result_max"]);

        $search_sql=str_replace("[[RESULT_VALUE]]",$sql_result_value,$search_sql);
        $search_sql=str_replace("[[RESULT_MAX]]",$sql_result_max,$search_sql);
        $search_sql=str_replace("[[WHERE]]",$sql_result_value,$search_sql);

$result = $db1->query($search_sql);
....} 

Vielleicht hat jemand ne elegantere Lösung
danke
der Trallala

[phoboslab]

Wenn ich das richtig sehe, versuchst du die ergebnisse zu Bewerten? Evtl. wäre Full-Text search was für dich.