Page 2 of 2
Not quite right
Posted: Mon Mar 21, 2005 12:44 pm
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.
NOT Solved
Posted: Mon Mar 21, 2005 1:46 pm
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.
Posted: Mon Mar 21, 2005 1:54 pm
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.
Thanks
Posted: Mon Mar 21, 2005 2:11 pm
by DJB
I can see that's the problem, but I don't actually know how to handle that within the defined statement.
Posted: Mon Mar 21, 2005 2:57 pm
by infolock
couldn't you just alter the field in the table itself (through something like phpmyadmin)? just a thought..
Tried
Posted: Mon Mar 21, 2005 3:44 pm
by DJB
changing it to an unsigned integer with a default value of 0 - still getting the num_rows did not run message.
Posted: Mon Mar 21, 2005 4:26 pm
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'";
}
Tim
Posted: Tue Mar 22, 2005 4:24 am
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
tags. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]