Jump to content

enter data in two table at same time one column links both tables


cedtech23

Recommended Posts

I have two tables appointments and patients where I need to enter information at the same time. The table structures is a follows

 

Patients

Id   primary key auto increment

F_name  varchar(30)

L_name  varchar(30)

Phone_num  varchar(30)

 

Appointments

Id  primary key auto increment

Appt_date  date

Appt_time time

Pt_id int       this will be id from patients table.

 

So the end user will enter the patients first name, last name,  phone number appointment date and time. When they hit the submit button that information will populate the appointments table and the patients table.

 

Here lies my question do I have to do three sql statements to enter the information into both tables since the column “pt_id”  in “appointment”  relies on the column “id” in patients and since the patient is a new patient that date will not be present?

 

So thus my insert statement will be

 

# use this statement to enter the patient data

$sql_1= INSERT INTO patients (f_name, l_name, phone_num) VALUES (‘$f_name’, ‘$l_name’, ‘$phone_num’);

 

$res_1 = mysql_query($sql, $conn) or die(mysql_error());

 

#use this state to get the patient id based on the user last and first name entered

$sql_2 = select id from patients where f_name = $f_name, l_name = $l_name;

$res_2 = mysql_query($sql_2, $conn) or die(mysql_error());

 

#store the patient id in a variable call $id

While ($row = mysql_fetch_array($res_2) {

 

$id = $row[‘id’];

}

 

#enter the data in the appointment table

$sql_3 = INSERT INTO appointment (appt_date, appt_time, id) VALUES (‘$appt_date’, ‘$appt_time’, ‘$id’)

 

Is this the correct method? Is there easier approach with less coding?

 

Link to comment
Share on other sites

Though there is nothing wrong with that, an easyer method is to use

 

mysql_insert_id()

http://ca3.php.net/manual/en/function.mysql-insert-id.php

 

# use this statement to enter the patient data

$sql_1= INSERT INTO patients (f_name, l_name, phone_num) VALUES (‘$f_name’, ‘$l_name’, ‘$phone_num’);

 

$res_1 = mysql_query($sql, $conn) or die(mysql_error());

 

#get the patient id based on the last insert

$id = mysql_insert_id($conn);

 

#enter the data in the appointment table

$sql_3 = INSERT INTO appointment (appt_date, appt_time, id) VALUES (‘$appt_date’, ‘$appt_time’, ‘$id’)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.