Help with NULL values in WHERE variables

Brett wrote:
> Thanks again Steve for helping me through this.

Thats ok Brett, but I wish I could read the PHP that you posted, to an
ASP person like me its hard to read :)

One god send about MS SQL is stored procedures, it allows me to create
the query and test it before putting it into the page. Once I have
tested it with all the possible combinations of input I can then use it
on the page.

The only reason I moved to Stored Procedures was because the site I was
working on got attacked with SQL Injection, this was pre DW8.2 and I was
told the best way to avoid it was to use Stored Procedures. Its a bit of
a learning curve but has changed the way I make web applications.

MySQL is starting to use them I believe, but not many hosts offer the
right version yet, and Dreamweaver is well behind on the support for
them too. I think once it does support them, you guys will have an
easier life.

Good luck with it all!

Steve

Steve,

Thanks for your reply.

Yes, NULL values definitely complicate things. I think one of the most
frustrating parts is there does not seem to be a way to test for NULL
values in the SQL test dialog. It's hard to know if you are on the
right track unless you know that what's in your SQL statement is
selecting records properly. And as you pointed out, as soon as you make
hand-coded changes, it breaks in Dreamweaver. Oh well, just have to get
used to that part.

I changed my variable values from text to numeric so that's not an
issue, but I noticed that DW continues to put quotes around the int.
value like this "-1" which I think is curious.

I think the difference between your example and mine is that within your
five categories, all can have NULL values and therefore be filtered out
based on their NULL. In my situation, I may need to select from only
one or two of the four categories, even though the remaining categories
have values other than NULL. That is what was tripping me up. With
great thanks to David Powers I have my solution which I'm posting here
for everyone's edification.

> You are learning rapidly that if you want to rely on Dreamweaver to
> build everything for you, you will be severely limited in what you can
> do. Try to start thinking in the same terms as PHP does.
> if (there are only two variables) {
> I need a basic SQL query
> } elseif (there are more variables) {
> I need to add something to my query
> } else {
> I need the whole shebang
> }
> Translate that into real code, and you end up with this:
> $query_chooseProducts = sprintf("SELECT * FROM products
> WHERE products.div_id = %s AND products.grp_id = %s",
> GetSQLValueString($var1_chooseProducts, "int"),
> GetSQLValueString($var2_chooseProducts, "int"));
> if (isset($_GET['seg_id'])) {
> $query_chooseProducts .= sprintf(" AND
> (products.seg_id = %s OR products.seg_id IS NULL)",
> GetSQLValueString($var3_chooseProducts, "int"));
> }
> if (isset($_GET['cat_id'])) {
> $query_chooseProducts .= sprintf(" AND
> (products.cat_id = %s OR products.cat_id IS NULL)",
> GetSQLValueString($var4_chooseProducts, "int"));
> }
> $query_chooseProducts .= " ORDER BY mod_num ASC";

Thanks again Steve for helping me through this.

Brett wrote:
> Well, that's what () are for!
>
> SELECT * FROM products WHERE products.div_id = %s AND products.grp_id =
> %s AND (products.seg_id = %s OR products.seg_id IS NULL) AND
> (products.cat_id = %s OR products.cat_id IS NULL) ORDER BY mod_num ASC
>
> Sometimes I can't see the forest for the trees.

Getting to grips with NULL can be hard, which is why when creating the
DB you have to decide it your going to accept a NULL entry in a column.

> OK, I have one final (I hope) issue.
>
> I want to be able to select based on only two or three criteria
> sometimes, but the logic fails if I don't include all 4 arguments in my
> URL. For example, I want to see all div_id=1 AND grp_id=2 regardless of
> the remaining two fields. But if I don't have seg_id or cat_id in the
> URL, it returns nothing. How do I get around this?

In your code where you inserted NULL, I noticed you were using quote
marks around it, that makes it into a string with a value of NULL, not a
NULL value, you need to remove all quote marks from it so that it is
more like: Var = NULL

See if that helps.

On a webshop that I recently created I have used a store procedure to
create the recordset based on 5 input variables, each one can be NULL.

http://www.aclighting.com/shop/cat_products.asp?SubCat1ID=3&CatID=1

In this url there is only 2 being passed, but the other 3 are being
passed NULL by the code on the page, and then the stored procedure is
doing just as you are, lots of these in the SQL:

AND (tblProducts.SubCat1ID IS NULL or tblProducts.SubCat1ID =
@SubCat1ID) AND ....

I am using MSSQL and ASP, but the idea is the same. My recordset on my
page that calls the stored procedure initially has the string value of
NULL for the defaults for each parameter being passed, but I then go
into code view and remove the quotes to make it an actual NULL value.

Only thing is that this not only breaks the recordset so I cant drag the
bindings onto the page, but it causes Dreamweaver to crash very
frequently when using this page.

To avoid this I leave the NULL string value in the parameter default
value until I have finished designing the page, I then change the value
and upload.

If I am working on this page in the future, I make sure that no other
pages are open and I save after every change. Its a pain but I can't
find any other way to deal with passing NULLs to a stored procedure via
a recordset.

Steve

Steve

Well, that's what () are for!

SELECT * FROM products WHERE products.div_id = %s AND products.grp_id =
%s AND (products.seg_id = %s OR products.seg_id IS NULL) AND
(products.cat_id = %s OR products.cat_id IS NULL) ORDER BY mod_num ASC

Sometimes I can't see the forest for the trees.

OK, I have one final (I hope) issue.

I want to be able to select based on only two or three criteria
sometimes, but the logic fails if I don't include all 4 arguments in my
URL. For example, I want to see all div_id=1 AND grp_id=2 regardless of
the remaining two fields. But if I don't have seg_id or cat_id in the
URL, it returns nothing. How do I get around this?

Thanks.

Steve,

Thanks for your response. Here is my code above the DOCTYPE:

<?php require_once('Connections/belleuser.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "",
$theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ?
mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" :
"NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue :
$theNotDefinedValue;
break;
}
return $theValue;
}
}

$var1_chooseProducts = "-1";
if (isset($_GET['div_id'])) {
$var1_chooseProducts = $_GET['div_id'];
}
$var2_chooseProducts = "-1";
if (isset($_GET['grp_id'])) {
$var2_chooseProducts = $_GET['grp_id'];
}
$var3_chooseProducts = "-1";
if (isset($_GET['seg_id'])) {
$var3_chooseProducts = $_GET['seg_id'];
}
mysql_select_db($database_belleuser, $belleuser);
$query_chooseProducts = sprintf("SELECT * FROM products WHERE
products.div_id = %s AND products.grp_id = %s AND products.seg_id = %s
OR products.seg_id IS NULL ORDER BY mod_num ASC",
GetSQLValueString($var1_chooseProducts,
"int"),GetSQLValueString($var2_chooseProducts,
"int"),GetSQLValueString($var3_chooseProducts, "int"));
$chooseProducts = mysql_query($query_chooseProducts, $belleuser) or
die(mysql_error());
$row_chooseProducts = mysql_fetch_assoc($chooseProducts);
$totalRows_chooseProducts = mysql_num_rows($chooseProducts);
?>
<?php include('style_rules.php') ;?>

In a nutshell, what I'm trying to do is select records in my table based
on certain criteria. In my table Products I have four foreign keys two
of which permit NULL value. It is based on the value of these four
fields that I determine which products to show. The following URL shows
one of my criteria:

<li><a
href="products_02.php?div_id=2&grp_id=3&seg_id='NULL'&cat_id=6">Miscellaneous</a></li>

You will notice that I have

...AND products.seg_id = %s OR products.seg_id IS NULL...

This however results in selecting all records with a NULL value. What I
don't understand is why it is selecting all records with a NULL value
when the two previous arguments (products.div_id and products.grp_id)
are included with AND. Shouldn't this WHERE statement return only
records with products.div_id = $_GET['div_id'] AND products.grp_id =
$_GET['grp_id'] AND products.seg_id = $_GET['seg_id'] OR products.seg_id
IS NULL. I'm trying for something like: (div_id AND grp_id AND (seg_id =
something OR null)), but I'm getting ((div_id AND grp_id AND seg_id) OR
null).

Sorry for my ignorance, I'm new to PHP/MySQL but I'm trying to learn, so
your patience is appreciated.

Gee, wouldn't it be easier to change the Product table so that the
fields that currently allow NULL, require something else like 0?

Thanks for your help.

Brett wrote:
> I can't get my SQL query to work.
>
> SELECT *
> FROM products
> WHERE products.div_id = var1 AND products.grp_id = var2 AND
> products.seg_id = var3
>
> When I test the SQL in the advanced recordset dialog using the test
> button it returns No Data. I have set the default of the variables to
> values that I know exist. This is clearly related to NULL values in
> products.seg_id. When I run the test on just the first two variables
> (without products.seg_id) it returns correctly, but as soon as I include
> the third variable I get No Data when tested. I don't know how to enter
> a NULL value in the dialog to test for those records that have a NULL
> value. What do I put in the Default value parameter to test for NULL
> values?

You can't do it in the interface, you have to go into the code and do
it, but that will render your recordset broken.

Steve