adding NULL to blank fields in UPDATE

[ntroycondo]

I want to make sure that NULL is entered into column (varchar) when a form field is left blank by a user.

I am using something like this:

 

mysql_query("UPDATE User set name = '$name',
    email = '$email', WHERE USERID = '$userid'");

[petroz]

you can set your variables to null if they are

if(!isset($name)) { $name = 'NULL'; }

[Pikachu2000]

you can set your variables to null if they are

if(!isset($name)) { $name = 'NULL'; }

 

If you want the variable to actually be a NULL value, be aware that doesn't set the variable to contain a NULL value; it sets the value to a string with the value of 'NULL'. To set it to a NULL value, you'd need to not use quotes. If you want it to contain the string 'NULL', the above is fine.

if(!isset($name)) {
     $name = NULL;
}

[petroz]

Awesome! Learning something everyday!

[objnoob]

This is a tricky topic... When using PHP variables to create a MySQL statement you should NOT define the variable in PHP as data type NULL, but rather as a string data type 'NULL' like Petroz's example. $var = 'NULL';

 

Now keep in mind, MySQL has it's own syntax rules and using single quotes around 'NULL' in an SQL Statement is taken as a literal string, so...................

 

Before creating your MySQL Statement you should validate every PHP variable going into the SQL for content and if its not the string NULL nor a number wrap single quotes around as required in MySQL statements:

if ($var != 'NULL' || isnan($var){

  $var = "'".$var."'";

}

Then you can create the sql statment worry free of single quotes  $sql = "INSERT INTO tTable (ID, Name) VALUES ($var, 'Steve')";

 

[ntroycondo]

I am working just with just a string so: if(!isset($name)) { $name = 'NULL'; } will work fine for me.

 

But I haven't figured out the right spot to put the code where it works. Should it be on the form page, or the page with the update php code? Should it be before or after the mysql_query statement?  I tried putting it a bunch of spots but haven't got it to work yet.

[petroz]

Put it with the PHP code that processes the form data. Post the script and I am sure we can find a spot that works.

[ntroycondo]

Thanks in advance!

 

I have two PHP pages with one html form page that searches the DB and list results. Code below for both PHP pages.

Search php page:

<?php
// Get value from HTML form
$name = $_POST['name'];

$connection = mysql_connect('xxxxxxx');
if (! $connection) {
  die('Error connecting to database ' .
  mysql_error());
}

$name = mysql_real_escape_string($name);

// select the proper database (your username)
mysql_select_db('User');

// run the query with the properly escaped string
$result = mysql_query(
  "SELECT * FROM User WHERE name like '%$name%'"
);

// Check that there were results
if(!$result){
  die('No results ' . mysql_error());
}

echo "<form method='post' action='update.php'>";
while ($row = mysql_fetch_array($result)) {
  echo "User Info<br />\n";
  echo "<input type='hidden' name='userid' value='$row[uSERID]' />\n";
  echo "User Name: <input type='text' name='name' value='$row[name]' /><br />\n";
  echo "User Email: <input type='text' name='email' value='$row[email]' /><br />\n";
echo "<input type='submit' value='Save' />\n</form>"; 

// Print number of matching donors
echo 'There were ', mysql_num_rows($result), 'Matching Users';
// process results
while ($row = mysql_fetch_array($result)) {
  echo "User Info<br />";
  echo "User Name: $row[name]<br />";
  echo "User Email: $row[email]<br />";
  echo "User ID: $row[uSERID]<br /><br />";
}

?>

 

And my update php page:

 

<?php
// Get value from HTML form
$name = $_POST['name'];
$email = $_POST['email'];
$userid = $_POST['userid'];

// Connect using your username and password.
$connection = mysql_connect(xxxxxxxxx);
if (!$connection) {
  die("Error connecting to database " . mysql_error());
}
// Secure the data before it is used
$name = mysql_real_escape_string($name);
$email = mysql_real_escape_string($email);

// select the proper database
mysql_select_db("User");

// Create the query
$result =
  mysql_query("UPDATE User set name= '$name',
    email = '$email' WHERE USERID = '$userid'");

// Find number of affected rows
echo mysql_affected_rows()," row was updated";
?>

[petroz]

I am not too sure what script you want to put this on... Both scripts use the $name variable.. Just put it right underneath either variable at the top.

 

 

if(!isset($name)) { $name = 'NULL'; }

[ntroycondo]

if(!isset($name)) { $name = 'NULL'; }

Does not work for me.

 

if(isset($name)) { $name = 'NULL'; }

Does work fine but in the opposite way than desired

 

so i tried IF ELSE but it doesn't work either"

if(isset($email)) 
{
$email = $email;
}
else
{ $email = 'NULL';}

[petroz]

You could try empty...

if(empty())
{
    $name = 'NULL';
} else {
    $name = $_POST['name'];
}

 

But whats happening with !isset? Are you getting an error?

 

[ntroycondo]

Using 'if empt'y works.

 

Using '!isset' doesn't give any error.

 

I probably could also put an 'if' into the mysql_query SELECT statement.

[ntroycondo]

So it does work with

if(empty($name))
{
    $name = 'NULL';
} else {
    $name = $_POST['name'];
}

but if I try to add another if statement for email they both don't work. Do I need anything separating them?

if(empty($name))
{
    $name = 'NULL';
} else {
    $name = $_POST['name'];
}

if(empty($email))
{
    $email = 'NULL';
} else {
    $email = $_POST['email'];
}

[ntroycondo]

Now I think I just I want to add an IF into the mysql statement to add the 'NULL' string.

 

Not sure of the syntax to use:

mysql_query("UPDATE User set name= IF("'$name'='' ,NULL, '$name'"), 
  email= '$email' WHERE USERID = '$userid'");

[petroz]

I am pretty sure that wont work.. Anybody, correct me if I am wrong.

 

I really think you need to figure out whats going wrong with those if statements..

 

Can you post the structure of your db and the script as you have them now and explain the context in how you are using them and I will try to reproduce the problem you are having on my side.

[ntroycondo]

I suspect the original IF statement isn't failing at all because the empty string is seen as a value. So comparing string length would probably work.

if (strlen($email)) == 0 { $email= 'NULL'; }

[Pikachu2000]

Can you post the current code for the search and update pages?

[ntroycondo]

I've gotten it work for email or name, but not for both. This was using the if(empty) or using strlen.

But I'm sure there a cleaner way and for getting both name and email to do this.

 

Search page:

<?php
// Get value from HTML form
$name = $_POST['name'];

$connection = mysql_connect('xxxxxxx');
if (! $connection) {
  die('Error connecting to database ' .
  mysql_error());
}

$name = mysql_real_escape_string($name);

// select the proper database (your username)
mysql_select_db('User');

// run the query with the properly escaped string
$result = mysql_query(
  "SELECT * FROM User WHERE name like '%$name%'"
);

// Check that there were results
if(!$result){
  die('No results ' . mysql_error());
}

echo "<form method='post' action='update.php'>";
while ($row = mysql_fetch_array($result)) {
  echo "User Info<br />\n";
  echo "<input type='hidden' name='userid' value='$row[uSERID]' />\n";
  echo "User Name: <input type='text' name='name' value='$row[name]' /><br />\n";
  echo "User Email: <input type='text' name='email' value='$row[email]' /><br />\n";
echo "<input type='submit' value='Save' />\n</form>"; 

// Print number of matching donors
echo 'There were ', mysql_num_rows($result), 'Matching Users';
// process results
while ($row = mysql_fetch_array($result)) {
  echo "User Info<br />";
  echo "User Name: $row[name]<br />";
  echo "User Email: $row[email]<br />";
  echo "User ID: $row[uSERID]<br /><br />";
}

?>

 

Update page:

<?php
// Get value from HTML form
$name = $_POST['name'];
$email = $_POST['email'];
$userid = $_POST['userid'];

// Connect using your username and password.
$connection = mysql_connect(xxxxxxxxx);
if (!$connection) {
  die("Error connecting to database " . mysql_error());
}
// Secure the data before it is used
$name = mysql_real_escape_string($name);
$email = mysql_real_escape_string($email);

// select the proper database
mysql_select_db("User");

// Create the query
$result =
  mysql_query("UPDATE User set name= '$name',
    email = '$email' WHERE USERID = '$userid'");

// Find number of affected rows
echo mysql_affected_rows()," row was updated";
?>

[Pikachu2000]

I'm guessing the problems you were encountering were when the update script ran, so here is something to try.

 

<?php
// Get value from HTML form
if( !empty($_POST['userid']) ) {
$errors = array();
array_map(trim, $_POST);
if( intval($_POST['userid']) > 0 ) { // I'm assuming that userid is an integer value, if not the validation will need to be changed.
$userid = (int) $_POST['userid'];
} else {
	$errors[] = 'User Id value is invalid.';
}
if( !empty( $_POST['name']) ) {
	$name = $_POST['name']; 
} else {
	$errors[] = 'User name field empty.';
}
if( !empty($_POST['email']) ) {
	$email = $_POST['email'];
} else {
	$errors[] = 'Email field empty.';
}
}
if( empty($errors) ) {
// Connect using your username and password.
$connection = mysql_connect(xxxxxxxxx);
if (!$connection) {
  die("Error connecting to database " . mysql_error());
}
// Secure the data before it is used
$name = mysql_real_escape_string($name);
$email = mysql_real_escape_string($email);

// select the proper database
mysql_select_db("User");

// Create the query
$result =
  mysql_query("UPDATE User set name= '$name',
    email = '$email' WHERE USERID = '$userid'");

// Find number of affected rows
echo mysql_affected_rows()," row was updated";
} else {
echo '<font color="red"><em>Errors were encountered:</em></font><br />';
$i = 1;
$num = count($errors);
foreach( $errors as $val ) {
	echo "<font color=\"red\">$val</font>";
	if( $i < $num ) {
		echo "<br />";
	}
}
}
?>

[ntroycondo]

Thanks for advice. I'll keep your idea for $errors for the future.

 

It's working now for me by using strlen:

if ((strlen($name)) == 0) { $name= 'NULL'; }
if ((strlen($email)) == 0) { $email= 'NULL'; }

[Pikachu2000]

Wow. I completely blew off the main point of the validation (setting the variables to 'null'), didn't I? Must be a caffeine deficiency . . . Anyhow, glad it's working.