cedtech23 Posted December 3, 2007 Share Posted December 3, 2007 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? Quote Link to comment Share on other sites More sharing options...
mrdamien Posted December 3, 2007 Share Posted December 3, 2007 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’) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.