CODEWITHSUNDEEP
phpmysql
xirukitepe
xirukitepe

Reputation: 1615

A null value becomes 0 in Mysql and PHP

I have 2 drop-down lists with different names, and I'm trying to query in just one field.

I'm using a jQuery function wherein if item 1 is selected, the drop-down list 1 will be displayed, and if the item 2 is selected, the drop-down list 2 will be displayed.

This is how I populated my drop-down list from the mysql database and tables:

     <div id="minquep">
       <label>Branch</label>
          <SELECT name="user_min"> 
              <OPTION VALUE="0">Choose a branch
              <?=$minq_options?> 
          </SELECT> 
     </div>
    <div id="albury">
        <label>Branch</label>
          <SELECT name="user_branch"> 
              <OPTION VALUE="0">Choose a branch
              <?=$al_options?> 
          </SELECT>

And this is how I insert queries into mysql by filling out the form with drop-down lists in it:

if (isset($_REQUEST['Submit'])) { 
$sql = "INSERT INTO $db_table(branch) values ('".mysql_real_escape_string(stripslashes($_REQUEST['user_branch'])).",".mysql_real_escape_string(stripslashes($_REQUEST['user_min']))."')";


if($_REQUEST['user_branch']= ""){

    ($_REQUEST['user_branch']) = NULL;
}


if($result = mysql_query($sql ,$db)) { 
echo '<script type="text/javascript">alert("The user has been added successfully!\n");return true;</script>';
echo "<meta http-equiv=\"refresh\" content=\"0;URL=add_user.php\">";
}

else { 
echo "ERROR: ".mysql_error(); 
} 
}

The testing scenario is that, I choose the value under <select name="user_min">. So I assume that sql will just bypass the result for user_branch because it is null. But it does prints '0' instead, after the insert query. For example, if the inserted ($_REQUEST['user_min']) value is "Brisbane" and the ($_REQUEST['user_branch']) value is null (because I didn't selected any value under the user_branch drop-down list), the branch field should just become "Brisbane", knowing that user_branch is NULL. But it does print "BRISBANE" with 0, like 0, Brisbane in my mysql table.

How can I fix this?

I already tried putting an if condition, it did not work.

if($_REQUEST['user_branch']= ""){

        ($_REQUEST['user_branch']) = NULL;
    }

I've also tried changing the user_min into same name user_branch, but it does not get the selected value, instead of Brisbane it just prints '0'

Upvotes: 0

Views: 3810

Answers (3)

Dilshi
Dilshi

Reputation: 553

$sql = "INSERT INTO reg(Name ,Email) values ('".mysql_real_escape_string(stripslashes($_REQUEST['Name']))."','".mysql_real_escape_string(stripslashes($_REQUEST['Email']))."')";

This is my code. Here I found error of Undefined index.

Upvotes: -1

TigerTiger
TigerTiger

Reputation: 10806

A few things -

  1. you are setting user_branch = NULL after the $sql.

  2. You are using quotes around value. It's fine non-Null values -you need to check if there is NULL value then don't use quotes in your $sql.

  3. You are using single "=" in your IF statement. This is failing your If statement. Change it to if($var == '')

Update

Some suggestions based on your existing code - however there are other best practices to achieve what you are trying to achieve....

$user_branch =  $_REQUEST['user_branch'];
$user_min =  $_REQUEST['user_min']; 
//you should validate above values first 

if$user_branch == "" || $user_min == "") {
    $db_value = "NULL"; 
} else 
{
$db_value = "'".mysql_real_escape_string(stripslashes($user_branch.','.$user_min))."'";
}


$sql = "INSERT INTO $db_table(branch) values (".$db_value.")";

Upvotes: 5

Nick
Nick

Reputation: 6346

If you don't want 0, then change:

<option value="0">Chose a Branch</option>

To:

<option value="">Chose a Branch</option>

Otherwise it will get passed through the form as 0

Also, your PHP code won't work as you want it to:

if (isset($_REQUEST['Submit'])) { 
  // do this before the query
  if($_REQUEST['user_branch']== ""){ // note the extra = in there, so you aren't assigning the variable, you are compairing
    $_REQUEST['user_branch'] = NULL; // no need for brackets round the variable
  }
  $sql = "INSERT INTO $db_table(branch) values (".mysql_real_escape_string(stripslashes($_REQUEST['user_branch'])).",".mysql_real_escape_string(stripslashes($_REQUEST['user_min'])).")"; // you had single quotes around everything inside the VALUES() function

  if($result = mysql_query($sql ,$db)) { 
    echo '<script type="text/javascript">alert("The user has been added successfully!\n");return true;</script>';
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=add_user.php\">";
  }
  else { 
    echo "ERROR: ".mysql_error(); 
  } 
}

Upvotes: 0

Related Questions