Topic: adding NULL to blank fields in UPDATE

[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.

Code: [Select]

<?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.