Inserting multiple rows to database from a while loop

Question

Good day! I am having a problem in storing values in my database. The flow of the program is the user will input in how many subject he will get. For example, he/she can put 6 subjects. So it will release 6 input types with values equal to text. My problem is I don't get 6 rows in my database as the user take 6 inputs.

HTML/PHP

<form method="POST">
    <table>
        <tr>
            <td>SUBJECT CODE/SUBJECT DESCRIPTION/SEMESTER</td>
        </tr>
        <?php $counter=1 ; 
            while($counter <= $subj){?>
            <tr>
                <td>
                    <select name="sub">
                        <?php $select=mysql_query("SELECT * FROM subject 
                                                   WHERE course_code = '$course' AND semester = '$sem'"); 
                              while($rows=m ysql_fetch_assoc($select)){ 
                                   $code=$rows['subj_code']; 
                                   $desc=$rows['subj_desc']; 
                                   $units=$rows['units']; 
                                   $yr=$rows[ 'year_level'];
                        ?>
                                 <**option value="<?php echo $codes[$code]; ?>">
                                   <?php echo $code. " - ".$desc; ?>
                                 </option>**
                             <?php } ?>
                    </select>
                </td>
                <td>

                </td>
            </tr>
            <?php $counter++; } } ?>
        <tr>
            <td>
                <input type="submit" name="add" value="add subjects">
            </td>
        </tr>
    </table>
</form>

PHP/SQL

<?php
if (isset($_POST['add'])) {
    $data    = array();
    $subject = $_POST['sub'];
    $sem     = $_SESSION['sem'];

    for ($i = 0; $i < count($subject); $i++) {
        $subject = mysql_real_escape_string($subject[$i]);
        $sem     = mysql_real_escape_string($sem[$i]);
        $yr      = mysql_real_escape_string($yr[$i]);
        $fac     = mysql_real_escape_string($fac[$i]);
        $col     = mysql_real_escape_string($col[$i]);

        $set = mysql_query("SET foreign_key_checks = 0");
        if (!$set) {
            die("Foreign key SET failed");
        } else {
            $insertmid = mysql_query("INSERT INTO grade_midterm 
                                     (midterm_grade, semester, year_level, subj_code, stud_id, fac_id, col_code) 
                                      VALUES ('NA','$sem','$yr','$subject', '$user','$fac','$col')");
            if (!$insertmid) {
                die("Failed to insert midterm" . mysql_error());
            } else {
                $insertfinal = mysql_query("INSERT INTO grade_final 
                                           (final_grade, semester, year_level, subj_code, stud_id, fac_id, col_code)
                                            VALUES ('NA','$sem','$yr','$subject','$user','$fac','$col')");
                if (!$insertfinal) {
                    die("Failed to indert final");
                } else {
                    $set2 = mysql_query("SET foreign_key_checks = 1");
                    echo "<script>alert('Success Adding Subject');</script>";
                }
            }
        }
    }
}
?>

First of all you should stop using mysql_ functions. they are deprecated for a while now. use pdo or mysqli_ instaed — Gert B.
– Gert B., Commented Sep 15, 2017 at 14:16
yes i will sir! im turning to php7 format soon. but im mostly familiar with php5. im just afraid that i will get stuck in some part, i only have short time for this assignment. but im studying mysqli. :) — Mike Ahren Rances
– Mike Ahren Rances, Commented Sep 15, 2017 at 14:22

Gert B. · Accepted Answer · 2017-09-15 14:21:06Z

1

You have a select box with name sub in your loop

<select name="sub">

this means every next selectbox will overwrite the previous one use

<select name="sub[]">

to create a array in your $_POST variable

answered Sep 15, 2017 at 14:21

Gert B.

2,34720 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Ahren Rances Over a year ago

sir i dont want to overwrite it. i want if the user put 3 inputs. all of it will store in the database. thanks for ur answer sir.

Collectives™ on Stack Overflow

Inserting multiple rows to database from a while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related