CODEWITHSUNDEEP
phppostgresql
user2641043
user2641043

Reputation: 405

PHP Postgresql using IN with pg_query_params

Can I pass values into this query? If so, what is the format of $arr?

$sql = "SELECT * FROM tree WHERE tree_id IN ($1);";
$result = pg_query_params($sql, [$arr]);

Upvotes: 0

Views: 560

Answers (2)

emkey08
emkey08

Reputation: 6171

If your SQL statement contains an IN clause which takes a varying number of parameters, then it's somewhat tricky to use a parameterized query. As you've found out, you can solve this by using ANY instead of IN (documentation here), and then passing a PostgreSQL array as the sole query parameter.

$sql = 'SELECT * FROM tab WHERE id = ANY ($1);';
$id_array = [ 1, 2, 3 ];        // id_array can be of any size
$result = pg_query_params($sql, [ toPostgresArray($id_array) ]);

Whereby toPostgresArray() is the following helper function, which converts a PHP array into a PostgreSQL array:

function toPostgresArray($values) {
    $strArray = [];
    foreach ($values as $value) {
        if (is_int($value) || is_float($value)) {
            // For integers and floats, we can simply use strval().
            $str = strval($value);
        } else if (is_string($value)) {
            // For strings, we must first do some text escaping.
            $value = str_replace('\\', '\\\\', $value);
            $value = str_replace('"', '\\"', $value);
            $str = '"' . $value . '"';
        } else if (is_bool($value)) {
            // Convert the boolean value into a PostgreSQL constant.
            $str = $value ? 'TRUE' : 'FALSE';
        } else if (is_null($value)) {
            // Convert the null value into a PostgreSQL constant.
            $str = 'NULL';
        } else {
            throw new Exception('Unsupported data type encountered.');
        }
        $strArray[] = $str;
    }
    return '{' . implode(',', $strArray) . '}';
}

Upvotes: 0

user2641043
user2641043

Reputation: 405

$sql = "SELECT * FROM tree WHERE tree_id = ANY ($1)";
$result2  = pg_query_params($sql2,[$arr]);

$arr format example = "{12,13}"

Upvotes: 2

Related Questions