rdimouro rdimouro - 1 month ago 11
jQuery Question

use arrayed checkbox values in php MySQL WHERE clause

I am trying to send the values of 7 jquery checkboxes to php via ajax. I am attempting to put the values in an array and serialize the array in ajax. Ultimately, I would like to use the values of the checkboxes as conditions in a MySQL WHERE clause. My ajax completes successfully but I'm not sure how to pull the values out of the php variable and use them in the WHERE clause of my MySQL SELECT statement.

Thank you!!!

CODE:

My HTML code:

<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();


?>


Note: I included the other ajax call for revenueto and revenuefrom date. This call is successful and my table displays correctly based off those dates. I just can't seem the get all actual values for my selected checkboxes from the same page as the dates.

Once I get the values, I'll also take any suggestions for properly using them in my WHERE statement. Each checkbox value matches the a value option invoices.stagestatus.

Thank you!

Answer

As you guessed, with your current code only the first checkbox value is added to the data you pass to Ajax.
To simply grasp the whole form data you can merely use $('form').serialize(), like demonstrated by this snippet:

$(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>

You see that, with this unique statement, you get a pretty complete set of all your data, so you can use it in your Ajax call with nothing more than this:

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

So in PHP you receive as many $_POST items as exist different names in your form. So the $_POST['revenue_checkboxes'] one will be an array (with only the checked values).

BTW PLEASE NOTE: in your current PHP code you're looking for $_POST['revenuechbx'], which was correct because it's how you directly named it in the data{} argument of the Ajax call. But with the above code you get the HTML name attributes.

Now regarding how to use the checkboxes values in your WHERE clause, your question is not clear enough to be totally sure.
But if the possible values of the stagestatus column in invoices are directly the ones cited as value attribute in your HTML <input>s, then it's pretty simple:

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

Above we use array_map() to wrap quotes around each item, then implode() to get a comma-separated list, so populating the SQL IN() clause.
This way, only the invoices with one of the checked statuses will be selected.

Comments