use arrayed checkbox values in php MySQL WHERE clause

Question

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!

cFreed · Accepted Answer · 2016-10-17 16:43:07Z

EDIT: you can also look at @rdimouro own answer below, where he shows its complete now working version.

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.

Thank you for your help! I was able to change my code to bring over all checked chbx values. As for using the value in the MySQL WHERE clause, the value attribute of each checkbox directly matches the value of the invoices column in MySQL so the code example you gave should work just fine. However, I am getting a syntax error in the code but I am not sure what it is. I am able to run through the sql select statement successfully, but the table can't display any data, only the headers. I also know that the array values are successfully sent to php. I will edit the above code for you. — rdimouro, yesterday
@rdimouro Glad if ti (at least partially) helps. In the other hand, please post the text and place of the syntax error you got. — cFreed, yesterday
I use dreamweaver for my code editor. It showed a syntax error in the where clause referencing the the line of code in the IN statement after invoices.stagestatus. I was able to remove those errors by putting double quotes in this line of code: — rdimouro, yesterday
is it correct to replace "item" with $revenu_check" (the variable that holds the arrayed chbx values)? — rdimouro, yesterday

Pep Lainez · Answer 2 · 2016-10-14 20:44:38Z

You can pass an array through an ajax request. Initialize as an empty array and then push the values of the each selected checkboxes into the array (I've commented your code to show where to do the change):

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

I don't know if there's a most elegant way to do it.

When submitting you will see something like this:

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

Note that not checked elements are not posted.

In the PHP side, to check if a checkbox is posted, check if its value is in the array :

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

rdimouro · Answer 3 · 2016-10-17 16:08:07Z

This is the complete answer that worked for my situation. Thank you to cFreed for all the help and edits!!!!!!

MY CODE:

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



    ?>

asked	3 days ago
viewed	57 times
active	today

current community

your communities

more stack exchange communities

use arrayed checkbox values in php MySQL WHERE clause

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged php jquery mysql ajax checkbox or ask your own question.

Linked

Hot Network Questions

current community

your communities

more stack exchange communities

use arrayed checkbox values in php MySQL WHERE clause

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php jquery mysql ajax checkbox or ask your own question.

Linked

Related

Hot Network Questions