insert into 2 table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

insert into 2 table

Post by hrubos »

_I have 2 table RESERVATION (id_reservation,id_student,day_moveInto,day_move)and table STUDENT(id_student,frist_name,last_name)

_In function php, I have used radiobutton for user to book room.

So I would to ask how to do to insert id_student in table STUDENT into table RESERVATION.(interface : user book room by cliking radiobutton -> complete information about ones self -> complete day _ move Into )

And I can't insert id_reservation into table RESERVATION and so I have to create table RESER_1(id_resrevation)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

If you want to insert records into two tables use two INSERT INTO statements, one for each table.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

volka wrote:If you want to insert records into two tables use two INSERT INTO statements, one for each table.
but my problem is that user only click into radiobutton and so only id_reservation is inserted by $id_resrevation. Would you know how to insert id_student in table STUDENT into id_student in table RESERVATION.

data.php

Code: Select all

<?php

$id_reservation = $_POST['id_reservation'];

if(!get_magic_quotes_gpc())
 {
 $id_reservation  = addslashes($id_reservation);
 }

 @ $db = mysql_connect("localhost", "root","") or die("Error");
 mysql_select_db("kolej",$db) or die("Can't choose database");
  $query = "insert into reservation " . "(id_reservation) values"
		. "('$id_reservation')";
 mysql_query($query) or die(mysql_error());
 ?>
outputRoom.php

Code: Select all

<?php
require_once ("D:\htdocs\BP\db_connectFun.php");
require_once("outputRoom.php");
db_connect();

$query="SELECT DISTINCT p.id_numberRoom,p.number_room,p.building,p.floor,p.state_room,
p.id_typeRoom, t.number_beg,t.price
FROM pokoj p,type_room t
WHERE p.id_typeRoom = t.id_typeRoom
  AND p.state_room = 'volný'";


$result=mysql_query($query)or die(mysql_error());

$num=mysql_numrows($result);
mysql_close();


$i=0;

while($i<$num){
$id_numberRoom=mysql_result($result,$i,"id_numberRoom");
$number_room=mysql_result($result,$i,"number_room");
$building=mysql_result($result,$i,"building");
$floor=mysql_result($result,$i,"floor");
$number_beg=mysql_result($result,$i,"number_beg");
$price=mysql_result($result,$i,"price");

?>
<tr>
<?php
$n=0;
echo "<td>$id_numberRoom</td>" ;
echo "<td>$number_room</td>" ;
echo "<td>$floor</td>";
echo "<td>$building</td>";
echo "<td>$number_beg</td>";
echo "<td>$price</td>";
echo "<td> <input type= 'radio' value = '$n' name= 'id_reservation'></td>";
$i++;
}
?>
<tr>
<td>
 <input type="submit" value="Submit" name="submit">
 </td>
</tr>
</tr>
</form>
 </body>
</html>
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

If I understand your question, when using MySQL (which you are) you can automatcically get the inserted id from a previous insert using mysql_insert_id and use this id in the subsequent insert.

For postgres (here only for reference for others) you normally have a sequence set containing containing the id. You need to "SELECT nextval('sequence');" to get the id and use that for both inserts.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

I would to ask when I use PhpMyAdmin and I choose autoincrement for id, it is the same as mysql_insert_id ???
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

mysql_insert_id needs to have "autoincrement for id" set to true as this is the "id" which is used. This should be the join field between the two tables.

Note: I very infrequently use mysql but that is stated on the php function description. If I am wrong I am sure someone will correct me... :wink:
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

mysql_insert_id() will return the ID generated from the previous INSERT operation. While using mysql_insert_id, pass the connection identifier as a parameter.

When you insert the student into table student, you will get a id_student inserted into that table by mysql_insert_id().

Code: Select all

$last_id_student = mysql_insert_id($connection);
You an use this id of the student for inserting into reservation table.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

wonderful that show me mysql_insert_id. and I have it I want to post for whom still unknow about this .

Code: Select all

@ $db = mysql_connect("localhost", "root","ha1985") or die("No way");
 
mysql_select_db("kolej",$db) or die("Nelze database");
 $query = "insert into student " . "(rc_student,first_name,last_name,street,city,country,number_telefone,faculty,year_school) values"
			. "('$rc_student','$first_name', '$last_name','$street','$city','$country','$number_telefone','$faculty','$year_school')";		
 

	mysql_query($query) or die(mysql_error());
	$last_idstudent = mysql_insert_id();
	$query_idstudent_reser = "insert into reservation"."(id_student) values"."('$last_idstudent')";
    mysql_query($query_idstudent_reser) or die(mysql_error());
--> it runs well
Thank much
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Wow! So is your problem solved ?
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

dibyendrah wrote:Wow! So is your problem solved ?
When I was shown mysql_insert_id(), then I have solved problem, I feel happy.

thank thank

And thank all

ps : who doen't know about it, i have posted code there. It will make you work easier
Post Reply