SQL sorgusunda birden fazla değer + yerine?

4 Cevap php

Ben bir alanda kullanıcı girişi kullanarak veri search ing yapmak istiyorum. Kullanıcı "D+t+y+g,k,j,h" harfler "d and t and y and g or k or j or h" olan değerleri aramak istiyorsanız girerse budur. Ben PHP str_replace fonksiyonu çalıştı, ancak sonuç gibi değildi.

//kw is text field... 
if($kw != "") {
  //here we check for some data in field; if yes, continue below

  //c is for ',' replaced in JavaScript 
  $kw1 = str_replace("c"," OR bcm.keywords LIKE '$kw%' ",$kw);

  //p is for '+' replaced in JavaScript//'bcm' is table name.
  $kw3 = str_replace("p"," AND bcm.keywords LIKE '$kw%' ",$kw1);

  //for appending into main query string
  $app.=$kw3; 
  //$app.=" AND bcm.keywords LIKE '$kw%'";
}

... Ama girişi için "D+t+y+g,k,j,h" sorgu böyle çıkıyor:

AND bcm.keywords LIKE 'Dptpypgckcjch%' t 
AND bcm.keywords LIKE 'Dptpypgckcjch%' y 
AND bcm.keywords LIKE 'Dptpypgckcjch%' g 
OR bcm.keywords LIKE 'D 
AND bcm.keywords LIKE 'Dptpypgckcjch%' t 
AND bcm.keywords LIKE 'Dptpypgckcjch%' y 
AND bcm.keywords LIKE 'Dptpypgckcjch%' gckcjch%' k 
OR bcm.keywords LIKE 'D 
AND bcm.keywords LIKE 'Dptpypgckcjch%' t 
AND bcm.keywords LIKE 'Dptpypgckcjch%' y 
AND bcm.keywords LIKE 'Dptpypgckcjch%' gckcjch%' j 
OR bcm.keywords LIKE 'D 
AND bcm.keywords LIKE 'Dptpypgckcjch%' t 
AND bcm.keywords LIKE 'Dptpypgckcjch%' y 
AND bcm.keywords LIKE 'Dptpypgckcjch%' gckcjch%' h**

... Ne / ihtiyaç istiyorum olduğunda:

AND bcm.keywords LIKE 'D%' 
AND bcm.keywords LIKE 't%' 
AND bcm.keywords LIKE 'y%' 
AND bcm.keywords LIKE 'g%' 
OR bcm.keywords LIKE 'k%' 
OR bcm.keywords LIKE 'j%' 
OR bcm.keywords LIKE 'h%'

4 Cevap

Javascript bir çözüm

var getSearchSql = function(kw) 
{
  if(kw != "") 
  {
    var ors = kw.split(",");
    var sql = "";
    var sqlVariable = "bcm.keywords";
    for(i = 0; i < ors.length; i++)
    {
      var ands = ors[i].split("+");
      sql += (i == 0)? "(": " OR (";
      for(j = 0; j < ands.length; j++)
      {
          sql += (j == 0)? "": " AND ";
          sql += sqlVariable + " LIKE '%" + ands[j] + "%' "
      }
      sql += ")"
    }
  }
  return sql;
}

şu sonucu verir (zaman kw = "D+t+y+g,k,j,h")

(bcm.keywords LIKE '%D%' 
    AND bcm.keywords LIKE '%t%' 
    AND bcm.keywords LIKE '%y%' 
    AND bcm.keywords LIKE '%g%') 
OR (bcm.keywords LIKE '%k%') 
OR (bcm.keywords LIKE '%j%') 
OR (bcm.keywords LIKE '%h%')

Bu deneyin:

<?
    $str = "D+t+y+g,k,j,h";

    $comNext = 0;
    for ($i = 0 ; $i < strlen($str);$i++){
    	if ($str[$i+1] == "+"){
    		$andKey .= " AND bcm.keywords LIKE '".$str[$i]."%'";
    	}else if ($str[$i+1] == "," || $str[$i+1] == ""){
    		if ($comNext == 0)
    			$andKey .= " AND bcm.keywords LIKE '".$str[$i]."%'";
    		else
    			$orKey .= " OR bcm.keywords LIKE '".$str[$i]."%'";
    		$comNext = 1;
    	}

    }
    echo $andKey.$orKey;
?>

Me verir:

AND bcm.keywords LIKE 'D%' AND bcm.keywords LIKE 't%' AND bcm.keywords LIKE 'y%' AND bcm.keywords LIKE 'g%' OR bcm.keywords LIKE 'k%' OR bcm.keywords LIKE 'j%' OR bcm.keywords LIKE 'h%'

The answer below with comments for someone's reference... Thanx for the help dear ones...

var ors = kw.split(",");//SPLITS WHERE , IS PRESENT
var sql = "";//INITIALISE SQL VARIABLE,TO BE PASSED TO AJAX
var sqlVariable = " LOWER(bcm.keywords)";//CONVERTS TO LOWER CASE FOR CHECKING, FROM DB
for(i = 0; i < ors.length; i++)// INITIALISE ARRAY TO GIVE OR WHERE , IS PRESENT 
                               // AND FOR FURTHER SPLITTING WHERE + IS PRESENT TILL ORS LENGTH ... 
                               // IE: ORS. LENGTH WILL HAVE VALUE FOR AT LEAST SINGLE , IN STRING
{
  var ands = ors[i].split("+");//SPLIT STRING WHERE + IS PRESENT
  sql += (i == 0)? "(": " OR (";//REPLACE , WITH OR
  for(j = 0; j < ands.length; j++)// SIMILAR STEPS TO ABOVE TO REPLACE + WITH AND
  {
      sql += (j == 0)? "": " AND ";
      sql += sqlVariable + " LIKE '%" + ands[j] + "%' "

  }
  sql += ")"// THE FINAL SQL ,WITH ALL BRACKETS, IS PASSED TO AJAX FOR 
               APPENDING TO SOME QUERY STATEMENT.
}

:)

SQL deyimlerini yazmak için javascript kullanarak size uygulamada geniş bir güvenlik ihlali açıyoruz, deli IMHO olduğunu!

Sen daha bunları submiting önce veri istemci tarafı tedavi etmek istiyorsanız json ya da herhangi başka bir biçimi kullanın, ancak, sunucu tarafında işleme için SQL bırakın lütfen ediyorum.

etiketler