In message <[EMAIL PROTECTED]>, David Halliday <[EMAIL PROTECTED]> writes > >--- Pete <[EMAIL PROTECTED]> wrote: > ><-snip-> > >This is where the third table comes in, with just two >fields, patient, >and drug, to link the tables together. >Patient 20 (John Smith) is on drug 23 >Patient 31 (Maria Jones) is on drug 13 >Patient 31 is on drug 915 >Patient 50 is on drug 23 > >Patient code and patient name in PATIENTS >Drug code and drug name in DRUGS >Patient code and drug code in LINK > ><-snip-> > >But there is no code, Pete, these values have been >entered manually by secretaries.
But you have to create it... isn't that what you want to do? > As I said in the >initial enquiry, there are two sets of data: > >The 70 tables (which was obviously the wrong >approach), each has a column listing all the patients >on that medicine. >For example: > > ASPIRIN lists 1989 patients's names (in one column) > VALIUM lists 734 patients' names so on > > There is also one table called PATIENTS. It lists all >the 2000 individuals' names. > >To be honest, I was hoping to be able somehow to have >a variable, an array stored in one field on the >"patients" table. That array can somehow reference >the other set of data from the medications table (the >70 rows). So that when a patients name is selected, >the array variable can be output giving a list of the >mediciactions!. What I can't get clear is, what you will (are able to / are allow to) do. It seems that you don't want to do any data shuffling to put the data right? You just want to add one field to the Patients table? And you don't have a code number for the drugs, just a tablename. Do you appreciate that you would build table3 with SQL commands, not by typing? You see, I don't understand why you have this problem. I can see that, historically, the database was designed badly, and needs putting right. But you seem to be saying that you cannot (are not allowed to?) put it right. Or is it just that you don't want to? > >Can a table column contain a variable? All table columns contain *nothing but* variables <G> > >For example:- > >PATIENTS TABLE >id | name | $medications[70] ] >1 | John | some code here to fetch the value of >$medications from the 70 rows? Some databases support Array fields (have we established what kind of database you are using?). But you wouldn't put code in the database. I cannot think of any method that will be safe and extensible, useful for the future. Somewhere along the line was the 'suggestion' that you could add 70 columns to the patients table. But what happens when a new drug comes out? And all the things that I try to think of, are much more complicated than simply putting the tables right. I also believe that you will also have problems because the patients names appear to have been typed in at least twice (once in the patients table, once in the aspirins table, once in the...), and I can guarantee that they won't match exactly. However, all this can be checked (using SQL) and corrected before the data is shuffled. -- Pete Clark http://www.hotcosta.com http://www.spanishholidaybookings.com ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/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/
