Multiple partial insert into two related tables

[Vivekneo]

Hi this my second request at bytes,

i am having a db with two tables,
table1 - has the details of student, stud_id as primary key set to auto increment, stud_name and other details

table2 - has the details of courses applied by each individual students, stud_id as foreign key and course_id as primary key

any help, i will be very thankful,

i need to create php mysql quires

a) partial insert data into two tables, for registering student in table1
b) after approval updated table2 with course selected by that particular student.

[Atli]

So your "table2" is a link table? That is: the info on the courses is in another table, but "table2" links the students to by recording the student ID and the course ID for the course he applies to?

[Vivekneo]

Hi Atli,

Yes students_details table is the main table, and course_details is the child table linking stud_id from the main table.

mean while i read about a function, but don't how to use it, any code example wold greatly help me.

mysql.insert_id();
 

[Atli]

The function you mention is meant to return the latest AUTO_INCREMENT value generated by MySQL.

Inside a MySQL query you can use LAST_INSERT_ID() to get this.

SELECT LAST_INSERT_ID()

PHP implements the C version of this function as mysql_insert_id.

<?php
if(mysql_query("INSERT INTO tbl(col) VALUES('something')") {
    $rowID = mysql_insert_id();
    echo "ID of the row that was just created is: $rowID";
}
?>

As to the main question...
I'm not entirely sure what you need from us. What are having problems with? Could you show us the code that is causing the problem and explain what it is supposed to be doing?

[Vivekneo]

Hi Atli,

Sorry for my late reply, i was trying the following code,
i have a radio button group, for status, if status==0 student is selected, if status == 1, student is rejected,

<? 
include('config.php'); 
if (isset($_POST['submitted'])) { 
foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } 
$sql = "INSERT INTO `cjet`.`student_details` (`stud_name`,`stud_address`,`stud_father_name`,
                                                `stud_father_occup`,`stud_mother_name`,`stud_mother_occup`,
                                                `no_stud_bros`,`no_stud_sis`,`stud_family_income`,
                                                `sslc_board`,`sslc_my_passing`,`sslc_percent`,
                                                `puc1_board`,`puc1_my_passing`,`puc1_percent`,
                                                `puc2_board`,`puc2_my_passing`,`puc2_percent`,
                                                `grad1_board`,`grad1_my_passing`,`grad1_percent`,
                                                `grad2_board`,`grad2_my_passing`,`grad2_percent`,
                                                `grad3_board`,`grad3_my_passing`,`grad3_percent`,
                                                `any_other_board`,`any_other_my_passing`,`any_other_percent`,
                                                `course_studying`,`insti_studying`,`status`) 
                                                VALUES('{$_POST['stud_name']}','{$_POST['stud_address']}',
                                                '{$_POST['stud_father_name']}','{$_POST['stud_father_occup']}',
                                                '{$_POST['stud_mother_name']}','{$_POST['stud_mother_occup']}',
                                                '{$_POST['no_stud_bros']}','{$_POST['no_stud_sis']}',
                                                '{$_POST['stud_family_income']}',
                                                '{$_POST['sslc_board']}','{$_POST['sslc_my_passing']}','{$_POST['sslc_percent']}',
                                                '{$_POST['puc1_board']}','{$_POST['puc1_my_passing']}','{$_POST['puc1_percent']}',
                                                '{$_POST['puc2_board']}','{$_POST['puc2_my_passing']}','{$_POST['puc2_percent']}',
                                                '{$_POST['grad1_board']}','{$_POST['grad1_my_passing']}','{$_POST['grad1_percent']}',
                                                '{$_POST['grad2_board']}','{$_POST['grad2_my_passing']}','{$_POST['grad2_percent']}',
                                                '{$_POST['grad3_board']}','{$_POST['grad3_my_passing']}','{$_POST['grad3_percent']}',
                                                '{$_POST['any_other_board']}','{$_POST['any_other_my_passing']}','{$_POST['any_other_percent']}',
                                                '{$_POST['course_studying']}','{$_POST['insti_studying']}','{$_POST['status']}') "; 
 
mysql_query($sql) or die(mysql_error());
 
$statuschk = $_POST['status'];
echo "$statuschk<br />";
if ($statuschk == "0")
{
    $last_inserted_mysql_id = mysql_insert_id();
 
    $sql = "INSERT INTO `cjet`.`student_don_reg_history` (`dstud_id`,`stud_full_name`) VALUES('$last_inserted_mysql_id','{$_POST['stud_name']}') ";
 
    mysql_query($sql) or die(mysql_error());
}
 
echo "Added row.<br />"; 
printf("Last inserted record has id %d\n", mysql_insert_id());
//echo "<a href='list.php'>Back To Listing</a>"; 
} 
?>