CODEWITHSUNDEEP
phpmysqldynamic
Mlagma
Mlagma

Reputation: 1260

Stored MySQL Procedures vs. Dynamic MySQL Queries

I'm developing a basic php based function that executes MySQL queries in order to sort the data in the database and present it to the user. As a general statement, is it considered best practice to simply construct static queries and to execute them as needed, or simply piece together elements of a MySQL statement based on conditions?

To narrow the question, I have a specific example:

Say I have a few drop-down menus that define the search on the user's end. Let one drop-down simply contain a list of names, say Bob Smith, and the two others a date range. From there, I can easily create a search along the lines of SELECT item FROM checkOUT WHERE... I can definitely write a general query with parameters to be executed when ALL three of the parameters are present. Consider the case when only a date range is selected regardless of individuals - that would require only two parameters and a different general query. And what if the data is spread across multiple tables? That has to be taken into account. I should add as well that my function is based on drop-down menus - not original user input.

Basically, what would be the best method to create the MySQL Query - prepared statements or dynamically created statements? Obviously, the end result will rely on a hybrid of the two, but to which side it leans the farthest is the issue. I'm asking this question as I am not too familiar with this particular side of handling multiple queries from one source. Typically, I simply have to execute one query to get what I need and work with it - not sorting.

Any insight is appreciated.

Upvotes: 0

Views: 241

Answers (1)

nathan hayfield
nathan hayfield

Reputation: 2685

I usually make a $where variable and an $order variable and then build them based on the filter input from the page. Then i just append that to the base $sql variable and run it. Seems to work great for me. Just don't forget to escape any nasty input from the user before running the query though.

Upvotes: 0

Related Questions