Page 1 of 1

Null value for integer

Posted: Mon Mar 23, 2009 2:33 pm
by zenon
Hello.

I create a database in MySql and i set an integer to default null.

example.

Code: Select all

create table members
(id int(4) NOT NULL AUTO_INCREMENT,
privNum int(16) default null,
password varchar(32) NOT NULL,
primary key (id));
Then, i import a csv (excel file) but with no value for privNum, but when i do a select, it shows me that privNum has value 0 but i want it to be null.Is there a way to fix this?

Thank you very much and sorry for my but english.

Re: Null value for integer

Posted: Mon Mar 23, 2009 4:06 pm
by JAB Creations
Maybe try setting it to =''? The value '0' exists to represent nothing but so then does null. Maybe somehow you're setting it to '0' without realizing it? Otherwise 0 I imagine may equal to false, so you might want to try checking for true or false?

Re: Null value for integer

Posted: Mon Mar 23, 2009 4:13 pm
by zenon
JAB Creations wrote:Maybe try setting it to =''? The value '0' exists to represent nothing but so then does null. Maybe somehow you're setting it to '0' without realizing it? Otherwise 0 I imagine may equal to false, so you might want to try checking for true or false?
Thank you for your quick answer. Can you explain me please? I'm newbie and i did't exactly understand what you told me.
I don't want to chech true or false but i want to show the values in a php file, where i have something like search engine and it prints me for example all integer < 20. And if a value is null i don't want to print it as 0. I hope you understand what i mean.

Please, i'd be very glad!

Thank you again!

Re: Null value for integer

Posted: Mon Mar 23, 2009 4:26 pm
by JAB Creations
Null means there is nothing set...nothing found (so it is empty). While '0' represents nothing if you have a row assigned the value of '0' the row is no longer empty.

I recommend setting the default value to '0'. Then in PHP when you process the array simply do not process a record if value equals '0'.

Remember to use ! to say "does not equal" in PHP. So when you look at the data you want to say in English...

If the value does not equal '0' then do something....

Code: Select all

if ($result['your_row'] != '0') {/* do stuff here */}

Re: Null value for integer

Posted: Mon Mar 23, 2009 4:32 pm
by zenon
JAB Creations wrote:Null means there is nothing set...nothing found (so it is empty). While '0' represents nothing if you have a row assigned the value of '0' the row is no longer empty.

I recommend setting the default value to '0'. Then in PHP when you process the array simply do not process a record if value equals '0'.

Remember to use ! to say "does not equal" in PHP. So when you look at the data you want to say in English...

If the value does not equal '0' then do something....

Code: Select all

if ($result['your_row'] != '0') {/* do stuff here */}
My default is '0' but i want it to be null, empty. And when i print the table via php, i want that column to present as empty and not as '0'. I do not want to make a comparison.

I'll really sorry if i confused you!

Re: Null value for integer

Posted: Mon Mar 23, 2009 6:03 pm
by JAB Creations
So does the default value say 'null' then? If so maybe change your initial MySQL query to this...

Code: Select all

CREATE TABLE members(id INT(4) NOT NULL AUTO_INCREMENT,privNum INT(16) DEFAULT '',password VARCHAR(32) NOT NULL,PRIMARY KEY (id));
...then the value will automatically be empty...which should be the same thing as null.

I'm hardly a MySQL guru so it's more if I know what I'm talking about then you knowing how to clarify what you're asking. :mrgreen:

I just tried to create a table with a row that has a default value of 'null' and phpMyAdmin said it's not a valid default value...so try removing the default value altogether and it should be blank by default then.

Re: Null value for integer

Posted: Tue Mar 24, 2009 8:09 am
by zenon
JAB Creations wrote:So does the default value say 'null' then? If so maybe change your initial MySQL query to this...

Code: Select all

CREATE TABLE members(id INT(4) NOT NULL AUTO_INCREMENT,privNum INT(16) DEFAULT '',password VARCHAR(32) NOT NULL,PRIMARY KEY (id));
...then the value will automatically be empty...which should be the same thing as null.

I'm hardly a MySQL guru so it's more if I know what I'm talking about then you knowing how to clarify what you're asking. :mrgreen:

I just tried to create a table with a row that has a default value of 'null' and phpMyAdmin said it's not a valid default value...so try removing the default value altogether and it should be blank by default then.
Hello again!

Thank you very much! I appreciate your help!

Have a nice day!