I'm new to mysql and php and wanted to clarify something on prepared-statements.
All the examples I've seen only add params for the actual data in the table, is it bad practice to paramaterise all the fields so there are less statements? e.g.
UPDATE ? SET ?=? WHERE ID=?
Thank you
You cannot parameterize identifiers. You can only parameterize data. Otherwise the main point of parameterization, the separation between statement structure and data, is pretty moot. Understand that parameterization is not just fancy copy-and-paste, it's a technique to make sure the database has a clear separation between the static parts (which it will take its instructions from) and the dynamic parts (which it will treat as data and data only).
Personally I don't really understand why everyone tries to minimize SQL as much as possible and doesn't want to write any. In a real world application there are a defined, finite number of SQL queries that an application will send to the database. There's going to be one query to find a user by username, one to calculate the number of foos in the bar table etc. etc. Write those queries once, long-hand. Have a method findUserByName() somewhere which contains the query to find a user by name written out in SQL. You don't need to dynamically cobble together every single query, you just need to write it once. That also gives you a good "inventory" of all the possible database interaction and allows hand-tweaking where necessary. </rant>
So, it's both not possible and misguided.
UPDATE statements are fixed, just like other queries. Your library does look nice though and surely is a good helper. Though to be honest, I'd trust database-level parameterized queries more... :)$model->save(); indeed easier. But this approach is either hard to demonstrate in the form of quick answer on SO (that's why nobody advertises ORMs in such questions, IMHO), and not always usable. Sometimes you have to just run insert query without creating a model. And of course, even ORM internals can benefit from such a helper function.Although excellent deceze's answer covers your question literally, there is something else to mention.
If you take a look at any major freamework, you will find a function... update() which works pretty the same way:
function update($table, $data, $id) {
so, the approach is quite popular in general.
Nevertheless, it's wrong and misguided as well.
Being initially inflexible, it quickly become unusable:
all these cases will make initially small & neat function into bloated monster.
But the remedy is simple.
What you really need is a placeholder for the every data type that may be added to the query. So, welcome to SafeMysql - a library that will fulfill your dreams:
$db->query("UPDATE ?n SET ?u WHERE id=?i", $table, $data, $id);
it is doing exactly what you want, but doing it proper way. And even such a complex query won't make it any trouble:
$data = array('field'=>$value,'field2'=>$value);
$ids = array(1,2,4);
$sql = "UPDATE t SET ts=unix_timestamp(), ip=inet_aton(?s),?u WHERE id IN(?a)";
$db->query($sql, $ip, $data, $ids);
Though it's purpose not to make "less [SQL] statements", but to write less PHP code and make your query safe, while keeping it familiar conventional SQL, without trade-offs like learning new language of some Query Builder which is merely an SQL dialect written in PHP.
Yes, obviously because it is not readable. Even with values, I would argue that it is better to have placeholders in key form. For example
UPDATE mytable SET `name`=:fullname WHERE ID=:id
