mysql - 查询返回空结果但数据存在

Question

我将 Slim 与 PDO 和 MySql 一起使用，以返回我的可用匹配的特定列表数据库。我的查询是这样的:

SELECT m.*, 
       t.name AS home_team_name, 
       t2.name AS away_team_name 
FROM `match` m 
LEFT JOIN team t ON m.home_team_id = t.id 
LEFT JOIN team t2 ON m.away_team_id = t2.id
WHERE (home_team_id = 117 OR away_team_id = 117) AND round_id = 488

如果我执行此查询，我将获得匹配项列表:

但在使用 Slim 开发的 API 中，我得到一个空数组。这是方法结构:

$app->get('/match/get_matches_by_team/{round_id}/{team_id}/{type}', function (Request $request, Response $response, array $args)
{
  $query = "SELECT m.*,
  t.name AS home_team_name,
  t2.name AS away_team_name
  FROM `match` m
  LEFT JOIN team t ON m.home_team_id = t.id
  LEFT JOIN team t2 ON m.away_team_id = t2.id
  WHERE ";

  switch($args["type"])
  {
    case "home":
          $query .= "home_team_id = :team_id AND ";
    break;
    case "away":
        $query .= "away_team_id = :team_id AND ";
    break;
    default:
        $query .= "(home_team_id = :team_id OR away_team_id = :team_id) AND ";
    break;
  }

  $query .= "round_id = :round_id";

  $sql = $this->db->prepare($query);
  $sql->bindParam("team_id", $args["team_id"]);
  $sql->bindParam("round_id", $args["round_id"]);
  $sql->execute();

  $result = $sql->fetchAll();
  return $response->withJson($result);
});

我做错了什么？

在此先感谢您的帮助。

更新

如果我echo $query;返回； 我会得到:

SELECT m.*,
t.name AS home_team_name,
  t2.name AS away_team_name
  FROM `match` m
  LEFT JOIN team t ON m.home_team_id = t.id
  LEFT JOIN team t2 ON m.away_team_id = t2.id
  WHERE away_team_id = :team_id AND round_id = :round_id

假设传递离开，如果我传递all，我会得到:

SELECT m.*,
t.name AS home_team_name,
  t2.name AS away_team_name
  FROM `match` m
  LEFT JOIN team t ON m.home_team_id = t.id
  LEFT JOIN team t2 ON m.away_team_id = t2.id
  WHERE (home_team_id = :team_id OR away_team_id = :team_id) AND round_id = :round_id

更新2

使用建议的提示更新方法

$app->get('/match/get_matches_by_team
/{round_id}/{team_id}/{type}', function (Request $request, Response $response, array $args)
{
  $query = "SELECT m.*,
  t.name AS home_team_name,
  t2.name AS away_team_name
  FROM `match` m
  LEFT JOIN team t ON m.home_team_id = t.id
  LEFT JOIN team t2 ON m.away_team_id = t2.id
  WHERE ";

switch($args["type"])
   {
    case "home":
          $query .= "home_team_id = :home_team_id 
    AND ";
        break;
        case "away":
            $query .= "away_team_id = :away_team_id AND ";
        break;
        default:
            $query .= "(home_team_id = :home_team_id OR away_team_id = :away_team_id) AND ";
        break;
      }

      $query .= "round_id = :round_id";

      $sql = $this->db->prepare($query);
      $sql->bindParam("home_team_id", $args["team_id"]);
      $sql->bindParam("away_team_id", $args["team_id"]);
      $sql->bindParam("round_id", $args["round_id"]);
      $sql->execute();

      $result = $sql->fetchAll();
      return $response->withJson($result);
    });

Answer 1

你的:

 $sql->bindParam("team_id", $args["team_id"]);
 $sql->bindParam("round_id", $args["round_id"]);

尝试一下，参数可能需要采用不同的格式

$sql->bindParam(":team_id", $args["team_id"], PDO::PARAM_INT);
$sql->bindParam(":round_id", $args["round_id"], PDO::PARAM_INT);

或

$sql->bindParam(":team_id", $args["team_id"]);
$sql->bindParam(":round_id", $args["round_id"]);