php - 如何在 mysql 中使用单个查询从两个表中获取数据
问题描述
我有两个表,包含以下列:
OneRecord
:team1
,team2
,id
fixture_list
:team1
,team2
,photo1
,photo2
我想检索photo1
, photo2
from fixture_list
, if team1
并且team2
在两个表上具有相同的值。
这是我到目前为止所尝试的:
<?php
//checking if the script received a post request or not
if($_SERVER['REQUEST_METHOD']=='POST'){
//Getting post data
$email=$_POST['email'];
require_once('connect.php');
$sql = "SELECT * FROM OneRecord, fixture_list
WHERE OneRecord.email='$email',
OneRecord.team1=fixture_list.team1_name
AND
OneRecord.team2=fixture_list.team2_name";
$res = mysqli_query($con,$sql);
$result = array();
while($row = mysqli_fetch_array($res)){
array_push($result, array(
'team1'=>$row[3],
'team2'=>$row[4],
'rs'=>$row[6],
'team1_score'=>$row[7],
'team1_wicket'=>$row[8],
'team2_score'=>$row[10],
'team2_wicket'=>$row[11],
'tournament'=>$row[13]
));
}
echo json_encode (array("bets_list"=>$result));
mysqli_close($con);
}
?>
这是已编辑的问题
<?php
//checking if the script received a post request or not
if($_SERVER['REQUEST_METHOD']=='POST'){
//Getting post data
$email=$_POST['email'];
require_once('connect.php');
$sql = "SELECT f.team1_photo as "team1_photo",
f.team2_photo as "team2_photo" FROM OneRecord o INNER JOIN fixture_list f
ON
o.team1=f.team1_name AND o.team2=f.team2_name WHERE o.email='$email'";
$res = mysqli_query($con,$sql);
$result = array();
while($row = mysqli_fetch_array($res)){
array_push($result,
array('team1'=>$row[3],'team2'=>$row[4],
'rs'=>$row[6],'team1_score'=>$row[7],'team1_wicket'=>$row[8],
'team2_score'=>$row[10],
'team2_wicket'=>$row[11],'tournament'=>$row[13],'photo1'=>$row[0],
'photo2'=>$row[1],
));
}
echo json_encode (array("bets_list"=>$result));
mysqli_close($con);
}
?>
解决方案
team1
在和上对这两个表进行内部连接team2
。
SELECT f.photo1 as "photo1",f.photo2 as "photo2",
FROM OneRecord o
INNER JOIN fixture_list f
ON o.team1 = f.team1 and o.team2 = f.team2
where o.email = $email;
更新- 如果您想要所有列,请执行select * from ...
.
推荐阅读
- database-connection - 从 DatabaseSwingManager 连接时 HSQL 服务器模式引发异常 java.sql.SQLTransientConnectionException
- smartcard - Seaory S22证卡打印机编码器是否兼容Java Card
- azure - 带有 SSIS 包的 Azure Synapse
- asp.net-core - 如何将默认值添加到授权标头值
- r - (R语言)理解什么是“加权”图
- airflow - 在 Airflow 中实现分支
- php - 如何自动为 /storage/ 文件夹中的所有文件设置标题?
- marklogic - 如何在 MarkLogic Grove 中从我自己的 API 调用后端 MarkLogic API?
- python - deg 在 np.polyfit numpy 中做了什么
- excel - 如果条件不匹配,则给出下拉列表