Tutorial :replace multiple values+in SQL query?



Question:

I want to do searching for data using users' entry in a field. That is if user enters "D+t+y+g,k,j,h" want to search for values having letters "d and t and y and g or k or j or h". I tried the PHP str_replace function, but didn't like the result.

//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%'";  }  

...but for the input "D+t+y+g,k,j,h" the query is coming out like this:

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**  

...when what I want/need is:

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%'  


Solution:1

A solution in javascript

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;  }  

yields the following (when 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%')  


Solution:2

Try This:

<?      $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;  ?>  

Gives Me:

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%'


Solution:3

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.  }  

:)


Solution:4

Using javascript to write SQL statements is insane IMHO, you're opening a wide security breach in you application !

You'd better use json or any other format if you want to treat data client-side before submiting them, but please, leave SQL for server-side processing.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »