通过php表单输入的mysql INT和DATE的空值

Question

I'm working on a form like this :

     <tr align="center"><!-- list A doc -->
            <td><b>Documents Collected:</b></td>
            <td>  
                <select name="listA_id">
                <option value="">List A documents</option>
                <?php
                include("connect-db.php");
                $list = mysql_query("SELECT doc_no, doc_name FROM hr_doc_master WHERE doc_id =1 ");

            while($row_list=mysql_fetch_array($list))
            {
                echo  "<option value=\"{$row_list[0]}\">{$row_list[1]}</option>";
            }
            mysql_close($connection);
    ?>
    </select></td></tr>
   <tr align="center"><td><b>Document No.</b></td>
    <td><input type="text" name="listA_doc_no"></td></tr>
            <tr align="center"><td>Expiry Date</td><td><input type="date" name="listA_doc_expiry"></td></tr>

           <tr align="center"><td>List B</td></tr>   
           <!----- list B ------->
            <tr align="center">
            <td><b>Documents Collected:</b></td>
            <td>  
                <select name="listB_id">
                <option value="">List B documents</option>  
                <?php 
                 include("connect-db.php");
                $list = mysql_query("SELECT doc_no, doc_name FROM hr_doc_master WHERE doc_id = 2");

            while($row_list=mysql_fetch_array($list))
            {
                echo  "<option value=\"{$row_list[0]}\">{$row_list[1]}</option>";
            }
            mysql_close($connection);
    ?>

                    </select>
                    </td>
            </tr>
            <tr><td>Document No.</td><td><input type="text" name="listB_doc_no" size =30 ></td></tr>
            <tr align="center"><td><b>Expiry Date</b></td><td><input type="date" name="listB_doc_expiry" size =30 ></td></tr>
           <tr><td colspan=2><hr/></td></tr>
           <tr align="center"><td>List C</td></tr>     
           <tr align="center">
            <td>
                    <b>Documents Collected:</b>
                </td>
                    <td>  
                    <select name="listC_id">
                        <option value="">List C documents</option>
                        <?php
        include("connect-db.php");
                            $list = mysql_query("SELECT doc_no, doc_name FROM hr_doc_master WHERE doc_id =3 ");

            while($row_list=mysql_fetch_array($list))
            {
                echo  "<option value=\"{$row_list[0]}\">{$row_list[1]}</option>";
            }
            mysql_close($connection); `?>`


                    </select>
                </td>
        </tr>
        <tr align="center"><td>Document No.</td><td><input type="text" name="listC_doc_no" size =30 ></td></tr>
        <tr align="center"><td>Expiry Date</td><td><input type="date" name="listC_doc_expiry" size =30 ></td></tr>

The user inputs will be POSTed on another page. Now its not necessary that user will select from all three lists. User may select from one list or two lists or from all three. This will give six cases list A only, B only, C only, AB, BC, AC, ABC. Now is there a way of adding null values to INT as well as DATE in mysql. I tried adding if else like this :

if($_POST['listA_doc_expiry'] ==""){$listA_doc_expiry = 'NULL' ; 
 $listA_id = 'NULL'; }
else{$listA_doc_expiry = $_POST['listA_doc_expiry'];
echo $listA_doc_expiry; }    

And The QUERY :

$result = mysql_query("INSERT INTO hr_information(consultant_id, listA_doc_no, listB_doc_no, listC_doc_no, listA_doc_expiry, listB_doc_expiry,
 listC_doc_expiry, listA_id, listB_id, listC_id) VALUES('$consultant_id','$listA_doc_no','$listB_doc_no','$listC_doc_no','$listA_doc_expiry','$listB_doc_expiry','$listC_doc_expiry','$listA_id','$listB_id','$listC_id');") or die(mysql_error());

It gives the following error Incorrect date value: 'NULL' for column 'listB_doc_expiry' at row 1 how should I enter a NULL date value and INT in DataBase. It works when I manually enter NULL(without '') directly in the query. P.S. I already have declared both as NULL while creating the database.

Answer 1

You're trying to insert the string 'NULL', rather than NULL. Your quotation marks in your VALUES part are causing the problem for NULL values.

Even more important is that your code is horribly vulnerable to SQL injection attacks. Rather than constructing a string to represent the statement, and putting the values into the string yourself, you should use a prepared statement, and then bind the values to the fields you introduce. There are lots of examples on SO of how to do that.

A nice bonus is that this will also get rid of your 'NULL' problem!