PHP & MySQL Null vs Not Null Problem Help

Dreamweaver Script for Null vs Not Null Conversion Problem

[Sherif]

Hi I am using Dreamweaver to assist me in writing a MySQL insert script to one of my databases. the issue is that in the database table, i have the fields types set to NOT NULL. when i leave some fields blank and submit the form, the empty fields should pass blank values ( "" ) which should be accepted by MySQL since it is not considered as not null.

The problem is that MySQL doesn't accept it. i think this is because of the GetSQLValueString that dreamweaver uses. Its definition is the first couple of code lines.

I am kind of a Noob here, and can someone help me in telling me what i should change in the code below to allow the blank fields to be sent as "" to the MySQL Table.

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_mydatabase, $databaseconnection);
$query_userInfo = "SELECT * FROM users GROUP BY Category ORDER BY Category ASC";
$userInfo = mysql_query($query_userInfo, $Edatabaseconnection) or die(mysql_error());
$row_userInfo = mysql_fetch_assoc($userInfo);
$totalRows_userInfo = mysql_num_rows($userInfo);

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

$insertSQL = sprintf("INSERT INTO userInfo (name, `password`, `access`) VALUES (%s, %s, %s)",
GetSQLValueString($_POST['name'], "text"),
GetSQLValueString($_POST['pass'], "text"),
GetSQLValueString($_POST['access'], "text"),

.....etc.....
?>

thanks in advance, and i hope to hear some of your solutions soon. Thanks

Sincerely,
Sherif

[TheMadScientist]

I'd set a default value for the fields if you are setting the them to NOT NULL...
It means they can't be empty: NULL, 0, ""

NOT NULL = INFORMATION IS REQUIRED

BTW: Welcome to WebmasterWorld!

AND: I have no clue on the DreamWeaver stuff, even though I use it for most of my coding... I actually haven't ever used it to generate code for me and I don't know what to tell you to change, because I don't want to read that much code... Sorry. There's two drawbacks to my posts: 1.) I'd rather give a starting point or direction than do all the work for people, because generally they learn more if they have to figure out what they're doing for themselves. 2.) I don't usually read more than I need (or think I need) to do the preceding, so sometimes I miss parts of the post or don't answer all the questions.

[rocknbil]

The problem is that MySQL doesn't accept it.

When you say "doesn't accept it" what exactly does it do? mySQL error, enter NULL for a value?

You might try changing these lines like so. Don't know if it will work, but worth a shot. I hate DW. :-)

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : ""; // not null, blank string
break;
case "long":
$theValue = ($theValue != "") ? floatval($theValue) : "0.0000"; // ADDED, like double below
break;
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "0"; // not null, it's int, so zero
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "0.00"; // depends on the way the DB is set up, might be 0.0000000000
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "000-00-00"; // empty date value
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; // being a func, this will be ''
// and no telling what mySQL will do, likely will
// be the blank string
break;
}

[Sherif]

No.... when i post the form, i get a blank page saying:

Column 'Name' cannot be null....

when i removed the NULL term from the case Text, i recieved the following message.

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 ' 'test', , , , , , , , , )' at line 1

can someone please explain what the components of this statement means?
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

like the "." , the "?" , and the " (....)" etc... thanks.

The strange thing, is that when i update a recently added record, i get no error in terms of blank fields. i only get this error when inserting new data. I am also using it in the update form.

Thank you so much for your support up until now.

[TheMadScientist]

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

It's a 'short hand' if statement...
$theValue = Either $theValue if $theValue is not (!=) empty() IOW if $theValue is not "" it is what it is, otherwise $theValue="NULL";

The question mark denotes 'if' ... If the () pattern evaluates to TRUE the left of the : is the result, else the right of the : is the result.

The ".$Variable." is concatenation...
"STRING ".$Variable." STRING" is easier on the processor than "STRING $Variable STRING" even though they both give you the same thing.

<?php
/* For a Visual */
$Varible='variable';
$Var2="STRING ".$Variable." STRING";
$Var3="STRING $Variable STRING";

echo $Var2.'<br>'.$Var3;
?>

Not sure on the error... I don't use sprintf(), but it could be you're trying to insert unquoted values into a non-numeric data type:

$insertSQL = sprintf("INSERT INTO userInfo (name, password, access) VALUES ('%s', '%s', '%s')";

[Sherif]

Thanks for the very quick response,

using the single quotes '%s' method did not work, i still get the same error

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 ' 'test', , , , , , , , , )' at line 1

But thanks for the explanation. When you explained the if statement, all what i did was

instead of:-

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

I Did this:-

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "'" . $theValue . "'";

Which basically takes it into a circle, and allowed it to work add trick MySQL in accepting it, which Fischerlaender explains in the third post of
MySQL NULL and NOT NULL? [webmasterworld.com] here at webmasterworld, that

If your "not null" field is a text type (TEXT, VARCHAR, ..) then it's not null, but empty

, and since all of my fields are VARCHAR, then it should accept it without any problems i suppose.

So again thanks.... if there is something risky or wrong in the concept of the code please tell me, but as far as i tried, it seems to work, but i am not sure of any of the consequences on the PHP & MySQL script...

Thanks a lot for the support.

Sincerely,
Sherif

[TheMadScientist]

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "'" . $theValue . "'";

That actually does nothing except add single quotes to the string...
It's the same as saying $theValue="'".$theValue."'";

IMO You can do the same thing more efficiently by just adding ' ' around the variable passed to MySQL in the INSERT.

echo $insertSQL = sprintf("INSERT INTO userInfo (name, password, access) VALUES ('%s', '%s', '%s')";

Will show you the actual insert... If that's the actual error below it looks like you are trying to enter blank (NULL) values into a col defined as NOT NULL.

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 ' 'test', , , , , , , , , )' at line 1

It's what we were trying to get you away from.

Why not just change the col types to NULL so if there is not information entered they default to NULL or 0 depending on col type? All NOT NULL does is require you to enter something into the col, and since it looks like you're trying to enter blank or 'empty' values the information must not be required, otherwise you'd be trying to figure out why the information is not being passed rather than trying to figure out how to make your DB accept an empty entry... There are two simple and easy ways to make your DB accept empty values:

1.) Set your col type to NULL.
2.) Set your col to have a default value.

I wouldn't make it tougher than it is...

Just use one of the two above solutions then rather than worrying about how to switch the value PHP passes to something your DB will accept you can set it to either accept an empty value or set a predefined value you can associate to 'nothing was entered by PHP'.

[TheMadScientist]

Personally, I'd write a standard INSERT statement using mysql_query() and forget about using sprintf() and the function to define the types of information entered. There are much less complicated ways to do what you are trying to do and personally, I prefer simple, straight through PHP...

/* Connection Info Here */

$Name=mysql_real_escape_string($_POST['name']);
$Password=md5($_POST['pass']);
$Access=mysql_real_escape_string($_POST['access']);

$Insert="INSERT INTO userInfo (name,password,access) VALUES ('".$Name."','".$Password."','".$Access."')";
mysql_query($Insert);

Also, rather than the single lines above (which many here use, are easy to understand, are easy to get your head wrapped around, and will suffice) I would use a conditional to check and make sure all three are set and entered using the correct characters prior to inserting, and if they are not, return to the form and ask for the corrections to be made...

Here's an example:

if(isset($_POST['name']) && preg_match('#^[a-z\s\']{4,30}$#i',$_POST['name'])) { 
 $Name=htmlentities(trim($_POST['name']),ENT_QUOTES); 
} 
else { 
 $error.='<p>Your Name Appears to be Entered Incorrectly. 
 Please double check and make sure you are only using letters, 
 apostrophies and spaces. You are allowed 4 to 30 characters.</p>'; 
} 

BTW: One of my favorite searches while learning (and still):

PHP functionHere(), so to figure out the above search for:
PHP mysql_real_escape_string()
PHP md5()
PHP preg_match()
PHP htmlentities()
PHP mysql_query()
PHP trim()
PHP isset()

The first result is usually the correct page on the PHP website in Bing or Google.

[rocknbil]

When you explained the if statement, all what i did was ....

Look at your original:

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

Then the error,

Column 'Name' cannot be null....

Now I got it, and got why it's broken. YES, my changes will error because here is what happens.

When you go to insert, you were doing this.

insert into table (field) values (NULL)

Unquoted. Without seeing your error, I made a suggestion - but that was wrong because it now did this

insert into table (field) values ()

After seeing your error, it makes perfect sense: you are trying to insert/update a not null field to null, and it's unquoted.

The end result you want is

insert into table (field) values ('')

(Note those are TWO SINGLE QUOTES)

So this should have done it, quote all your stuff in that switch.

This means you need a QUOTE inside a QUOTE because of the goofy way that switch is managing the values, like

" ' ' "

Apparently your program is placing the literal value in the terniary instead of quoting it. It needs to quoted. Try again . . . note the subtle changes here, quoting the empty values.

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "''"; // not null, blank string
break;
case "long":
$theValue = ($theValue != "") ? floatval($theValue) : "'0.0000'"; // ADDED, like double below
break;
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "'0'"; // not null, it's int, so zero
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "'0.00'"; // depends on the way the DB is set up, might be 0.0000000000
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "'000-00-00'"; // empty date value
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; // being a func, this will be ''
// and no telling what mySQL will do, likely will
// be the blank string
break;
}

I agree, the easier path with automated code like this is to just go with the flow.

Alter table tablename change field field varchar(255);

... will remove the not null . . . .

[Sherif]

Dear rocknbil,

Yes, i do agree with you in terms of fixing the automated code since it will allow us to keep dreamweavers compatibility running.

i tried

case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "''"; // not null, blank string
break;

with the single quotes, and it worked.... i do think this is much cleaner than the one i did, since it avoids any possible conflicts.

Everyone, Thanks a lot for all of your support.

Sincerely,
Sherif