I have a simple question regarding structuring MySQLi Querys with multiple inputs for the same Table Column. Basically, I want people to be able to show results for (1) and (2) vs. (1 and 2), but I keep running into issues.
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'm having a problem where if they're BOTH checked, the query does nothing and I get zero results. But when they're individually selected it works great.
I think what's happening is the database is searching for Properties that equal both inputs, as opposed to equaling each input separately and returning each row for each separate input. Make sense?
I'm guessing it's a matter of encapsulation or something, but when I search for "MySQL SELECT Multiple" or "MySQLi encapsulation", I get a bunch of not-so-helpful examples.
Thanks in advance!
Good thing would be to put these in the array and just implode them.
edit: Also yeah, you should use OR while searching for property_type since you won't find 1 row with 2 different property_types 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 empty array just add if (if there is only one AND needed) or add additional array to concatenate AND statements, like:
$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";
I believe you should use OR when you selected 2 checkboxes, since you are selecting a field with two 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 ";
}
}
consider rewriting how you are doing this in the following fashion.
the html will use an array element for the property types, for much easier addition of 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>
The PHP is re-written to allow adding new stuff to the where clause and adding other allowed property types without much code change.
/** this will ensure that it's 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)));
Try 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 may be extended 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 ability to check how it works right now, but I hope the idea is clear