CODEWITHSUNDEEP
phppostgresqlpdoprepared-statement
Albert Bakker
Albert Bakker

Reputation: 51

PHP PDO (PostgreSQL): can't get multiple parameters to work

I have the following, and I can't get it to work:

$pdo = new \PDO('pgsql:host=xxxx;port=xxx;dbname=xxx;user=xxx;password=xxx');

$foo = 123;
$bar = 123;

$query = '
SELECT *, 
(
    SELECT allow
    FROM foo 
    WHERE foo_field = ?
    AND lorem.lorem_id = foo.lorem_id
) as foo_allowed, (
    SELECT allow
    FROM bar
    WHERE bar_id = ?
    AND lorem.lorem_id = bar.lorem_id
) as bar_allowed
FROM lorem';

$stmt = $pdo->prepare($query);
$stmt->bindValue(1, $foo);
$stmt->bindValue(2, $bar);
$stmt->execute();

var_dump($stmt->fetchAll());

The code above gives me the following error:

Message: An exception occurred while executing '(query)': SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 2 parameters, but prepared statement "" requires 1

I tried the same in mysql and it worked fine. I also tried named parameters, but that doesn't work either.

Using PHP version 5.4.6 and PostgreSQL 9.1.7.

@Kovge I have users, roles and resources. Users can have Resources and Roles can have Resources. A user has one role. In table user_resource there's a field that says if a user is allowed to access the resource. The same for roles. I want a table (HTML table) with all the resources and see if a user is allowed to access a resource or not (first via user_resource, then role_resource)

Upvotes: 3

Views: 521

Answers (2)

byrnedo
byrnedo

Reputation: 1465

Have you tried calling the execute directly without the bind.

$stmt = $pdo->prepare($query);
$stmt->execute(array( 1 => $foo, 2 => $bar));

Upvotes: 1

Phil
Phil

Reputation: 274

Could you use table joins instead? So your query becomes something like:

SELECT l.*,
       f.allow,
       b.allow
  FROM foo_allowed f INNER JOIN
       lorem l ON ( l.lorem_id = f.lorem_id ) INNER JOIN       
       bar_allowed b ON ( l.lorem_id = b.lorem_id )       
 WHERE f.foo_field = ?
   AND b.bar_field = ?

I haven't tested this so the syntax may be wrong.

Upvotes: 1

Related Questions