Posting (NULL) into a field from a form...

[Jim R]

I have one field which is set up as a Text field in my database.  What I'm wanting is if the field is blank in the form to just leave the field alone in the database.  I need it to be NULL.

 

OR...is there a way to query a database to recognize a field is blank?

[KevinM1]

If your backing DB column allows null, than all you need to do is simply not add that form field reference to your insert/update query.  The DB column will be null by default in that case.

[JonnoTheDev]

You could also do something like below

 

<?php
mysql_query("INSERT INTO database_table SET field_name=".(strlen(trim($_POST['field_name'])) ? "'".mysql_real_escape_string($_POST['field_name'])."'" : "NULL")."");
?>

 

If the field_name field has a value it will get stored else it will use NULL

[Jim R]

@nighslyr, the problem with that is it's part of a bigger form and not a required field.  In fact, I would say more times than not, it would be left blank.  It's coaches entering roster information, and they would likely put in some comments about top players or players needing to take on a bigger role.

 

In the comment section of the output, I only want to echo the names that have comments...and the comments.

 

It's the $msg variable below.  I should have posted the code initially.  My apologies.  The rest of this code works extremely well as intended, and I use its structure on two sites.

 

$playerFirst = $_POST['playerFirst'];
$playerLast = $_POST['playerLast'];
$tid = $_POST['tid'];
$school = $_POST['school'];
$feet = $_POST['feet'];
$inches = $_POST['inches'];
$year = $_POST['year'];
$position = $_POST['position'];
$msg = $_POST['content'];
$ppg = $_POST['ppg'];
$rpg = $_POST['rpg'];
$apg = $_POST['apg'];
$spg = $_POST['spg'];
$bpg = $_POST['bpg'];
$fgp = $_POST['fgp'];
$ftp = $_POST['ftp'];
$status = $_POST['status'];


/*
search for existing row
*/	

$sql = "SELECT msg_id FROM players as p
INNER JOIN schools as s
WHERE p.playerFirst='".mysql_real_escape_string($playerFirst)."'
AND p.playerLast='".mysql_real_escape_string($playerLast)."'
AND p.tid=$tid";

if(!$result = mysql_query($sql)) {
   die(mysql_error()."<br />Query: ".$sql);   
}

if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  /*
  update existing row
  */
  $sql = "UPDATE players SET 
  				  pschool='" . $school . "',
			  feet='".mysql_real_escape_string($feet)."', 
                  inches='".mysql_real_escape_string($inches)."',
			  year='".mysql_real_escape_string($year)."',
                  position='".mysql_real_escape_string($position)."', 
			  msg='".mysql_real_escape_string($msg)."', 
			  ppg='".$ppg."', 
			  rpg='".$rpg."',
			  apg='".$apg."',
			  spg='".$spg."',  
			  bpg='".$bpg."',
			  fgp='".$fgp."',
			  ftp='".$ftp."',
                  status='".$status."'				   
                  WHERE msg_id='".$row['msg_id']."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}
else {
  /*
  insert new row
  */   
  $sql = "INSERT INTO players SET 
              playerFirst='".mysql_real_escape_string($playerFirst)."',
              playerLast='".mysql_real_escape_string($playerLast)."',
		  tid='". $tid ."',
		  pschool='" . $school . "',
              feet='".mysql_real_escape_string($feet)."', 
              inches='".mysql_real_escape_string($inches)."',
		  year='".mysql_real_escape_string($year)."',
              position='".mysql_real_escape_string($position)."', 
		  msg='".mysql_real_escape_string($msg)."', 
		  ppg='".$ppg."', 
		  rpg='".$rpg."',
		  apg='".$apg."',
		  spg='".$spg."',  
		  bpg='".$bpg."',
		  fgp='".$fgp."',
		  ftp='".$ftp."',
		  status='".$status."'";
		  
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}

[Jim R]

I realize I might not have been clear.  I want the form to recognize that field is blank, and if it is do nothing.  I also want to make sure "do nothing" leaves that field as (NULL)

[ManiacDan]

I want the form to recognize that field is blank, and if it is do nothing.

Nothing at all?  If one field is emtpy, don't perform any actions or display any errors, just stop where you are?

 

If you want the field to not be updated unless there's content submitted, then you'll have to build your field list by checking to see which submitted fields are empty.

 

-Dan

[Muddy_Funster]

you could just change your SELECT statement to use

 WHERE (comments IS NULL AND comments != '')

[Jim R]

The only field I need left NULL is the comments (form)/msg (query) field.  If it's empty, the query just needs to bypass it because submitting it as blank just leaves the field in my data table blank. 

 

 

[Jim R]

Ok...this goes into the "duh" column.  I can just use !empty.  : )

Sometimes posting about it here helps me think through it.

 

 

 

if (!empty($comments['msg'])) {

 

echo '<span class="roster_player_name">' . $comments['playerFirst'] . ' ' . $comments['playerLast'] . ':  </span>';

 

echo $comments['msg'] . '<br><br>';

}

[Jim R]

Maybe a similar issue dealing with empty fields.  In the Update query, can I use a if(!empty) as part of the query for each field?  I want the User/coaches to be able to update their mistake, and the Update query allows the coach to do that if they match the player's first name and last.  It was originally set up to make sure the same player isn't entered multiple times, but it also gives the coach a chance to update any information. 

 

[Muddy_Funster]

that's all great untill someone puts in white space or a control character.  incidently, why are you only running some inputs through mysql_real_escape_string() and not all of them?

[Jim R]

I did it to eliminate special characters from names, such as apostrophes and hyphens from names.  They don't play nicely with WordPress' taxonomy. 

[Muddy_Funster]

ahhh, so if I want to inject some code on your site I'll just not use a name field, got it 

[Jim R]

Is there another way I should do it?

 

 

I guess if you can find it, get to it, have the same name as the coach with the same user id...go for it. 

[JonnoTheDev]

I gave you the code in the first reply I put on this topic that will do exactly what you are asking for. Why did you not implement this in your code for any fields that should be NULL when no data is entered into the corresponding field? It goes into your SQL query.

 

Here it is again

 

<?php
mysql_query("INSERT INTO database_table SET 
field_name=".(strlen(trim($_POST['field_name'])) ? "'".mysql_real_escape_string($_POST['field_name'])."'" : "NULL").",
another_field_name=".(strlen(trim($_POST['another_field_name'])) ? "'".mysql_real_escape_string($_POST['another_field_name'])."'" : "NULL")."");
?>

 

If you want more data validation checks then do them before the insert query i.e

 

<?php

/*
check the following fields contain letters only
*/
$fields = array('firstname','lastname');
foreach($fields as $field) {
if(strlen(trim($_POST[$field]))) {
  if(!ctype_alpha($_POST[$field])) {
   /* destroy POST data if not valid */
   $_POST[$field] = NULL;
  }
}
}

mysql_query("INSERT INTO database_table SET 
firstname=".(strlen(trim($_POST['firstname'])) ? "'".mysql_real_escape_string($_POST['firstname'])."'" : "NULL").",
lastname=".(strlen(trim($_POST['lastname'])) ? "'".mysql_real_escape_string($_POST['lastname'])."'" : "NULL")."");
?>