One last try on evaluating nulls.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

One last try on evaluating nulls.

Post 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]
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

I think this would be the way to do it

Post 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 !
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Coder

Post by DJB »

Thanks for the idea - it's much more elegant than my solution. Can you tell I don't program much ! :)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

If Clause

Post 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 ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

So

Post by DJB »

my construct on this thread using and/of is logically, if not syntactically, correct then ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Thing is

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
Post Reply