Dealing with Inter-related Insert Statements

[kannasekar]

Hi all. Here is my problem. I'm a newbie to PHP programming. For my project I just need to run 3 Insert statements in a single button click. The concept is I have a registration form. It has parent details and children details. If parents want to register more than one kid they need to click add more the entire form will reappear and they just enter the details for the number of children they want and save. During save I need to save the parent details in a separate table first. Then I need to pick the last inserted id from parent table and save the id in to next table for 'n' number of children. Again I need to pick the individual student id for the same exact parent and save it in to 'stud_class' table as stud_id for multiple class selection from checkbox.

<?php
$conn = mysql_connect("localhost","rock","bach");
        mysql_select_db("rockandbach",$conn);

    if(!empty($_POST["save"])) {
$name=mysql_real_escape_string($_POST['name']);
        $update=mysql_query("INSERT INTO data1 (fname,status) VALUES ('$name','1')");
        $last_id = mysql_insert_id($conn);
        echo $last_id;  

        $itemCount = count($_POST["fname"]);        
        $itemValues=0;              
        $query = "INSERT INTO data_test (fname,lname,gender,status,dataid) VALUES ";
        $queryValue = "";
        for($i=0;$i<$itemCount;$i++) {
            if(!empty($_POST["fname"][$i]) || !empty($_POST["lname"][$i]) || !empty($_POST["gender"][$i])) {
                $itemValues++;
                if($queryValue!="") {
                    $queryValue .= ",";
                }
                $queryValue .= "('" . $_POST["fname"][$i] . "', '" . $_POST["lname"][$i] . "','" . $_POST["gender"][$i] . "','1',$last_id)";
                $last_id1 = mysql_insert_id($conn);
        echo $last_id1;
        $checkbox1 = $_POST['country'];                 
            for( $i=0 ; $i<count($checkbox1); $i++){         
            $query = "insert into stud_class (stud_id,class_id) values ('$last_id1','".$checkbox1[$i]."')";
            mysql_query($query) or die (mysql_error());
            }
            }       
        }
        $sql = $query.$queryValue;
        if($itemValues!=0) {
            $result = mysql_query($sql);
            if(!empty($result)) $message = "Added Successfully.";
        }
    }
?>

[tobyITguy]

So what probelm is your code having?

[kannasekar]

In this my output what I'm getting is 1st query inserting well and the id of the inserted row also fetched properly but it getting inserted in to 3rd table (I mean stud_class table stud_id) instead 2nd table parent_id. All I need to do is fetch the inserted id from 1st insert query and place it in to 2nd table as parent_id with multiple times because a parent may add more than 1 student. Again I need to fetch each student id from 2nd table and place it in to 3rd table stud_id for multiple times because a student may enroll in to multiple classes. I populated classes from database as checkbox (Consider this scenario this is what I needed to achieve a parent is trying to register 2 children my expected 1st inserted query result should be (parent_id 1,parent_name xxxx) my expected 2nd inserting query result should be ((stud_id stud_001,parent_id 1) (stud_id stud_002,parent_id 1)) my expected 3rd inserting query result should be ((stud_id stud_001,class_id 1)(stud_id stud_002,class_id 1)(stud_id stud_002,class_id 3)) in this example I explained student 1 enrolled in to 1st class and student 2 enrolled in to 1st and 3rd class. This is what I wanted. Please help me anyone to achieve this. Thanks in advance.

[diafol]

This is pretty straightforward and I've modelled your deprecated code to PDO, but I need to know how the checkboxes are entered in your form.
The problem being if they're like name="checkbox[3][]" pointing to class id=3, it won't work since the "array" will SHRINK to accommodate ONLY checked fields. SO for example:

STUDENT 1

...other inputs...
<input type="checkbox" name="checkbox[1][]" checked /> Class #1
<input type="checkbox" name="checkbox[2][]"  /> Class #2
<input type="checkbox" name="checkbox[3][]" checked /> Class #3
<input type="checkbox" name="checkbox[4][]"  /> Class #4

STUDENT 2

...other inputs...
<input type="checkbox" name="checkbox[1][]"  /> Class #1
<input type="checkbox" name="checkbox[2][]"  /> Class #2
<input type="checkbox" name="checkbox[3][]" checked /> Class #3
<input type="checkbox" name="checkbox[4][]"  /> Class #4

STUDENT 3

...other inputs...
<input type="checkbox" name="checkbox[1][]" checked /> Class #1
<input type="checkbox" name="checkbox[2][]" checked /> Class #2
<input type="checkbox" name="checkbox[3][]"  /> Class #3
<input type="checkbox" name="checkbox[4][]"  /> Class #4

You may think that thee checkbox[1] (class 1) would return 1 0 1 (student 1, student 2, student 3) but in fact it returns 1 1 (which appears to be student 1, student 2). This is because unchecked checboxes aren't sent at all and the array would have: checkbox[1][0] and checkbox[1][1] only.

Similarly, checkbox[2], would only show 1 (not 0 0 1) and would have checkbox[2][0] as its only member, suggesting student 1 instead of student 3.

I hope that's clear. So, could you show your Form markup?

[kannasekar]

Hi diafol Thank you for your concerned reply. I'm not facing problem with checkboxes. it's inserting properly Instead I'm facing problem with fetching latest id of the second insert statement. For your information I let you know my exact output what I'm getting now

1. For 1st insert statement(parent_id 1,parent_name xxxx)
   $last_id = mysql_insert_id($conn); by using this statement I fetch last parent_id inserted and give this in to second insert statement as input. For example if my output for mysql_insert_id($conn); is 10 I need to place in to 2nd insert statement as parent_id for number of students he or she is going to insert
2. For 2nd insert statement(stud_id,stud_name,parent_id) my values should be
   (stud_id stud_001, stud_name yyyy,parent_id 10),(stud_id stud_002, stud_name zzzz,parent_id 10)
3. Again in the third part I need to fetch the id from second insert statement inserted id and place it in to third insert query(stud_id stud_001, class_id 1),(stud_id stud_001, class_id 3),(stud_id stud_002, class_id 5) The above explained should be my input.

But with my following php statement

if(!empty($_POST["save"])) {
        $name=mysql_real_escape_string($_POST['name']);
        $update=mysql_query("INSERT INTO data1 (fname,status) VALUES ('$name','1')");
        $last_id = mysql_insert_id($conn);
        echo $last_id;  

        $itemCount = count($_POST["fname"]);
        echo $itemCount;
        $itemValues=0;              
        $query = "INSERT INTO data_test (fname,lname,gender,dataid,status) VALUES ";
        $queryValue = "";
        for($i=0;$i<$itemCount;$i++) {
            if(!empty($_POST["fname"][$i]) || !empty($_POST["lname"][$i]) || !empty($_POST["gender"][$i])) {
                $itemValues++;
                if($queryValue!="") {
                    $queryValue .= ",";
                }
                $queryValue .= "('" . $_POST["fname"][$i] . "', '" . $_POST["lname"][$i] . "','" . $_POST["gender"][$i] . "','$last_id','1')";      
        }}
                $checkbox1 = $_POST['class'];                 
                $id = mysql_query("select max(id) from data_test");
                for( $i=0 ; $i<count($checkbox1); $i++){         
            $query = "insert into stud_class_test (stud_id,class_id) values ('$id','".$checkbox1[$i]."')";
            mysql_query($query) or die (mysql_error());
            }       


        header('Location:success.html'); 
        $sql = $query.$queryValue;
        if($itemValues!=0) {
            $result = mysql_query($sql);
            if(!empty($result)) $message = "Added Successfully.";
        }
    }

My output is for 1st statement is correct. It's picking the correct id but it is not getting place in second insert statement instead it's getting inserted in to third insert statement. The second insert statement is even not getting execute.

[diafol]

I understood your first post. Was optimizing and fixing your code until I got to checkboxes as I couldn't understand how you were attaching a particular checkbox to a student. So seeing your markup would have explained it. Your new post doesn't add any new useful info.

[kannasekar]

Hi friends I explained my problem in before 2 posts. With the follwing code first 2 queries are working well. What I just want to do now is I should save the checked checkboxes values against the last_id1 from mysql_insert_id. From the following code my output is: - if I select a single class for 2 student is(last_id => 1 class_id => 1, last_id => 1 class_id => 3, last_id => 2 class_id => 1, last_id => 2 class_id => 3) but I just want (last_id => 1 class_id => 1,last_id => 2 class_id => 3). I need to match every student with their corresponding checking classes.

 <?php
    if(!empty($_POST["save"])) {
            $name=mysql_real_escape_string($_POST['name']);
            $update=mysql_query("INSERT INTO data1 (fname,status) VALUES ('".$name."',1)");         
            $last_id = mysql_insert_id();   
            $itemCount = count($_POST["fname"]);
            //echo $itemCount;                  
            for($i=0;$i<$itemCount;$i++) {
                //$query = mysql_query("INSERT INTO data_test (fname,lname,gender,dataid,status) VALUES ('" . $_POST["fname"][$i] . "', '" . $_POST["lname"][$i] . "','" . $_POST["gender"][$i] . "','$last_id','1')");         
                /*$query1 = "INSERT INTO data_test (fname,lname,status,dob,gender,dataid) VALUES ('1', '2','3','0000-00-00','4','1')";          
                mysql_query($query1) or die (mysql_error());
                $last_id1 = mysql_insert_id();
                echo $last_id1; */
                $query1 = ("insert into data_test (fname,lname,dob,gender,dataid,status) values ('" . $_POST["fname"][$i] . "','" . $_POST["lname"][$i] . "','0000-00-00','" . $_POST["gender"][$i] . "','" . $last_id . "','1')");         
                mysql_query($query1) or die (mysql_error());
                $last_id1 = mysql_insert_id();          
                if(isset($_POST["course"])) 
                    {
                        foreach($_POST["course"] as $key=>$value)
                        {
                            echo  $last_id1 ." id = > ".$value   
                        }
                    }
                /*$checkbox1 = $_POST['course'];                
                for( $i=0 ; $i<count($checkbox1); $i++){         
                    $query2 = "insert into stud_class_test (stud_id,class_id) values ('" . $last_id1 . "','".$checkbox1[$i]."')";
                mysql_query($query2) or die (mysql_error());}*/

            }
            }   
    ?>

Please anyone help. Thanks in advance.......

[diafol]

Show your form html (the markup)

[kannasekar]

This is my index page.

 <?php
            $conn = mysql_connect("localhost","root","");
            mysql_select_db("student",$conn);       
        if(!empty($_POST["save"])) {
            $name=mysql_real_escape_string($_POST['name']);
            $update=mysql_query("INSERT INTO data1 (fname,status) VALUES ('".$name."',1)");         
            $last_id = mysql_insert_id();   
            $itemCount = count($_POST["fname"]);
            //echo $itemCount;                  
            for($i=0;$i<$itemCount;$i++) {
                //$query = mysql_query("INSERT INTO data_test (fname,lname,gender,dataid,status) VALUES ('" . $_POST["fname"][$i] . "', '" . $_POST["lname"][$i] . "','" . $_POST["gender"][$i] . "','$last_id','1')");         
                /*$query1 = "INSERT INTO data_test (fname,lname,status,dob,gender,dataid) VALUES ('1', '2','3','0000-00-00','4','1')";          
                mysql_query($query1) or die (mysql_error());
                $last_id1 = mysql_insert_id();
                echo $last_id1; */
                $query1 = ("insert into data_test (fname,lname,dob,gender,dataid,status) values ('" . $_POST["fname"][$i] . "','" . $_POST["lname"][$i] . "','0000-00-00','" . $_POST["gender"][$i] . "','" . $last_id . "','1')");         
                mysql_query($query1) or die (mysql_error());
                $last_id1 = mysql_insert_id();          
                if(isset($_POST["course"])) //checks if any interest is checked
                    {
                        foreach($_POST["course"] as $key=>$value) //Iterate the interest array and get the keys and values
                        {
                            echo  $last_id1 ." id = > ".$value   //print the keys and values
                        }
                    }
                /*$checkbox1 = $_POST['course'];                
                for( $i=0 ; $i<count($checkbox1); $i++){         
                    $query2 = "insert into stud_class_test (stud_id,class_id) values ('" . $last_id1 . "','".$checkbox1[$i]."')";
                mysql_query($query2) or die (mysql_error());}*/

            }
            }   
            //header('Location:index.php'); 


    ?>
    <HTML>
    <HEAD>
    <TITLE></TITLE>
    <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description">
        <meta name="author">
        <title>Registration</title>
        <link rel="favicon" href="assets/images/favicon.png">
        <link rel="stylesheet" media="screen" href="http://fonts.googleapis.com/css?family=Open+Sans:300,400,700">
        <link rel="stylesheet" href="assets/css/bootstrap.min.css">
        <link rel="stylesheet" href="assets/css/font-awesome.min.css">
        <!-- Custom styles for our template -->
        <link rel="stylesheet" href="assets/css/bootstrap-theme.css" media="screen">
        <link rel="stylesheet" href="assets/css/style.css">
        <link rel="stylesheet" href="assets/css/custom.css">
        <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
      <script src="//code.jquery.com/jquery-1.10.2.js"></script>
      <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
    <LINK href="style.css" rel="stylesheet" type="text/css" />
    <script>
      $(function() {
        $( "#datepicker" ).datepicker({
          changeMonth: true,
          changeYear: true
        });
      });
      </script>
    <SCRIPT>
    function addMore() {
        $("<div>").load("input.php", function() {
                $("#product").append($(this).html());
        }); 
    }
    function deleteRow() {
        $('div.product-item').each(function(index, item){
            jQuery(':checkbox', this).each(function () {
                if ($(this).is(':checked')) {
                    $(item).remove();
                }
            });
        });
    }
    </SCRIPT>

    </HEAD>
    <BODY>
    <FORM name="frmProduct" method="post" action="">
    <DIV id="outer">
    <DIV id="header">
    </DIV>
    <div class="form-group">
        <label class="control-label">Last Name:</label>
        <input maxlength="200" type="text" name = "name"  class="form-control" placeholder="Enter City"  />
    </div>
    <DIV id="product">
    <?php require_once("input.php") ?>
    </DIV>
    <DIV class="btn-action float-clear">
    <input type="button" name="add_item" value="Add More" onClick="addMore();" />
    <input type="button" name="del_item" value="Delete" onClick="deleteRow();" />
    <span class="success"><?php if(isset($message)) { echo $message; }?></span>
    </DIV>
    <DIV class="footer">
    <input type="submit" name="save" value="Save" />
    </DIV>
    </DIV>
    </form>
    <script src="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/js/bootstrap.min.js"></script>
    </BODY>
    </HTML>

The following is my input page input.php(for inserting Set of div statements)

<div class="product-item float-clear" style="clear:both;">
    <div class="float-left"><input type="checkbox" name="item_index[]" /></DIV>
    <div class="form-group">
                            <label class="control-label">First Name:</label>
                            <input maxlength="200" type="text" name = "fname[]"  class="form-control" placeholder="Enter Home Address" />
                        </div>
                        <div class="form-group">
                            <label class="control-label">Last Name:</label>
                            <input maxlength="200" type="text" name = "lname[]"  class="form-control" placeholder="Enter City"  />
                        </div>
                        <div class="form-group">
                            <label class="control-label">Date of Birth:</label>                       
                            <input maxlength="200" name="dob[]" type="text" class="form-control" id="datepicker" placeholder="Enter Date of Birth">
                        </div>
    <div class="form-group">
                            <label class="control-label">Gender:</label>                                              
                            <select class="form-control" id="gender" name="gender[]">                
                                    <option>--Select Any Option--</option>
                                    <option value="Male">Male</option>
                                    <option value="Female">Female</option>
                                    <option value="Others">Others</option>
                                </select>    
                        </div>   

                        <div class="form-group">
                            <label class="control-label">Select Your Classes:</label>
                            <?php    
                            $conn = mysql_connect("localhost","root","");
                            mysql_select_db("student",$conn);
                            $data =  mysql_query("select * from course") or die(mysql_error());
                            while ($val = mysql_fetch_array($data))
                                {  ?>
                            <input type="checkbox" name="course[]" id="course" value="<?php print $val['id']; ?>"> 
                            <?php print isset($val['course_name'])?$val['course_name']:"";  ?>  <br>
                            <?php    }   
                        ?>  
                        </div>   
                        </div>