CODEWITHSUNDEEP
phppostgresql
James
James

Reputation: 1852

PDO Postgres Invalid error

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

Upvotes: 0

Views: 107

Answers (1)

Acácio Veit Schneider
Acácio Veit Schneider

Reputation: 360

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.

Upvotes: 2

Related Questions