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

Question

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)

May be you need joins? Or foo_allowed and bar_allowed will have the same values over the rows. — sybear
– sybear, Commented Apr 8, 2013 at 12:48
@Jari Maybe a join is a good idea indeed, but for the foo_allowed and bar_allowed, I get those values from to different tables so how can they become the same values over the rows? — Albert Bakker
– Albert Bakker, Commented Apr 8, 2013 at 13:02
Also, I tried the same query in mysql and it worked just fine.. — Albert Bakker
– Albert Bakker, Commented Apr 8, 2013 at 13:03
What is the result you want to see? Could you add to your question a sample output what you expect from the query? — Kovge
– Kovge, Commented Apr 8, 2013 at 13:08
@Kovge and Jari thanks for the suggestions you made. I tried a simpler query and that worked..I think i will go for the join query — Albert Bakker
– Albert Bakker, Commented Apr 8, 2013 at 13:35

Phil · Accepted Answer · 2013-04-09 16:06:01Z

1

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.

answered Apr 9, 2013 at 16:06

Phil

2722 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

byrnedo · Accepted Answer · 2014-10-09 11:19:43Z

1

Have you tried calling the execute directly without the bind.

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

answered Oct 9, 2014 at 11:19

byrnedo

1,47510 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related