News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Inserting null for an int field

Started by cgeek, July 02, 2015, 11:39:43 AM

Previous topic - Next topic

cgeek

Hi folks.  I am trying to find out if there is a way to insert a null value on an int field in MySQL.  The field allows for NULLs but I can't seem to get past doing it for db_queries which include {int:field_name}.  If I set $field_name to NULL it says it is missing.  If I set is to 'NULL' is doesn't like as it expects an int not a string.  Is there away to pass in NULL for int?

Livaco

Can you display the edited code for me? I think your doing it wrong.

cgeek

Receiving form post:

$item_id = (int)$_REQUEST['item_id'];
$item_name = $smcFunc['htmlspecialchars']($_REQUEST['_item_name'], ENT_QUOTES);
$rating = !empty($_REQUEST['rating']) ? (int)$_REQUEST['rating'] : NULL;   //  <-- Here is my issue

if ($item_id == 0)
fatal_error($txt['err_no_item_id'], false);

$smcFunc['db_query']('', 'INSERT INTO {db_prefix}ratings
(item_id, item_name, rating)
VALUES ({int:item_id}, {string:item_name}, {int:rating})',
array(
'item_id' => $item_id,
'item_name' => $item_name,
'rating' => $rating,
)
);

Suki

Don't insert a null value, you don't really needed, just use a boolean 0 for empty and 1/another number for non empty.

$rating = !empty($_REQUEST['rating']) ? (int)$_REQUEST['rating'] : 0;
Disclaimer: unless otherwise stated, all my posts are personal and does not represent any views or opinions held by Simple Machines.

cgeek

Quote from: Suki on July 02, 2015, 12:11:48 PM
Don't insert a null value, you don't really needed, just use a boolean 0 for empty and 1/another number for non empty.

$rating = !empty($_REQUEST['rating']) ? (int)$_REQUEST['rating'] : 0;

The problem is that 0 and NULL, in this case, have very different meanings.  These are ratings of which 0 is considered the worst, while NULL means it hasn't been rated yet.  I am porting this over from a .net site where these rules were already in place so I really need to be able to have this particular field be inserted as NULL.  Is there no way to do that without circumventing the db_query int validator?  I can do that, but I just thought there must be some way SMF would allow for this.

Suki

There are multiple ways to mitigate this, starting with not recording any rating if there isn't anything to record which would be the optimal thing since it avoids filling the DB with null entries.

You can use -1  as a replacement for null. You don't really have to explicitly use null, you can use whatever other variable you want as a replacement.


If you really really want to insert a null var then use something like this:

$item_id = (int)$_REQUEST['item_id'];
$item_name = $smcFunc['htmlspecialchars']($_REQUEST['_item_name'], ENT_QUOTES);
$rating = !empty($_REQUEST['rating']) ? (int)$_REQUEST['rating'] : NULL;   //  <-- Here is my issue

if ($item_id == 0)
fatal_error($txt['err_no_item_id'], false);

$smcFunc['db_query']('', 'INSERT INTO {db_prefix}ratings
(item_id, item_name, rating)
VALUES ({int:item_id}, {string:item_name}, {raw:rating})',
array(
'item_id' => $item_id,
'item_name' => $item_name,
'rating' => is_null($rating) ? var_export($rating) : $rating,
)
);


Disclaimer: unless otherwise stated, all my posts are personal and does not represent any views or opinions held by Simple Machines.

cgeek

Thank you.  I forgot about the 'raw' validator.  That worked fine except that I had to add the second parameter of 'true' to the var_export call so that it is returned rather than output.


$item_id = (int)$_REQUEST['item_id'];
$item_name = $smcFunc['htmlspecialchars']($_REQUEST['item_name'], ENT_QUOTES);
$rating = !empty($_REQUEST['rating']) ? (int)$_REQUEST['rating'] : NULL;   //  <-- Here is my issue

if ($item_id == 0)
fatal_error($txt['err_no_item_id'], false);

$smcFunc['db_query']('', 'INSERT INTO {db_prefix}ratings
(item_id, item_name, rating)
VALUES ({int:item_id}, {string:item_name}, {raw:rating})',
array(
'item_id' => $item_id,
'item_name' => $item_name,
'rating' => is_null($rating) ? var_export($rating, true) : $rating,
)
);

Advertisement: