PDO Postgres Invalid error

Question

I'm getting this problem when connecting to a database. I'm trying to write a query containing sub-queries to run on Postgres, usind PDO. Unfortunately I get an "Invalid parameter number: :sensor"

    $statement="select  di_timestamp, di_item_value
from data_item
where
fk_fc_id=(select fc_id 
        from field_column
        where 
        fc_description is like ':sensor'
        and
        fk_mds_id=( select mds_id 
                    from monitored_data_set
                    where fk_pa_id=(select pa_id 
                        from pilot_ambient 
                        where   
                        pa_ambient_name ilike ':room'
                        and 
                        fk_sp_id=(
                            select sp_id 
                            from School_Pilot 
                            where sp_description ilike '%:name%'
                            )
                        )
                    )
                )";
$query = $databaseConn->prepare($statement);

$query->execute(array(':sensor'=>'Room Temperature',':room'=>'rm1',':name' => 'school1'));

I think my problem is due to the escaping of the ' characters surrounding the :(item). I've tried using \ but that then creates a syntax error. I presume there's a convention I'm unaware of for PHP to successfully substitute the string and then it not cause an error in Postgres.

Thanks for looking at this, James

use in instead of = also this many sub queries will kill your db — Arun Killu
– Arun Killu, Commented Oct 31, 2013 at 13:41

Milen A. Radev · Accepted Answer · 2013-10-31 14:02:36Z

2

Try removing the single quotes surrounding the name of the parameters, something like this:

From => ':sensor' To => :sensor

In this link you will find an explanation.

edited Oct 31, 2013 at 14:02

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

answered Oct 31, 2013 at 13:59

Acácio Veit Schneider

3602 silver badges6 bronze badges

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

3 Comments

James Over a year ago

If you mean in the query, it doesn't work, I get this error,Exception called. SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "%" LINE 19: where sp_description ilike %$3%

Milen A. Radev Over a year ago

Try to remove the % sighs from the SQL statements and put them in the arguments, i.e. ':name' => '%school1%'));

James Over a year ago

Ah, the combination of moving the % signs into the variable and unquoting in the query worked.

Collectives™ on Stack Overflow

PDO Postgres Invalid error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related