我在用 Slim
与 PDO
与 MySql
返回特定的 matches
在我的数据库中可用。我的问题是:
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
如果我执行这个 query
我要一份 matches
:
但是在里面 API
开发 Slim
我得到一个空数组。这是方法结构:
$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; return;
我会得到:
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
假设通过 away
,如果我通过 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);
});
2条答案
按热度按时间vohkndzv1#
您的:
试试这个,参数可能需要不同的格式
或
r8uurelv2#
当选择默认开关时,您将尝试用同一个参数标记绑定一个值(在您的示例中为:team\u id)两次。为了让它工作,你必须在pdo中打开仿真模式。
http://www.php.net/manual/en/pdo.prepare.php
在调用pdostatement::execute()时,必须为要传入语句的每个值包含一个唯一的参数标记。除非启用了仿真模式,否则不能在准备好的语句中多次使用同名的命名参数标记。