es geht darum, ein sql-statement dynamisch zu generieren. das
statement selber und faktoren sollen über eine config einstellbar sein.
es geht hier um DB-Suche: übergeben wird per post:
hab es dann mal folgendermaßen gelöst, was mir allerdings nicht sonderlich elegant erscheint.
in der config hab ich folgende vars
die werden dann folgendermaßen verarbeitet
Vielleicht hat jemand ne elegantere Lösung
danke
der Trallala
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;
+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;
PHP Code:
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
PHP Code:
$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");
PHP Code:
// 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);
....}
danke
der Trallala
Comment