jake2891 Posted May 12, 2008 Share Posted May 12, 2008 Hey guys, i have this query im trying to write which is 80% working. I have 4 checkboxes and when they are selected or unselected they return or dont return specific data. the problem is, is that when all are unselected and then i select a specific one or two the query does not work it scrambles up. the propblem is in the WHERE part of the query. The $REQUEST pending, unavailable, available and archived get the values from the four checkboxes. any ideas on how to write this query so that if 2 checkboxes are selected or none or all etc they dont interfere with eachother. any help would be greatly appreciated. sql below $query = " SELECT p.propertyID, p.p_buildingName, p.p_street, p.p_bedrooms, p.negotiator, pds.p_sale_price, pds.propertyID AS isSale FROM property p INNER JOIN property_detail_sale pds ON p.propertyID=pds.propertyID INNER JOIN custom_property_internal_status_sales cpiss ON cpiss.internalStatus=pds.p_sale_internalStatus WHERE p.p_visibility = 'Shown' ".($_REQUEST['propertyListShowType'] == 'my' ? " AND p.negotiator = '".userID()."' " : "")." ".($_REQUEST['includeArchived'] == 'no' ? ' AND (cpiss.isArchived IS NULL)' : '')." ".($_REQUEST['includeAvailable'] == 'no' ? ' AND (cpiss.isAvailable IS NULL)' : '')." ".($_REQUEST['includeUnavailable'] == 'no' ? ' AND (cpiss.isAvailable = 1 OR cpiss.isPending = 1 AND cpiss.isArchived IS NULL OR cpiss.isPending IS NULL AND cpiss.isArchived = 1)' : '')." ".($_REQUEST['includePending'] == 'no' ? ' AND (cpiss.isArchived = 1)': '')." ".($subWhereQuery ? $subWhereQuery : '')." ORDER BY ".$orderBy." LIMIT 200"; Quote Link to comment Share on other sites More sharing options...
mezise Posted May 12, 2008 Share Posted May 12, 2008 What is the wrong result of this query and what result it should be? Provide us some tabular data and post generated SQL query. Quote Link to comment Share on other sites More sharing options...
jake2891 Posted May 12, 2008 Author Share Posted May 12, 2008 What is the wrong result of this query and what result it should be? Provide us some tabular data and post generated SQL query. This is the reult i get when all 4 checkboxes are unticked. SELECT p.propertyID, p.p_buildingName, p.p_street, p.p_bedrooms, p.negotiator, pds.p_sale_price, pds.propertyID AS isSale FROM property p INNER JOIN property_detail_sale pds ON p.propertyID=pds.propertyID INNER JOIN custom_property_internal_status_sales cpiss ON cpiss.internalStatus=pds.p_sale_internalStatus WHERE p.p_visibility = 'Shown' AND (cpiss.isArchived IS NULL) AND (cpiss.isAvailable IS NULL) AND (cpiss.isAvailable = 1 OR cpiss.isPending = 1 AND cpiss.isArchived IS NULL OR cpiss.isPending IS NULL AND cpiss.isArchived = 1) AND (cpiss.isArchived = 1) so this is showing no results which is rite. But when i select the "isAvailable" checkbox or "Unavailable" it stil shows no results which is wrong. This is the query when i check those two checkboxes. SELECT p.propertyID, p.p_buildingName, p.p_street, p.p_bedrooms, p.negotiator, pds.p_sale_price, pds.propertyID AS isSale FROM property p INNER JOIN property_detail_sale pds ON p.propertyID=pds.propertyID INNER JOIN custom_property_internal_status_sales cpiss ON cpiss.internalStatus=pds.p_sale_internalStatus WHERE p.p_visibility = 'Shown' AND (cpiss.isArchived IS NULL) AND (cpiss.isArchived = 1) however if i select the last two boxes "pending" and "archived" first , it returns results then if i select the two that were not working they work correctly. Tabular data id status isAvailable isPending isArchived 1 MA 1 2 PD 1 3 OM 1 4 W 1 5 UO 1 1 6 UN 1 1 7 S 1 1 8 E 1 1 Quote Link to comment Share on other sites More sharing options...
mezise Posted May 12, 2008 Share Posted May 12, 2008 Could you format those queries, because it is very hard to read it? Quote Link to comment Share on other sites More sharing options...
jake2891 Posted May 12, 2008 Author Share Posted May 12, 2008 Could you format those queries, because it is very hard to read it? SELECT p.propertyID, p.p_buildingName, p.p_street, p.p_bedrooms, p.negotiator, pds.p_sale_price, pds.propertyID AS isSale FROM property p INNER JOIN property_detail_sale pds ON p.propertyID=pds.propertyID INNER JOIN custom_property_internal_status_sales cpiss ON cpiss.internalStatus=pds.p_sale_internalStatus WHERE p.p_visibility = 'Shown' AND (cpiss.isArchived IS NULL) AND (cpiss.isAvailable IS NULL) AND (cpiss.isAvailable = 1 OR cpiss.isPending = 1 AND cpiss.isArchived IS NULL OR cpiss.isPending IS NULL AND cpiss.isArchived = 1) AND (cpiss.isArchived = 1) SELECT p.propertyID, p.p_buildingName, p.p_street, p.p_bedrooms, p.negotiator, pds.p_sale_price, pds.propertyID AS isSale FROM property p INNER JOIN property_detail_sale pds ON p.propertyID=pds.propertyID INNER JOIN custom_property_internal_status_sales cpiss ON cpiss.internalStatus=pds.p_sale_internalStatus WHERE p.p_visibility = 'Shown' AND (cpiss.isArchived IS NULL) AND (cpiss.isArchived = 1) Quote Link to comment Share on other sites More sharing options...
fenway Posted May 12, 2008 Share Posted May 12, 2008 The only checkbox that is tricky in the one that spans multiple columns... simply translate this into an OR. Quote Link to comment Share on other sites More sharing options...
jake2891 Posted May 13, 2008 Author Share Posted May 13, 2008 The only checkbox that is tricky in the one that spans multiple columns... simply translate this into an OR. Hey guys thanks for all your input and suggestions. I have fixed the problem and im posting the fix incase any of you come across a problem like this. Fix below $query = " SELECT p.propertyID, p.p_buildingName, p.p_street, p.p_bedrooms, p.negotiator, pds.p_sale_price, pds.propertyID AS isSale FROM property p INNER JOIN property_detail_sale pds ON p.propertyID=pds.propertyID INNER JOIN custom_property_internal_status_sales cpiss ON cpiss.internalStatus=pds.p_sale_internalStatus WHERE p.p_visibility = 'Shown' ".$whereClause." ".($subWhereQuery ? $subWhereQuery : ''); $whereClause = '('; if($_REQUEST['includeArchived'] == 'yes') { $whereClause .= '(cpiss.isArchived = 1 AND cpiss.isPending IS NULL) '; } if($_REQUEST['includeAvailable'] == 'yes') { if($whereClause != '(') { $whereClause .= ' OR '; } $whereClause .= ' (cpiss.isAvailable = 1) '; } if($_REQUEST['includePending'] == 'yes'){ if($whereClause != '(') { $whereClause .= ' OR '; } $whereClause .= ' (cpiss.isPending = 1 AND cpiss.isAvailable IS NULL AND cpiss.isArchived IS NULL) '; } if($_REQUEST['includeUnavailable'] == 'yes') { if($whereClause != '(') { $whereClause .= ' OR '; } $whereClause .= ' (cpiss.isPending = 1 AND cpiss.isAvailable IS NULL AND cpiss.isArchived = 1) '; } $whereClause .= ')'; if($whereClause != '()') { $query .= ' AND '.$whereClause; $query .= " ORDER BY ".$orderBy." LIMIT 200"; } else { $query = 'select false;'; } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.