[SOLVED] SQL query help

[jake2891]

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";

[mezise]

What is the wrong result of this query and what result it should be? Provide us some tabular data and post generated SQL query.

[jake2891]

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

 

 

 

 

 

 

 

 

 

 

 

[mezise]

Could you format those queries, because it is very hard to read it?

[jake2891]

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)

[fenway]

The only checkbox that is tricky in the one that spans multiple columns... simply translate this into an OR.

[jake2891]

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;';
  }