Hello Dave,
Sorry about this delay but i wasnt feeling well
yesterday.
At long last .. it clicked :) It is now very clear
and thank you so much for the detailed reponse.
Just two remaining points .. I think that MYSQL tables
can have a maximum of about 65000 rows and about 3400
columns. Now in this particular case .. in theory
only .. 2000 patients could be on 70 drugs each ..
hence the in third table "patient_med", we would have
70 X 2000 = 140,000 rows. How is this limit dealt with
generally. Could one have several tables ..
patient_med1 .. patient_med2 and so on, and then add
something in the script to check them all?
And a point about speed, on average .. how long would
it take to check 65000 rows in this case ..
obviously it depends on several factors. I am thinking
really about ways of optimising the script and making
it faster.
Best regards
David
---------------------------------
Mike Ro has responded and what he said is absolutely
the case for actually
coding this solution. I'll try to explain it a bit
more.
Here's the tables again:
Patient:
ID | First | Last | Address | Phone
1 | Joe | Blow | 123 Main | (555)555-5555
2 | Susie | Ort | 444 Main | (555)555-1234
Medication:
ID | Name | Description | URL
1 | Aspirin | Headache Relief | http://...
2 | Codeine | Serious Pain relief | http://...
3 | Advil | Pain Relief | http://...
4 | THC | Glaucoma Relief | http://...
patient_med:
patient_ID | med_ID
1 | 1
1 | 3
2 | 2
2 | 3
2 | 4
The patient_med table is what links patients and the
meds they are taking.
Let's take it row by row:
The first row of patient_med is
patient_ID | med_ID
1 | 1
If we look in the patient table for the patient who
has an ID of 1, we see
that the patient is Joe Blow who lives at 123 Main. So
this row is about
Joe. Then if we look at the med_ID field of this row,
we see 1. So we use
that to look up the medicine in the medicine table. We
want the medicine
that has an ID of 1. That medicine is aspirin. So now
because of this
linking of patient and medicine in the patient_med
table, we see that Joe
Blow is taking aspirin. Let's look at the next row:
patient_ID | med_ID
1 | 3
Again we see patient_ID is 1. Looking in the patient
table, we see that
we're talking about Joe Blow again. The med_ID field
is different though. If
we see what medicine has an ID of 3, we see that it is
Advil. So now we know
the Joe Blow takes Advil. Between these two rows we
know that Joe Blow takes
Aspirin and Advil.
Let's look at the third row:
patient_ID | med_ID
2 | 2
If we look for the patient with ID of 2, we see that
it is Susie Ort, so
this row will let us know something about Susie Ort's
meds. The med_ID of 2
matches the medicine in the medicine table with an ID
of 2, which is
Codeine, letting us know that Susie has a serious
headache.
We can continue through the table, matching patient_ID
with an ID in the
patient table and matching a med_ID with an ID in the
medicine table. Each
row in the patient_med table works this way,
specifying a connection between
the patient and the medicine, in this case that the
particular patient is
using a particular medication.
Now your application most likely would *not* need to
do what we just did.
That's because there's probably not a case where you
want to know what meds
all of your patients are taking all at the same time.
More likely you want
to know what medications a particular patient is
using, or in the opposite
direction, find a list of all the patients who are
using a particular
medication (maybe to let them know about a class
action lawsuit against the
manufacturer, or to let them know of a new medicine
that has come out that
they may be interested in.
In either case, the information you want can be
fetched with a single SQL
query.
Let's look at a couple of the scenarios I talked
about:
You have a patient in mind and want to know what meds
they are on:
If you have the patient info, you probably have the
patient ID from the
table. If not you could change this query slightly:
SELECT name FROM medicine, patient_med WHERE
patient_med.patient_ID='<the
patient's id>' AND patient_med.medicine_ID=medicine.ID
This will give you a result set that contains one row
for each medicine the
patient is on. This row will consist of the name of
the medication, so you
could easily go through each of the rows and list the
medications the
patient is using. With only a slight change to get the
URL or other
information, you could also output a link to the
webpage about that
medication or the description of the med, or both.
You have a medicine in mind and want to know who is
taking it:
Similar to above, if you are looking at information on
a med, I am assuming
you already have the ID for the medicine. If not you
can modify the query to
get it at the same time.
SELECT first, last FROM patient, patient_med WHERE
patient_med.med_ID='<the
medicine's id>' AND patient_med.patient_ID=patient.ID
This will give you a resultset with the first and last
name of each patient
who is taking a particular medication.
If you know what patient you are interested in and
just want to know if they
take a particular med, you can query just the
patient_med table to see if
there is a row that has the patient's ID and the
medication's ID. If there
is, then you know the patient is taking that med. If
you don't get any rows
then as far as you know the patient is not taking that
medication.
I hope this helps to explain it better.
Dave
On 11/3/05, Mike Ro <[EMAIL PROTECTED]> wrote:
>
> David,
>
> you are responsible for adding the data to ALL 3
files.
>
> You will need 3 routines at minimum -
>
> one to add a patient (to the patient file)
> one to add a medicine (to the medicine file)
> one to add a particular patient using a particular
medicine (to the link
> file)
>
> so when you get a new medicine, you
> 1) add it to the medicine file
>
> when you get a new patient, you have to do 2 things
> 1) add the patient to the patient file
> 2) add an entry to the link file for each medicine
that the new patient is
> using
>
> Of course 3 routines is minimum - you will probably
need routines to edit
> and/or delete, as well as search, etc.
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/CefplB/TM
--------------------------------------------------------------------~->
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
