Reuse parameter and InterpolateQuery

[Antjac]

Hi,

If I run this query : "INSERT INTO myTable (id,Mycolumn) VALUES (:myId, :myColumn) ON DUPLICATE UPDATE Mycolumn = :myColumn)"

and params are : array(myColumn => "Toto", myId => 125)

If I want to see the query by using interpolateQuery , I've only the first parameter which is replaced : " INSERT INTO myTable (id,Mycolumn) VALUES (125, "Toto) ON DUPLICATE UPDATE Mycolumn ="") "

But the query is well executed...

Is there a way to show the full query if a parameter is used more than one time ?

[noahheck]

Hi @Antjac,

Thanks for the report, however, this isn't a limitation of the library, but rather the expected behavior within PHP. From the official documentation on the prepare method (http://php.net/manual/en/pdo.prepare.php):

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

While we must have a unique placeholder for each value we want to pass in, we can re-use the variables as needed, so you could update your process as follows:

INSERT INTO myTable (id, Mycolumn) VALUES (:myId, :myColumn) ON DUPLICATE UPDATE Mycolumn = :myColumn2

Then have your input array structured as is here:

array(
    "myId"      => $myId,
    "myColumn"  => $myColumn,
    "myColumn2" => $myColumn,
)

It's probably worth pointing out that emulation mode is on by default. This likely explains why the query will execute correctly even though result of the call to interpolateQuery doesn't show the full query.

I looked around the documentation to try to find out if we can determine if the connection is currently configured to emulate prepared statements, but we aren't able to access that attribute of the PDO class (http://php.net/manual/en/pdo.getattribute.php). Assuming emulation seems like it could be dangerous, so I don't want to make that assumption with this library.

Thanks again for the report though. Happy coding!

[Antjac]

Thx for your answer :)

wish u the best ----- Mail original ----- De: "Noah Heck" notifications@github.com À: "noahheck/E_PDOStatement" E_PDOStatement@noreply.github.com Cc: "Antjac" antjac.tux@free.fr, "Mention" mention@noreply.github.com Envoyé: Jeudi 1 Septembre 2016 04:19:49 Objet: Re: [noahheck/E_PDOStatement] Reuse parameter and InterpolateQuery (#8)

Hi @Antjac ,

Thanks for the report, however, this isn't a limitation of the library, but rather the expected behavior within PHP. From the official documentation on the prepare method ( http://php.net/manual/en/pdo.prepare.php ):

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

While we must have a unique placeholder for each value we want to pass in, we can re-use the variables as needed, so you could update your process as follows: INSERT INTO myTable (id, Mycolumn) VALUES (:myId, :myColumn) ON DUPLICATE UPDATE Mycolumn = :myColumn2

Then have your input array structured as is here: array( "myId" => $myId, "myColumn" => $myColumn, "myColumn2" => $myColumn, )

It's probably worth pointing out that emulation mode is on by default. This likely explains why the query will execute correctly even though result of the call to interpolateQuery doesn't show the full query.

I looked around the documentation to try to find out if we can determine if the connection is currently configured to emulate prepared statements, but we aren't able to access that attribute of the PDO class ( http://php.net/manual/en/pdo.getattribute.php ). Assuming emulation seems like it could be dangerous, so I don't want to make that assumption with this library.

Thanks again for the report though. Happy coding!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub , or mute the thread .