3 ms·

What's more, even with just bare prepared statements.. how do I use dynamically built SQL queries and prepared statements together? And please don't just say "d

What's more, even with just bare prepared statements.. how do I use dynamically built SQL queries and prepared statements together? And please don't just say "don't", at least not without telling me how to achieve what I need the proper way :) For example, let's say you have a query that gets search results, and depending on whether the visitor is logged in or not you also may want to know whether a given search result happens to be a favourited item. The way I understand it, I would have to do for example: $query_text = 'blah'; if (user is logged in) { $query_text .= 'SQL pertaining to favourites'; } $stmt = $dbh->prepare($query_text); if (user is logged in) { $stmt->bindParam(':user_id', $user_id); } Or am I missing something?

prodigal_erik 10y ago
Queries with and without user_id probably have different plans (e.g., table scan vs. index lookup) and need to be prepared separately. I've never seen an ORM that takes prepared statements and query planning seriously, generally they only emit trivial queries.
- PavlovsCat 10y ago
  Since the queries only get re-used per connection IIRC, that's moot for my case (personal derpy CMS) anyway, since they all just get used once per request, anything loopish is admin-related and fast enough anyway. What really bugged me though was the idea of having to repeat the decision logic that determines what the query ends up being twice... though I just realized that putting name/value pairs into an array while building the query, and then using that when the query is built to bind them to it, is probably fine.
krapp 10y ago
Just use separate statements. You should already know before that point whether or not the request is coming from a logged in user, so to me even the if statements are redundant - have functions or methods that just take parameters for and return the result set of a single SQL query, and figure out which function to call and how valid the results are elsewhere: function getResultsForUser($DB, $user_id) { $query="search for user :user_id, SQL pertaining to favourites"; $stmt=$DB->prepare($query); $stmt->execute(["user_id"=>$user_id]); return $stmt->fetchAll(PDO::FETCH_ASSOC); } function getResultsForVisitor($DB) { $query="blah"; $stmt=$DB->prepare($query); $stmt->execute(); return $stmt->fetchAll(PDO::FETCH_ASSOC); } Yes, it does mean more code, but that code will be much cleaner, more readable, more easily transportable (by not depending on a third party library) and less bug-prone.
- PavlovsCat 10y ago
  Deciding between user or not is the most simple case though. The most monstrous function I have by far considers things such as - is the user logged in, and maybe even owner of the profile we're at? - are we looking at all nodes, or the subnodes/subtree (don't ask) of a node? - do we want to display tags/authors/sources? - are we filtering by tag/author? - what's the display mode: list or full? There might be more too, that's from the top of my head... Yes, it's messy, but also kina DRY. To "unroll" that would be even more monstrous, that can't be the way D:
  - toast0 10y ago
    This actually feels a lot like localization -- unrolling all the ifs is actually the best way to do it. It feels like you're repeating yourself, but having the whole sentence / query together as one unit gives you the ability to understand the whole context -- often times in a database context, you can omit parts that are useless if you get the full context.
    - PavlovsCat 10y ago
      Unrolling would mean a lot of permutations.. in my example 576 if I'm not mistaken. Understanding the context or dead SQL code isn't really a problem in my case, since the various query bits are mostly orthogonal to each other, it's that they're optional that makes it a bit complex. But as I said in my other comment, I realized I can just remember the parameters/values as I build the query string, and then bind them all after creating the query. That way I can have my messy cake (that should feel so wrong but tastes so right) and eat it with a plate and a fork like a proper person.

prodigal_erik 10y ago

Queries with and without user_id probably have different plans (e.g., table scan vs. index lookup) and need to be prepared separately. I've never seen an ORM that takes prepared statements and query planning seriously, generally they only emit trivial queries.

PavlovsCat 10y ago
Since the queries only get re-used per connection IIRC, that's moot for my case (personal derpy CMS) anyway, since they all just get used once per request, anything loopish is admin-related and fast enough anyway. What really bugged me though was the idea of having to repeat the decision logic that determines what the query ends up being twice... though I just realized that putting name/value pairs into an array while building the query, and then using that when the query is built to bind them to it, is probably fine.

krapp 10y ago

Just use separate statements. You should already know before that point whether or not the request is coming from a logged in user, so to me even the if statements are redundant - have functions or methods that just take parameters for and return the result set of a single SQL query, and figure out which function to call and how valid the results are elsewhere: function getResultsForUser($DB, $user_id) { $query="search for user :user_id, SQL pertaining to favourites"; $stmt=$DB->prepare($query); $stmt->execute(["user_id"=>$user_id]); return $stmt->fetchAll(PDO::FETCH_ASSOC); } function getResultsForVisitor($DB) { $query="blah"; $stmt=$DB->prepare($query); $stmt->execute(); return $stmt->fetchAll(PDO::FETCH_ASSOC); } Yes, it does mean more code, but that code will be much cleaner, more readable, more easily transportable (by not depending on a third party library) and less bug-prone.

PavlovsCat 10y ago
Deciding between user or not is the most simple case though. The most monstrous function I have by far considers things such as - is the user logged in, and maybe even owner of the profile we're at? - are we looking at all nodes, or the subnodes/subtree (don't ask) of a node? - do we want to display tags/authors/sources? - are we filtering by tag/author? - what's the display mode: list or full? There might be more too, that's from the top of my head... Yes, it's messy, but also kina DRY. To "unroll" that would be even more monstrous, that can't be the way D:
- toast0 10y ago
  This actually feels a lot like localization -- unrolling all the ifs is actually the best way to do it. It feels like you're repeating yourself, but having the whole sentence / query together as one unit gives you the ability to understand the whole context -- often times in a database context, you can omit parts that are useless if you get the full context.
  - PavlovsCat 10y ago
    Unrolling would mean a lot of permutations.. in my example 576 if I'm not mistaken. Understanding the context or dead SQL code isn't really a problem in my case, since the various query bits are mostly orthogonal to each other, it's that they're optional that makes it a bit complex. But as I said in my other comment, I realized I can just remember the parameters/values as I build the query string, and then bind them all after creating the query. That way I can have my messy cake (that should feel so wrong but tastes so right) and eat it with a plate and a fork like a proper person.