Insert a value into multiple tables

Question

Okay, I am trying to submit values from a php form into multiple tables. My php code is working fine but values such as patientID are inserting into "patients" for example: PatientID; 100 fine but the same value for PatientID is not inserting the same unique value into another table for example: the "Disease" table. Am I doing something wrong?

**revised question

I'm not sure if I have the relationships between the tables correctly assigned. Here are the tables and the relationships between them.

Patient Attends Accident & Emergency 
Patient seen_by Nurse
Nurse assesses disease of patient 
{{nurse assigns priority to patient}} Priority linked to patient and nurse
{{nurse gives patient waiting time}} Time linked to nurse and patient 
{{doctor will see patient based on their waiting time and priority}} Doctor linked to both time and priority. 
Accident & Emergency; (ID(PK), PatientID(FK) Address, City, Postcode, Telephone)
Patient (ID(PK), Forename, Surname, Gender, Dateofbirth, Address, Patienthistory, illness, 
Nurse(ID(PK) Forename, surname)
Assesses(ID(PK)NurseID(FK), PatientID(FK))
Disease(ID(PK), illness, symptoms, diagnosis, treatment) {{nurse assesses disease of patient (these tables should all be linked}}
Priority (ID, NurseID(FK), PatientID(FK), DoctorID(FK), Priority)
Time(ID,NurseID, PatientID, DoctorID, Arrival Time, Expected waiting time, Discharge time)
Doctor (ID,Firstname, Surname)

Revised PHP code. ID is not inserting into tables; for example: PatientID is not inserting into the Disease table.

<?php
$con = mysql_connect("localhost","root","") or die('Could not connect: ' . mysql_error());
mysql_select_db("a&e", $con) or die('Could not select database.');

//get NURSE values from form
$nurse_ID = $_POST['nurse_ID'];
$nurse_name = $_POST['nurse_name'];
$nurse_lastname = $_POST['nurse_lastname'];

//get Disease values from form
$disease_ID = $_POST['disease_ID'];
$symptoms = $_POST['symptoms'];
 $diagnosis = $_POST['diagnosis'];
$treatment = $_POST['treatment'];

//get Patient values from form 
$patient_id = $_POST['patient_id'];
$patient_name = $_POST['patient_name'];
$patient_lastname = $_POST['patient_lastname'];
$gender = $_POST['gender'];
 $dateOfBirth = $_POST['dateOfBirth'];
$monthOfBirth = $_POST['monthOfBirth'];
$yearOfBirth = $_POST['yearOfBirth'];
$address = $_POST['address'];
$history = $_POST['history'];
$illness = $_POST['illness'];
$priority = $_POST['priority'];
$priority_id = $_POST['priority_id'];

// Validate
$date = $dateOfBirth.'-'.$monthOfBirth.'-'.$yearOfBirth;

$sql ="INSERT INTO Nurse(Forename, Surname)
VALUES('$nurse_name', '$nurse_lastname')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());
echo "$nurse_ID"; mysql_insert_id(); //get the assigned id for a nurse

$sql ="INSERT INTO Disease(Illness, Symptoms, Diagnosis, Treatment, PatientID)
   VALUES('$illness', '$symptoms', '$diagnosis', '$treatment', '$patient_id')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());
echo "$patient_id"; mysql_insert_id(); //get the assigned id for a patient 

//use nurse_id and patient_id
$sql ="INSERT INTO Priority(NurseID, PatientID, Priority)
   VALUES('$nurse_ID', '$patient_id', '$priority')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());
echo "$priority_id"; mysql_insert_id(); //get the assigned id for priority
echo "$patient_id"; mysql_insert_id(); //get the assigned id for a patient

$sql="INSERT INTO Patient(Forename, Surname, Gender, Date_Of_Birth, Address, Patient_History, Illness, Priority)
  VALUES     ('$patient_name', '$patient_lastname', '$gender', '$date', '$address', '$history', '$illness', '$priority')";
 mysql_query($sql,$con) or die('Error: ' . mysql_error());
 echo "$patient_id"; mysql_insert_id(); //get the assigned id for a patient

echo "1 record added";
 // close connection 
 mysql_close($con);
 ?>

Just a note, your code is prone to SQL injections and also you're using the deprecated mysql library. You should filter your POST variables with the filter functions or at least with something like addslashes() to prevent casual injections. — Ynhockey, Feb 02 '13 at 17:46
"trying to submit values from a php form into multiple tables in _phpmyadmin_" — Wait, what? — lanzz, Feb 02 '13 at 17:58

score 0 · Answer 1 · edited May 23 '17 at 12:04

While I don't entirely understand how your system is supposed to work, you can see in the following code that it will never insert different IDs for the disease ID and the patient ID:

$sql ="INSERT INTO Disease(ID, Illness, Symptoms, Diagnosis, Treatment, PatientID)
VALUES('$id', '$illness', '$symptoms', '$diagnosis', '$treatment', '$id')";

Basically you're inserting a disease ID which is exactly the same as the patient ID. You probably want to have different variables for those.

Regarding my comment above:

You can filter like this:

$id = intval($_POST['ID']);
$name = filter_input(INPUT_GET | INPUT_POST, $_POST['name']); // works in PHP 5.2.x and above

Regarding MySQL, see this post: Why shouldn't I use mysql_* functions in PHP?

I know exactly what you mean..I tried having different variables for each ID but an error continued to occurr..which wasnt occurring when i had all set to "id". I will edit the code now. Thankyou for explaining — user2027028, Feb 02 '13 at 17:57

peterm · Accepted Answer · 2013-02-02T19:48:47.950

you need to use unique ids, names and lastname for different entities (nurse, patient, disease etc). And then use them appropriately in INSERT statements. See revised code below.
select your db only once at the beginning of the script with mysql_select_db (if you planning to stick with mysql_*).
Sanitize and validate input from the user before inserting it.
Insert your records in correct (logical) order (nurse, patient, disease, priority).
Now all of your ids come via POST. You might consider using id auto-reneration in mysql.
You have a missing variable $priority_id. I've put it in the revised code assuming that you get it the same way via POST.
Do proper error handling not just die().
Better consider to switch to PDO or mysqli_* and use prepared statements.

Revised code (updated):

Assumption is that auto_increment is enabled for the id column of every table.

$con = mysql_connect("localhost","root","") or die('Could not connect: ' . mysql_error());
mysql_select_db("a&e", $con) or or die('Could not select database.');

//get NURSE values from form
//We don't need to post an id for a Nurse since mysql will assign it for us
//$nurse_id = $_POST['nurse_id'];
$nurse_name = $_POST['nurse_name']; 
$nurse_lastname = $_POST['nurse_lastname'];

//get Disease values from form
// We don't need to post an id for a Disease since mysql will assign it for us
//$disease_id = $_POST['disease_id'];
$symptoms = $_POST['symptoms'];
$diagnosis = $_POST['diagnosis'];
$treatment = $_POST['treatment'];

//get Patient values from form
//We don't need to post an id for a Patient since mysql will assign it for us
//$patient_id = $_POST['patient_id'];
$patient_name = $_POST['patient_name'];
$patient_lastname = $_POST['patient_lastname'];
$gender = $_POST['gender'];
$dateOfBirth = $_POST['dateOfBirth'];
$monthOfBirth = $_POST['monthOfBirth'];
$yearOfBirth = $_POST['yearOfBirth'];
$address = $_POST['address'];
$history = $_POST['history'];
$illness = $_POST['illness'];
$priority = $_POST['priority'];

//We don't need to post an id for a Priority entity since mysql will assign it for us
//missing variable
//$priority_id = $_POST['priority_id'];

//Sanitize and validate your input here 
// ...skipped
// Validate
$date = $dateOfBirth.'-'.$monthOfBirth.'-'.$yearOfBirth;

//We don't provide an id for a Nurse since mysql will assign it for us
$sql ="INSERT INTO Nurse(Forename, Surname)
       VALUES('$nurse_name', '$nurse_lastname')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());
$nurse_id = mysql_insert_id(); //get the assigned id for a nurse

//We don't provide an id for a Patient since mysql will assign it for us
$sql="INSERT INTO Patient(Forename, Surname, Gender, Date_Of_Birth, Address, Patient_History, Illness, Priority)
      VALUES('$patient_name', '$patient_lastname', '$gender', '$date', '$address', '$history', '$illness', '$priority')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());
$patient_id = mysql_insert_id(); //get the assigned id for a patient

//We don't provide an id for a Disease since mysql will assign it for us
$sql ="INSERT INTO Disease(Illness, Symptoms, Diagnosis, Treatment, PatientID)
       VALUES('$illness', '$symptoms', '$diagnosis', '$treatment', '$patient_id')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());

//We don't provide an id for a Priority since mysql will assign it for us
//But we use $nurse_id and $patient_id that we get earlier
$sql ="INSERT INTO Priority(NurseID, PatientID, Priority)
       VALUES('$nurse_id', '$patient_id', '$priority')";
mysql_query($sql,$con) or die('Error: ' . mysql_error());

echo "1 record added";
// close connection 
mysql_close($con);

@user2027028 You're welcome. Do you need more help with your question? — peterm, Feb 02 '13 at 19:04
I am getting an error message "duplicate entry for primary key". I have set the ID's to auto_increment so this should not be occurring. — user2027028, Feb 02 '13 at 19:13
Well it's not that simple. If you enabled `auto_increment` then you don't need to pass an id to `INSERT` but you need to retrieve assigned by mysql id value for that entity with `mysql_insert_id()` before you use it in the next INSERT. e.g. `$nurse_id = mysql_insert_id()` — peterm, Feb 02 '13 at 19:24
could you give me an example using the code that you previously provided? If I understand you correctly I dont have to define the variable for example: $nurse_id = $_POST['nurse_id']; and I dont need the Id for insert either? — user2027028, Feb 02 '13 at 19:30
i shall try that solution now. Thanks for your help, much appreciated — user2027028, Feb 02 '13 at 19:53
the NurseId and PatientId isnt inserting into the "priority" table. — user2027028, Feb 02 '13 at 20:31
there is no error; it says "1 record added". PatientID enters fine into the Patients table but not in the Disease or Priority table. — user2027028, Feb 03 '13 at 10:27
In order to help you with that I need a lot more details: DDL (create table statements) for all of your tables, do `echo $sql`; before every `mysql_query()`, do `echo "nurse_id: $nurse_id";` and `echo "patient_id: $patient_id";`. Post all that information it in your original question. — peterm, Feb 03 '13 at 17:12
I dont think I have the relationships between the tables correctly assigned. It's rather complicated but I have explained it the best way I can in the revised question. I apologise if its confusing, if so I will try to explain it better. — user2027028, Feb 05 '13 at 12:26
@user2027028 Sorry, it getting to much for the original question. You might consider to narrow it down and post additional question(s). — peterm, Feb 08 '13 at 05:21

Insert a value into multiple tables

2 Answers2

Linked