Enough posts that I don't want to quote 
Prepared statements do have their place and there are certainly times when doing a query in a loop might be the way to go. For example, when you need to do a query using the results of a previous query. Now, a lot of times you can do a subquery to get the same results but:
- It can make the query really complex
- Which can make it hard to understand
- And take a long time to execute
So it becomes a kind of trade off.
Building a query in code is pretty common when you have different pieces that might or might not be included. While that could be handled by separately written queries it can easily get unmanageable. Think about if you had three parts to a complex query that might or might not be present, you'd have to write 8 different queries to handle the possible inclusions.
Since I deal mostly in loosely typed languages I actually like the following system:
Code:
$result = db_query("
SELECT item.name, item.cost, stock.quantity
FROM (items AS item, locations AS location)
JOIN stock ON (stock.id_item = item.id_item
AND stock.id_location = location.id_location)
WHERE item.name='{string:itemname}'
AND location.name='{string:locationame}'",
array(
'itemname' => 'Mt Dew',
'locationame' => 'Fridge',
)
);
The function itself does the type checking, escapes the strings, etc. Added benefit is that by reading the query alone you know what the replacements represent and what their types are suppose to be.
Originally Posted by bithub
, there's also the problem that with a preparared statement you are actually introducing into your design a state method. But often the case is you are "prohibited" from doing that either because you are bound by the coding practices in place in your working environment to only create stateless methods for database connections, or because you know damn well it's just horrid to not close a connection you opened when you don't need it anymore.
CornedBee