MySQL and PHP - Filtering by multiple criteria in one column

Question

I have a standard code to search by column and am now trying to add the option of searching multiple criteria in one column. Currently I can search by one criteria, so if the user selects location QLD it will display all QLD entries, what changes do i need to make to enable the user to search both for location QLD and NSW?

I have updated the element to be multiple, but am not sure how to adjust the PHP and MySQL to process multiple criteria.

Can someone help?

Thanks, sbgmedia

<?php
$condition  =   '';
if(isset($_REQUEST['Location']) and $_REQUEST['Location']!=""){
    $condition  .=  ' AND Location LIKE "%'.$_REQUEST['Location'].'%" ';
}

$userData   =   $db->getAllRecords('candidates','*',$condition,'ORDER BY id ASC');
?>

<select name="Location[]" id="Location" class="form-control" value="<?php echo isset($_REQUEST['Location'])?$_REQUEST['Location']:''?>" multiple>
  <option value="" <?php if(isset($_REQUEST['Location']) && $_REQUEST['Location'] == '') 
          echo ' selected="selected"';?></option>
  <option value="ACT" <?php if(isset($_REQUEST['Location']) && $_REQUEST['Location'] == 'ACT') 
          echo ' selected="selected"';?>ACT</option>
  <option value="NSW" <?php if(isset($_REQUEST['Location']) && $_REQUEST['Location'] == 'NSW') 
          echo ' selected="selected"';?>NSW</option>
   <option value="QLD" <?php if(isset($_REQUEST['Location']) && $_REQUEST['Location'] == 'QLD') 
          echo ' selected="selected"';?>QLD</option>
</select>

WARNING: Whenever possible use prepared statements with placeholder values to avoid injecting arbitrary data in your queries and creating SQL injection bugs. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. — tadman
– tadman, Commented Apr 15, 2021 at 6:32
Try Location IN (...) or expand to a series of x LIKE y joined by OR. You can also crunch them down into a single regular expression for RLIKE. — tadman
– tadman, Commented Apr 15, 2021 at 6:33

Repox · Accepted Answer · 2021-04-15 06:49:56Z

If the Locations are represented one-to-one, you can use IN to search, like so:

SELECT * FROM candidates WHERE Location IN ('QLD', 'NSW');

If the Locations are just part of the string, you can compare with OR in your query, like so:

SELECT * FROM candidates WHERE Location LIKE '%QLD%' OR Location LIKE '%NSW%';

To represent a solution, based on your own code, I'll try and fit it in using IN and replacing $_REQUEST with $_POST (because control over your HTTP methods is more secure).

<?php
$condition = '';
if(!empty($_POST['Location')){
    $condition  .=  " AND Location IN ('" . implode("', '", $_POST['Location']) ."')";
}

$userData   =   $db->getAllRecords('candidates','*',$condition,'ORDER BY id ASC');
?>

Please be ware that this is just an example! Your code is open to SQL injection which you should address ASAP!

Without knowing what abstraction layer $db is, I can only urge you to look into "sql injection" and "mysql prepared statements" on your favorite search engine.

Collectives™ on Stack Overflow

MySQL and PHP - Filtering by multiple criteria in one column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related