oracle - Query returns different results between PL/SQL Developer and PHP -

i have made small intranet website collect , store data used expedite our logistics processes. i'm in process of adding search functionality which, if records found match criteria, allow user select parts of data pre-populate new shipping request data (e.g, user types 'mar' in recipient name input textbox , '109' in street address input textbox , query returns 2 records: {"mary smith", "1090 south central st"} , {"mark swanson", "109 e. 31st st."}).

at moment, when search criteria entered , submitted, data returned query in php 100% accurate if , if single criteria entered (such recipient name). when attempt use 2 different search criterias in php, record results not match results when running same query in oracle pl/sql developer. if 3 different search criterias used, query ran in php return 0 records. in 3 of aforementioned scenarios, query executed without error in oracle pl/sql developer.

the following code php search function. input data function associate array of field names , user inputted search criteria data field.

     public function search()      {         if($this->dbcon)         {             $query = "select * ship_request ";             $postcount = count($this->post_data);             $counter = 0;              if ($postcount > 0)              {                 $query .= "where ";             }              foreach ($this->post_data $k => $v)             {                 $counter++;                 if (strlen($v) > 0)                 {                     if ($k == 'sr_date')                      {                         $query .= $k . " = :" . $k . " , ";                     } else {                         $query .= "upper(" . $k . ") upper(:" . $k . ") , ";                     }                 }             }               if (substr($query,-4) == "and ")             {                 $query = substr($query, 0, strlen($query) - 4);             }              $stid = oci_parse($this->ifsdb, $query);               foreach ($this->post_data $k => $v)             {                 if (strlen($v) > 0)                 {                     if ($k == 'sr_date')                      {                         $this->post_data[$k] = date("d-m-y", strtotime($this->post_data[$k]));                         $placeholder = $this->post_data[$k];                     } else {                         $placeholder = '%' . $this->post_data[$k] . '%';                      }                     oci_bind_by_name($stid, $k, $placeholder);                 }             }             oci_execute($stid);             $nrows = oci_fetch_all($stid, $recordsfound);             $recordsfound = json_encode($recordsfound);             oci_free_statement($stid);             echo $recordsfound;         } else {             die("could not connect database!");         }     } } 

i've done var_dump on $query see query looks when enter multiple search criteria values. example of see:

select * hol_ship_request upper(sr_shipper_name) upper(:sr_shipper_name) , upper(sr_recipient_name) upper(:sr_recipient_name) , sr_recipient_phone upper(:sr_recipient_phone)

that query returns 0 records when enter "a" shipper name, "m" recipient name, , "2" phone number.

this query, when executed in oracle pl/sql developer, however, returns 27 records.

select * hol_ship_request upper(sr_shipper_name) upper('%a%') , upper(sr_recipient_name) upper('%m%') , sr_recipient_phone upper('%2%')

is there wrong way i'm trying bind parameters in php? there different have when using multiple like statements?

you've forgotten % wildcard chars in built query string. db interface libraries not parse query you're building, , not like clauses - it's not job guess kind of match you're trying do. e.g. doing

where 'b' 'b%' '%b' '%b%' 

it's provide appropriate wildcards, , since you're using placeholders, you'll have yourself, e.g.

where upper(sr_shipper_name) concat('%', :sr_shipper_name, '%') 

if this:

$shipper = '%foo%';   ... :shipper 

you'd end equivalent of:

where ... '\%foo\%' 

the placeholder system doesn't parse provided text , try figure out if you're trying use wilcard or passing in literal % char. that's why have use concat hack build proper wildcarded construct.