Combined Insert Statement for MySql from PHP

[kannasekar]

Hi I am newbie to PHP. I just wanted to create a registration page for students. In that page I've populated checkboxes for students who wants to enroll more than one classes. Everything got done. But I'm stuck with the final phase of this idea. I just wanted to save data in 2 tables with single submit. Tables names ('data' table for student details, 'stud_class' table for student_id with class_id)One is for only students details and the another is for saving selected checkboxes(classes) in the same registration form. Sorry! I don't know what exactly the technical term is as of my knowledge it's detail table. Because student may select n number of checkboxes. I just want to save one row in 'data' table for student other details and fetch the id of that inserted line from 'data' table and save it to stud_class table with selected checkboxes id's. I mean stud_id should multiple with selected unique checkboxes id's. My output should look like this (stud_id, class_id) (For Ex : stud_001 1, stud_002 5, stud_002 2, stud_002 3, stud_003, 6) and save it to 'stud_class' table.

$checkbox1 = $_POST['classes'];                 
for( $i=0 ; $i<count($checkbox1); $i++){
//$query = "insert into stud_class (stud_id) select max(id) from data";
//$query = "insert into stud_class (class_id) values ('".$checkbox1[$i]."')";
$query = "insert into stud_class (class_id,stud_id) values ('".$checkbox1[$i]."',select max(id) from data)";
mysql_query($query) or die (mysql_error());
}
echo "Record Inserted"; 

I tried to save individual fields in to the stud_class table commented lines in the above code. It's works fine. But I want to save both in single query as I tried in

$query = "insert into stud_class (class_id,stud_id) values ('".$checkbox1[$i]."',select max(id) from data)"; 

this line. Anyone help me please to achieve this. And please let me know what problem in this above line. Thanks in advance.

[lps]

First of all, mysql_* is depreciated. Please consider to use mysqli_* or PDO as alternative.

Please do give us details like what is the exact error caught, the information of the database structure of your stud_class table, especially its indexes.

Through digging the provided code, what I suspected is your select max(id) from data is actually returning same id for each looping which may cause the data prompt to be duplicated in the loop and causing the failure.

[kannasekar]

Hi Ips. Thanks for your kind reply. I have doubt in next step. I hope u caught my problem. The error is

SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';select max(id) from data)' at line 

Students details table is 'data' (id INT AUTO_INCREMENT,firstname varchar(250) with other fields )
Class details table is 'class_details' (id ITN AUTO_INCREMENT,class_name varchar(250))
Combined table is 'stud_class' and it's my Table structure (Id INT AUTO_INCREMENT, stud_id INT,class_id INT)

I am trying to insert the registration page student details except classes(checkboxes) they choose in to 'data' table and the selected class details in to 'stud_class' table. For that 1st I am inserting row in to 'data' and try to pick and insert the latest inserted id along checkboxes id values from registration page.

$checkbox1 = $_POST['classes'];                 
for( $i=0 ; $i<count($checkbox1); $i++){
//$query = "insert into stud_class (stud_id) select max(id) from data";
//$query = "insert into stud_class (class_id) values ('".$checkbox1[$i]."')";
$query = "insert into stud_class (class_id,stud_id) values ('".$checkbox1[$i]."',select max(id) from data)";
mysql_query($query) or die (mysql_error());
}
echo "Record Inserted"; 

If I try to run the above commented insert statement for stud_id or class_id lines individually it's working. I mean in 'stud_class' table it's inserting the appropriate individual values properly. But if I try to insert both in a single query it's getting error.But your suspicion is what I need. Because I want to insert same stud_id with selected class_id whatever they choose. For Example stud_001 is selecting 2 classes 2nd,5th in registration form i need to save in the format of 2 rows like stud_001 2,stud_002 5. Likewise if students are selecting the classes i need to save their stud_id repeatedly with their selected class_id. I hope you understand my problem clearly now. How can I achieve this? I'm stuck with this. I need to run a insert query with values for class_id and select statement for pick and insert id from the 'data' table to 'stud_class' table stud_id. Kindly help me.

[kannasekar]

Hi Guys I solved this problem by myself here is the code.

 <?
    $last_id = mysql_insert_id($dbConnection);
            echo $last_id;                  
            $checkbox1 = $_POST['country'];                 
                for( $i=0 ; $i<count($checkbox1); $i++){         
                $query = "insert into stud_class (class_id,stud_id) values ('".$checkbox1[$i]."',$last_id)";
                mysql_query($query) or die (mysql_error());
                }
            mysql_close($dbConnection);
    ?>

Try use and enjoy!!! Anyway thanks for your support