Help with NULL values in WHERE variables

[banacan]

I'm passing URL parameters to a recordset which uses the values to determine which thumbnail images to display. Here is an example of my link from my SPRY navigation:

 

<li><a href="products_02.php?div_id=2&grp_id=3&seg_id=null&cat_id=4">Pork</a></li>

 

Here is the SQL query that is mostly working:

 

$var1_chooseProducts = "something";

if (isset($_GET['div_id'])) {

$var1_chooseProducts = $_GET['div_id'];

}

$var4_chooseProducts = "NULL";

if (isset($_GET['cat_id'])) {

$var4_chooseProducts = $_GET['cat_id'];

}

$var2_chooseProducts = "whatever";

if (isset($_GET['grp_id'])) {

$var2_chooseProducts = $_GET['grp_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.cat_id = %s ORDER BY mod_num ASC", GetSQLValueString($var1_chooseProducts, "text"),GetSQLValueString($var2_chooseProducts, "text"),GetSQLValueString($var4_chooseProducts, "text"));

$query_limit_chooseProducts = sprintf("%s LIMIT %d, %d", $query_chooseProducts, $startRow_chooseProducts, $maxRows_chooseProducts);

$chooseProducts = mysql_query($query_limit_chooseProducts, $belleuser) or die(mysql_error());

$row_chooseProducts = mysql_fetch_assoc($chooseProducts);

 

My problem is with NULL values in the WHERE clause. As you see above, the link for Pork contains seg_id=null, but I did not include it in the query because as soon as I do no thumbnail images will display (I get the broken image icon). When I don't have seg_id (var3) in the query, it works fine, all of the correct thumbnail images display properly – at least for those links that contain all of the arguments in the query. That's where I'm stumped. I have four foreign keys in this table two of which (seg_id and cat_id) permit NULL values. How do I deal with that?

 

On a related issue. I'd like to be able to select based on just div_id which would give me every product in the Division, even though they all have a value for grp_id. But again, if I make my link like this:

 

<li><a href="products_02.php?div_id=2">Fresh Foods</a></li>

 

I get the broken image icon where the thumbnails should be. As the query is designed, I must have a value in each of the fields because of the AND. How can I make the query work when only one of the arguments has value? It seems to me that OR would not be suitable because it would return any record that contains a value in any of the arguments. I believe that if this issue is solved it would also solve the first issue.

 

I'm getting the URL values passed to the query and in many cases it's working fine. Now I just need to understand how to design the query to allow for NULL or fewer values.

 

Thanks for your help.

 

[laffin]

use something

if(isset($_POST['var1']))
{
$var1= mysql_real_esc(  $_POST['var1']);
  $where[]="var_id='$var1'";
}

 

and so forth for every variable that shud be in the where clause.

 

now do something like this

if(isset($where))
  $where = "WHERE " . implode(' AND ',$where);
else
  $where = "";

 

Muahahahaha, now ya got yer WHERE clause generatedm just add to end of yer query

[banacan]

Thanks Laffin,

 

I'm still new to php/mysql and I'd like to better understand what is happening in the code you suggested and where it fits in my current code.

 

My first question is, should it be

if(isset($_POST['var1_chooseProducts']))

or

if(isset($_GET['var1_chooseProducts']))

since this is initiated by a URL and not a form.

 

As I read your code, I'm assuming that

if(isset($_POST['var1_chooseProducts']))
{
$var1= mysql_real_esc(  $_POST['var1_chooseProducts']);
  $where[]="var_id='$var1'";
}

should follow my code

$var1_chooseProducts = "something";
if (isset($_GET['div_id'])) {
$var1_chooseProducts = $_GET['div_id'];
}

so that your code picks up the value of my var1_chooseProducts and then sends its value to the $where array.  Is that right?

Then I assume that I would repeat your code after each of my variables to continue to build the $where array.  Do I have that right so far?

 

I'm really not clear on where I place your next piece of code.

if(isset($where))
  $where = "WHERE " . implode(' AND ',$where);
else
  $where = "";

How does this get integrated into the SQL?

 

I appreciate your help.

[laffin]

forms mainly used POST (but can be changed via form tags optional METHOD parameter)

url links use GET which the parameters appear on the url line (http://www.my.site/some.php?var1=xxx&var2=yyy)

 

so 1st) Yes, use $_GET

 

2nd a little more complicated

 

the code presented was a quick way of doing it, drop into a page to check it's functionality. so ya have to take the concept/method and integrate into yer code

$var1_chooseProducts = "something";
if (isset($_GET['div_id'])) {
$var1_chooseProducts = $_GET['div_id'];
$where[]="ProductsID='$var1_chooseProducts';
}
[code]

than just before the query.
if(isset($where))  // do we got any $where conditionals
  $where = "WHERE " . implode(' AND ',$where); // Yes, Build a Where Clause
else
  $where = ""; // just leave it blank
$Query="SELECT * FROM mytable $where;";
$res=mysql_query($Query);

 

just as an example, u wud still want to keep any validation/sanitization of yer variables before sending it to mysql, for security

 

[/code]

[Barand]

Going back to your original question, you need to use

 

WHERE colname IS NULL

 

instead of

 

WHERE colname=NULL

[banacan]

Barand,

 

Thanks for your reply.  Is this what you meant?

 

$var1_chooseProducts = "none";
if (isset($_GET['div_id'])) {
  $var1_chooseProducts = $_GET['div_id'];
}
$var3_chooseProducts = "IS NULL";
if (isset($_GET['seg_id'])) {
  $var3_chooseProducts = $_GET['seg_id'];
}
$var4_chooseProducts = "IS NULL";
if (isset($_GET['cat_id'])) {
  $var4_chooseProducts = $_GET['cat_id'];
}
$var2_chooseProducts = "this";
if (isset($_GET['grp_id'])) {
  $var2_chooseProducts = $_GET['grp_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.cat_id = %s OR products.cat_id IS NULL AND products.seg_id = %s OR products.seg_id IS NULL ORDER BY mod_num ASC", GetSQLValueString($var1_chooseProducts, "text"),GetSQLValueString($var2_chooseProducts, "text"),GetSQLValueString($var4_chooseProducts, "text"),GetSQLValueString($var3_chooseProducts, "text"));
$chooseProducts = mysql_query($query_chooseProducts, $belleuser) or die(mysql_error());
$row_chooseProducts = mysql_fetch_assoc($chooseProducts);
$totalRows_chooseProducts = mysql_num_rows($chooseProducts);

 

In the WHERE clause I have added OR to the product_id's that can have a NULL value so that they will accept either a value from the URL or a NULL value.  The problem is it shows ALL products with a seg_id or cat_id of NULL regardless of the values of the first two arguments for div_id and grp_id.  Why is that?  The WHERE clause has div_id AND grp_id AND seg_id value or NULL so wouldn't that force the seg_id NULL value to have the correct div_id and grp_id values?

 

Thanks for your help.

 

 

[Barand]

No. I meant in the SQL WHERE clause if you want to test for a null value.

[banacan]

Barand,

 

I do have IS NULL in my WHERE, it follows OR.

 

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

 

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.

[Barand]

If you mix AND and OR in queries, USE (..) to specify the logic

 

eg A AND B OR C

 

is it

 

A AND (B OR C)

 

or

 

(A AND B) OR C

[banacan]

Barand,

 

Boy do I feel STUPID!  It is SO obvious, so obvious that I even used that way of writing in my previous post to describe what I meant.

 

Thanks for helping me out.

 

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

 

Below is how my database is structured.  I'm giving simplistic examples which are hypothetical because the actual database would have divisions, groups, segments, and categories that wouldn't make any sense to you or anyone else unless you knew the products and how they relate.

 

These are the tables. As you can see PRODUCTS is the main table and contains foreign keys.

 

PRODUCTS  null  DIVISIONS    GROUP          SEGMENT        CATEGORY

 

prod_id      n        div_id          group_id        seg_id            cat_id                             

mod_num    n        div_name      group_name    seg_name        cat_name         

div_id        n

group_id    n

seg_id        y

cat_id        y

title          y

desc          y

thick        y

width        y

height      y

project      y

image      n

 

Staying with the food hypothetical:

 

FOOD

 

Fresh                                  |                    Frozen

 

Meats                                |                    Fruits & Vegetable

 

                                        |                    Fruits

 

                                                              Melons    Berries    Citrus

Beef    Chicken    Lamb                                                      Oranges    Grapefruit

 

As you can see, (I have abbreviated the concept) I have Divisions: Fresh and Frozen.  I have Groups: Meat and Fruits & Vegetables.  I have Segments: Fruits and Vegetables, and I have Categories: Melons, Berries, Citrus.

 

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 fresh meats so I would select Fresh AND Meats which should then display Beef, Chicken, Lamb.  But if I don't have seg_id or cat_id in the URL, it returns nothing.  How do I get around this?

 

Thanks.

[Barand]

That's where laffin's code (which is exactly as I would do it) comes in to play. If you don'tt have a value for it, don't include it in the query

[banacan]

I'm sorry to say I get lost in that code.  I don't know where it goes in my code and how the values get extracted for use in my display.  I'm still quite new to this and I think this one is a bit over my head.  Can you explain it to me?

[banacan]

Hi Barand and Laffin,

 

Thanks for your continued help.  I'm making progress but I still do not fully understanding how Laffin's code is implemented.  I see how each variable – if it is set – is included in the $where array.  But I need this line explained more so I understand how the array is built: $where[] = "ProductsID = '$var1_chooseProducts';  What is ProductsID is it the array key, is it my auto-incremented primary key?

 

$var1_chooseProducts = "something";
if (isset($_GET['div_id'])) {
$var1_chooseProducts = $_GET['div_id'];
$where[]="ProductsID='$var1_chooseProducts';
}

 

So using the preceding code on each variable adds that variables value to the $where array, right?  But if the variable was not set, it is not included in the array.  Those that are set are included in the WHERE clause and separated by AND using implode.  Is this right so far?  The next part is what I really don't get.  $Query = "SELECT * FROM mytable $where;";  Am I understanding this correctly, you are using a PHP variable in an SQL statement, is that allowed?

 

if(isset($where))  // do we got any $where conditionals
  $where = "WHERE " . implode(' AND ',$where); // Yes, Build a Where Clause
else
  $where = ""; // just leave it blank
$Query="SELECT * FROM mytable $where;";
$res=mysql_query($Query);

 

If that is the case it means the recordset is created on-the-fly and is custom for each search criteria, rather than being one recordset that is filtered.  Wow, I didn't know that could be done.  But how do I get my table rows out of this query for display?  Dreamweaver uses the following method:

 

$chooseProducts = mysql_query($query_chooseProducts, $belleuser) or die(mysql_error());
$row_chooseProducts = mysql_fetch_assoc($chooseProducts);

 

Using your code would it be:

$res=mysql_query($Query, $belleuser) or die(mysql_error());
$row_res = mysql_fetch_assoc($Query);

 

I hope I'm getting this correctly.

[Barand]

Looks like you've got it. If you want to create queries on-the-fly then you have to use variables in the SQL. If not they would just be hard-coded.

[banacan]

Barand,

 

Thanks for your quick reply.  I've got a meeting to go to now so I won't be able to test this until tomorrow, but I'm pleased that I'm getting a handle on this.  Thanks again for your help and Laffin's, and don't be surprised if I come back with more questions.

 

Cheers.

[Barand]

Just noticed

 

$res=mysql_query($Query, $belleuser) or die(mysql_error());

$row_res = mysql_fetch_assoc($Query);

 

should be

<?php
$res=mysql_query($Query, $belleuser) or die(mysql_error());
$row_res = mysql_fetch_assoc($res);               // $res, not $Query

[banacan]

Hi Barand,

 

I'm getting an error and I can't figure it out.

 

$var1_chooseProducts = "-1";
if (isset($_GET['div_id'])) {
$var1_chooseProducts = $_GET['div_id'];
$where[]=ProductsID='$var1_chooseProducts';
}

 

The error says: Pause error: syntax error, unexpected "=" in...  and it points to the row that contains the $where array.  It seems to be choking on the = after the [] square brackets.  I'm afraid I still don't understand that line, what does it mean and what does it do?

 

Thanks for your help.

 

Brett

 

[Barand]

Missing ""s

$where[] = "ProductsID='$var1_chooseProducts' ";