How to use php script to create a table in the database??

emfung

I am a medical student.
I am working on a project to create a database that stores patient's records of medical problems. Instead of creating a row in a table for each patient, I would like to create seperate table for each patient so that I can store the patient's medical problem that is occasionally updated when the patient visit the clinic again.

Each table will be named under patient's username and the fields for each table are:
-Date of Visit
-Presenting Complaint
-Diagnosis
-Prescribed Treatment
-Next Appointment

My question is How to use php to create tables??

This may not be a good idea. I'm n00 in mysql-php.
I thought of this way so that each patient will have their own organised records of visits (in a single table for each of them). If I create a single table for patient's visits for all the patients, then the table will contains visits of different patients. How can I query to view the history of visits of a single patient then??
You may suggest a better way.

OhLordy

You can create tables from within PHP using the mysql_query function, however this is not a good idea as you will eventually have lots of tables. Besides you should not have to do this, that is the point of a relational database.

Have one table, patients, with all the patients details in and then anoter table called visits. This table will hold all the visit details and also the primary key of relevant patient from the patient table.

Good Luck
Rob

bad76

Hi, try this:

Just create one time a model table for each table.
Then export table sql in a text file.
To make this you can use phpmyadmin self.

In php file, assign this line of sql to a variable and
execute it:


$query="CREATE TABLE ".$patient_name."
  ID int(4) unsigned NOT NULL auto_increment,
  CodGrp char(10) default NULL,
  Gruppo char(70) default NULL,
... snip ...
  PRIMARY KEY  (ID),
  UNIQUE KEY FieldName (ID),
  KEY FieldName_2 (ID)
) TYPE=MyISAM;";

mysql_query ($query) or die ("MySql Error on : ".mysql_error());

see you

emfung

Thanks OhLordy,

I know that I shouldn't do in this way.
As I have said, If I create a single table for patient's visits for all the patients, then the table will contains visits of all the patients.

My problem is that I will have to create a page that allow patient to view his/her own history of visits. The patient must enter username and password to view.
Since the table 'visits' contain visits of different patients, How can I query to view the history of visits of a single patient then??

OhLordy

That is why you have the user's id in the visits table. This way when the user logs in you get their user id. Then when the user goes to their own visits page you you can search the visits table with

SELECT * FROM visits WHERE userID='provided id'

See?
HTH
Rob

emfung

OhLordy wrote:

SELECT * FROM visits WHERE userID='provided id'

So, do you meant I have to make an html form that allow patient to enter the 'provided id'? Such as the following:

<form name="form" method="post" action="register.php"> 
  <input name="patient_id" type="text" id="patient_id" value="<? echo (isset($patient_id)); ?>">
<input type="submit" name="Submit" value="Submit">

laserlight

Well, a possible solution (which I noticed OhLordly also suggested earlier), would be:

Table: patients
patient_id
patient_name
//other patient info

Table visits
visit_id
patient_id
visit_date
complaint
diagnosis
treatment
next_appt

Now, most probably the patient himself will not search for this info.
Rather the receptionist or medical staff may need this info.
As such, the staff member will first obtain the patient's name.
You probably would not use patient_name, but rather have different columns for name and surname.
From that, he can get the patient_id of that patient from the patients table.
With the patient_id, he can use another form to search for the patient's visits in the visits table.
patients.patient_id is the primary key of the patients table.
visits.patient_id on the other hand is a foreign key of the visits table.