title: Conditional WHERE clause in SELECT using if statements and php variable
body: <p>I'm trying to build a table that is conditionally organized based off user input. I need my WHERE and GROUP BY clauses to change based off user input values (from radio selects sent to php through ajax - these tested successfully), stored in php variables. I tried to run a series of if statements to change the WHERE and GROUP BY clauses based on the values of the ajax data variables and then concat then into my SELECT statement. I try the same method for building the column headers and the table itself. When I test the current code, the table headers display (the first column is empty because of the variable) but the columns aren't built. I don't receive any errors when I inspect the page and my ajax variables display the appropriate values.</p>
  
  <p>The problem is definately with the IF statements but I'm not sure how to fix that.</p>
  
  <p>Note: the SQL statements works perfectly when I manually enter a WHERE or GROUP BY clause using the same values I'm trying to store in variables.</p>
  
  <p>MY CODE:</p>
  
  <p>AJAX Response:</p>
  
  <pre><code>//Get Table Type.
  if (isset($_POST['revenueTblType'])) {
    $revenueTblType = $_POST['revenueTblType'];
    print_r($revenueTblType);
  };
  
  //Get Report Type
  if (isset($_POST['revenueWO'])) {
    $revenueWO = $_POST['revenueWO'];
    print_r($revenueWO);
  };
  
  //Get date include value.
  if (isset($_POST['revenueWODate'])) {
    $revenueWODate = $_POST['revenueWODate'];
    print_r($revenueWODate);
  };
  
  //Get date range.
  $revenuefromajax=$_POST['revenuefrom'];
  $revenuetoajax=$_POST['revenueto'];
  
  $revenuefromstring = strtotime($revenuefromajax);
  $revenuetostring = strtotime($revenuetoajax);
  
  $revenuefrom=date("Y-m-d", $revenuefromstring);
  $revenueto=date("Y-m-d", $revenuetostring);
  
  //Get selected Status Values.
  if (isset($_POST['revenue_checkboxes'])) {
    $revenue_check = $_POST['revenue_checkboxes'];
  };
  </code></pre>
  
  <p>IF STATEMENTS and TABLE BUILD:</p>
  
  <pre><code>/////////////   Select Data and Display it in a table.   //////////////
  
  
  //$groupbyclause = "";
  
      if ($revenueTblType=='Customer') {
          $groupbyclause="x.company ASC";
          $columnheader='Customer';
          $columnname='company';
      }
      else if ($revenueTblType=='Revenue Category') {
          $groupbyclause="x.revenue ASC";
          $columnheader='Revenue Category';
          $columnname='revenue';
      }
  
  
  //$whereclause = "";
  
      if (($revenueWO=='Completed Workorders') and ($revenueWODate=='All Workorders')) {
          $whereclause="x.stagestatus = 'Complete'";
      }
      else if (($revenueWO=='Completed Workorders') and ($revenueWODate=='Workorder Date Range')) {
          $whereclause ="x.stagestatus = 'Complete' AND x.shippeddate BETWEEN '".$revenuefrom."' AND '".$revenueto."'"; 
      }
      else if ($revenueWO=='Workorder Status') {
           $whereclause ="x.stagestatus IN (". implode(',', array_map(function($item) {return '"' . $item . '"'; }, $revenue_check)) .")";
      }
  
  
      echo "&lt;BR&gt;";
      echo "&lt;BR&gt;";
  
  
      //SELECT statement pulls ALL COMPLETED history info by CUSTOMER.
      $sql="SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (t1.totalprice/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
        FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
        WHERE (t2.invoiceid = t1.id)
        GROUP BY t1.id) x
        WHERE '".$whereclause."'
        GROUP BY ".$groupbyclause.""; //this line edited per comment suggestions.//
  
  
  
  $result = $conn-&gt;query($sql);       
  
  
      echo "&lt;table id='revenueReportA' align='center' class='report_DT'&gt;
      &lt;thead&gt;
      &lt;tr&gt;
  
  
      &lt;th&gt;".$columnheader."&lt;/th&gt;
      &lt;th&gt;Total Revenue&lt;/th&gt;
      &lt;th&gt;Total SQ FT&lt;/th&gt;
      &lt;th&gt;AVG Revenue Per SQ FT&lt;/th&gt;
      &lt;th&gt;Total Number of Units&lt;/th&gt;
      &lt;th&gt;AVG Revenue Per Unit&lt;/th&gt;
      &lt;/tr&gt;
      &lt;/head&gt;";
  
  
          if ($result = $conn-&gt;query($sql)) {
  
              // fetch associative array 
              while ($row = $result-&gt;fetch_assoc()) {
  
                echo "&lt;tbody&gt;";
                echo "&lt;tr&gt;";
                echo "&lt;td&gt;" . $row[$columnname] . "&lt;/td&gt;";
                echo "&lt;td&gt;" ."$". $row['totalprice'] . "&lt;/td&gt;";
                echo "&lt;td&gt;" . $row['sgtotsqft'] ."&amp;nbsp;&amp;nbsp;". "ft&lt;sup&gt;2&lt;/sup&gt;". "&lt;/td&gt;";
                echo "&lt;td&gt;" ."$". $row['avgsqftrevenue'] . "&lt;/td&gt;";
                echo "&lt;td&gt;" . $row['totqty'] . "&lt;/td&gt;";
                echo "&lt;td&gt;" ."$". $row['avgunitrevenue'] . "&lt;/td&gt;";
                echo "&lt;/tr&gt;";
                echo "&lt;/tbody&gt;";
                }//End table while.
                echo "&lt;/table&gt;";
                echo "&lt;BR&gt;";
  
           }//End table if.
  
  ///////////////////////////////////////////////////////////////////////////////
  
  
  //Free the result variable. 
  $result-&gt;free();
  
  
  
  //Close the Database connection.
  $conn-&gt;close(); 
  </code></pre>
  
  <p>All suggestions are welcome. Thank you!</p>