MySQL Forums
Forum List  »  PHP

Re: How do you dynamically add additional variables to a PDO SQL query?
Posted by: Peter Brawley
Date: November 20, 2019 11:03AM

You start by writing the detailed requirement for your parameterised query handler, for example ...

1 define a query parameter string $dynamic_query_param (say, "DYN_QRY_PARAM") as a placeholder, to be filled by a user at runtime, for any valid sql query on any available database

2 if there is a UI for users defining their own queries, build into it the capability of adding and removing such placeholders (and, preferably, testing the validity of such sql when appropriate values are substituted for it)

3 in your dynamic query handler, write a function or class method that detects the presence of at least one $dynamic_query_param in a user query

4 for such queries, build an interface for the user to enter runtime values for each instance of $dynamic_query_param in it; the module should be able to test the result and loop till the result is valid or until the user backs out

5 pass the result of #4 to your general query execution module

You may need to revise the above for your context.

When you're satisfied with the requirement statement, write its pseudocode. This will be different for PDO than for mysqli. I mostly use mysqli (I don't need PDO's database-agnostic generality, and I find the PDO object model cumbersome), so my implementation won't apply directly to yours. My context was a general purpose mysql database maintenance utility which already had a general mechanism for adding, editing and executing user queries. The job was to add in dynamic user params. Given a defined $qparamtag, that needed only three new functions or modules...

qry_is_parameterised( $qry ) returns true if $qry contains the query param tag, else false

qparamform( $qry ) returns an html form showing qry with all qparamtags replaced by html input elements for appropriate values

qparamprocess( qry ) returns qry with POSTed user inputs replacing qparamtags in query

The query execution module calls qry_is_parameterised( $qry ) for any query passed to it. If that result is true, it puts up qparamform( $qry ). The form runs. When the user hits Go, the POSTed result is passed to qparamprocess( $qry ) to substitute the user's inputs into the query text. Then the query executes.

It'll be more complicated with PDO, but the general idea should be doable.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.