Populating Table with Nulls through PHP? 2

[Tasuki]

Hope that Subject describes my problem well enough...

Anyway...

I have a form, about 5 fields are required and the rest could be null inserted into database. If a user fills out every field on the form, it inserts into the MySQL database perfectly, all data is retrievable. BUT, if they left even one (non-required) field blank, I would get a MySQL error... something like this:

INSERT INTO tblNetDesc (SubSample, StretchMeshSize, Twist, NumStrands, Floats, Rope, Weight, RopeDiam1, RopeLngth1min, RopeLngth1max, RopeDiam2, RopeLngth2min, RopeLngth2max, RopeDiam3, RopeLngth3min, RopeLngth3max, TrimmedEdge, Foul, BreaksEasily, Initial, InitialDate, NetClassID, ConstructionID, ColorID, NetTypeID, TrimColorID, PictureName, Comment, TwineDiam) VALUES ('A', , '', , '', '', , , , , , , , , , , '', , '', '', , 12, , , 2, , '', '', )You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' '', , '', '', , , , , , , , , , , '', , '', '',

Is there a reason why MySQL can not tell that a blank should be stored as a NULL automatically in MySQL? BTW, this is the 2nd query of a multiple insert, the first went through fine, in this query, only the 12 (which is the ID of the first insert) and 'A' is required in the database. One other thing is that all fields except the ones that are required has been set DEFAULT NULL in MySQL.

Does anyone know what the like problem is? I'm not thinking I need to check every field to see if it isempty() and setting it to NULL and sending NULL to the database rather than a blank.

Thanks for looking at my issue...

-T

 

[Tasuki]

*Spelling fix, for last part*

Does anyone know what the likely problem is? I'm now thinking I need to check every field to see if it isempty() and setting it to NULL and sending NULL to the database rather than a blank.

 

[kaancho12]

hi,
you could do the check to see if the field is empty and send "NULL" to the database or when entering the values you could send it this way:
"INSERT INTO table (column name) VALUES ('$field1', '$field2', '$field3')"
if you insert a "single quote" before and after every field that you are inserting that should work.
ko12

 

[Tasuki]

Hmmm... even if the field is an integer? Wouldn't that make it a string with the quotes?

 

[kenrbnsn]

How are the fields defined in MySQL? If they have a default value of NULL, don't even set them in your query. Here's code similar to how I would handle it.
(The following code assumes you have a Submit button named "submit" and that the names of you input fields match the names of the fields in your database)

<?

if (isset($_POST['submit'])) {
  $tmp = array();
  foreach ($_POST as $key => $val)
      switch ($key) {
         case 'SubSample':
         case 'StretchMeshSize':
         case 'Twist':
//           etc....
             if (trim($val) != '')
                 $tmp[] = 'set ' . $key . " = '" . urlencode(stripslashes($val)) . "'";
             break;
         }
  if (!empty($tmp)) {
     $q = 'insert into tblNetDesc ' . implode(',',$tmp);
     $rs = @mysql_query($q) or die('Could not insert into DB, query: ' . $q . '<br>' . mysql_error()); }
}

This will create an Insert query using only those fields that have been filled in. It also applies urlencode() to each value so you won't get affected by malicious inputs. Just remember to urldecode() the information before using it.

Ken

 

[Tasuki]

Hey kaancho12,

Just tried it your way by adding all the '', it inserted perfectly this time. I was afraid I would have to verify for every single field (there's plenty).

Thanks for your help!

-T

 

[sleipnir214]

Part of the problem is that there is a difference between a column that is blank and a column that is NULL. You must specify one or the other in your query.

Generally, if a column can take a NULL value and my query isn't going to insert a value for that column, I don't reference the column in my query.

Also, I've found it useful to create a form with fieldnames like:

<html><body>
<form method="post" action="show_post.php">
First Name:<input type="text" name="[red]db_field[0][/red]"><br>
Last Name:<input type="text" name="[red]db_field[0][/red]"><br>
<input type="submit"></form></body></html>

When the fields are submitted, all the values will be in an array. My show_post.php reads:

<?php
print '<html><body><pre>';

print_r ($_POST);

print '</pre></body></html>';

And will typically output something like:

Array
(
    [db_field] => Array
        (
            [0] => a
            [1] => b
        )

)

So my code to construct the loop looks something like:

<?php
$columnnames = array ('fname', 'lname');

$query_columns = '';
$query_values = '';
foreach ($_POST['db_field'] as $fieldname => $fieldvalue)
{
	if (strlen($fieldvalue) != 0)
	{
		if (strlen($query_columns) != 0)
		{
			$query_columns .= ',';
		}
		
		$query_columns .= $columnnames[$fieldname];
	
		if (strlen($query_values) != 0)
		{
			$query_values .= ',';
		}
		
		$query_values .= "'" . $fieldvalue . "'";
	}
}

$query = "INSERT INTO tablename (" . $query_columns . ") VALUES (" . $query_values . ")";

print $query;
?>

Which will only include those columns required for the data submitted to the script.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Tasuki]

Didn't catch your message Ken, but that is plenty helpful too.

Thanks,

-T

 

[Tasuki]

--new posts while posting...

Great post sleipnir214,

The code is very clean and something I should adopt when doing inserts like this.. now thinking whether to try it your way altogether. This is an excellent reference for any future scripts I will do. Thank you.

-T

 

[Tasuki]

Not analyzing everything clearly, Ken's post is similar to sleipnirs... ty both. I will do it your way from now on.