Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
Thank you for your answers! – Masi Aug 25 '09 at 5:11

2 Answers

up vote 3 down vote accepted

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'] )
);
share|improve this answer
up vote 1 down vote

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'] . '%')                                                                                                                                                       
);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.