Phpmyadmin query help (null)

[marklcfc]

I need to enter quite a lot of entries into phpmyadmin using the following format

insert into databasetable values('','19','firstname','lastname','1111','0','0','0','0','1','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','0','NULL','NULL')

The problem is, null is not working. They are going into the database as 0, is there any way to correct this? The columns are set to default to null

[riahc3]

Hello,

What database is this?

What datatype do the columns have?

Also I highly recommend, to avoid entering a "NULL" into a string and other common mistakes

insert into person(name,age,sex) values ("John",16,"Male");

[Brian M.]

What's the structure of the table?

[marklcfc]

Thanks, that worked very well when I changed to your setup and just inserted the values I needed to.

[riahc3]

Hello,

Thanks, that worked very well when I changed to your setup and just inserted the values I needed to.

Yeah, its something that usually happens. You miss/put a extra "'" or "," and you mess up. The string I wrote make it 100% sure you are inserting the values you want in the exact columns you want.

Please mark my answer as solved :) Thank you

[+Fahim S.]

NULL is a reserved keyword in SQL. The error in your statement is that you have put quotes around NULL.

[riahc3]

Hello,

NULL is a reserved keyword in SQL. The error in your statement is that you have put quotes around NULL.

Some automatically convert "NULL" into NULL, if Im not mistaken (depends on datatype column and the program you use).

Correct me if Im wrong Fahim S. I usually don't use NULLs

[+Fahim S.]

Hello,

Some automatically convert "NULL" into NULL, if Im not mistaken (depends on datatype column and the program you use).

Correct me if Im wrong Fahim S. I usually don't use NULLs

 

I just did a quick test in MySQL, only database engine I have readily available right now.

 

I created a table (nulltest1) with the three nullable columns:

1) column1 varchar(4)

2) column2 interger

3) column3 decimal

 

I then tried running the insert statement:

insert into nulltest1 values ("NULL", "NULL", "NULL");

This inserted the values: NULL (the string NULL), 0, 0.

 

When I ran the queries:

select * from nulltest1 where column1 is null;

select * from nulltest1 where column2 is null;

select * from nulltest1 where column3 is null;

Each returned 0 results.

 

When I ran the query:

insert into nulltest1 values (NULL, NULL, NULL);

 

I then tried running the same three select statements:

select * from nulltest1 where column1 is null;

select * from nulltest1 where column2 is null;

select * from nulltest1 where column3 is null;

each returned 1 row.

 

This says that despite the engine doing conversion of the string to a value appropriate to an integer or a decimal, it is not the same thing as NULL.

I have to say it surprised me that the engine does this conversion - I would have expected an error on the insert statement.

[vhane]

"NULL" is not NULL. The latter denotes the absence of data in SQL.

[riahc3]

Hello,

I just did a quick test in MySQL, only database engine I have readily available right now.

 

I created a table (nulltest1) with the three nullable columns:

1) column1 varchar(4)

2) column2 interger

3) column3 decimal

 

I then tried running the insert statement:

insert into nulltest1 values ("NULL", "NULL", "NULL");

This inserted the values: NULL (the string NULL), 0, 0.

 

When I ran the queries:

select * from nulltest1 where column1 is null;

select * from nulltest1 where column2 is null;

select * from nulltest1 where column3 is null;

Each returned 0 results.

 

When I ran the query:

insert into nulltest1 values (NULL, NULL, NULL);

 

I then tried running the same three select statements:

select * from nulltest1 where column1 is null;

select * from nulltest1 where column2 is null;

select * from nulltest1 where column3 is null;

each returned 1 row.

 

This says that despite the engine doing conversion of the string to a value appropriate to an integer or a decimal, it is not the same thing as NULL.

I have to say it surprised me that the engine does this conversion - I would have expected an error on the insert statement.

Thank you for testing it out :laugh: I didnt even look it up.

Needthless to say, Im sure you recommend that never count on engine conversions; They can have some ugly side effects that can drive you mad.

"NULL" is not NULL. The latter denotes the absence of data in SQL.

NULL is one of the worst inventions ever of databasing. And when you mix programs using datasources, it makes it worst :no:

One of the many reasons I never use NULL

[+Fahim S.]

"NULL" is not NULL. The latter denotes the absence of data in SQL.

 

Exactly.

 

NULL is one of the worst inventions ever of databasing. And when you mix programs using datasources, it makes it worst :no:

One of the many reasons I never use NULL

NULL is an important part of modern day RDBMS - to say that you never use it is to prevent yourself that important part.

 

I cannot understand why you wouldn't use it.  Anyone who is dealing with databases properly can't avoid it without employing some horrible hacks to achieve the same result.  You should therefore use it and learn how to use it properly.

[riahc3]

Hello,

I cannot understand why you wouldn't use it.  Anyone who is dealing with databases properly can't avoid it without employing some horrible hacks to achieve the same result.  You should therefore use it and learn how to use it properly.

Im speaking from my point of view.

Web app that basically loads client's information and loads names of items and such from DB.

When, for example, a client's phone number isnt avaliable I just put 0. If I dont know his second last name, I just put "". NULL really serves no purpose as everything is basically "yes" or "no", not "yes" "no" "unknown" which is what NULL's purpose is.

As a joke, just that NULL is allowed as a type in Java, worst language ever, shows how crappy it is :laugh:

BTW, we can have a huge argument about this so Fahim reply if you want, but I wont continue.

[+Fahim S.]

Hello,

Im speaking from my point of view.

Web app that basically loads client's information and loads names of items and such from DB.

When, for example, a client's phone number isnt avaliable I just put 0. If I dont know his second last name, I just put "". NULL really serves no purpose as everything is basically "yes" or "no", not "yes" "no" "unknown" which is what NULL's purpose is.

As a joke, just that NULL is allowed as a type in Java, worst language ever, shows how crappy it is :laugh:

BTW, we can have a huge argument about this so Fahim reply if you want, but I wont continue.

 

I'm interested...  I'm not arguing, just interested.

 

what do you do where "" is a valid value and you need to support a not supplied value?

 

The Java language itself isn't actually that bad - actually arguably the purest object oriented language out there.  The platform itself could be far better - you need to ensure you aren't confusing the two.

 

null isn't a type in Java, it is a value of a variable that doesn't reference an actual object.

Setting something null in Java is used to tell the runtime that part of the heap is ready for garbage collection and allows some control of memory management.