Query / Where clause issue with Null values

[jj0311]

First off thanks for your time.

 

I have 6 variable values being passed from drop-down menus on another page with php to this page. The variable values are then used to set operator values for the where clause as well as used in the where clause of the sql statement.  If someone selects 'All' from the drop down PHP passes a variable value for that field of '0' then the the next page evaluates that $variable and makes the sql operator '<>'  theoretically selecting all records based on '0' not being a value in and field, if 'All' is not selected then whatever specific field values is selected is passed and the operator variable is '=' and the query works correctly. So the problem I am having is that when a  users selects 'All' (<> '0' for a field value the WHERE clause it doesn't return any of the records if they have a null value in that field.

 

Is there a way to get the sql statement to pull all values including nulls?

Is there a better way to get All values other than rigging the php and sql up as I have?

 

Here is my PHP and SQL:

 

<?php
$Wine_Name = $_POST['Wine_Name'];
$Vintage = $_POST['Vintage'];
$Vineyard = $_POST['Vineyard'];
$Varietal = $_POST['Varietal'];
$Appellation = $_POST['Appellation'];
$Producer = $_POST['Producer'];
?>

<?php 
IF ($Wine_Name == '0') {
$Operator_Wine_Name = '<>';
}
Else {
$Operator_Wine_Name = '=';
}
?>
<?php
IF ($Vintage == '0') {
$Operator_Vintage = '<>';
}
Else {
$Operator_Vintage = '=';
}
?>
<?php
IF ($Vineyard == '0') {
$Operator_Vineyard = '<>';
}
Else {
$Operator_Vineyard = '=';
}
?>
<?php
IF ($Varietal == '0') {
$Operator_Varietal = '<>';
}
Else {
$Operator_Varietal = '=';
}
?>
<?php
IF ($Appellation == '0') {
$Operator_Appellation = '<>';
}
Else {
$Operator_Appellation = '=';
}
?>
<?php
IF ($Producer == '0') {
$Operator_Producer = '<>';
}
Else {
$Operator_Producer = '=';
}
?>



"SELECT WINE.WINE_IMAGE_LABEL, WINE.WINE_IMAGE_BOTTLE, WINE.WINE_PROPRIETARY_NAME, WINE.WINE_VINTAGE, WINE.WINE_ALCOHOL_CONTENT, WINE.WINE_PRICE_RANGE, WINE.WINE_UPC, VINEYARD.VINEYARD_DESCRIPTION, VARIETAL_TYPE.VARIETAL_TYPE_DESCRIPTION, TASTING_NOTE.TASTING_NOTE_TEXT, TASTING_NOTE.TASTING_NOTE_DATE, PRODUCER.PRODUCERS_DESCRIPTION, APPELLATION.APPELLATION_DESCRIPTION 
FROM WINE 
LEFT JOIN APPELLATION ON WINE.APPELLATION_ID = APPELLATION.APPELLATION_ID
LEFT JOIN PRODUCER ON WINE.PRODUCER_ID = PRODUCER.PRODUCER_ID
LEFT JOIN VINEYARD ON WINE.VINEYARD_ID = VINEYARD.VINEYARD_ID
LEFT JOIN VARIETAL_TYPE ON WINE.VARIETAL_TYPE_ID = VARIETAL_TYPE.VARIETAL_TYPE_ID
LEFT JOIN TASTING_NOTE ON WINE.WINE_ID = TASTING_NOTE.WINE_ID
LEFT JOIN RATING ON TASTING_NOTE.RATING_ID = RATING.RATING_ID
WHERE WINE.WINE_PROPRIETARY_NAME $Operator_Wine_Name '$Wine_Name'
AND WINE.WINE_VINTAGE $Operator_Vintage $Vintage
AND WINE.VINEYARD_ID $Operator_Vineyard $Vineyard
AND WINE.VARIETAL_TYPE_ID $Operator_Varietal $Varietal
AND WINE.APPELLATION_ID $Operator_Appellation $Appellation
AND WINE.PRODUCER_ID $Operator_Producer $Producer
ORDER BY WINE.WINE_ID"

[cags]

0 and Null are not the same values. You can't do comparisons using <> and NULL You would be using IS NOT NULL.

 

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

[jj0311]

Ok, so is there a better way to handle someone selecting 'All'?

 

perhaps something like:

 

SELECT * FROM WINE WHERE WINE.NAME = ??? <---(something that will select all records with entered values and Null values)

[cags]

You use IS NOT NULL as I mentioned in my first post...

[fenway]

Besides, you decide what to do with the variables passed by the form.

[jj0311]

Thanks for the help.

[DavidAM]

In this situation, I would build up the where clause in a variable:

 

<?php 
$where = '';
if (! empty($Wine_Name)) { // using empty() on a value of zero will return true, so if not empty
   $where .= (empty($where) ? 'WHERE ' : ' AND ') . "WINE.WINE_PROPRIETARY_NAME = '" . mysql_real_escape($Wine_Name) . "'";

// you don't need to keep skipping out and in of PHP so just loose these two lines (below)
?>
<?php
if (! empty($Vintage))
  $where .= (empty($where) ? 'WHERE ' : ' AND ') . "WINE.WINE_VINTAGE = '" . mysql_real_escape($Vintage) . "'";

// ... and so on for all of the others
// ...
// ...

// where are you outside of php here?
// anyway, just stick the $where variable into the query ...
$sql = "SELECT WINE.WINE_IMAGE_LABEL, WINE.WINE_IMAGE_BOTTLE, WINE.WINE_PROPRIETARY_NAME, WINE.WINE_VINTAGE, WINE.WINE_ALCOHOL_CONTENT, WINE.WINE_PRICE_RANGE, WINE.WINE_UPC, VINEYARD.VINEYARD_DESCRIPTION, VARIETAL_TYPE.VARIETAL_TYPE_DESCRIPTION, TASTING_NOTE.TASTING_NOTE_TEXT, TASTING_NOTE.TASTING_NOTE_DATE, PRODUCER.PRODUCERS_DESCRIPTION, APPELLATION.APPELLATION_DESCRIPTION
FROM WINE
LEFT JOIN APPELLATION ON WINE.APPELLATION_ID = APPELLATION.APPELLATION_ID
LEFT JOIN PRODUCER ON WINE.PRODUCER_ID = PRODUCER.PRODUCER_ID
LEFT JOIN VINEYARD ON WINE.VINEYARD_ID = VINEYARD.VINEYARD_ID
LEFT JOIN VARIETAL_TYPE ON WINE.VARIETAL_TYPE_ID = VARIETAL_TYPE.VARIETAL_TYPE_ID
LEFT JOIN TASTING_NOTE ON WINE.WINE_ID = TASTING_NOTE.WINE_ID
LEFT JOIN RATING ON TASTING_NOTE.RATING_ID = RATING.RATING_ID " . 
$where . 
" ORDER BY WINE.WINE_ID";

 

This is just a cut-and-paste example.  It has not been tested.  But I would build up the where clause this way; you don't have to worry about the value of any field the user did not restrict, and you are not doing unnecessary test at the server.