php - 需要 SQL 速记 - PHP PDO

Question

好吧，我的眼睛受伤了:)有人建议用更短的方法来完成相同的代码吗？

以下变量的有效值可以是:$ryg 可以是:“all”、“r”、“y”或“g”$region 可以是:“all”、“na”、“emea”、“latam”或“apac”

我需要缩短/优化这个，但经过一整天的编码后我的眼睛受伤了(难怪)

if(($region=="all")&&($ryg=="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0));
} else if(($region=="all")&&($ryg!="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,$ryg));
} else if(($region=="na")&&($ryg=="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"na"));
} else if(($region=="na")&&($ryg!="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"na",$ryg));
} else if(($region=="emea")&&($ryg=="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"emea"));
} else if(($region=="emea")&&($ryg!="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"emea",$ryg));
} else if(($region=="latam")&&($ryg=="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"latam"));
} else if(($region=="latam")&&($ryg!="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"latam",$ryg));
} else if(($region=="apac")&&($ryg=="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"apac"));
} else if(($region=="apac")&&($ryg!="all")) {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,"apac",$ryg));
} else {
  $sql_main= "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? ORDER BY data1_summary.projected_margin ASC";
  $stmt = $pdo->prepare($sql_main);
  $stmt->execute(array('y',0,$region));
}

我厌倦了剪切和粘贴所有这些......

关于重写这个的最短方法的想法？

提前致谢!!

Answer 1

这是减少代码的一种方法:

if ($region == "all" && $ryg == "all") {
    $sql_main = "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? ORDER BY data1_summary.projected_margin ASC";
    $stmt = $pdo->prepare($sql_main);
    $stmt->execute(array('y', 0));
} elseif ($region != "all" && $ryg != "all") {
    $sql_main = "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
    $stmt = $pdo->prepare($sql_main);
    $stmt->execute(array('y', 0, $region, $ryg));
} elseif ($region != "all" && $ryg == "all") {
    $sql_main = "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND project.region=? ORDER BY data1_summary.projected_margin ASC";
    $stmt = $pdo->prepare($sql_main);
    $stmt->execute(array('y', 0, $region));
} elseif ($region == "all" && $ryg != "all") {
    $sql_main = "SELECT * FROM project INNER JOIN data1_summary ON project.p_key = data1_summary.p_key_project WHERE project.active=? AND project.account_key=? AND data1_summary.overall_ryg=? ORDER BY data1_summary.projected_margin ASC";
    $stmt = $pdo->prepare($sql_main);
    $stmt->execute(array('y', 0, $ryg));
}

尽管您仍然可以对此进行优化，但您不必再为每个地区或地区添加条件。