How to get query to ignore empty search fields in

问题:

I was using and if statement to append $query .= "AND column LIKE $suchandsuch" in my search engine when I had it set up with mysqli, but I'm updating it to PDO and am struggling to figure out how to make sure it will ignore the fields that are left blank.

The form fields are: condition, degree, specialty, city, state. Condition is required, but nothing else is, so it needs to ignore those fields when they're empty. I feel like this should be obvious but I'm new to PDO and stumped.

if(isset($_POST['submit'])){
  $condition = $_GET["condition"];

  if(isset($_GET["degree"]) && !empty($_GET["degree"])){
    $degree = $_GET["degree"];


 }
  if(isset($_GET["specialty"]) && !empty($_GET["specialty"])){
    $specialty = $_GET["specialty"];
  }
  if(isset($_GET["city"]) && !empty($_GET["city"])){
    $city = $_GET["city"];
  }
  if(isset($_GET["state"]) && !empty($_GET["state"])){
    $state = $_GET["state"];
  }

$query = $connection->prepare("SELECT
  `providers`.`provider_first_name`, `providers`.`provider_last_name`,
  `providers`.`p_date_added`, `providers`.`id`,
  (SELECT GROUP_CONCAT(DISTINCT `degree_type` SEPARATOR ', ')
          FROM `degrees`
          WHERE `providers`.`id` = `prov_id`
    ) AS all_degrees,
  (SELECT GROUP_CONCAT(DISTINCT `specialty` SEPARATOR ', ')
          FROM `specialties`
          WHERE `providers`.`id` = `prov_id`
    ) AS all_specialties,
  (SELECT   GROUP_CONCAT(DISTINCT CONCAT(`locations`.`city`, ', ',
    `locations`.`state`)
    SEPARATOR '<br>')
    FROM `locations`
    WHERE `providers`.`id` = `prov_id`
  ) AS all_locations,
  (SELECT GROUP_CONCAT(DISTINCT `practice_name` SEPARATOR ', ')
          FROM `practices`
          WHERE `providers`.`id` = `prov_id`
    ) AS all_practices,
  (SELECT GROUP_CONCAT(DISTINCT `link` SEPARATOR '<br>')
          FROM `links`
          WHERE `providers`.`id` = `prov_id`
    ) AS all_links,
  (SELECT GROUP_CONCAT(DISTINCT `condition_name` SEPARATOR ', ')
          FROM `conditions`
          WHERE `providers`.`id` = `prov_id`
    ) AS all_conditions,
  (SELECT AVG(`reviews`.`star_rating`)
          FROM `reviews`
          WHERE `providers`.`id` = `prov_id`
    ) AS rating
  FROM `providers`
  WHERE `all_conditions` LIKE :condition");


  if($degree && !empty($degree)){
      $query.= " AND (`all_degrees` LIKE :degree)";
      $degree = "%".$degree."%";
      $query->bindParam(':degree', $degree, PDO::PARAM_STR);
  }
  if($specialty && !empty($specialty)){
      $query.= " AND (`all_specialties` LIKE :specialty)";
      $specialty = "%".$specialty."%";
      $query->bindParam(':specialty', $specialty, PDO::PARAM_STR);
  }
  if($city && !empty($city)){
      $query .= " AND (`all_locations` LIKE :city)";
      $city = "%".$city."%";
      $query->bindParam(':city', $city, PDO::PARAM_STR);
  }
  if($state && !empty($state)){
      $query .= " AND (`all_locations` LIKE :state)";
      $state = "%".$state."%";
      $query->bindParam(':state', $state, PDO::PARAM_STR);
  }

$condition = "%".$condition."%";
$query->bindParam(':condition', $condition, PDO::PARAM_STR);

$query->execute();

回答1:

Please add the is not empty condition.

if(isset($_GET["degree"]) && !empty($_GET["degree"])){
  $degree = $_GET["degree"];
}

回答2:

try

var_dump($_GET["degree"]);

they might have some value

回答3:

UPDATE - Not sure if this is kosher, but I managed to get what I want with the following code:

include("config/db.php");
include ("SearchEngineProvider.php");

if(isset($_POST['submit'])){
  $condition = $_POST["condition"];

    if(isset($_POST["degree"])){
      $degree = $_POST["degree"];
    }
    if(isset($_POST["specialty"])){
      $spec = $_POST["specialty"];
    }
    if(isset($_POST["city"])){
      $city = $_POST["city"];
    }
    if(isset($_POST["state"])){
      $state = $_POST["state"];
    }

    if($condition != "")$whereCond = "AND condition_name LIKE :condition";
    if($degree != "")$whereDeg = "AND degree_type LIKE :degree";
    if($specialty != "")$whereSpec = "AND specialty LIKE :specialty";
    if($city != "")$whereCity = "AND city LIKE :city";
    if($state != "")$whereState = "AND state LIKE :state";
    $groupBy = " GROUP BY `id`";

$query = $connection->prepare("SELECT `providers`.`id`,
  `providers`.`provider_first_name`, `providers`.`provider_last_name`,
  `providers`.`p_date_added`,
  `degrees`.`degree_type`,
  GROUP_CONCAT(DISTINCT `degrees`.`degree_type` SEPARATOR ', ') AS all_degress,
  `specialties`.`specialty`,
  GROUP_CONCAT(DISTINCT specialties.specialty SEPARATOR ', ') AS all_specialties,
  `locations`.`city`, `locations`.`state`,
  `practices`.`practice_name`,
  GROUP_CONCAT(DISTINCT practices.practice_name SEPARATOR ', ') AS all_practices,
  `links`.`link`,
  GROUP_CONCAT(DISTINCT links.link SEPARATOR ', ') AS all_links,
  `conditions`.`condition_name`,
  GROUP_CONCAT(DISTINCT conditions.condition_name SEPARATOR ', ') AS all_conditions
  FROM `providers`
  LEFT JOIN `degrees` ON `providers`.`id` = `degrees`.`prov_id`
  LEFT JOIN `specialties` ON `providers`.`id` = `specialties`.`prov_id`
  LEFT JOIN `locations` ON `providers`.`id` = `locations`.`prov_id`
  LEFT JOIN `practices` ON `providers`.`id` = `practices`.`prov_id`
  LEFT JOIN `links` ON `providers`.`id` = `links`.`prov_id`
  LEFT JOIN `conditions` ON `providers`.`id` = `conditions`.`prov_id`
  WHERE `id` IS NOT NULL {$whereCond} {$whereDeg} {$whereSpec} {$whereCity} {$whereState} {$groupBy}
  ");

    $condition = "%".$condition."%";
    $query->bindParam(':condition', $condition, PDO::PARAM_STR);

    if($whereDeg != ""){
      $degree = "%".$degree."%";
      $query->bindParam(':degree', $degree, PDO::PARAM_STR);
    }
    if($whereSpec != ""){
      $specialty = "%".$specialty."%";
      $query->bindParam(':specialty', $specialty, PDO::PARAM_STR);
    }
    if($whereCity != ""){
      $city = "%".$city."%";
      $query->bindParam(':city', $city, PDO::PARAM_STR);
    }
    if($whereState != ""){
      $state = "%".$state."%";
      $query->bindParam(':state', $state, PDO::PARAM_STR);
    }

$query->execute();

回答4:

$city = @$_GET['city'];          // before PHP 7
$city = $_GET['city'] ?? null;   // with PHP 7
if (! empty($city)) {
    $query .= " AND (`all_locations` LIKE :city)";
    $query->bindParam(':city', "%$city%", PDO::PARAM_STR);
}

(etc)