Dealing with $smcFunc and Null values

[m.c]

I've got the following bit code -

Code Select Expand


$smcFunc['db_query']('', "
UPDATE {db_prefix}parts
SET manufacturer_id = {int:manu_id}, part_category_id = {int:cat_id}, part_name = {string:part_name}, part_year_start = {int:part_year_start}, part_year_end = {int:part_year_end}
WHERE part_id = {int:part_id}",
array(
'part_id' => $part['part']['id'],
'cat_id' => $_POST['part_cat'],
'part_name' => $_POST['part_name'],
'manu_id' => $_POST['part_manu'],
'part_year_start' => $_POST['part_year_start'],
'part_year_end' => $_POST['part_year_end']
)
);

It all works fine, however 'part_year_end' can be a null value.
What's the best way to handle fields/variables that may contain a null value?

In normal coding, I'd simply put conditionals in, and build the SQL query accordingly, however, it doesn't seem to be quite as easy with the new $smcFunc function.

Do I need to have two versions of the $smcFunc call, or is there some other easier way that I'm missing?

[Arantor]

Easiest solution I can think of - may not be the perfect solution but the only way I can see is to have it specified as {raw:part_year_end} and feed in either a number or 'NULL' (without the quotes)

[m.c]

That worked.

Have added in an extra if/else to populate a variable with the form value, and change the value to 'NULL' if the form value is empty.
I don't think it's the ideal solution, however it works for now, and it's in a section of code I'm wanting to rewrite anyway, so I'll live with it.


But it would be interesting to see how other people are handling queries/updates where there's optional variables/fields involved.

[Arantor]

Well, NULL never comes up in SMF itself, it's either set to use 0 or in the case of a row, the row never gets created.

[m.c]

I might change my code to use 0, as I've got conditional statements to pick up the null value anyway.
At least that way I'll still have the additional safety net of the smf validation (the form values should of already been validated by this point in the script)

[Arantor]

Then you can switch to using the {int:value} form rather than {raw}, but yeah, the DB layer is really a sanity check rather than doing your validation for you.