我如何使用mysql中的单个查询从两个表中获取数据

debugcn 发表于 Dev

维普尔·辛格

我有两个表，有以下列：

OneRecord: team1, team2,id
fixture_list: team1, team2, photo1,photo2

我想检索photo1, photo2from 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);

   }

   ?>

nice_dev

在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 ....

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。