in the mysqli php library, in the bind_param() method one binds the parameters to the query. bind_param()'s first argument is types, a string where each character represents the datatype pass, eg, 's' for string.
$query = "update users set update_usr_id = ? where user_id = ?";
$arg1 = ($update_usr_id=$usr_id) ? '2011-01-01' : 'CURRENT_DATE';
$arg2 = $update_usr_id;
how can one represent NULL or CURRENT_DATE as a parameter?
for example, tried 'CURRENT_DATE' as string, but that posted as "0000-00-00". (as far as I can tell "0000-00-00", an illegal date, is a mysqlism for NULL for people who don't use NULL, which apparently is quite a few).
using parameters, how can one use NULL and CURRENT_DATE?
@Johan pointed out that NULL does work. on testing, this appears to be true, but is not. what happens is that, starting with the first NULL parameter, all parameters are set to NULL!
default values have nothing to do with this. There are many times I would wish to set a column to NULL or CURRENT_DATE in an UPDATE - not an INSERT - so default values play no part in the transaction.
also, the idea of writing a trigger to cover the inadequacy of mysqli is pretty bad programming - having written spaghetti triggers in my day, I am one to talk. triggers to update logs are one thing - triggers like this will be a constant maintenance nightmare with little odds the code will ever be maintained correctly.
mysqli parameters are limited and difficult to use. Not only do they not accept NULL and internal functions like CURRENT_DATE as parameters, each parameter to be passed by reference. (This means a parameter needs to be created for each parameter - which might be necessary for INOUT procedure arguments or SELECT INTO, but for normal dml is simply a waste of time.) On top of that, they are evidently not stored by the session or db to make subsequent calls more efficient.
(Compare to Postgres prepared statement, for example, which is named and my be re-used (howbeit only at the session level). This means the db does not need to be re-taught the statement; it also means the statement is already "planned", so not only the semantics but the execution plan is learned and re-used to increase speed.)
Currently writing something for MySQL that is, I hope, actually usable. Will post later.