Select List with a NULL that gets committed in SQL

Question

I have a select list like the following inside of a form.

<select name="0[voicemail]" >
<option value="" selected="selected"></option>
<option value="800">800</option>
<option value="801">801</option>
<option value="802">802</option>
<option value="803">803</option>

<option value="805">805</option>
<option value="807">807</option>
<option value="808">808</option>
<option value="809">809</option>
<option value="810">810</option>
<option value="811">811</option>
<option value="820">820</option>
<option value="830">830</option>
<option value="831">831</option>

<option value="9778">9778</option>
<option value="9995">9995</option>
</select>

This was generated by some Kohana PHP code.

     $id = 0;
      $disabled = '';
      foreach ( $line_detail as $line ){
          echo '<tr class="d'.($id & 1).'">';
          echo '<td>'.$line->did.form::hidden($id."[did]", $line->did).'</td>';
          echo '<td>'.form::input($id."[cid_prefix]", $line->cid_prefix).'</td>';
          echo '<td>'.$line->type.'</td>';
          echo '<td>'.form::input($id."[ivr_context]", $line->ivr_context, "disabled='true'").'</td>';

          if ($line->ivr_context != ''){
              $disabled = "disabled='true'";
              echo '<td>'.form::input(array('name'=>$id."[dial_timeout]", 'size'=>15, 
                                            'maxlength'=>3), $line->dial_timeout, $disabled).
                  form::hidden($id."[dial_timeout]", $line->dial_timeout).'</td>';

              echo '<td>'.form::dropdown($id."[voicemail]", $phones, $line->voicemail, $disabled).
                  form::hidden($id."[voicemail]", $line->voicemail).'</td>';
          } else {
              echo '<td>'.form::input(array('name'=>$id."[dial_timeout]", 'size'=>15, 
                                            'maxlength'=>3), $line->dial_timeout, $disabled).'</td>';
              echo '<td>'.form::dropdown($id."[voicemail]", $phones, $line->voicemail, $disabled).'</td>';
          }

          echo '<td>'.form::input($id."[notes]", $line->notes).'</td>';
          echo "</tr>";
          $id++; 
      }

Not everything shown, but basically the options are in the $phones variable.

Now the problem. When I use a form submit all is fine until I choose the empty value in the submit. This is inside a method where $detail is equivalent to $_POST

 foreach($detail as $key => $val){
       $this->db->query("UPDATE dids SET cid_prefix=?, dial_timeout=?, voicemail=?, notes=? WHERE did=?", 
         array($val['cid_prefix'], 
             $val['dial_timeout'], 
             $val['voicemail'], 
             $val['notes'], 
             $val['did']));                   

The problem here is that I set the value for the empty option to be "NULL", but because Kohana adds commas around everything it inserts it'll try to put "NULL" into the database instead of NULL. In this case this will violate a foreign key constraint.

Is there a simple way to deal with NULL in PHP/Kohana so that I don't have to check for blank and rewrite each query that might contain NULL more than one time. What happens when you could get multiple valid NULLs? Surely there is a way to deal with these types of situations simply?

Answer 1

Placing this code before the database update seems to fix the problem. I.e. set $val['voicemail] to the PHP constant NULL.

if (empty($val['voicemail'])){
   $val['voicemail'] = NULL;
}

Then I can continue to use the original SQL statement. Kohana seems to behave itself and NULL is set through in the update statement.

Answer 2

You can use the DB::expr method which will not escape the value in the query. That way you insert a raw NULL

However, I'm amazed you've used a object orientated framework and yet chucked out all the advantages by creating a mess of a form. Job security?