MYSQL :: IS NOT NULL

MySQL 4.0.17 Data Scrambles, Not-null Fields Become Null, Etc.
I'm having the most bizarre problem with PHP/MySQL that I've ever
faced and it's an urgent matter (of course) to try to fix it ASAP.

We have one record inserted into a table with 75 columns, whereby half
of the columns are not-null, but for some bizarre reason, the fields
are null. The rest of the columns that are null are completely
scrambled (e.g. "first_name" is in "address2", "fax" is in "url", "url"
is in "last_name", etc.)

This just recently occurred and seems to have no history otherwise. It
occurred sometime on Saturday when the record was inserted but logs
indicate no unusual activity at that time.

Has anyone seen this before and found a way to prevent it from
occurring again?

PK Field Not Null Is Being Null
I m newbie. I made a MySQL database table and in the primary key field I set the data type VAR CHAR.I also set it not null .BUT still I can insert blank values in this PRIMARY KEY field. If I just blank my primary key field by this query- Insert Tablename Values ('','','','','',''); (if my table has 6 fields).HOW IS IT POSSIBLE. How can PK field allow blank value when I set not null. HOW TO SOLVE IT.

2. I use a MySQL GUI Tools from http://dev.mysql.com/downloads/. In Table viewer of Query browser, each time I insert the first field, it sets the datatype to INTEGER,NOT NULL, AUTO INCREMENT. But I change it to VARCHAR as needed. Do I have to/should use Integer type in PK field and can Varchar be incremented.

3.Can I set user privilege to a particular row (on a particular PK); HOW?

4. Is it correct/incorrect that the name of the foreign key has to be the same as the name of the corresponding PK. I saw that in MySQL I can easily change the FK name to anything else and then just make a reference of FK to the PK.

5. How can I make a one-to-one relationship in MySQL;

VARCHAR Null/Not Null
I'm having a problem with a table that is already created - I want insert attempts to FAIL when the column for 'firstname' is empty (a VARCHAR field). Currently, the column is set to Null=YES and a Default of 'NULL'. This allows records to be inserted with no 'firstname' value (NULL appears in the column). If I change it in phpmyadmin 'structure' to Null=NO and Default of [empty field] then it allows records to be inserted with no 'firstname', and 'firstname' shows empty in the column

Using NULL
I was wondering is it good practice to use NULL for unknown values?

In the PHP forum sticky it says:

2) Declare all of your columns NOT NULL unless you need to store NULL values (NULL is not the same as 0 or the empty string). If you need to store NULL, you'll know. Again, NOT NULL saves space and speeds things up.

In the fields I am thinking of storing NULL, I could instead use empty string in the CHAR fields, and 0 in the numeric fields. However using NULL would make life a bit easier, and is probably more readable.

Should I use NULL? Or, is it better practice to avoid it where possible and use 0/""? Or, am I misunderstanding use of NULL altogether?

NOT NULL Vs. NULL
I have a table that's populated by a form. There are some fields that are optional. Should I send empty strings to a NOT NULL column or should I set the column as NULL and send nothing at all?

Am I right in interpreting the meaning of NOT NULL by saying: NOT NULL means an INSERT will fail if the corresponding field is left out of the query?

Null Value
I have a query which returns exam results as a string say a value of 55, which i convert to an int, but in some case's a student wont have a result and so a null value is returned. In the below code out.println(CA419_Overall); have a value of 50, but out.println(CA421_OverallX); has a value of null, CA419_Overall and CA419_Overall are both ints. When I try to view the page I get java.lang.NullPointerException.

while(rs.next())
{
String CA419_OverallX = rs.getString("CA419_Overall");
if (CA419_OverallX.equals("null"))
{
CA419_Overall = 0;
}
else
CA419_Overall = Integer.parseInt(CA419_OverallX);
out.println(CA419_Overall);

String CA421_OverallX = rs.getString("CA421_Overall");
out.println(CA421_OverallX);
if (CA421_OverallX.equals("null"))
{
CA421_Overall = 0;
}
else
CA421_Overall = Integer.parseInt(CA421_OverallX);

NULL And NOT NULL
I understand the meaning of NULL, but I don't understand its usage. For example, a field declared to be NOT NULL, but has a NULL value. Or when a field is declared to be NULL, and yet values can still be placed into it. I don't understand the logic behind this.

Null Value
I added a field to my company table (PBV_rstCompany.Fields("Installer")) the
default value of the field is Null. I place this If statement and it doesn't
work

If PBV_rstCompany.Fields("Installer") <> "Y" Then
txtInstaller.Visible = False
lblInstallerLBL.Visible = False
cmdNextInstaller.Visible = False
cmdPrevInstaller.Visible = False
End If

It assumes the statement is false when in fact it's true... Null in not
equal "Y"!

LIKE And IS NOT NULL
Are the LIKE and IS NOT NULL options available in MySQL - these 2 options
are availble in Access and MS SQL.

LIKE is for searching using wildcards - e.g WHERE Name Like 'AD%'.

Null Or Not Null
I am setting up my db, and am reading a great book on all of this, but am confused on exactly when to use null and not null.

NULL
I have table with columns which has NOT NULL definition. What can I do to be ensure that update or insert will not modify table with empty values?

MY EXAMPLE:

if($zm=='')
$zm=null ;

or what can I do?

How can I use NOT NULL table column definition? or what NOT NULL definition is for?

Trying To Put NULL
When I pick the option "allowed null" while creating a table field, and If that field does not have a value i see NULL written in that field when i browse the table using phpadmin,

But when I update the table doing followig

update accessories set field_A = null
I don't see "NULL" written in the field, does that mean that above SQL did not put null?

What Should Be NULL Is Zero
I am collecting user feedback via an HTML form and sending it to a DB via a PHP form handler. My problem is that values that should be NULL are showing up in the DB as zero.

When a user skips a question, I want the value for that field to be NULL. In the form, I set the variables used to capture the user's choices to default to NULL (not enclosed in quotes). All the PHP handler does is gather the submitted values and send them off to the database. When I echo the variables from the form handler, they are NULL. In the database, the fields are defined as INT(1) with a NULL default value. But when the values (which should be NULL) are submitted to the DB via the form handler, the resulting fields are zero.

(I have also tried setting the form's default values to "" and '' with the same result.)

What can I do to make these values NULL?

NULL
I have table with columns which has NOT NULL definition.
What can I do to be ensure that update or insert will not modify
table with empty values?
MY EXAMPLE:

if($zm=='')
$zm=null ;

or what can I do?
How can I use NOT NULL table column definition?
or what NOT NULL definition is for?

NULL/NOT NULL
I'm drawing a blank when it comes to using NULL/NOT NULL in my tables... can someone refresh my memory about this option in laemen terms. Also how or why does the field with this option have "NULL" entered in the field as the default value?

Getting The Null
I have two tables to compare
Code:
Schedule of Subject(table 1) previous subject taken(table 2)
Semister ID Subj ID | Semister ID Subj ID
1 1 | 1 1
1 2 | 1 2
1 3 | 1 3
1 4 | null null
1 5 | null null

My PK for this should be Semister ID.

My table 2 has only 3 records, so obviously if I compare
table2 from table1 (which has 6 records), table 2 should show the null value because it has no record for the following semister and subjects.

NULL
I have a date field in my database which is set to accept NULLs. If I enter
a record from phpMyAdmin and leave the date field blank, the record is
entered properly. However, if I do the same thing from a form, I get an
error that the date cannot be blank. It seems that I need to enter a NULL
in the date field from the form rather that nothing.

NULL
This is really silly, I used to do it many times in the past but now I completely forgot the syntax, so here I go:
What I want to do is select from one specific column, if this column is empty (NULL or = "") get the value from a second column instead. I remember doing it with a single query, but now I can't seem to remember or find how to do that.

WHERE A OR B OR C IS NOT NULL
I am trying to sort my recordset so that records will only display if certain columns contain information. I am unsure of the syntax on this. My first two columns will always be populated with data, but I only want to display the record if any one of the rest of the columns contain data. I know that .Code:

SELECT *
WHERE a OR b OR c IS NOT NULL

is not quite right.

Using NULL
Is it possible to insert NULLs when making an INSERT statement where the column counts dont match to have the extra columns be inserted with NULLs automatically?
The following is my query

insert into team select playerID,teamID from batting where playerID like 'aaro%' group by teamID

aaron played for 3 teams, but the table team has space for 7 teams. I would like team4..team7 to get NULLs, if possible or another way to not get the column count doesn't match error.

Using NULL Vs 0
I have a DB i MySQL that uses many int(1) type fields. I store there some time value 0 or NULL.
My question is which solution is faster from DB point of view?
Should I store NULL for all those fields or is "0" better solution?
Which one takes less space and time to process?

NULL
I have a MySQL table that stores name and age of a person. The name field needs to have a value; the age field is optional.I'm quite confused now with the NULL and NOT NULL settings... It seems that neither one does what I want (rejecting an INSERT query where the name field is empty)...So what exactly do I have to do?

Column Specified As Not Null
I have a column in mysql that is specified as "Not Null" I have noticed that my form fields that map to these columns are able to populate data into the table regardless of the not null, like if I have an email field and I leave that blank on the form then submit well all other data of my form fields populate and I guess I am expecting the behaviour of the not null on the email column to deny data entry for that entire form submission thus record not entered.

SET NULL Doesn't Work
I have this table:
MySQL
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`login` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`name` VARCHAR(30) NOT NULL,
`email` VARCHAR(30) NOT NULL,
`icq` INT(9) UNSIGNED DEFAULT NULL,
`personal_stream_id` INT(11) NOT NULL,
`TIME` DATETIME NOT NULL,
`url_name` VARCHAR(30) DEFAULT NULL,
`avatar` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `login` (`login`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `icq` (`icq`),
CONSTRAINT `users_fk` FOREIGN KEY (`id`) REFERENCES `photos` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;

The constraint is linking this table:

MySQL
CREATE TABLE `photos` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`user_id` INT(11) DEFAULT NULL,
`streams_id` INT(11) NOT NULL,
`description` TEXT,
`hardware_id` INT(11) DEFAULT NULL,
`ratings_id` INT(11) DEFAULT NULL,
`thread_id` INT(11) DEFAULT NULL,
`url_name` VARCHAR(20) DEFAULT NULL,
`views` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `ratings_id` (`ratings_id`),
KEY `user_id` (`user_id`),
KEY `streams_id` (`streams_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

It works, but as you can see, the constraint ondelete action is set to CASCADE, which is not good - it is bad to delete all photos when the user is deleted, I want the database to set user_id value to NULL using the SET NULL action - PHP frontend should display something like "User deleted" then. But when I try to alter the constraint to SET NULL, EMS SQL Manager I'm using shows a very strange error like "Can't create table '.dfk#sql-8e4_4.frm' (errno: 150)". Similiar error is shown when I try to link unsigned int with not unsigned int.

Use 0 Instead Of NULL In This Case?
I am designing a table that will be used for a translation lookup. I want the option to make global rules, and then come back later and make more specific rules if I need to. My current design calls for 4 columns:
id(int(10)) = surrogate key, auto_increment
designer_id(int(4)) = relates to another table on a foreign index to give me information about the designer -- this column is an "optional" column
code(varchar(25)) = code that will be translated
prompt(varchar(50)) = translation to be displayed

if the rule is "global", then there will be no designer. I want to create a unique index on columns designer_id,code so that there will never be any duplicate (contradictory rules), but this doesn't work if I leave designer_it default to NULL? I was able to create the same row twice (prompt was different) without breaking any rules... I think that if I made the default designer_id 0, then the index would work. Is this true? If so, is this the best way to prevent duplicate rules?
The designer_id will always be an int > 0.

Query NULL
SELECT sales_reps.sr_id,
sales_reps.order_id,
sales_reps.name,
sales_reps.job_number,
UNIX_TIMESTAMP( shop_orders.date ) AS date,
sales_reps.stage,
sales_reps.status,
pi.fname AS pfname,
pi.lname AS plname,
sales_reps.date_to_shop,
users.builder,
users.division,
shop_assignments.editor_id,
shopper.fname AS sfname,
shopper.lname AS slname,
ei.shop_result,
ei.good_tape
FROM sales_reps
LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id
LEFT JOIN users ON users.user_id = shop_orders.builder_id
LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id
LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id
LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id
LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id
WHERE sales_reps.allow_shop = 'Y'
AND users.user_id = ཎ'
AND sales_reps.status = 'Rejected'
AND ei.shop_result NOT
IN (
'Trip x1', 'Trip x2', 'Non-Reg Trip x1', 'Non-Reg Trip x2', 'Office Closed'
)
ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC
the ei.shop_result NOT IN is also including NULL in that list. I don't want this since there are some Rejected records that have a NULL value in that field.

AVG Returning 0, Not Null
I'm writing a script to allow visitors to rate articles on my site. Sometimes a question does not apply to that particular article or a person is just too lazy to fill out the entire form. Either way, I'm passing NULL to the MySQL database if the question was not filled out.

I want to do averages for each article for each question I ask. The problem is that I'm getting 0 only when I use AVG () even though the MySQL site says that should return a null if empty. It is not.

I have a "control group" table pulling the same data without using AVG. Of course, every vote is visible, but it's clear that null is working properly in that example.

Here's is a shortened version of my

PHP

$queryA = " SELECT URL , AVG(Vote) ,AVG(A1) , AVG(A2) , AVG(A3) FROM Galleries GROUP BY URL ";

$result = mysql_query($queryA) or die(mysql_error());

while($row = mysql_fetch_array($result)){
?>

<tr>

<td><?php echo $row['AVG(A1)']; ?> </td>
<td><?php echo $row['AVG(A2)']; ?> </td>
<td><?php echo $row['AVG(A3)']; ?> </td>

</tr>

<?
}
echo "</table>";

Any thoughts as to why AVG() would return 0 and not null when my database seams to be setup correctly?

Select All Null??
two Db's

1. clubs - clubId | clubName|.....
2. recentClubsList - id | clubId | dispOrder

all clubs are listed in clubs. only a few are represented in recentClubsList. I want to query a result set that includes only clubs that do not show up in the recentClubsList Db.

I have tried this query

SELECT CONCAT(clubName, ', ', state ) AS theName, c.clubId, rc.id, rc.clubId
FROM clubs c
LEFT JOIN recentClientsList rc ON (rc.clubId=c.clubId)
ORDER BY c.clubName
It is very close as it returns all clubs but with null values when there is no rows within recentClubsList.

Update Only If Value Is Not Null
Is there any way to do an update only if value is not null?

E.g. using PHP ...

ORDER BY - IF Not Null
I am wanting to create a query that will have 2 order by parameters:

SELECT * FROM table ORDER BY field2, field1

My problem is I only want field 2 to be first in order if it exists (ie: I don't want NULL records indexed first).

My example: First Name, Maiden Name, Last Name

SELECT * FROM names ORDER BY middle_name, last_name;

This way if someone had a maiden name of "anderson" and their last name was "johnson" they would show up with the "A's" and not the "J's".

NULL Default Value
I have a simple table. I'm using phpmyadmin. As you can see my default values for NULL are 0 but for some reason wen a null value is posted to the database it appears as "NULL" instead of "0"....

Inside IF NULL
I have this table:
------------------
id lang name
1 en name1
1 de NULL
2 en name2
2 de name3

I want to return all (WHERE lang='de') rows but if the (name=NULL) then replace it with name from the same id but lang='en'. Is it possible?

I have this query:

SELECT IFNULL(name, SELECT name FROM table WHERE lang='en' AND id=????) name FROM table WHERE lang='de'

Can you fix it?

Set Date As NULL
I am converting an Access database to MySQL. I am going through some of the issues I need to resolve before I just jump into it.

The biggest question I have is can I set a Date Field in MySQL to NULL? Meaning Null, no entry and not 0000/00/00 00:00:00, which I understand is MySQL's default date entry. I have several queries already written for the old access database that look for Null value in the date field.

SET Something To NULL If Value Not Matched.
I've got two tables. An info table and a description table. The two tables share an ID column. The info table has no null values. The description table will have at least one description to correspond to an item in the info table... sometimes more. If there's only one description, it will be in English. If it has more, there's a chance it's in Japanese (denoted as 'e' or 'j' by a column).

My query is fine and dandy for English, but it doesn't work for Japanese. As before, there isn't always a Japanese description. If there's not, my last conditional (WHERE dsc.language ="j") will always fail (as you shall see). Here's the query when want to display a Japanese description:

SELECT title,developer,publisher,
IFNULL(dsc.description, ' ') as description
FROM info
LEFT JOIN dsc ON info.id = dsc.id
WHERE dsc.language = "j"
AND info.id = "100"

I'm using PHP as the interface. I'm sure I could find a way around using an extra command, but would rather do it in one query if possible.

Order By Null First
Does anyone know of a way to sort by a datetime type field desc, but have nulls show first?

Statement currently looks like this:
SELECT * FROM $tables[portfolio] ORDER BY `date_completed` DESC

But the NULL Fields are last in the sort.

I'm editing this incase anyone in future searches this and needs it.
The following code will do the trick.

(
SELECT *
FROM `$tables[portfolio]`
WHERE `date_completed` IS NULL
ORDER BY `title`
)
UNION ALL
(
SELECT *
FROM `$tables[portfolio]`
WHERE `date_completed` IS NOT NULL
ORDER BY `date_completed` DESC, `title`
)

Null Field
I have a mysql table with a password field. (varchar(20), Null YES). When i input data into the field from the form, I look at the table and it say null. It's not taking the data that I input into it.

Sort NULL Last
I have a mysql query, "SELECT * FROM task ORDER BY duedate ASC", where
"duedate" is a unix_timestamp and can be NULL. I want any NULL results
to be at the end of the list, how might I do this?

NOT NULL - Not Working
I have created an HTML page that has an Iframe on it, linked to a PHP page. When I refresh the HTML page (and thus the PHP page), it will resend the code to MySQL, creating a blank row (except date, which is automatically entered by a variable with the PHP date() command), even if the HTML forms yield no value. Here is the code:

CREATE TABLE withdraw (
id int NOT NULL auto_increment,
reason varchar(20) NOT NULL,
amount decimal(8,2) NOT NULL,
date varchar(20) NOT NULL,
primary key (id));

Not Null-field
I have a C# application that calls a MySQL procedure and writes data to the db. All of the fields get populated properly but for the ID field, which is my PK field. It populates the field with an empty string instead of the value. the ID field is a varchar field.

When I try a direct insert through the MySql Query Browser, it populates the field correctly, but when using the C# code/appl. the above issue occurs.

When To Use NULL As Default Value
I pretty much set a fields default value to NULL if it not a "required" field in my app.

NULL As Default Value
I pretty much set a fields default value to NULL if it not a "required" field in my app. Just wondering if this is the way to go since I just sort of guessed at this method.

When To Use 'NOT NULL' In Tables
My database receives HTML form values (text, I don't store binary files in
any of my tables).When is it adviseable to create a table column with NOT
NULL?I make a habit of having all my table columns this way but wonder if
I am just putting additional overhead (however small) on the server and
getting zero advantages.

Join On NOT NULL
SELECT company.isin
FROM company
LEFT JOIN stocks ON company.isin = stocks.isin
WHERE stocks.isin IS NOT NULL

This gets the company.isin field once for every time it exists in the stocks table. I only want it once for each company. Using DISTINCT(company.isin) works, but is this the correkt way to go?

Subselect With NULL
why don't i get some results for the second query?

mysql> select * from a;
+---+
| b |
+---+
| a |
| b |
+---+

mysql> select * from a where b not in (select NULL from dual);
Empty set (0.00 sec).

NULL Field
I have created a DB and started using PHP to pull data and display it.
However I am having trouble understanding the Null field.
I've searched the web and read what I could but I am no more intelligent from that research .Can someone put into simple words what this field means ? What happens when it is set to "null" or "not null".

Null Field
How can I see if a field has a null value? I have a date field that has a default value of NULL.

if($getline[charged] IS NULL){
echo "Yes field is NULL"}

Null Values
i created a new database for an application that i am working on , from the delphi application that connects to mysql 5 database i can pass records with no data to not null fields , i was suprise and i did the same thin from mysql query browser, it seems that mysql dosn't check tha property before it stores the reccord.

Null Value Is Eliminated
Hey all. I am getting this warning when I execute my query. It is still populating the results correctly, its just giving me this warning and I dont know how to get rid of it. I know one of my solutions is:
SET ANSI_WARNINGS OFF

-- sql code

SET ANSI_WARNINGS ON

but I want to know why that is happening. It seems to be caused by the MAX function in the following line:

DECLARE @BusinessDay DATETIME
DECLARE @PreviousBusinessDay DATETIME
SELECT @BusinessDay = (SELECT MAX(PeriodDate) FROM TableName)
SELECT @PreviousBusinessDay = DATEADD(dd,-1,@BusinessDay) FROM TableName.