Display contenst from 2 mysql tables on a single page

[diznut]

Dear All,

I have a Mysql Database of Patients with two tables.

Table 1 is Patients Personal Contacts and Table 2 holds Patients Hospital records.

Example:

Table 1:- Patient ID, Name of Patient, City, Country, Tel, Email, Postal Address

Table 2:- Visit ID, Date of Visit, Next Visit date, Diagnosis, Dr. Report, Medications


Now I need to create a PHP MYSQL page that will Display Data of certain Patient of Table 1 and Table 2 in a single Page, Also to have a Page that will be used to Enter/update Data to this Two Tables from a single page that will Hold Two Forms, one form for data of Table 1 and another Form for Table 2.To have a Searching tool, to be able to Delete Data.

The Primary Key for Table 1 is PATIENT ID and For Table 2 is VISIT ID but one patient should have only one Patient ID but he/she can have one or more Visit ID.


Hope it is under stable, Hope to read from you Guys as soon as Possible. Any Input is appreciated.

THANKS.

NOTE:
This Database is working Fine in MS Access but as you know MS access have Limit also the Security is not Good and it's not good on Multiple Connection for entering or Update data.

[bob_the _builder]

Hi,

you need another field in the 2nd table to hold "Patient ID" so you can create the relationship between the 2 tables ..

hth

[diznut]

I have the Patient ID on the other table too. can you show me the Code? how to do it?THANKS.

[bob_the _builder]

What code do you allready have, at what point are you stuck?

[diznut]

<?php
include 'config.php';
include 'connect.php';

$query = "SELECT c.Patient_Name,c.City, c.Country, c.Tel, c.Email, c.Postal, r.Date_visit, r.Next_Visit, r.Diagnosis, r.Dr_report, r.Medication FROM contact AS c
LEFT JOIN records AS r ON c.Patient_id = r.Patient_id
GROUP BY c.Patient_name";

$result = mysql_query($query);

$result = mysql_query($query)
  or die("Query failed: " . mysql_error() .
        "<br>Actual query: " . $query);


while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "<b>ID</b> : {$row['Patient_ID']} <br><br>".
"<b>Patient Name</b> :{$row['Patient_Name']} <br>" .
        "<b>City</b> : {$row['City']} <br><br>".
"<b>Country</b> : {$row['Country']} <br><br>".
"<b>Telphone</b> : {$row['Tel']} <br><br>".
"<b>Email</b> : {$row['Email']} <br><br>".
"<b>Postal Address</b> : {$row['Postal']} <br><br>".
"<b>Date of Visit</b> : {$row['Date_Visit']} <br><br>".
"<b>Next Visit</b> : {$row['Next_Visit']} <br><br>".
"<b>Diagnosis</b> : {$row['Diagnosis']} <br><br>".
"<b>Dr. Report</b> : {$row['Dr_Report']} <br><br>".
"<b>Medications taken</b> : {$row['Medication']} <br><br>".;
} 

include 'closedb.php';

?>
---/////end of Code /////---

As you can see at the Code Now am Able to Dispaly Data of a patient by Single record,So I need a page that will Show all the records EXample: If a Patient have Visit Like 20 times So I need to see all Visit Dates and Only to see The Latest Next Visit, To see Each Visit date with it's Doctor report and It's Diagnosis and It's Medication.

HOPE it's understable.

[bob_the _builder]

Hi,

If its just one patient, and all their prior visits why not just 2 querys. First to show the patient details, the next to list all their visits in which you could use pagination etc on.

[code=php:0]$details = "SELECT * FROM contact WHERE Patient_id = {$_GET['Patient_id']}";
while($row = mysql_fetch_array($sql)) {

//list patient name + details here

}

$records = "SELECT * FROM records WHERE Patient_id = {$_GET['Patient_id']} ORDER BY Next_Visit ASC";
while($row = mysql_fetch_array($sql)) {

// List all patient records here
}[/code]

[craygo]

Change your group statement.

GROUP BY c.Patient_name, r.Date_visit

You can add any other group as long as it is different from the patient name

Ray

[ober]

I'm going to change your title.  This is your first and only warning.  Do NOT use all caps in a thread title.

[diznut]

Hi There,

Thanks for the Code,It's Great Idea am going to try it and let you know.

Well one more favor, What about the PHP form which I will USe to Post New Entry into Both two Table form a Single Form,Also if I have the Details and Records of one of Patient and I need to update the Data or Enter new record to such Patient example: a new Date of Visit,Next Visit,Medication,Dr. Report.

HELP PLse.

[bob_the _builder]

Hi,

it will be 2 INSERT querys, placing the correct variables into each query.