How to use LIKE in oracle with PHP

I am trying to use a simple SQL statment with the LIKE operator. This SQL statement works on SQL developer but not when I try it on PHP.

SELECT * FROM hotels WHERE lower(name) LIKE '%luxury%';

​x
 
SELECT * FROM hotels WHERE lower(name) LIKE '%luxury%';​

However when I do the same thing in php I get this error:

Warning: oci_bind_by_name(): ORA-01036: illegal variable name/number

PHP Code:

$sql = "SELECT * FROM hotels WHERE lower(name) LIKE '%:term%'";
$stid = oci_parse($conn, $sql);
$term = "luxury";
oci_bind_by_name($stid, ":term", $term);
oci_execute($stid);

 
$sql = "SELECT * FROM hotels WHERE lower(name) LIKE '%:term%'";$stid = oci_parse($conn, $sql);$term = "luxury";oci_bind_by_name($stid, ":term", $term);oci_execute($stid);​

Answer

You would need to concatenate the string parts with the parameter, like :

$sql = "SELECT * FROM hotels WHERE lower(name) = '%' || :term || '%'";

 
$sql = "SELECT * FROM hotels WHERE lower(name) = '%' || :term || '%'";​

NB: do you really want to search for a string surrounded by litteral '%'s? Given the title of the question, you might be looking for the LIKE operator instead:

$sql = "SELECT * FROM hotels WHERE lower(name) LIKE '%' || :term || '%'";

 
$sql = "SELECT * FROM hotels WHERE lower(name) LIKE '%' || :term || '%'";​

Advertisement

Answer