how to insert html table values in mysql table

Question

Using following code I have fetched Student ID and Student name from mysql table in html table. In third column which is Obtained Marks I am getting student marks through input box. Now I want to insert all three columns (Student ID, Student Name and Obtained Marks) in new table which is testrecord.

<html>
<body>
<?php
$connection = mysqli_connect ('localhost', 'admin', 'password', 'db');
if (!$connection) {
    echo 'Not connected to server';
}

$select_db = mysqli_select_db($connection, 'db');
if (!$select_db) {
    echo 'Not connected to database';
}

$SelectClass = $_POST ['selectclass'];
$sql= "SELECT * FROM students WHERE class = '$SelectClass'";
$query = mysqli_query($connection, $sql);
if (!$query) {
    die ('SQL Error: ' . mysqli_error($connection));
}

mysqli_close($connection);
?>
 <body>
    <div class="container">
    <form class="well form-horizontal" action="insert_marks.php" method="post">
    <h1><strong>Please enter marks of each student for subject</strong></h1>
    <form action="" method="post">
    <table id = "result" class="data-table">
        <caption class="title"></caption>
        <thead>
            <tr>    
                <th>Sr.No.</th>
                <th>Student ID</th>
                <th>Student Name</th>
                <th>Marks Obtained</th>
            </tr>
        </thead>
        <tbody>
        <?php
        $no     = 1;
        $total  = 0;
        while ($row = mysqli_fetch_array($query)) {
            $stu  = $row['stu_id'] == 0 ? '' : number_format($row['stu_id']);
            echo '<tr>
                    <td>'.$no.'</td>
                    <td>'.$row['student_id'].'</td>
                    <input type="hidden" name="student_id" value='.$row['student_id'].'>
                    <td>'.$row['student_name'].'</td>
                    <input type="hidden" name="student_name" value='.$row['student_name'].'>
                    <td>
                        <div class="search-block clearfix">
                            <input name="obtmarks" placeholder="" type="number">
                        </div>
                    </td>
                </tr>';
            $total += $row['stu_id'];
            $no++;
        }
        ?>
        </tbody>
    </table>
    <button type="submit" class="btn btn-warning" value="insert" align="right">Update<span class="glyphicon glyphicon-send"></span></button>
    </form>
</div>
</body>
</html>

insert_marks.php:

<html>
<body>
<?php
$connection = mysqli_connect ('localhost', 'admin', 'password', 'db');
if (!$connection) {
    echo 'Not connected to server';
}

$select_db = mysqli_select_db($connection, 'db');
if (!$select_db) {
    echo 'Not connected to database';
}
//***********Form Submit Goes Here***********//
while 
if($_POST) {
    $student_id     =   $_POST['student_id'];
    $student_name   =   $_POST['student_name'];
    $student_marks  =   $_POST['obtmarks'];

    $sql= "INSERT INTO testrecord (student_id,student_name,obtained_marks) VALUES ('$student_id','$student_name','$student_marks')";
    if (mysqli_query($connection, $sql)) {
    echo "Marks added successfully.";
    echo "<br>";
    echo "<br>";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($connection);
    }
}
mysqli_close($connection);
?>
</body>
</html>

There are 20 entries in table. After inserting marks for each student in text box, above coding inserts only last record in mysql table 'testrecord'. Can you please correct the insert_marks.php code.

Mahidul Islam · Accepted Answer · 2018-10-17 03:44:57Z

2

Change on Html table tbody part:

<tbody>
    <?php
    $no     = 1;
    $total  = 0;
    while ($row = mysqli_fetch_array($query)) {
        $stu  = $row['stu_id'] == 0 ? '' : number_format($row['stu_id']);
        echo '<tr>
                <td>'.$no.'</td>
                <td>'.$row['student_id'].'</td>
                <input type="hidden" name="student_id[]" value='.$row['student_id'].'>
                <td>'.$row['student_name'].'</td>
                <input type="hidden" name="student_name[]" value='.$row['student_name'].'>
                <td>
                    <div class="search-block clearfix">
                        <input name="obtmarks[]" placeholder="" type="number">
                    </div>
                </td>
            </tr>';
        $total += $row['stu_id'];
        $no++;
    }
    ?>
</tbody>

in insert_marks.php change while part:

//***********Form Submit Goes Here***********//
while 
if($_POST) {
    $student_id     =   $_POST['student_id'];
    $student_name   =   $_POST['student_name'];
    $student_marks  =   $_POST['obtmarks'];

    for($i = 0; $i < count($student_id); $i++){
        $sql= "INSERT INTO testrecord (student_id,student_name,obtained_marks) VALUES ('$student_id[$i]','$student_name[$i]','$student_marks[$i]')";
        if (mysqli_query($connection, $sql)) {
             echo "Marks added successfully.";
             echo "<br>";
             echo "<br>";
        } else {
             echo "Error: " . $sql . "<br>" . mysqli_error($connection);
        }
    }


}

edited Oct 17, 2018 at 3:44

Mahidul Islam

62612 silver badges29 bronze badges

answered Sep 26, 2018 at 8:12

Delwar Sumon

4702 silver badges15 bronze badges

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

3 Comments

Akhtar Over a year ago

It worked for me. But its giving "marks added successfully" 20 times

Delwar Sumon Over a year ago

You can print success message out of for loop. Then it will print one time only.

Akhtar Over a year ago

but when i am printing success message out of for loop, its only inserting last value in mysql table testrecord i.e. 20th..

Jigar Prajapati · Accepted Answer · 2018-09-27 12:11:33Z

Try this

<html>
<body>
<?php
$connection = mysqli_connect ('localhost', 'admin', 'password', 'db');
if (!$connection) {
    echo 'Not connected to server';
}

$select_db = mysqli_select_db($connection, 'db');
if (!$select_db) {
    echo 'Not connected to database';
}

$SelectClass = $_POST ['selectclass'];
$sql= "SELECT * FROM students WHERE class = '$SelectClass'";
$query = mysqli_query($connection, $sql);
if (!$query) {
    die ('SQL Error: ' . mysqli_error($connection));
}

mysqli_close($connection);
?>
 <body>
    <div class="container">
    <form class="well form-horizontal" action="insert_marks.php" method="post">
    <h1><strong>Please enter marks of each student for subject</strong></h1>
    <table id = "result" class="data-table">
        <caption class="title"></caption>
        <thead>
            <tr>    
                <th>Sr.No.</th>
                <th>Student ID</th>
                <th>Student Name</th>
                <th>Marks Obtained</th>
            </tr>
        </thead>
        <tbody>
        <?php
        $no     = 1;
        $total  = 0;
        while ($row = mysqli_fetch_array($query)) {
            $stu  = $row['stu_id'] == 0 ? '' : number_format($row['stu_id']);
            echo '<tr>
                    <td>'.$no.'</td>
                    <td>'.$row['student_id'].'</td>
                    <input type="hidden" name="student['.$no.'][student_id]" value='.$row['student_id'].'>
                    <td>'.$row['student_name'].'</td>
                    <input type="hidden" name="student['.$no.'][student_name]" value='.$row['student_name'].'>
                    <td>
                        <div class="search-block clearfix">
                            <input name="student['.$no.'][obtmarks]" placeholder="" type="number">
                        </div>
                    </td>
                </tr>';
            $total += $row['stu_id'];
            $no++;
        }
        ?>
        </tbody>
    </table>
    <button type="submit" class="btn btn-warning" value="insert" align="right">Update<span class="glyphicon glyphicon-send"></span></button>
    </form>
</div>
</body>
</html>

insert_marks.php:

<html>
<body>
<?php
$connection = mysqli_connect ('localhost', 'admin', 'password', 'db');
if (!$connection) {
    echo 'Not connected to server';
}

$select_db = mysqli_select_db($connection, 'db');
if (!$select_db) {
    echo 'Not connected to database';
}
//***********Form Submit Goes Here***********//

if(isset($_POST['student']) && !empty($_POST['student'])) {
    $queryStr = '';
    $cnt = count($_POST['student']);
    foreach ($_POST['student'] as $key => $student) {
        $queryStr .= "('".$student['student_id']."','".$student['student_name']."','".$student['obtmarks']."') ";  
        if (($key + 1) !=  $cnt) {
            $queryStr .= " , ";
        }
    }

     if ($queryStr != '') {
        $sql= "INSERT INTO testrecord (student_id,student_name,obtained_marks) VALUES $queryStr";
        if (mysqli_query($connection, $sql)) {
        echo "Marks added successfully.";
        echo "<br>";
        echo "<br>";    
     }  
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($connection);
    }
}
mysqli_close($connection);
?>
</body>
</html>

its only showing white window when i am submitting the marks. And its also not inserting marks mysql table 'testrecord'
I have made some changes please check it. Maybe this will helpful to you :)
Again showing white screen when clicking on update after adding marks....your code is good but there is some mistake.. can you please point out... stucked badly here

Collectives™ on Stack Overflow

how to insert html table values in mysql table

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related