CODEWITHSUNDEEP
phpmysql
James
James

Reputation: 197

HTML form, set blank form to NULL

I have a HTML form; I want to be able to set it so that if a field is empty, the field in the DB will actually be NULL and not just have the word NULL in the field. I thought that using this code would help, but it just puts the word NULL in the field. PHP Code:

<pre>
<?php
            if (isset($_POST['oc_item'])) { 
            $oc_item = mysql_escape_string($_POST['oc_item']);
            $oc_itemdesc = (!empty($_POST['oc_itemdesc'])) ? $_POST['oc_itemdesc'] : NULL;

           $sql = "INSERT INTO catalog_dev (oc_item,oc_itemdesc)
            VALUES(''$oc_item','$oc_itemdesc')";

        mysql_query($SQL);
        if (mysql_query($sql)) {
            echo '<strong><em>Your data has been submitted</em></strong><br /><br />';
                } else {
            echo '<p>Error adding submitted info: ' . mysql_error(). '</p>';
        }
        }
        ?></pre>

HTML Code:

<pre>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

        <table>
        <tr>
            <td>Item Name</td>
            <td><input class="forms" type="text" size="50" maxlength="50" name="oc_item" /></td>
        </tr>
        <tr>
            <td>Item Description</td>
            <td><input class="forms" type="text" size="50" maxlength="50" name="oc_itemdesc" /></td>
        </tr>
</table>

        <p><input type="submit" value="Submit item" /></p>
        </form></pre>

I want the field to actually be NULL and not have the field contain the word NULL. Thanks in advance.

Upvotes: 3

Views: 3353

Answers (4)

Emrah Tuncel
Emrah Tuncel

Reputation: 749

The function that I use for the MySQL data from the HTML forms.

function emptyHtmlFormToNull($arr){
   foreach($arr as $key => $val){
       if(empty($val) || strtolower($val) === 'null'){
           $arr[$key] = null;
       }
   }
   return $arr;
}

Upvotes: 0

aufziehvogel
aufziehvogel

Reputation: 7297

If you want to write NULL to a MySQL database, you really have to put NULL there without quotation marks.

Like this:

INSERT INTO table (column, column2) VALUES ('mystring', NULL);

It’s always a bit effort if you want to do this manually by hand, because you would have to make if-conditions for the query.

Remember: PHP null != MySQL NULL. They both do not know each other at all.

But I am still wondering, what does that all have to do with the question name? (SELECT unless)

You could write your code like this:

$oc_item = mysql_escape_string($_POST['oc_item']);
$oc_itemdesc = (isset($_POST['oc_itemdesc']) && trim($_POST['oc_itemdesc']) != '') ? "'" . mysql_escape_string($_POST['oc_itemdesc']) . "'" : 'NULL';

$sql = "INSERT INTO catalog_dev (oc_item,oc_itemdesc)
        VALUES('" . $oc_item . "', " . $oc_itemdesc . ")";
# sorry for adding " . all the time, but I dislike variables within strings :D

But I have to admit I do not like that much either, as it moves the duty to wrap quotation marks around MySQL strings away from the SQL-query itself.

Upvotes: 1

Tim Withers
Tim Withers

Reputation: 12059

Try this:

$oc_itemdesc = (!empty($_POST['oc_itemdesc'])) ? "'".$_POST['oc_itemdesc']."'" : "NULL";

$sql = "INSERT INTO catalog_dev (oc_item,oc_itemdesc) 
    VALUES('$oc_item',$oc_itemdesc)";

As it stands you are adding 'NULL' instead of NULL so it is adding the words instead of the value.

Side Note: I would be careful and properly escape/encode the $_POST['oc_itemdesc'] as someone who put an apostrophe in the description would completely throw off the insert statement. For example: I don't like this would look like this:

$sql = "INSERT INTO catalog_dev (oc_item,oc_itemdesc) 
    VALUES('$oc_item','I don't like this')";//MYSQL Error

PPS: As it stands you are inserting TWICE:

mysql_query($SQL);//insert 1
if (mysql_query($sql)) {//insert 2

Upvotes: 0

paulsm4
paulsm4

Reputation: 121669

If you printed out $sql, you'd see that it's inserting , 'NULL'.

You need to modify your code so that it inserts the work "NULL" (without '' quotes) or, better, doesn't insert that parameter at all (eliminate the entire , 'NULL' part of the string.

Upvotes: 1

Related Questions