Not Null Problem of mysql

[naveed_fsd]

Hi All,

I create a table in msyql and marked some fields not null,
They are mandatory.

When I submit form mysql inserts
empty string instead of showing Null value error.

I am using mysql 5.0.16 and using InnoDb engine.

Can any one help?

[NogDog]

An empty string is not the same as a null value (as you have discovered). If you do not want to allow empty strings, then you will need to do the validation in your application logic, rather than depending on a database error.

[naveed_fsd]

Thanks for your early reply
I have search this problem on web but did not find any solution.
But how can I control it on application level.

Basically I am building a class, I want that this class should "Null Value Error".
This is my query.

"INSERT INTO BG_USERS(USERID,FIRST_NAME,LAST_NAME,USER_NAME,PASS_WORD,EMAIL,ADDRESS,COUNTRY,OPEN_DT,CLOSE_DT)
VALUES($this->maxid,$FIRST_NAME,$LAST_NAME,$USER_NAME,$PASSWORD,$EMAIL,$ADDRESS,$COUNTRY,$this->open_date,$CLOSE_DT)"

I tried to assign empty fields = NULL
but it again insert it as a string.

Do u have solution in your mind.

Regards

[NogDog]

If you want to assign a value of NULL, use the keyword NULL without quotes in the value list of the query, e.g.:

INSERT INTO table_name (col1, col2, col3) VALUES ('some text', 123, NULL)

[naveed_fsd]

Thanks for your reply
Its ok,
But want to writie a generic query that works for all.

How can i guess which column is Null.

In this case I have to write very complex query or many queries for each case.
case statements , and many if statements, that is not a professional approach.

That is not a good solution i think.

[rulian]

Validate before you do the query,
so in your code make sure your strings arent empty before ever running the insert
or build your query dynamically

if (strlen(trim($USER_NAME)) < 1)  echo "You must supply a User Name"
elseif (strlen(trim($LAST_NAME)) < 1) echo "You must supply a Last Name"; 
.....
else 
{
$SQL....
}


/// OR



$sql="
INSERT INTO BG_USERS(USERID,FIRST_NAME,LAST_NAME,USER_NAME,PASS_WORD,EMAIL,ADDRESS,COUNTRY,OPEN_DT,CLOSE_DT)
VALUES($this->maxid"; 

if (strlen(trim($USER_NAME)) < 1)  $sql .=",NULL, "; 
else $sql .=",".$USER_NAME; 

if (strlen(trim($LAST_NAME)) < 1)  $sql .=",NULL, "; 
else $sql .=",".$LAST_NAME; 

..... and so on

[naveed_fsd]

Thanks rulian ,

This trick is working.

Again Thanks a lot

I tried to make it more short & tricky using ternary operator.

$query = [COLOR="Red"]"INSERT INTO BG_USERS(USERID,FIRST_NAME,LAST_NAME,USER_NAME,PASS_WORD,EMAIL,ADDRESS,COUNTRY,OPEN_DT,CLOSE_DT) 
VALUES($this->maxid";[/COLOR]

[COLOR="Blue"](strlen(trim([/COLOR]$FIRST_NAME)) [COLOR="RoyalBlue"]<[/COLOR] [COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$FIRST_NAME'";
[COLOR="blue"](strlen(trim([/COLOR]$LAST_NAME)) [COLOR="royalblue"]< [/COLOR][COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$LAST_NAME'"; 
[COLOR="blue"](strlen(trim([/COLOR]$USER_NAME)) [COLOR="royalblue"]< [/COLOR][COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$USER_NAME'"; 
[COLOR="blue"](strlen(trim([/COLOR]$PASS_WORD)) [COLOR="royalblue"]< [/COLOR][COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$PASS_WORD'"; 
[COLOR="blue"](strlen(trim([/COLOR]$EMAIL)) [COLOR="royalblue"]<[/COLOR] [COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$EMAIL'"; 
[COLOR="blue"](strlen(trim([/COLOR]$ADDRESS)) [COLOR="royalblue"]< [/COLOR][COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$ADDRESS'"; 
[COLOR="blue"](strlen(trim([/COLOR]$COUNTRY)) [COLOR="royalblue"]<[/COLOR] [COLOR="red"]1[/COLOR])?$query .=",NULL ":$query .=",'$COUNTRY'"; 
$query .= ",'$this->open_date','$CLOSE_DT')";