Insert data into two tables using php and mysql

Question

HB25 0 Junior Poster in Training

15 Years Ago

Hi
I have two tables called client and booking, the primary key in client table is clientID which is autoincress number and it’s a primary key, in the second table (booking) the primary key is bookingID and its autoincress number and the clientID is foren key.
I have a questions:

How could I get the clientID number from the first table and embedded to a Mysql querry to insert it with the other booking details i.e (arrival date, departure date,...) into the booking table.

Any help will be highly appreciated ( I have read about the last_insert_id() but I don’t know how to use it an example will very good help).
Kind Regards

mysql

3 Contributors
15 Replies
6K Views
11 Months Discussion Span
Latest Post 14 Years Ago Latest Post by Babaty

All 15 Replies

nav33n 472 Purple hazed!

15 Years Ago

last_insert_id() in mysql or mysql_insert_id() in php gets the value of the autoincrement field from the last insert query.
Eg.

//using mysql's LAST_INSERT_ID()
// considering clientID as auto increment - primary key
$add_to_client_query = "insert into client (name,age) values ('test','20')";
mysql_query($add_to_client_query);
$add_to_booking_query = "insert into booking (busname, totalseats,clientID) values ('Air bus','30',LAST_INSERT_ID())";
mysql_query($add_to_booking_query);

//using php's mysql_insert_id()
// considering clientID as auto increment - primary key
$add_to_client_query = "insert into client (name,age) values ('test','20')";
mysql_query($add_to_client_query);
$last_inserted_mysql_id = mysql_insert_id();
$add_to_booking_query = "insert into booking (busname, totalseats,clientID) values ('Air bus','30','$last_inserted_mysql_id)";
mysql_query($add_to_booking_query);

I hope thats clear ?

Cheers!

nav33n 472 Purple hazed!

15 Years Ago

Where are you inserting the value to client table ?

nav33n 472 Purple hazed!

15 Years Ago

Well, You can do it in quite easily. Before redirecting them to the page where they book a room, use $last_inserted_mysql_id = mysql_insert_id(); to get their clientID.
After getting their clientID, you can direct them to "Book a room" page along with their clientID or save the clientID to a session variable and use it in your booking table.

nav33n 472 Purple hazed!

15 Years Ago

<?php
// open database connection code and then my code as follows

//$id = mysql_insert_id();  << This line wouldn't work ie., $id will be null because there isn't any insert statement before this function call. 

//$sql="INSERT INTO clients (clientID, firstname, surname, address1, address2, town,  postcode,  telephone, email, cardno, expirydate) VALUES ($id,'$_POST[firstname]','$_POST[surname]','$_POST[address1]','$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]','$_POST[expirydate]')";
//since clientID is an autoincrement field, you don't need to mention it. Use this query instead.
$sql="INSERT INTO clients (firstname, surname, address1, address2, town,  postcode,  telephone, email, cardno, expirydate) VALUES ('$_POST[firstname]','$_POST[surname]','$_POST[address1]','$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]','$_POST[expirydate]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
$last_insert_client_id = mysql_insert_id();
//get previous insert statement's clientID

//$sql="INSERT INTO bookings (bookingID, clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ($id, LAST_INSERT_ID(),'NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";
$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  $last_insert_booking_id = mysql_insert_id();
  //last insert statement's bookingID
  //$result = mysql_query("SELECT * FROM clients WHERE clientID=LAST_INSERT_ID()");
	//this also wouldn't work because LAST_INSERT_ID will give you the last inserted id of booking table.. Instead use this
	$result = mysql_query("SELECT * FROM clients WHERE clientID='$last_insert_client_id'");
	
  //$result = mysql_query("SELECT * FROM bookings WHERE bookingID=LAST_INSERT_ID()");
  //This will work but it will override the value stored in the variable $result. You should use a different variable. 
  $result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>address1</th>
<th>address2</th>
<th>town</th>
<th>postcode</th>
<th>telephone</th>
<th>email</th>
<th>Arrival</th>
<th>Departure</th>
<th>Adults</th>
<th>children</th>
<th>Room Type</th>
<th>Requirements</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['clientID'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['surname'] . "</td>";
 echo "<td>" .$row['address1'] . "</td>";
echo "<td>" .$row['address2'] . "</td>";
echo "<td>" .$row['town'] . "</td>";
echo "<td>" .$row['postcode'] . "</td>";
echo "<td>" .$row['telephone'] . "</td>";
echo "<td>" .$row['email'] . "</td>";
  } 
  //considering these are the values returned by 1st query (ie., table client)
  while($row = mysql_fetch_array($result1)) {
echo "<td>" .$row['startdate'] . "</td>";
echo "<td>" .$row['enddate'] . "</td>";
echo "<td>" .$row['adults'] . "</td>";
echo "<td>" .$row['children'] . "</td>";
echo "<td>" .$row['roomtype'] . "</td>";
echo "<td>" .$row['requirements'] . "</td>";

  echo "</tr>";
  }
echo "</table>";


  
echo "Booking is complete";

print "Thank you for booking with us and your booking ID number is  ".$last_insert_booking_id;
mysql_close($con);
?>

Try this. :) I hope its clear enough..
Cheers!

P.S. Whenever you are getting the values from the user, you should sanitize the input by using mysql_real_escape_string.
ie., instead of directly using $_POST , use mysql_real_escape_string($_POST['name']) This will prevent sql injections.

HB25 commented: very very helpful +2

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

HB25 0 Junior Poster in Training · Answer 1 · 2009-02-27T01:01:07+00:00

Hi nav33n

Thank you for your quick reply to my post, but I am not sure which one to use.

I will explain what I am using. I have created a PHP called it add_booking_record.php which have a form to get the user input and once user submit the form my add_booking.php page will be used to open the connection with the Mysql database and insert the data into table booking using the insert query but I don’t know what to include in the query for the clientID filed to retrieve the clientID autoincres number from the client table.

Hope you understand this.

Kind Regards
HB25

HB25 0 Junior Poster in Training · Answer 2 · 2009-02-27T01:24:26+00:00

This will be done using different page. The process is like this:

1. user visit the website
2. they will register their detail which will be inserted into the client table (clientID is the primary key and its autoincresss number)
3. then they will be directed to the book a room page where they will b providing details then this data will be sent to the booking table (bookingID is the primary key) but also have the clientID as a fornkey and I need to retrieve the clientID number from the client table and put it into the booking table clientID field.

Hope this is a bit clearer. Thanks ever so much for your help.

Regards
HB 25

HB25 0 Junior Poster in Training · Answer 3 · 2009-02-27T02:59:47+00:00

Hi
Thanks for your reply but I am not very familiar with PHP and MySQL therefore forgive me if I am a bit slow to understand everything very quickly.
My code start here :
The coonection to my databse

$sql="INSERT INTO bookings (bookingID, startdate, enddate, adults, children, roomtype,  requirements)
VALUES
('NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Booking is complete";
mysql_close($con)

between the bookingID and startdae there is another field called clientID in my bookings table, i don’t know how to get the ClientID back from the client table and place it there.

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 4 · 2009-02-27T09:32:34+00:00

I can't explain you any better :( Post all the relevant code here. And please use code tags.

HB25 0 Junior Poster in Training · Answer 5 · 2009-02-28T00:20:22+00:00

Dear nav33n
Thank you for your comments yesterday, I did manage to insert data into two tables at the same time by putting both registration and booking details into one form. But my new problem is I am trying to give booking confirmation to the user once they made their booking.
I have used the fallowing code to insert the data and get the data back but it is only running the second select query to get the data back from bookings table and ignores the first query to get the data back from clients table any advice?
Thanks for your help, my code is as follows:

// open database connection code and then my code as follows

$id = mysql_insert_id();

$sql="INSERT INTO clients (clientID, firstname, surname, address1, address2, town,  postcode,  telephone, email, cardno, expirydate)
VALUES
($id,'$_POST[firstname]','$_POST[surname]','$_POST[address1]','$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]','$_POST[expirydate]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

$sql="INSERT INTO bookings (bookingID, clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements)
VALUES
($id, LAST_INSERT_ID(),'NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  
  $result = mysql_query("SELECT * FROM clients WHERE clientID=LAST_INSERT_ID()");

  $result = mysql_query("SELECT * FROM bookings WHERE bookingID=LAST_INSERT_ID()");

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>address1</th>
<th>address2</th>
<th>town</th>
<th>postcode</th>
<th>telephone</th>
<th>email</th>
<th>Arrival</th>
<th>Departure</th>
<th>Adults</th>
<th>children</th>
<th>Room Type</th>
<th>Requirements</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['clientID'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['surname'] . "</td>";
 echo "<td>" .$row['address1'] . "</td>";
echo "<td>" .$row['address2'] . "</td>";
echo "<td>" .$row['town'] . "</td>";
echo "<td>" .$row['postcode'] . "</td>";
echo "<td>" .$row['telephone'] . "</td>";
echo "<td>" .$row['email'] . "</td>";
echo "<td>" .$row['startdate'] . "</td>";
echo "<td>" .$row['enddate'] . "</td>";
echo "<td>" .$row['adults'] . "</td>";
echo "<td>" .$row['children'] . "</td>";
echo "<td>" .$row['roomtype'] . "</td>";
echo "<td>" .$row['requirements'] . "</td>";

  echo "</tr>";
  }
echo "</table>";


  
echo "Booking is complete";

printf("Thank you for booking with us and your booking ID number is  %d\n", mysql_insert_id());

mysql_close($con)

HB25 0 Junior Poster in Training · Answer 6 · 2009-02-28T01:45:23+00:00

Dear nav33n
Thank you very much your code is much much better than the one I have used, did work and thanks for solving this problem for me but I do have one more problem and hopefully I want trouble you again, my problem is, I have a page to allow user to delete their booking if they need to I have written the following code to delete the booking once they have provided the booking ID number it works fine but I don’t know what should be the code to go at the end to remind them about the booking ID which they have just deleted.

// open database connection code and then my code as follows:
mysql_query("DELETE FROM bookings WHERE bookingID=$_POST[bookingID]");
echo "your booking has been cancelled";
 mysql_close($con)

BTW the previous code will be displayed like the attached image, any suggestion how I could make it better.
Thanks for your help

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 7 · 2009-02-28T02:40:10+00:00

First, query the table and get all the details of that particular bookingID and then display it. You can also give an option for the user saying something like, "Are you sure want to delete ?" :) Have a hidden variable in your form to hold the bookingID. If he clicks yes, then, delete the record. If he clicks no, then redirect him to some other page. Many users might give a second thought before deleting :)
And about making your report look better, well, add some style to it.
Its 2 in the morning and I ll probably hit the sack!

HB25 0 Junior Poster in Training · Answer 8 · 2009-02-28T03:04:21+00:00

Thanks for your help, I will start work on it to make it better but if i have a problem i will get back to you.
Good night
HB 25

HB25 0 Junior Poster in Training · Answer 9 · 2009-02-28T05:10:48+00:00

Hi nav33n

I have been trying to sort the table to display the information in columns and rows but I am not getting there any help will be highly appreciated.

I would like to displayed the information in this way i.e:

-----------------------------------------
First name | Smith
------------------------------------
last Nmae | Alan
------------------------------------
Arrival date| 28/02/2009
------------------------------------
and so on ......

Regards
HB 25

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 10 · 2009-02-28T12:25:05+00:00

How does your code look like ?

P.S. I will be out of town for 3 days, so, If not me, someone else will help you with your problem :)

Babaty 0 Newbie Poster · Answer 11 · 2010-02-26T21:50:52+00:00

hey sup dude where is the php and html code for the following codes ?
1.
<?php
2.
// open database connection code and then my code as follows
3.

4.
//$id = mysql_insert_id(); << This line wouldn't work ie., $id will be null because there isn't any insert statement before this function call.
5.

6.
//$sql="INSERT INTO clients (clientID, firstname, surname, address1, address2, town, postcode, telephone, email, cardno, expirydate) VALUES ($id,'$_POST[firstname]','$_POST[surname]','$_POST[address1]','$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]','$_POST[expirydate]')";
7.
//since clientID is an autoincrement field, you don't need to mention it. Use this query instead.
8.
$sql="INSERT INTO clients (firstname, surname, address1, address2, town, postcode, telephone, email, cardno, expirydate) VALUES ('$_POST[firstname]','$_POST[surname]','$_POST[address1]','$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]','$_POST[expirydate]')";
9.

10.
if (!mysql_query($sql,$con))
11.
{
12.
die('Error: ' . mysql_error());
13.
}
14.
$last_insert_client_id = mysql_insert_id();
15.
//get previous insert statement's clientID
16.

17.
//$sql="INSERT INTO bookings (bookingID, clientID, roomID, startdate, enddate, adults, children, roomtype, requirements) VALUES ($id, LAST_INSERT_ID(),'NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";
18.
$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype, requirements) VALUES ('$last_insert_client_id','NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";
19.

20.
if (!mysql_query($sql,$con))
21.
{
22.
die('Error: ' . mysql_error());
23.
}
24.
$last_insert_booking_id = mysql_insert_id();
25.
//last insert statement's bookingID
26.
//$result = mysql_query("SELECT * FROM clients WHERE clientID=LAST_INSERT_ID()");
27.
//this also wouldn't work because LAST_INSERT_ID will give you the last inserted id of booking table.. Instead use this
28.
$result = mysql_query("SELECT * FROM clients WHERE clientID='$last_insert_client_id'");
29.

30.
//$result = mysql_query("SELECT * FROM bookings WHERE bookingID=LAST_INSERT_ID()");
31.
//This will work but it will override the value stored in the variable $result. You should use a different variable.
32.
$result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");
33.

34.
echo "<table border='1'>
35.
<tr>
36.
<th>ID</th>
37.
<th>Firstname</th>
38.
<th>Lastname</th>
39.
<th>address1</th>
40.
<th>address2</th>
41.
<th>town</th>
42.
<th>postcode</th>
43.
<th>telephone</th>
44.
<th>email</th>
45.
<th>Arrival</th>
46.
<th>Departure</th>
47.
<th>Adults</th>
48.
<th>children</th>
49.
<th>Room Type</th>
50.
<th>Requirements</th>
51.

52.
</tr>";
53.

54.
while($row = mysql_fetch_array($result))
55.
{
56.
echo "<tr>";
57.
echo "<td>" . $row . "</td>";
58.
echo "<td>" . $row . "</td>";
59.
echo "<td>" . $row . "</td>";
60.
echo "<td>" .$row . "</td>";
61.
echo "<td>" .$row . "</td>";
62.
echo "<td>" .$row . "</td>";
63.
echo "<td>" .$row . "</td>";
64.
echo "<td>" .$row . "</td>";
65.
echo "<td>" .$row . "</td>";
66.
}
67.
//considering these are the values returned by 1st query (ie., table client)
68.
while($row = mysql_fetch_array($result1)) {
69.
echo "<td>" .$row . "</td>";
70.
echo "<td>" .$row . "</td>";
71.
echo "<td>" .$row . "</td>";
72.
echo "<td>" .$row . "</td>";
73.
echo "<td>" .$row . "</td>";
74.
echo "<td>" .$row . "</td>";
75.

76.
echo "</tr>";
77.
}
78.
echo "</table>";
79.

80.

81.

82.
echo "Booking is complete";
83.

84.
print "Thank you for booking with us and your booking ID number is ".$last_insert_booking_id;
85.
mysql_close($con);
86.
?>

Insert data into two tables using php and mysql

Recommended Answers Collapse Answers

All 15 Replies

Recommended Answers