Jump to content

[SOLVED] SQL query help


jake2891

Recommended Posts

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

Link to comment
Share on other sites

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

 

 

 

 

 

 

 

 

 

 

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.