SQL problem

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

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

Not quite right

Post by DJB »

when I change 'and all_modules.Coreq_for = '' ' (second last condition)
to 'and all_modules.Coreq_for is NULL'

it executes correctly and returns 1 row, as it should.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

NOT Solved

Post by DJB »

The previous query works when the 'IS NULL' is specified in the 'native' sql statement, but the constructed statement in the script still returns no rows as it seems it can't handle the null clause. If I delete the prereq_for field, making it null, and set the coreq_for field ( the previous nulled field ) to have a value, the statement still returns the 'num_rows' function not executed. It must be the way the declared statement is handling a null field - it can't recognise that it fulfills the selection criteria.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

having a null value for a variable will display nothing.. if you want to switch off of the value of the variable being null, you have to write special processing to switch the string to use IS NULL/IS NOT NULL instead of = '' .. as an empty string is not equal to NULL.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Thanks

Post by DJB »

I can see that's the problem, but I don't actually know how to handle that within the defined statement.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

couldn't you just alter the field in the table itself (through something like phpmyadmin)? just a thought..
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Tried

Post by DJB »

changing it to an unsigned integer with a default value of 0 - still getting the num_rows did not run message.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

just change your code generation...

Code: Select all

if (is_null($value))
{
  $query .= "$field IS NULL";
}
else
{
  $value = mysql_real_escape_string($value);
  $query .= "$field='$value'";
}
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Tim

Post by DJB »

Thanks for the suggestion, but I'm not sure where this should go. The problem is in the creation of the statement as follows:-

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"] . '\' ');
The nulls shouldn't really cause a problem, unless there is some quirk I'm unaware of.


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