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

[cedtech23]

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?

 

[mrdamien]

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’)