Hi,
I did try that. I have even built the query in a separate query builder that will tell me if I have an error. I am still thinking it is somewhere in the PHP code for the date range.
My code for the date range now looks like this:
//comparison list additions
$WADbSearch1->addComparisonFromEdit("OrderDate","S_OrderDate","AND",">=",2);
$WADbSearch1->addComparisonFromEdit("OrderDate","E_OrderDate","AND","<=",2);
$WADbSearch1->addComparison("OrderDate","NULL","OR","IS","<=>",2);
And my query looks like this:
SELECT inventory.itemId AS Item_ID, inventory.itemName AS Item, inventory.itemDesc AS Description, inventory.itemSize AS Size, inventory.itemColor AS Color, inventory.partNum AS Part_Number, inventory.prodLine AS Product_Line, inventory.itemPrice AS Price, inventory.itemQty AS Quantity_Available, (inventory.itemQty*inventory.itemPrice)AS Total_Value_of_Available, SUM(orderdetails.DetailQuantity)AS Quantity_Sold, SUM(orderdetails.DetailQuantity*inventory.itemPrice) AS Total_Value_of_Usage
FROM orderdetails RIGHT OUTER JOIN inventory ON orderdetails.DetailItemId = inventory.itemId LEFT OUTER JOIN orders ON orderdetails.DetailOrderId = orders.OrderId
GROUP BY inventory.itemId
ORDER BY inventory.itemName ASC
Your suggestion almost worked, but it wouldn’t return anything. Through testing I found that $WADbSearch1->addComparison("OrderDate","NULL","OR","IS","<=>",2); works, but when I want to view all items without any date range, it is now returning just 123 rows, but there is 253 rows in my inventory table. I can’t figure out why it isn’t picking up the other 130 items.
When I take out the line $WADbSearch1->addComparison("OrderDate","NULL","OR","IS","<=>",2); it returns all items when I DO NOT enter a date range, but then when I do enter a date range search it does not pick up unsold items.
I have also tried the date range like this:
//comparison list additions
$WADbSearch1->addComparisonFromEdit("itemName","S_itemName","AND","Includes",0);
$WADbSearch1->addComparisonFromEdit("itemDesc","S_itemDesc","AND","Includes",0);
$WADbSearch1->addComparisonFromEdit("itemPrice","S_itemPrice","AND","Includes",0);
$WADbSearch1->addComparisonFromEdit("itemQty","S_itemQty","AND","=",1);
$WADbSearch1->addComparisonFromEdit("partNum","S_partNum","AND","Includes",0);
$WADbSearch1->addComparisonFromEdit("prodLine","S_prodLine","AND","Includes",0);
$WADbSearch1->addComparison("orders.OrderDate","".((isset($_GET["S_OrderDate"]))?WAQB_getEndDate($_GET["E_OrderDate"]):"") ."","AND","<=",2);
But this one is not picking up any null values period. Can I maybe add something at the end of this line to make it return null values within the date range? I have tried several combinations with no luck.
Any help would be appreciated.