Understanding parameters in Postgres Like command in PHP

Question

How can you use parameters in Postgres Like command?

I am reading this document. I am trying to search a word lorem in the table questions and in its column body.

I run unsuccessfully the following code inside PHP

$result = pg_query_params ( $dbconn, 
    "SELECT question_id, body
    FROM questions
    WHERE body ilike '%$1%'",
    array ( $_GET['search'])                                                                                                                                                       
);

I get this warning

Warning: pg_query_params() [function.pg-query-params]: Query failed: ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0 in /var/www/codes/search_body.php on line 10 Call Stack

Answer 1

I think because the $1 is in single quotes it is taken as literal, instead of a placeholder.

Try:

$result = pg_query_params ( $dbconn, 
    "SELECT question_id, body
    FROM questions
    WHERE body ilike $1",
    array ('%' . $_GET['search'] . '%')                                                                                                                                                       
);

Answer 2

You have to use a parameter in place of one whole value, and not inside quotes.

Solution 1: concatenate the LIKE wildcards into the parameter value:

$result = pg_query_params ( $dbconn, 
    "SELECT question_id, body
    FROM questions
    WHERE body ilike $1",
    array ( "%" . $_GET['search'] . "%")
);

Solution 2: concatenate the LIKE wildcards in the SQL expression:

$result = pg_query_params ( $dbconn, 
    "SELECT question_id, body
    FROM questions
    WHERE body ilike '%' || $1 || '%'",
    array ( $_GET['search'] )
);