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]);
Add a comment
|
2 Answers
$sql = "SELECT * FROM tree WHERE tree_id = ANY ($1)";
$result2 = pg_query_params($sql2,[$arr]);
$arr format example = "{12,13}"
1 Comment
greg
There must be as many parameters as values in the IN statement.
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) . '}';
}