shahid khan shahid khan - 2 months ago 6
MySQL Question

selecting values from two tables using union but with different key

I have two tables one is named as teacher-record and other is student record teacher-record has columns teacher_id,email(email of teacher) like this student-record has two columns student_id,email(email of student) i trying to select teacher_id and student_id using single query for that i am using union

mysql query i used for above purpose is like this also i using codeignitor

public function get_std_tech_id($std_email,$tech_email)
{
$query = $this->db->query("SELECT `student_id` AS std FROM `student-record` WHERE `email`= '$std_email' UNION ALL SELECT `teacher_id` AS tech FROM `teacher-record` WHERE `email` ='$tech_email'");
return $query->result_array();
}


now i get an array

Array ( [std] => SID2016063 )(student_id) Array ( [std] => TECH20160922(teacher_id) )

what i am trying or like to get is like this

Array ( [std] => SID2016063 ) Array ( [tech] => TECH20160922 )

Answer

UNION won't allow you this, it needs same column name.

You can add a 2nd column containing what type of id that specific row contains:

SELECT `student_id` AS id, 'std' AS type FROM `student-record` WHERE `email`= '$std_email' 
UNION ALL 
SELECT `teacher_id` AS id, 'tech' AS type FROM `teacher-record` WHERE `email` ='$tech_email'

then in php you can differentiate the results based on the column type.

PS: I am not a PHP guy so I can't predict in what format this dataset will come.