Jump to content


Photo

Display contenst from 2 mysql tables on a single page


  • Please log in to reply
9 replies to this topic

#1 diznut

diznut
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 19 September 2006 - 06:29 AM

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.


#2 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 19 September 2006 - 06:46 AM

Hi,

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

hth

#3 diznut

diznut
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 19 September 2006 - 07:13 AM

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

#4 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 19 September 2006 - 08:49 AM

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

#5 diznut

diznut
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 19 September 2006 - 10:43 AM

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

#6 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 19 September 2006 - 11:32 AM

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.

$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
}


#7 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 19 September 2006 - 12:34 PM

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

#8 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 19 September 2006 - 12:39 PM

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

Info: PHP Manual


#9 diznut

diznut
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 20 September 2006 - 05:29 AM

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.


#10 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 20 September 2006 - 06:14 AM

Hi,

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users