How to search for multiple inputs (separately) selected in HTML checkboxes?

I have a simple question regarding structuring MySQLi queries with multiple inputs for one column of a table. Basically I want people to be able to show results for (1) and (2) versus (1 and 2), but I run into problems all the time.

HTML:

<form method="get" action="page.php">
  <ul>
    <li><input type="checkbox" name="PT1" value="Condo">Condo</li>
    <li><input type="checkbox" name="PT2" value="Single Family">Single Family</li>
  </ul>
</form>

      

PHP:

if($_GET['PT1'] == "Condo") {
    $PropType1 = "(property_type = 'Condo') AND ";
}

if($_GET['PT2'] == "Single Family") {
     $PropType2 = "(property_type = 'Single Family') AND ";
}

$setPropType = $PropType1 . $PropType2;

      

MySQLi statement:

$customSelectSQL = "SELECT * FROM $listingsTable WHERE $setPropType (listing_active = 'yes')";

      

I have a problem if they BOTH are checked, the query does nothing and I get zero results. But when individually selected it works great.

I think what's going on in the database is looking for properties that are equal to both inputs, as opposed to equal to each input separately and returning each row for each individual input. It makes sense?

I guess it's an encapsulation issue or something else, but when I search for "MySQL SELECT Multiple" or "MySQLi encapsulation" I get a bunch of not very useful examples.

Thanks in advance!

+3


source to share


4 answers


It would be nice to put them in an array and just blow them up.

edit : Also yes, you have to use OR when looking for property_type, since you won't find 1 row with 2 different properties_name as AND (since it is one column).

Something like:

$where = array();
if($_GET['PT1'] == "Condo") {
        $where[] = "(property_type = 'Condo')";
}
if($_GET['PT2'] == "Single Family") {
        $where[] = "(property_type = 'Single Family')";
}   

$where_string = implode(' OR ', $where);
$customSelectSQL = "SELECT * FROM $listingsTable WHERE (listing_active = 'yes') AND ($where_string)";

      



edit2: To protect against an empty array, just add an if (if there is only one AND needed) or add an additional array to concatenate the AND operators, like this:

$where_or = array();
$where_and = array();
$where_and[] = "(listing_active = 'yes')";
if($_GET['PT1'] == "Condo") {
        $where_or[] = "(property_type = 'Condo')";
}
if($_GET['PT2'] == "Single Family") {
        $where_or[] = "(property_type = 'Single Family')";
}   

if(count($where_or) > 0) {
    $where_and[] = '('.implode(' OR ', $where_or).')';
}

if(count($where_and) > 0) {
    $where_string = implode(' AND ', $where_and);
} else {
    $where_string = '';
}
$customSelectSQL = "SELECT * FROM $listingsTable $where_string";

      

0


source


I believe you should use OR when you have selected 2 checkboxes, since you are selecting a field with 2 values.



    $PropType1 = "";
    if($_GET['PT1'] == "Condo") {
        $PropType1 .= "(property_type = 'Condo') ";
    }

    if($_GET['PT2'] == "Single Family") {
      $PropType2 = "";
      // if first checkbox "Condo" is selected also, add OR
      if($_GET['PT1'] == "Condo") {
        $PropType2 .= " OR ";
      }

      $PropType2 .= "(property_type = 'Single Family') AND ";
    }
    else {
      if($_GET['PT1'] == "Condo") {
        $PropType1 .= " AND ";
      }
    }

      

0


source


Try the KISS approach)

$setPropType = '';

if($_GET['PT1'] == "Condo") {
    $setPropType = "(property_type = 'Condo') AND ";
}

if($_GET['PT2'] == "Single Family") {
    $setPropType = "(property_type = 'Single Family') AND ";
}

if($_GET['PT1'] == "Condo" && $_GET['PT2'] == "Single Family") {
    $setPropType = "((property_type = 'Condo') OR (property_type = 'Single Family')) AND ";
}

      

UPD

It can be expanded to any number of expected properties

$setPropType = '';
$props_expected = ['Condo', 'Single Family', 'Prop3',  'Prop4',  'Prop5',  'Prop6'];
$prop_array = [];

for ($i = 1; $i < 6; $i++) {
    if($_GET['PT'.$i] == $props_expected[$i]) {
        $prop_array[] = "(property_type = '". $props_expected[$i] ."')";
    }
}

$setPropType = implode(" OR ", $prop_array);

if ($setPropType != "") $setPropType = "(". $setPropType ") AND ";

      

Sorry, I have no way to test how this works right now, but I hope the idea is clear.

0


source


consider how you do it like this.

html will use an array element for property types, to easily add new property types

<form method="get" action="page.php">
    <ul>
        <li><input type="checkbox" name="PT[]" value="Condo">Condo</li>
        <li><input type="checkbox" name="PT[]" value="Single Family">Single Family</li>
    </ul>
</form>

      

PHP has been rewritten to add new things to the where clause and add other valid property types without major code changes.

/** this will ensure that it always an array **/
$propertyTypes = array_key_exists('PT', $_GET) ? (is_array($_GET['PT']) ? $_GET['PT'] : [ $_GET['PT'] ]) : [];
/** edit this to add more options, while this could be omitted it protects you against sql injection or searching for values you don't want searchable **/
$allowedPropertyTypes = [ 'Condo', 'Single Family' ];
/** this will be the property types that match from the selected ones **/
$selectedPropertyTypes = [];

$whereClause = [];

$whereClause[] = "listing_active = 'yes'";

/** find the selected property types against the allowed ones **/
foreach($propertyTypes as $propertyType) {
    if (true === in_array($propertyType, $allowedPropertyTypes) {
        $selectedPropertyTypes[] = sprintf("'%s'", addslashes($propertyType));
    }
}

/** if one or more property type is selected, then add it to the where clause **/
if (sizeOf($selectedPropertyTypes) > 0) {
    $whereClause[] = sprintf('property_type IN (%s)', implode(',', $selectedPropertyTypes));
}

/** build the final sql statement, checking to make sure the where clause has at least one element **/
$sql = sprintf('SELECT * FROM listingsTable %s;', sizeOf($whereClause) > 0 ? sprintf('WHERE %s', implode(' AND ', $whereClause))); 

      

-1


source







All Articles