小贝子编程

在php MySQL WHERE子句中使用数组复选框值



我试图通过ajax将7个jquery复选框的值发送到php。我试图将值放在数组中，并在ajax中序列化数组。最后，我想在MySQL WHERE子句中使用复选框的值作为条件。我的ajax完成成功，但我不确定如何拉出的值php变量，并在我的MySQL SELECT语句的WHERE子句中使用它们。

谢谢! !

代码:

我的HTML代码: 
<label for="prestage_select">Prestage</label>
<input type="checkbox" name="revenue_checkboxes[]" id="prestage_select" class="revenuechbxs" value="Prestage">

<label for="validation_select">Validation</label>
<input type="checkbox" name="revenue_checkboxes[]" id="validation_select" class="revenuechbxs" value="Validation"> 

<label for="scheduling_select">Scheduling</label>
<input type="checkbox" name="revenue_checkboxes[]" id="scheduling_select" class="revenuechbxs" value="Scheduling">

<label for="production_select">Production</label>
<input type="checkbox" name="revenue_checkboxes[]" id="production_select" class="revenuechbxs" value="Production">

<label for="needsBOL_select">Needs BOL</label>
<input type="checkbox" name="revenue_checkboxes[]" id="needsBOL_select" class="revenuechbxs" value="Needs BOL">

<label for="shpAcct2Close_select">Shipped: Account to Close</label>
<input type="checkbox" name="revenue_checkboxes[]" id="shpAcct2Close_select" class="revenuechbxs" value="Shipped: Acctg. To Close Out">

<label for="movedToComplete_select">Moved to Complete for Selected Period</label>
<input type="checkbox" name="revenue_checkboxes[]" id="movedToComplete_select" class="revenuechbxs" value="Complete">

My Ajax Code: 

j("#create_submit").click(function(){

     //This works perfectly as long but the dates don't get sent through, causing my WHERE clause to fail unless I remove the $revenuefrom and $revenueto parts of the WHERE clause...
     j.ajax ({
                  method: 'POST',
                  url: "revenue_report.php",
                  data: j('#revenue_form').serializeArray(),
                  success: function( response ) {
                      j('#fieldset_ReportDiv').html(response);
                  }
              });

        //No longer works...
            //send Revenue Data values to php using ajax.
                 // var revenuechbxarray = j('.revenuechbxs:checked').serializeArray();
                  var revenuefrom = j('#revenuefrom').val();
                  var revenueto = j('#revenueto').val();
                  j.ajax ({
                      method: 'POST',
                      url: "revenue_report.php",
                      data: { revenuefromtext: revenuefrom, revenuetotext: revenueto },
                  success: function( response ) {
                      j('#fieldset_ReportDiv').html(response);
                  }
                  });

        //   console.log(revenuechbxarray);

My PHP Code:

<?php
include('inc.php');

//Get date range.
$revenuefromajax=$_POST['revenuefromtext'];
$revenuetoajax=$_POST['revenuetotext'];
$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'];
    print_r($revenue_check);

    }; //Correctly displays only the values of selected checkboxes.

//connect  to the database 
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if(mysqli_connect_errno() ) {
    printf('Could not connect: ' . mysqli_connect_error());
    exit();
}
//echo 'MySQL Connected successfully.'."<BR>";

$conn->select_db("some database name");  /////Database name has been changed for security reasons/////////
if(! $conn->select_db("some database name") ) {
    echo 'Could not select database. '."<BR>";
}
// echo 'Successfully selected database. '."<BR>";
//Select Data and Display it in a table.

$sql = "SELECT invoices.id, invoices.orderdate, invoices.stagestatus, FORMAT(TRIM(LEADING '$' FROM invoices.totalprice), 2) AS totalprice, clients.company, lineitems.invoiceid, FORMAT((lineitems.width * lineitems.height) /144, 2 ) AS sqft, lineitems.quantity AS qty, FORMAT((invoices.totalprice / ((lineitems.width * lineitems.height) /144)), 2) as avgsqftrevenue, FORMAT((TRIM(LEADING '$' FROM invoices.totalprice) / lineitems.quantity), 2) AS avgunitrevenue
    FROM clients
    INNER JOIN invoices ON clients.id = invoices.clientid
    INNER JOIN lineitems ON invoices.id = lineitems.invoiceid
    WHERE invoices.orderdate BETWEEN '".$revenuefrom."' AND '".$revenueto."' AND invoices.stagestatus IN (' .
    implode(',' array_map(function($revenue_check) {
      return '" . $revenue_check . "';
    })) . '
    )
    ORDER BY invoices.id DESC";

$result = $conn->query($sql);

echo "<table id='revenueReportA' align='center' class='report_DT'>
<tr>
<th>Customer</th>
<th>SG</th>
<th>Revenue</th>
<th>SQ FT</th>
<th>AVG Revenue Per SQ FT</th>
<th>Number of Units</th>
<th>AVG Revenue Per Unit</th>
</tr>";

 if ($result = $conn->query($sql)) {
     // fetch associative array 
     while ($row = $result->fetch_assoc()) {
     echo "<tr>";
     echo "<td>" . $row['company'] . "</td>";
     echo "<td>" . $row['id'] . "</td>";
     echo "<td>" ."$". $row['totalprice'] . "</td>";
     echo "<td>" . $row['sqft'] ."&nbsp;&nbsp;". "ft<sup>2</sup>". "</td>";
     echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
     echo "<td>" . $row['qty'] . "</td>";
     echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
     echo "</tr>";
     } 
     echo "</table>";
      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
     //Free the result variable. 
     $result->free();
}
//Close the Database connection.
$conn->close(); 

?>

注意:我包括了另一个ajax调用revenueto和revenuefromdate。这个调用是成功的，我的表根据这些日期正确显示。我似乎无法从与日期相同的页面中获取所选复选框的所有实际值。

一旦我得到了这些值，我还将采纳在WHERE语句中正确使用它们的任何建议。每个复选框的值都匹配a值选项invoice .stagestatus.

谢谢!

编辑:你也可以看看下面@rdimouro自己的回答，在那里他展示了它现在完整的工作版本。

正如您所猜测的，对于当前代码，只有第一个复选框值被添加到传递给Ajax的数据中。
要简单地掌握整个表单数据，只需使用$('form').serialize()，如下代码段所示:

$(document).ready(function() {
  console.log($('form').serialize());
});
label, button {
  display: block;
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<form>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Prestage" checked>
    Prestage
  </label>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Validation" checked>
    Validation
  </label>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Scheduling">
    Scheduling
  </label>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Production" checked>Production
  </label>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Needs BOL">Needs BOL
  </label>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Shipped: Acctg. To Close Out">Shipped: Account to Close
  </label>
  <label>
    <input type="checkbox" name="revenue_checkboxes[]" value="Complete">Moved to Complete for Selected Period
  </label>
  <label>
    Other data
    <input type="text" name="other-data" value="Some other data">
  </label>
</form>

您可以看到，使用这个唯一语句，您可以获得所有数据的相当完整的集合，因此您可以在Ajax调用中使用它，仅需要以下内容:

j("#create_submit").click(function(){
    //send Revenue Data values to php using ajax.
    j.ajax ({
        method: 'POST',
        url: "revenue_report.php",
        data: j('#yourFormId').serialize(),
        success: function( response ) {
            j('#fieldset_ReportDiv').html(response);
        }
    });
    return false;
});

因此在PHP中，您收到的$_POST项与表单中存在的不同name项一样多。因此，$_POST['revenue_checkboxes']将是一个数组(只有检查值)。

BTW请注意:在您当前的PHP代码中，您正在寻找$_POST['revenuechbx']，这是正确的，因为这是您如何在Ajax调用的data{}参数中直接命名它。但是通过上面的代码，你得到了HTML name属性。

现在关于如何在WHERE子句中使用复选框值，您的问题不够清楚，无法完全确定。
但是，如果invoices中stagestatus列的可能值直接引用为HTML <input> s中的value属性，那么它非常简单:

$sql = '
SELECT invoices.id, invoices.orderdate, invoices.stagestatus, ...
FROM clients
    INNER JOIN invoices ON clients.id = invoices.clientid
    INNER JOIN lineitems ON invoices.id = lineitems.invoiceid
WHERE invoices.orderdate BETWEEN '".$revenuefrom."' AND '".$revenueto."'
    AND invoices stagestatus IN (' .
        implode(',', array_map(function($item) {
          return '"' . $item . '"';
        }, $revenue_check)) . '
    )
ORDER BY invoices.id DESC
';

上面我们使用array_map()在每个项目周围加上引号，然后使用implode()获得一个逗号分隔的列表，从而填充SQL IN()子句。这样，只有具有选中状态之一的发票才会被选中。

可以通过ajax请求传递数组。初始化为一个空数组，然后将每个选中的复选框的值推入数组(我已经注释了您的代码以显示在哪里进行更改):

//send Revenue Data values to php using ajax.
//var revenuechbxarray = j('.revenuechbxs:checked').val();
var revenuechbxarray = [];
j('.revenuechbxs:checked').each(function(){
   revenuechbxarray.push(j(this).val());
});

我不知道是否有一种最优雅的方式。

提交时，你会看到这样的内容:

Array ( [0] => Prestage [1] => Validation [2] => Scheduling )

请注意，未选中的元素不会被发布。

在PHP端，检查一个复选框是否被提交，检查它的值是否在数组中:

if (in_array('Prestage', $revenue_check)){
     // your stuff here
}

这是适用于我的情况的完整答案。感谢cFreed的所有帮助和编辑!!!!!!

我代码:

HTML: 

    <label for="prestage_select">Prestage</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="prestage_select" class="revenuechbxs" value="Prestage">

    <label for="validation_select">Validation</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="validation_select" class="revenuechbxs" value="Validation"> 

    <label for="scheduling_select">Scheduling</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="scheduling_select" class="revenuechbxs" value="Scheduling">

    <label for="production_select">Production</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="production_select" class="revenuechbxs" value="Production">

    <label for="needsBOL_select">Needs BOL</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="needsBOL_select" class="revenuechbxs" value="Needs BOL">

    <label for="shpAcct2Close_select">Shipped: Account to Close</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="shpAcct2Close_select" class="revenuechbxs" value="Shipped: Acctg. To Close Out">

    <label for="movedToComplete_select">Moved to Complete for Selected Period</label>
    <input type="checkbox" name="revenue_checkboxes[]" id="movedToComplete_select" class="revenuechbxs" value="Complete">
AJAX: 

          j("#create_submit").click(function(){

            //send Revenue Data values to php using ajax.
               j.ajax ({
                  method: 'POST',
                  url: "revenue_report.php",
                  data: j('#revenue_form').serializeArray(),
                  success: function( response ) {
                      j('#fieldset_ReportDiv').html(response);
                  }
               });

            //Remove Criteria selection fields.
            j("#fieldset_2").remove(); 
            j("#fieldset_3").remove();
            j("#fieldset_4").remove();
            j("#fieldset_5").remove();
            j("#fieldset_6").remove();
            j("#fieldset_7").remove();
            j("#createDiv").remove();
            //Show selected tables.
            j("#revenueDT").show();
            j("#revenueReport").css({"visibility":"visible", "display":"block"});
            j("#ontimeReport").css({"visibility":"visible", "display":"block"});
            j("#rejectReport").css({"visibility":"visible", "display":"block"});
            j("#scheduleReport").css({"visibility":"visible", "display":"block"});
            j("#customReport").css({"visibility":"visible", "display":"block"});


          });

       return false;
PHP: 

    <?php
    include('inc.php');

    //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.
    //$revenuecheckboxes=$_POST['revenuechbx'];
    //echo $revenuecheckboxes;
    //$revenuecheckboxes=var_dump($_POST['revenuechbx']);
    if (isset($_POST['revenue_checkboxes'])) {
    $revenue_check = $_POST['revenue_checkboxes'];

    };



    //connect  to the database 
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if(mysqli_connect_errno() ) {
      printf('Could not connect: ' . mysqli_connect_error());
      exit();
    }
    //echo 'MySQL Connected successfully.'."<BR>";

    $conn->select_db("some_DB_name");
     if(! $conn->select_db("some_DB_name") ) {
         echo 'Could not select database. '.'<BR>';
     }
    // echo 'Successfully selected database. '."<BR>";
    //Select Data and Display it in a table.

    $sql = "SELECT invoices.id, invoices.orderdate, invoices.stagestatus, FORMAT(TRIM(LEADING '$' FROM invoices.totalprice), 2) AS totalprice, clients.company, lineitems.invoiceid, FORMAT((lineitems.width * lineitems.height) /144, 2 ) AS sqft, lineitems.quantity AS qty, FORMAT((invoices.totalprice / ((lineitems.width * lineitems.height) /144)), 2) as avgsqftrevenue, FORMAT((TRIM(LEADING '$' FROM invoices.totalprice) / lineitems.quantity), 2) AS avgunitrevenue
    FROM clients
    INNER JOIN invoices ON clients.id = invoices.clientid
    INNER JOIN lineitems ON invoices.id = lineitems.invoiceid
    WHERE invoices.orderdate BETWEEN '".$revenuefrom."' AND '".$revenueto."' AND invoices.stagestatus IN (". implode(',', array_map(function($item) {return '"' . $item . '"'; }, $revenue_check)) .")
    ORDER BY invoices.id DESC";



    //Display daterange and table.
    echo 'Displaying results for: '.$revenuefrom.' to '.$revenueto.'. '.'<BR><BR><BR>';
    $result = $conn->query($sql);

    echo "<table id='revenueReportA' align='center' class='report_DT'>
    <tr>
    <th>Customer</th>
    <th>Stage Status</th>
    <th>SG</th>
    <th>Revenue</th>
    <th>SQ FT</th>
    <th>AVG Revenue Per SQ FT</th>
    <th>Number of Units</th>
    <th>AVG Revenue Per Unit</th>
    </tr>";

     if ($result = $conn->query($sql)) {
        // fetch associative array 
      while ($row = $result->fetch_assoc()) {

        echo "<tr>";
        echo "<td>" . $row['company'] . "</td>";
        echo "<td>" . $row['stagestatus'] . "</td>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" ."$". $row['totalprice'] . "</td>";
        echo "<td>" . $row['sqft'] ."&nbsp;&nbsp;". "ft<sup>2</sup>". "</td>";
        echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
        echo "<td>" . $row['qty'] . "</td>";
        echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
        echo "</tr>";
        } 
        echo "</table>";

     //Free the result variable. 
     $result->free();
     }

    //Close the Database connection.
    $conn->close(); 

    ?>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium