2

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
cc young
  • 18,939
  • 31
  • 90
  • 148
  • 1
    Rather than trying to do this in your PHP code, would it not be better to design your database schema so those values are defaults for the given columns. That way all you then need to do is just not provide the data in your php code. – shawty Sep 07 '11 at 11:11
  • that does not work for two reasons: first, if trying to update an existent value, and second, having to provide two (or more) entirely different queries whenever a value might be null. – cc young Sep 07 '11 at 16:18

2 Answers2

0

If you want a default parameter, you can either specify a default in the database schema.
For current_date you need to set the datatype to timestamp not date, that way MySQL will automatically insert now() into the field upon missing data.

Or create a trigger

DELIMITER $$

CREATE TRIGGER BEFORE INSERT ON FOR EACH ROW
BEGIN
  IF new.mydate IS NULL THEN SET new.mydate = NOW();
END $$

DELIMITER ;

If you want to pass null just set the var to null:

$var = null;

Links
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

Johan
  • 74,508
  • 24
  • 191
  • 319
  • that does not work for two reasons: first, if trying to update an existent value, and second, having to provide two (or more) entirely different queries whenever a value might be null. – cc young Sep 07 '11 at 16:20
  • setting to NULL _does_ work correctly, even setting to NULL and not 0 for integer - my reporting was incorrect. thanks! – cc young Sep 07 '11 at 16:30
  • actually, setting to NULL does _not_ work. if it's the last parameter, it sets null; but if not last parameter, it and all parameters following it are set to NULL – cc young Sep 12 '11 at 10:09
-1

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.

cc young
  • 18,939
  • 31
  • 90
  • 148