codeigniter 如何基于电话号码从两个表中获取所有值

von4xj4u  于 2022-12-07  发布在  其他
关注(0)|答案(4)|浏览(156)

这里我有两个表,我想从这两个表得到的结果。我尝试了很多,但我无法得到确切的结果,你可以请任何帮助我,我正在使用这个应用程序在codeiniter。
第一个表格
新员工

staff_id              firstName        Mobile          userType

  1                  Soupranjali       9986125566       Teacher
  2                  Sujata            8553880306       Teacher

第二张table
新学生

student_id           first_Name        fatherMobile        user_type

  1                  janarthan         8553880306         Student
  2                  Santanu           8277904354         Student
  3                  Sarvan            8553880306         Student

此处为8553880306两个表都存在此移动的号码,因此需要获取两个表的结果
预期结果

{
  "status": "Success",
  "Profile": [
    {
      "staff_id": "2",
      "firstName": "Sujata",
      "userType" : "Teacher"
    },
    {
      "student_id": "1",
      "firstName": "janarthan",
      "user_type" : "Student"
    },
    {
      "student_id": "3",
      "firstName": "Sarvan",
      "user_type" : "Student"
    }
  ]
}

所以我试着这样做,但我无法得到答案,所以请任何人帮助我,
我的模型

public function android_memberList($mobile)
    {
        $this->db->select('new_staff.staff_id, new_staff.firstName, new_staff.userType, new_student.student_id, new_student.first_Name, new_student.user_type');
        $this->db->from('new_staff');
        $this->db->join('new_student', 'new_student.fatherMobile  = new_staff.Mobile');
        $query = $this->db->get();

        # result_array is used to convert the data into an array
        $result = $query->result_array(); 
        echo json_encode($result);
    }

根据我的查询,它返回的输出如下所示,但这不是我所期望的json格式

[
  {
    "staff_id": "2",
    "firstName": "Sujata",
    "userType": "Teacher",
    "student_id": "1",
    "first_Name": "janarthan",
    "user_type": "Student"
  },
  {
    "staff_id": "2",
    "firstName": "Sujata",
    "userType": "Teacher",
    "student_id": "2",
    "first_Name": "Santanu",
    "user_type": "Student"
  }
]

更新的答案

{
  "status": "Success",
  "Profile": [
    {
      "staff_id": "2",
      "firstName": "Sujata",
      "userType": "Teacher"
    },
    {
      "staff_id": "2",
      "firstName": "Sujata",
      "userType": "Teacher"
    },
    {
      "student_id": "1",
      "first_Name": "janarthan",
      "user_type": "Student"
    },
    {
      "student_id": "2",
      "first_Name": "Santanu",
      "user_type": "Student"
    }
  ]
}
e7arh2l6

e7arh2l61#

$this->db->join('new_student', 'new_student.fatherMobile  = new_staff.Mobile','left');

请替换代码中的这一行并检查,我认为它工作正常,并且您可以获得所需的数据。

myss37ts

myss37ts2#

您正在使用MySQL join合并两个表中的数据,这永远不会得到您想要的结果。您可以使用union合并两个表中的数据。在您的情况下,问题是两个表中的字段名称不同。

解决方案1:

在SQL中,使用alias泛化列名,问题是在您的json数组中,您将获得泛化键。

解决方案2:

运行两个不同的查询,在两个不同的数组中获取数据,合并两个数组,得到想要的结果。

public function android_memberList($mobile)
{
    $this->db->select('distinct(new_staff.staff_id), new_staff.firstName, new_staff.userType');
    $this->db->from('new_staff');
    $this->db->join('new_student', 'new_student.fatherMobile  = new_staff.Mobile');
    $query1 = $this->db->get();

    # result_array is used to convert the data into an array
    $result_new_staff = $query1->result_array(); 

    $this->db->select('distinctnew_student.student_id), new_student.first_Name, new_student.user_type');
    $this->db->from('new_staff');
    $this->db->join('new_student', 'new_student.fatherMobile  = new_staff.Mobile');
    $query2 = $this->db->get();

    # result_array is used to convert the data into an array
    $result_new_student = $query2->result_array(); 

    $final_result=array_merge($result_new_staff,$result_new_student);

    $result["status"] = "Success";
    $result["Profile"] = $final_result;
    echo json_encode($result);
}
vdgimpew

vdgimpew3#

您可以使用连接来获取具有一个键的两个表数据左连接是最佳选择
你可以这样试试

$this->db->join('new_student', 'new_student.fatherMobile  = new_staff.Mobile','left');
9cbw7uwe

9cbw7uwe4#

您可以在codeigniter项目中尝试此查询,它将为您提供所有具有新名称的列。
或者,您可以根据兼容性修改列名。

$this->db->select('ns1.student_id, ns1.first_name as student_first_name'); 
$this->db->select('ns1.fatherMobile, ns1.user_type as student_user_type');
$this->db->select('ns2.firstName as staff_first_name');
$this->db->select('ns2.Mobile as staff_mobile, ns2.userType as staff_user_type');
$this->db->from('new_student as ns1');
$this->db->join('new_staff as ns2', 'ns2.Mobile = ns1.fatherMobile', 'left');
$this->db->get()->result_array();

您可以在这里看到您的查询已解决https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0288bef1b4f753a134000ae73d6bd58d

相关问题