Couchbase N1QL's select statement with named parameters in PHP

query
n1ql
#1

I was trying to use N1QL in PHP…this first one is working:

$query = CouchbaseN1qlQuery::fromString("select count(*) from bucket1 where type = 'user'");
$res = $myBucket->query($query);

Output:

array(1) {
  [0]=>
  object(stdClass)#3 (1) {
    ["$1"]=>
    int(58)
  }
}
  1. When I changed to named parameter(s), it failed:
$query = CouchbaseN1qlQuery::fromString("select count(*) from bucket1 where type = $type");
$res = $myBucket->query($query, ['type' => 'user']);

I got ‘PHP Notice: Undefined variable: type…’ error

Can anyone help to instruct me the correct syntax to use named parameters for N1QL in PHP? The document of PHP SDK does not have N11QL’s WHERE clause example using named parameters…

my couchbase server is 4.1

appreciated for your help.

#2

You should be aware that both N1QL and PHP uses $ as special symbol in the string, and when string enclosed in double quotes, PHP will first try to interpolate it (hence Notice: Undefined variable: type), but because it is only notice, it gets to real N1QL requests, but at that point $type already removed from statement, which leaves

select count(*) from `travel-sample` where type =

which is not valid N1QL statement, and it fails. So the correct solution, should look like this:

$cluster = new CouchbaseCluster("http://localhost:8091");
$bucket = $cluster->openBucket('travel-sample');

$query = CouchbaseN1qlQuery::fromString("select count(*) from `travel-sample` where type = 'hotel'");
$res = $bucket->query($query);
var_dump($res);

// NOTE: single quotes
$query = CouchbaseN1qlQuery::fromString('select count(*) from `travel-sample` where type = $type');
$res = $bucket->query($query, array('type' => 'hotel'));
var_dump($res);

// NOTE: escaping
$query = CouchbaseN1qlQuery::fromString("select count(*) from `travel-sample` where type = \$type");
$res = $bucket->query($query, array('type' => 'hotel'));
var_dump($res);