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.
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:
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.
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.
