Page 1 of 1

insert into 2 table

Posted: Wed Dec 06, 2006 4:03 am
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)

Posted: Wed Dec 06, 2006 4:20 am
by volka
If you want to insert records into two tables use two INSERT INTO statements, one for each table.

Posted: Wed Dec 06, 2006 4:35 am
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>

Posted: Wed Dec 06, 2006 4:40 am
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.

Posted: Wed Dec 06, 2006 4:47 am
by hrubos
I would to ask when I use PhpMyAdmin and I choose autoincrement for id, it is the same as mysql_insert_id ???

Posted: Wed Dec 06, 2006 4:54 am
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:

Posted: Wed Dec 06, 2006 5:02 am
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.

Posted: Wed Dec 06, 2006 6:16 pm
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

Posted: Thu Dec 07, 2006 12:03 am
by dibyendrah
Wow! So is your problem solved ?

Posted: Thu Dec 07, 2006 2:35 am
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