how to search in multiple table?

Question

i have 2 tables medicalhistory and patient in medicalhistory i have fields name DoctorId and PatientId and in patient ihave PatientId, FirstName,MiddleName,LastName

as of now my query is this it only filter one table and the patientid from medicalhistory.

$dids = $_SESSION["DoctorId"];
if(isset($_POST["btnsrch"])&& ($_POST["textbox"]!="")){
   $pid = $_POST["textbox"];
    $pid= mysqli_real_escape_string($_POST["textbox"]);

   $query2= mysqli_query($link,"Select DISTINCT PatientId from medicalhistory where DoctorId='". $dids ."' or PatientId='". $pid ."'" );

}

my problem is how will i query the FirstName MiddleName and LastName but it will query only the name that have records on that doctor in medicalhistory ?

Read about MySQL joins its a simple enough problem you have and there is hundreds of questions/articles on this — Mike Miller
Join your tables and then use whichever column from whichever table as your where clause. Your problem is 101. currently you have NO join. Create join, try it and post results — Mike Miller
$query2= mysqli_query($link,"SELECT p.PatientId FROM patient p INNER JOIN medicalhistory as pa ON (p.PatientId = pa.PatientId) WHERE pa.PatientId='".$pid."'" ); — Maricar Manlulu
You probably need an AND. Post code in your question its not readable in a comment — Mike Miller

HashSu HashSu · Accepted Answer · 2015-07-17T15:19:57

$query2= mysqli_query($link,"SELECT p.PatientId,p.firstname,p.middlename,p.lastname 
FROM patient p 
INNER JOIN medicalhistory as pa ON (p.PatientId = pa.PatientId) 
WHERE pa.PatientId='".$pid."'" );

This would be your code which is modified:

$dids = $_SESSION["DoctorId"];
if(isset($_POST["btnsrch"])&& ($_POST["textbox"]!="")){
    $pid = $_POST["textbox"];
    $pid= mysqli_real_escape_string($_POST["textbox"]);

    $query2= mysqli_query($link,"SELECT p.PatientId, p.FirstName, p.MiddleName, p.LastName,pa.doctorID,pa.Evaluation FROM patient p INNER JOIN medicalhistory as pa ON (p.PatientId=pa.PatientId) WHERE pa.PatientId='".$pid."'" );
}

Sample Output:

mysql> select p.id,p.First,p.last, p.middle,pa.id,pa.evalution from test1 as p left join test as pa on (p.id=pa.id) where p.first='Dan' and p.id=4 and p.last='smi
th';
+----+-------+-------+----------+----+-----------+
| id | First | last  | middle   | id | evalution |
+----+-------+-------+----------+----+-----------+
|  4 | Dan   | Smith | chandran |  4 | sick      |
+----+-------+-------+----------+----+-----------+
1 row in set (0.00 sec)

how to search in multiple table?

1 Answers