Page 1 of 1

One last try on evaluating nulls.

Posted: Wed Mar 23, 2005 7:01 am
by DJB
Sorry to repeat myself, but as this week is the first time I've even looked at MySql ( I'm a dba ) I have a major problem comparing null values. The following is a defined statement to be called when comparing input on a form to that in the underlying table to achieve a match:-

Code: Select all

// Create the sql query
$consistency = ('SELECT all_modules.Module_code, all_modules.Module_title, all_modules.Module_credits,                 all_modules.New_module, all_modules.Prereq_for, all_modules.Coreq_for, is_specific.Pass_required                 FROM all_modules INNER JOIN is_specific ON all_modules.Module_code = is_specific.Module_code WHERE         all_modules.Module_code = \'' .                    $_POST["code$i"] . '\' and all_modules.Module_title = \'' .           $_POST["title$i"] . '\' and all_modules.Module_credits = \'' .           $_POST["credits$i"] . '\' and all_modules.New_module = \'' .           $_POST["new$i"] . '\' and all_modules.Prereq_for = \'' .           $_POST["pre-requisite$i"] . '\' and all_modules.Coreq_for = \'' .           $_POST["co-requisite$i"] . '\' and is_specific.Pass_required = \'' .           $_POST["pass$i"] . '\' ');
These 2 lines:-
all_modules.Prereq_for = \'' .$_POST["pre-requisite$i"] . '\' and all_modules.Coreq_for = \'' .$_POST["co-requisite$i"] . '\' and

compare values which can be null, and default to null. There must be a match between them. However I know that you can't use '=' with nulls and should check them using is null/is not null. Would you have to do the comparison using a nested sub-query, and if so what would the format be ? It seems a little clumsy to do the evaluation in such a query for each item, set a flag for them and compare, but even if this was the way to do it, I don't know how to code it. Any help would be greatly appreciated ! :?:


feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

I think this would be the way to do it

Posted: Wed Mar 23, 2005 7:29 am
by DJB
'and if (is_null(all_modules.Prereq_for)
and
is_null($_POST["pre-requisite$i"])
or
is_not_null(all_modules.Prereq_for)
and
is_not_null($_POST["pre-requisite$i"]))'

but I've no idea whether this is syntactically correct ! I could check it by running it, but as it's not my project I don't want to screw it up !

Posted: Wed Mar 23, 2005 7:29 am
by CoderGoblin
If table1.column1 is null and table2.column2 is also null, the SQL command

Code: Select all

SELECT * from table1,table2 WHERE table1.column1=table2.column2
will return rows (no messing about with "IS NULL" :wink: )


The main problem you get with null values is when you are trying to access information.

Code: Select all

SELECT * from mytable where status=0
may have to be rewritten as

Code: Select all

SELECT * FROM mytable WHERE status=0 OR status IS NULL
It is a common database practice to always default values to avoid these sorts of problems and ensure any applications set things correctly. If not otherwise required, strings/text etc default to an empty string. Numbers default either to 0 or -1 depending on the situation.

If you absolutely have to have null values and only want to return values which are not null simply add an additional check...

Code: Select all

SELECT * FROM table1,table2 WHERE table1.column1=table2.column2 AND table1.column1 IS NOT NULL

Coder

Posted: Wed Mar 23, 2005 7:33 am
by DJB
Thanks for the idea - it's much more elegant than my solution. Can you tell I don't program much ! :)

Posted: Wed Mar 23, 2005 7:49 am
by CoderGoblin
Just another quick post to show a bit more complexity

Code: Select all

SELECT columnms FROM all_modules,is_specific 
WHERE all_modules.Module_code = is_specific.Module_code AND
      all_modules.Module_code = 'codex' AND
      all_modules.Module_title = 'titlex' AND
      all_modules.Module_credits = 'creditsx' AND
      all_modules.New_module = 'newx' AND 
      (all_modules.Prereq_for = 'pre-requisitex' OR all_modules.Prereq_for IS NULL) AND
      (all_modules.Coreq_for = 'co-requisitex' OR all_modules.Coreq_for IS NULL) AND
      is_specific.Pass_required = 'passx';
Will match all rows where Prereq_for matches 'pre-requisitex' OR null and Coreq_for = co-requisitex or null. Notice the brackets.

If Clause

Posted: Wed Mar 23, 2005 10:54 am
by DJB
Just checked a couple of manuals - there doesn't seem to be an 'If... Or' construct. Instead you need 'If... Elsif'. Is that correct ?

Posted: Wed Mar 23, 2005 11:00 am
by feyd
if .. else, and if .. elseif are the main if() constructs. You can use 'or' and 'and' in the expression you pass to if() though.

So

Posted: Wed Mar 23, 2005 11:04 am
by DJB
my construct on this thread using and/of is logically, if not syntactically, correct then ?

Posted: Wed Mar 23, 2005 11:06 am
by feyd
sort of.... the logic you wish to perform may differ from the precedence operations made by php for it.. plus, 'and' at the beginning will error out.

Thing is

Posted: Wed Mar 23, 2005 11:34 am
by DJB
I need to evaluate the two conditions together - both must be either null or not null. Looking at the suggestions made by Coder I realised they are using the '=' which won't work on nulls.

Posted: Thu Mar 24, 2005 3:24 am
by CoderGoblin
Try the following select (Additional spacing added only for readability :wink:):

Code: Select all

SELECT columnms FROM all_modules,is_specific 
WHERE all_modules.Module_code = is_specific.Module_code AND
      all_modules.Module_code = 'codex' AND
      all_modules.Module_title = 'titlex' AND
      all_modules.Module_credits = 'creditsx' AND
      all_modules.New_module = 'newx' AND 
      (
         (all_modules.Prereq_for = 'pre-requisitex' AND all_modules.Coreq_for = 'co-requisitex') OR
         (all_modules.Prereq_for IS NULL AND all_modules.Coreq_for IS NULL)
      ) AND
      is_specific.Pass_required = 'passx';
It is all about nesting the logic branches. I prefer to do this in the SQL rather than process it using PHP afterwards.