[SOLVED] A db check and insert

[Snooble]

Right does this make sense...

 

I will have a paitent database (MySQL) each row is each paitent. Every paitent has 4 fields filled in. I want a script that adds 2 fields one called "Date" and one called "Score". but if they already have them fields i want to create "Date1" and "Score1" then if they have them i want the info to be inserted into "Date2" and "Score2" instead.... Understand what i'm thinking?

 

I will be seeing the paitents and i want to simply be able to type the date and the score in a box with the name in a GET variable. I can do everything BUT check to see if the Date & Score fields are empty for THAT paitent. if they're not i need it to check Date1 & Score1 when it finds an empty cell i want it to input the info.

 

I hope that explains it :S

 

I'm really after the code that can check for a value, something like if($cell != "null") or however. but if its not empty it needs to check the Date1 field, then Date2, etc... until one is empty to insert into.

 

Thank you ever so much,

 

Snooble

[cmgmyr]

why don't you just make a separate table to hold scores/dates. then when you add a new one it will just add another row to that

[Snooble]

I don't understand how that would help me?

 

Thank you for the help. but it will work adding fields. it's just the finding "the first" empty field to put it in. If "Date" is empty put it there, if not try "Date1" if that also contains data try "Date2" etc...

 

Thanks

 

Snooble

[cmgmyr]

but that would mean that you are adding columns right?

[chiprivers]

You should have two tables:

 

CREATE TABLE patients (
patientID int unsigned not null auto_increment primary key,
patient varchar(50)
// any other fields required for each patient
)

CREATE TABLE scores (
scoreID int unsigned not null auto_incrrement primary key,
patientREF int,
when date,
score int
)

 

You can then have one entry in the patient table for each patient and create a new entry into the scores table on each occassion as required.  With this set up you can then either return all score records for a given patient:

 

SELECT s.* FROM scores AS s WHERE s.patientREF = $patientID ORDER BY s.when DESC

 

or you can return just the most recent score:

 

SELECT s.* FROM scores AS s WHERE s.patientREF = $patientID ORDER BY s.when DESC LIMIT 1

[Snooble]

I understand parts of that, sorry.

 

I have one db called paitents in which i have 4 fields -

 

Name    Diagnosis    DIAMD    FirstInjection

 

 

All of that information is added correctly using a page i made. But i assume you are suggesting when that's created i should also input the name into another database called "scores". ?

 

So scores will be:

 

Name      Date        Score

 

?

 

The output to the page should look like this:

 

 

Name                    |    Date    |    Date1...

Diagnosis              |    Score    |    Score1...

DIAMD                 

FirstInjection

 

Bare in mind there will be 50 or more paitents in the rows. And each will have a different amount of revisits. Each revisit means i take down the date and their score. I want it to output them in order of which i input. Or by Date as that would be the same.

 

But i think i can do the outputting. it's just the inserting, because how many fields should "scores" have, as there might be as many as 100 revisits!

 

Thank you for your help, sorry i couldn't get back to you all until now.

 

Snooble

[chiprivers]

To expand a little further on my previous post...

 

The patients table should have fields to hold constant information about each individual patient and there should be one entry into the table for each patient, such as below:

 

| patientID  |  name                  |  (any other constant data such as address or tel...)

|-----------|-------------------|

|  1            | Joe Bloggs            |

|  2            | John Smith          |

|  3            | Simon Jones        |

|-----------|-------------------| etc etc

 

The scores table will hold details relevent to each occassion of a score for a patient, with one entry for each occassion, such as below:

 

| scoreID    | patientREF  | date            |  score    |  (any other details relevent to the score / event...)

|-----------|-------------|-------------|----------|

| 1            |  2              | 2007-01-01  | 150      |

| 2            |  1              | 2007-01-05  | 200      |

| 3            |  2              | 2007-02-02  | 160      |

| 4            |  3              | 2007-02-02  | 200      |

| 5            |  1              | 2007-02-05  | 180      |

|-----------|-------------|--------------|---------| etc etc

 

You will see above that if you take for example Joe Bloggs (patient ID 1) he has entries in the score table for dates 5th Jan and 5th Feb.

 

You can then query the scores table to either return all records for a certain patient and order by date, most recent first:

 

SELECT s.* FROM scores AS s WHERE s.patientREF = $patientID ORDER BY s.date DESC

 

or you can select the most recent score details for a given patient using the following:

 

SELECT s.* FROM scores AS s WHERE s.patientREF = $patientID ORDER BY s.date DESC LIMIT 1

 

 

Hope this helps.

[Snooble]

that makes complete sense to me i just am having a little trouble with your query.

 

SELECT s.* FROM scores AS s WHERE s.patientREF = $patientID ORDER BY s.date DESC

 

what are the "s." 's?

is * a wild card?

 

thank you for your help. So the paitent id's link the scores to the names etc. Just need the answers for the above questions.

 

Snooble

 

Many thanks!!!!!!!!!

[cmgmyr]

you are assigning the table "scores" to "s" so you can write s.column instead of scores.column it's just a shorthand version

 

and * means that you are selecting all of the columns in the specific table

[chiprivers]

You can leave out the s. in this example, it become more necessary when you are joining two table such as you would if you wanted to combine the patient details with their results:

 

SELECT p.name, s.date, s.score FROM scores AS s LEFT JOIN patients AS p ON (s.patientREF = p.patientID) ORDER BY s.date DESC

 

This would return all of the entries from the scores table but would also add a column to each record with the relevent patients name.  You can see the shorthand initials for the two tables makes this query shorter and simpler to read.

[Snooble]

woh... Erm...

 

i still am confused, sorry.

 

I usually just use "Select * from TABLE" etc.

 

can i not just

$id = $_GET['id']
"Select Date, Score FROM scores WHERE PaitentID = $id"

 

Or am i far from the point.

 

I think i understand?...

 

Tables

---------------

p = paitents

s = scores

---------------

 

So it's just to distiguish between tables. If i had scores.name and paitent.name i could simplify it to s.name and p.name?

 

One more question since you're all damn good...

 

I'm echo'ing out the results. i want it to look like this:

 

Name                    |    Date    |    Date1...

Diagnosis              |    Score    |    Score1...

DIAMD                  |              |

FirstInjection          |              |

 

I have the first column done, just by creating if statements like "If ($col == Name)" and <br>'d the others but each paitent should have it's own table with as many columns as they need.

 

Eg. If paitentID No.1 had 3 sets of scores it would have 4 columns, one with the name etc. and the other three from left to right starting with the oldest. and ending with the most recent score. (I'm assuming an ORDER BY date) Could you give me a few of the important bits of code i would need to do so. mainly the foreach or while etc.

 

(My assumption is that i need a "while it pulls a line from the SQL table with the query |Select s.* FROM scores WHERE paitentid = 1|" Each times it pulls out a row for that id it should create a new column in the table?)

 

Thanks for your help! If i get a few examples from this i'll get fixing the table tomorrow morning.

 

Snooble