Hello, Hello.

I'm having a hell of a time (probably due to lack of sleep and deminished brain capacity, not that I had some to begin with) inserting null values. For example, not all fields are required.

lname VARCHAR(25) = last name (text) = required
fname VARCHAR(25) = first name (text) = required
hospital INT(11) = hospital (hospital_id linked to other table) = required
room VARCHAR(10) = room number (text or num) = not required
gender VARCHAR(1) = gender (text: m, f) = not required

Any help is greatly appreciated, I can provide more information as well at any time. THANK YOU ALL AGAIN! I LOVE THIS PLACE!

//Retrieve variables
  $ar_fields = array('lname', 'fname', 'hospital', 'room', 'gender');
    foreach($_POST as $key => $value) {
	if (in_array($key, $ar_fields)) {
	  //echo $key, ": ", $value, "<br>";
	  //If $value is empty then the text substituted will be NULL, otherwise $value.
	  //$value = ($value ? $value : "NULL");
	  $value = ($value ? "'".mysql_real_escape_string($value)."'" : 'NULL');
	  $$key = trim($value); //i.e: $name = Manny (trim whitespace before and after)
	}
} // end foreach

/*	if(!isset($gender)) { $gender = NULL; }*/

  //if no data submitted, display error and retry
    if ($lname=='' || $fname=='' || $hospital=='') {
	header("LOCATION: patient.php?q=error");
	exit;
  } else {
	//First Insert New Patient Record
       /* THIS IS THE INSANE MANUAL WAY OF DOING IT, WORKS!
		$patientSQL = "INSERT INTO patients 
		(lname, fname, room, gender, hospital_id) 
		VALUES 
		('$lname', '$fname', ";
		$patientSQL .= empty($room) ? "NULL" : $room;
		$patientSQL .= ", '$gender', '$hospital')."')";*/
      /*THIS IS SO CLOSE BUT THE DATA DOES NOT GET INPUTTED INTO MYSQL*/
		$patientSQL = "INSERT INTO patients 
		(lname, fname, room, gender, hospital_id) 
		VALUES 
		($lname, $fname, $room, $gender, $hospital)";

	echo $patientSQL.'<br>';
	mysql_query($patientSQL); // execute sql

    in reason not to use blank\empty instead of null, saves db space to.

      Thank you for the suggestion, however I'm more looking to correct the issue at hand which is why my code will not allow the variables to insert into the table and apply null values to those without values passed.

      If I need to supply the form code, I can just let me know.

        can you post the value of $patientSQL

          Sure, as you can see I echo the SQL out to confirm that the formatting is correct. And sure enough it is but it doesn't insert. Again, when I use the manual way (what I had commented out - see below) of inserting nulls it works along with commenting out this line earlier in my code: $value = ($value ? "'".mysql_real_escape_string($value)."'" : 'NULL');

                  $patientSQL = "INSERT INTO patients
        (lname, fname, room, gender, hospital_id)
        VALUES
        ($lname, $fname, $room, $gender, $hospital)";

          What patientSQL outputs is:
          Last Name: Rod
          First Name: Man
          Room: 400
          Gender: m
          Hospital_id: 1

          Again, Lastname, Firstname, and Hospital_id are required. Everything else can be null.

            you should use apostrofes in the SQL INSERT query if we are talking about strings, and add apostrofe not just those array keys which are in the required field.

            error handling is an important thing:

            mysql_query($patientSQL) or die(mysql_error() . " query.: " . htmlspecialchars($patientSQL) );

              Set the default value of the 'not required' columns to NULL. Then when you do not supply any other value they will have NULL stored instead.

              The main value of using NULL for a no-value column is that NULL always evaluates to FALSE and a NULL never equals another NULL while an empty string equals all other empty strings. This saves you having to specify an additional condition to exclude empty strings when querying for col1 = col2.

              ALSO: why are you still using mysql_query and consequently mysql_real_escape_string ???
              Surely by now you are using PHP 5 in which case you can use PDO and Prepared Statements. As the manual states:

              Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them.

              Using prepared statements means that you don't have to worry about quoting user input to prevent sql injections, nor worry about getting the correct quote syntax when you build raw sql in code: prepare the sql as a prepared statement with placeholders and then pass the vales as they are received. Just So Easy 😃

                Thank you Roger, the reason why I'm still using those methods is because I'm still just a beginner. However thanks to your post I will go out and purchase a beginners guide to php5 and really learn the in's and out's. Thanks again.

                  Perhaps a beginners' guide to MySQL as well - to learn the ins and outs of MySQL (assuming a beginners' guide would go to such depth)?

                    Write a Reply...