Filtering results depending on form results

I’m trying to submit a form is automatically submitted when somebody clicks one of the checkboxes. This part isn’t a problem but I’m having a problem with getting the results each time. The form is a series of checkboxes where people can choose to filter results by various options but how do I only get the results if the box is checked?

For example if box a is checked and so is box b I want the results to be something like this:

$sql_select = "SELECT * "; 
$sql_from = " FROM stock, product_categories, delivery, options "; 
$sql_where = " WHERE stock.enabled=1 and stock.deleted=0 and stock.dealer_name= delivery.dealer ";
$sql_group = " GROUP BY stock.id";
$sql_limit = " LIMIT 500";

if ($boxes_group1<>"") {
$sql_where = " AND options.category_1 LIKE %box a% %box b%
}

$query=$sql_select . $sql_from . $sql_where . $sql_end . $sql_group . $sql_limit;

This gives me no results at all even though there are loads of results with box a and also with box b in the category_1 field of the options table.

Also there could be around 20 different options so I need to be able to execute the same code every time regardless of which option is selected

So presumably when you auto-submit the form when someone clicks on one of the checkboxes, you need to then use $_POST to read your form elements into the PHP so you can use them to build up your query. Are you doing that somewhere outside the code you showed? Where does $boxes_group1 come from?

(I have no experience of jQuery so apologies if the above is unhelpful).

only check boxes in the form are submitted.

@droopsnoot jQuery is a JavaScript library and does not have any effect on PHP.

@droopsnoot $boxes_group1 is just an example to show.

Basically what I want to know how to do it show the results if somebody checks the box but not if they don’t. I only have a basic knowledge of php and that’s where I’m struggling.

So if somebody checks box a the code would look like this: $sql_where = " AND options.category_1 LIKE %box a%. If the checked box a and d then it would look like this AND options.category_1 LIKE %box a% AND options.category_1 LIKE %box d% but if the didn’t check any boxes then that wouldn’t show at all

So basically, you’ll need to name your checkboxes with something suitable, then read up on form processing and the $_POST array. So if you name your first checkbox as

<input type="checkbox" name="cb1">

then it will come into PHP as $_POST[‘cb1’] and you can then use it as a variable. You need to check if it exists, have a look at its value (usually “on”, this denotes it’s clicked, usually they don’t appear in $_POST if they’re not ticked) and build the query that way.

You can look at naming your checkboxes as an array, which might make it easier to build the different query conditions - you can use a foreach() loop rather than having to check each individual one. Have a read up on form processing and see what clicks, come back with anything that doesn’t.

Thank you so much, that sounds perfect!

Sorry to be a pain but how do I use foreach? I don’t know much about php and am just learning

Foreach is just an easy way of dealing with multiple variables - if you’re a beginner then probably best not to complicate things.

While you get used to things, I’d think about just having two or three checkboxes, and practice dealing with them. Name them as you want to, but you need to consider what data you’re going to be looking in the database for. The form data will come through with any ticked checkboxes containing the string “on”, so there’s no point in searching in your “options_category1” column for that value. You’ll need to translate that into the actual string you want to look for. I’d probably do something like

$extracond = "";
if (isset($_POST['cb1'])) { // checkbox 1 is ticked
  $extracond .= " AND option_category1 like %yellow%";
  }
if (isset($_POST['cb2'])) { //checkbox 2 is ticked
  $extracond .= " AND option_category1 like %red%";
  }

So that will look for either of two checkboxes being ticked, and append an additional AND clause which you can then add into your query if there’s anything in it at the end. Obviously I’ve assumed checkbox 1 relates to searching for “yellow”, and checkbox 2 for “red”. Once in production, you might need to recover that information from a database table to make it more adaptable.

As you can see, that will get quite tedious once you have twenty checkboxes as you’ll have to duplicate that code for each. But it’s OK for now to get things moving and give you some familiarity with how it works. Also you need to think about whether having twenty checkboxes is the best way for the user to specify these things.

Thank you so much, that’s helped me no end - and saved me a huge amount of stress!! Thanks you.

This might not be possible but is it possible to limit search results to just two for a particular value in a set field? So for example if I wanted to only display two results for each unique entry in field_a it would get two results for every different entry?

You could sort the results by field_a which would make it easy to count them as you display them:

$count = 0;
$lastval = "";
while ($row = $s->fetchAll()) {  // or however you get the results
  if ($row['field_a'] != $lastval) { // if the value of field_a has changed
    $count = 0;
    $lastval = $row['field_a'];
    }
  if ($count < 2) {
    // display your results here if it's the first or second time
    }
  $count++;  // increment the counter
  }

If you can’t sort them by field_a, you’d probably have to build an array with a count for each potential value of field_a to make sure you don’t display more than two of them. Probably won’t make much sense if you don’t sort by that column, though.

You should be able to do an order by limit with the query you have

$sql_limit = " ORDER BY 'field_a' ASC  LIMIT 2";

I hadn’t realised you could use LIMIT in more than one section of the query, thanks for the info.

Thank you both, I’ll give that a go

I did try that but unfortunately got an error saying I couldn’t use LIMIT twice. I’m already limiting the whole query to 50 results but also want to limit it to two results per field_a

Sorry I’m probably being a bit dumb here but I don’t quite understand how I would use the code you gave me. I have a field in the database that is the manufacturer name but I only want to display two products from each manufacturer.

If you include the manufacturer in your sort order, the code would basically start a counter as you run through the results. Each time the manufacturer changes (the comparison to $lastval, the manufacturer from the previous row) the counter is reset, and you only display the results when the counter is zero or one, i.e. the first two results.

If you can’t include it in the sort order so that manufacturers are grouped together, another way would be to build an array of manufacturers as you retrieve each row, and a count of how many times you’ve displayed a record from that manufacturer, and not display the record if the count is greater than 2. Something like:

start of loop
  retrieve row
  is manufacturer for this row in the array already?
    - if yes, increment the counter for the manufacturer
    - if no, add manufacturer to array, set count to zero
  is display-count for this manufacturer below 2?
    - if yes, display row
    - if no, don't display row
  increment counter for the manufacturer
end of loop

aww okay I see what you’re saying but how would I use it with the code I’m already using to get the results:

<table>
  <tr>
  <td>Product</td>
  <td>Manufaturer</td>
  </tr>
  <?php if( is_array( $results ) ) { ?>
		<?php foreach( $results as $key=>$value ) { ?>
  <tr>
  <td><?php echo htmldisplay( $value['product'] ) ?></td>
  <td><?php echo htmldisplay( $value['manufaturer'] ) ?></td>
  </tr>
        
		<?php } } ?>
		</table>

Something like:

<?php
$mfgs = array();
?>
<table>
  <tr>
  <td>Product</td>
  <td>Manufaturer</td>
  </tr>
  <?php if( is_array( $results ) ) { ?>
		<?php foreach( $results as $key=>$value ) { 
if (array_search($value['manufacturer'], $mfgs)) { 
  // nothing, already in array
  }
else {
  $mfgs[$value['manufacturer']] = 0;
  }
if ($mfgs[$value['manufacturer']] < 2) { // only display row if under this count for this mfg
?>
  <tr>
  <td><?php echo htmldisplay( $value['product'] ) ?></td>
  <td><?php echo htmldisplay( $value['manufacturer'] ) ?></td>
  </tr>
        
		<?php
} // for end of if() check
$mfgs[$value['manufacturer']] = $mfgs[$value['manufacturer']] + 1;
 } // end of foreach()
} // end of isarray()
 ?>
		</table>

I don’t know if I did something wrong as I did that exactly and it didn’t work. For testing I limited it to just 10 results and they were all for the same manufacturer.

if your SQL query gives you 10 results with the same manufacturer then that does not necessarily imply an error.

But shouldn’t it be giving me only two results for each manufacturer?