• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

Phpmyadmin query help (null)

Question

marklcfc    0

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

Share this post


Link to post
Share on other sites

12 answers to this question

Recommended Posts

  • 0
riahc3    440

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");
  • Like 2

Share this post


Link to post
Share on other sites
  • 0
Brian M.    778

What's the structure of the table?

Share this post


Link to post
Share on other sites
  • 0
marklcfc    0

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

Share this post


Link to post
Share on other sites
  • 0
riahc3    440

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

Share this post


Link to post
Share on other sites
  • 0
+Fahim S.    1,087

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

  • Like 2

Share this post


Link to post
Share on other sites
  • 0
riahc3    440

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

Share this post


Link to post
Share on other sites
  • 0
+Fahim S.    1,087

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.

Share this post


Link to post
Share on other sites
  • 0
vhane    94

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

Share this post


Link to post
Share on other sites
  • 0
riahc3    440

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

Share this post


Link to post
Share on other sites
  • 0
+Fahim S.    1,087

"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.

Share this post


Link to post
Share on other sites
  • 0
riahc3    440

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.

Share this post


Link to post
Share on other sites
  • 0
+Fahim S.    1,087

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.