How to insert multiple row to new table using single submit button

Question

I am try to inset multiple rows to new table fetched from other table, but problem is that only last single row is being inserted and no other now is getting insert, so please tell the issue where i am lacking

<?php
error_reporting(1);
session_start();
$s=$_SESSION['username'];


//connect database 
$con=mysql_connect("localhost","root","") or die(mysql_error());
 
// select database 
mysql_select_db("education",$con);
   
   
  $date= date("Y/m/d");
//select all values from empInfo table
$data="SELECT * FROM student";

$val=mysql_query($data);
   
?>

<html>

<body>
<table>

</table>
        


<form action="submit.php" method="post" >
 <table>
<tr>

<th>Teacher name</th>
<th>Date</th>
<th>Roll No</th>
<th>Student name</th>
<th>Father name</th>
<th>Addhaar No</th>
<th>Status(P)</th>
<th>Status(A)</th>
<th>Status(L)</th>

</tr>
<?php while($r=mysql_fetch_array($val))
{?>
  
 
<tr style="border:2px solid black;">
<td><input type="text"  name="teacher" value="
<?php echo $s; ?>"></td>
<td><input type="text"  name="date" value="
<?php echo $date; ?>"></td>

<td ><input  name="roll_no" value="
<?php echo $r['roll_no']; ?>">

</td>
<td><input  name="student_name" value="
<?php  echo $r['student_name'] ?>">
</td>
<td><input  name="father_name" value="
<?php  echo $r['father_name'] ?>">
</td>
<td>
<input  name="addhaar_no" value="
<?php  echo $r['addhaar_no'] ?>">
</td>
<td>

<input type="checkbox" value="present" name="status"> Present
</td>
<td>
<input type="checkbox" name="status" value="absent">Absent
</td>

<td>
<input type="checkbox" name="status" value="leave">Leave

</td>


</tr>

</table>
   <?
}  

  
?>     
<input type="submit" name="submit" value="submit">


</form>

</body>

</html>

submit.php -

<?php

error_reporting(1);
$con=mysql_connect("localhost","root","") or die(mysql_error());
 
// select database 
mysql_select_db("education",$con);
   
//get data from html form
$roll_no=$_POST['roll_no'];

$student_name=$_POST['student_name'];

$father_name=$_POST['father_name'];

$addhaar_no=$_POST['addhaar_no'];
$status=$_POST['status'];
   
//Insert values in empInfo table with column name 

$query="INSERT INTO attandance
 VALUES ('', '$roll_no','$student_name','$father_name','$addhaar_no','$status'),

 VALUES ('', '$roll_no','$student_name','$father_name','$addhaar_no','$status')";


 echo $query;
 die();

mysql_query($query);   
   



?>

page

Your syntax is off. `VALUES` only appears _once_ for an `INSERT` of multiple records in MySQL. — Tim Biegeleisen, Oct 06 '16 at 07:11
First of all you need to stop using mysql. Either use mysqli or PDO - https://www.sitepoint.com/re-introducing-pdo-the-right-way-to-access-databases-in-php/ Then once you have sorted that you need to loop through the insert script to insert a row at a time. As far as I know, and I am sure someone will correct me if I am wrong, you can only have a single `VALUES` in an insert statement. — Blinkydamo, Oct 06 '16 at 07:16
[Multiple inputs with same name through POST in php](http://stackoverflow.com/questions/7880619/multiple-inputs-with-same-name-through-post-in-php) — Solarflare, Oct 06 '16 at 07:21
@Blinkydamo You can insert multiple rows at the same time, but the syntax uses `values` only once: `insert into table (colname1, colname2) values (value1_1, value1_2), (value2_1, value2_2), (value3_1, value_3_2)` inserts 3 rows. — Solarflare, Oct 06 '16 at 07:26

score 0 · Answer 1 · answered Oct 06 '16 at 07:20

You need to have unique name for each fields. What you can do is have a counter in loop and add it the names of the fields to make it unique.

Sample:

$ctr = 0;

while($r=mysql_fetch_array($val)){

    echo "<input type="text"  name='teacher_".$ctr."'>";

$ctr++;
}

Or make the names array, and loop through the values in saving the data.

while($r=mysql_fetch_array($val)){
    echo "<input type="text"  name='teacher[]'>";
}

score 0 · Answer 2 · answered Oct 06 '16 at 07:23

I think you should study PHP a bit more... As i can see in your code, you haven't understood fundamentals of PHP.

1: Normally, you won't mix up HTML and PHP like you did in your first code. Its just confusing and really annoying to read the code later.

2: When you post your form, for example the variable $_POST['student_name']; will just contain the value of the last row (your problem). So, why? Because you can't assign more than one value to a variable. Or at least, not the way you tried it. Array would be a good keywoard for this problem.

3: Please check your SQL syntax... Thats why i'm saying you haven't understand fundamentals... http://www.w3schools.com/sql/sql_insert.asp Why you're repeating your values? You think the second time the variables will contain the values of the next row? Thats just false. A Variable contains everytime the same value, as long as you don't assign a new value to it.

4: mysql is depracted. Use mysqli or PDO instead.

My tip: You need to have unique input names. Just take a look at PHP, how for/while loops work, study a bit more and try it again. It's not difficult to solve, but i think you'll learn a lot more if we don't give you the direct solution.

Razib Al Mamun · Answer 3 · 2016-10-06T17:47:02.410

Now mysql is depracted. So, you can use mysqli or PDO instead. I can use now PDO. Please follow bellow code carefully:

<?php
$user = 'root';
$pass = '';
$dbh = new PDO('mysql:host=localhost;dbname=education', $user, $pass);

try {
    $select = $dbh->query('SELECT * from student');
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>
<html>
    <body>
        <form action="submit.php" method="post" >
            <table>
                <tr>
                    <th>Teacher name</th>
                    <th>Date</th>
                    <th>Roll No</th>
                    <th>Student name</th>
                    <th>Father name</th>
                    <th>Addhaar No</th>
                    <th>Status(P)</th>
                    <th>Status(A)</th>
                    <th>Status(L)</th>
                </tr>

                <?php
                foreach($select as $val) {
                ?>
                <tr style="border:2px solid black;">
                    <td><input type="text"  name="teacher" value="<?php echo $s; ?>"></td>
                    <td><input type="text"  name="date" value="<?php echo $date; ?>"></td>
                    <td><input  name="roll_no" value="<?php echo $r['roll_no']; ?>"></td>
                    <td><input  name="student_name" value="<?php  echo $r['student_name'] ?>"></td>
                    <td><input  name="father_name" value="<?php  echo $r['father_name'] ?>"></td>
                    <td><input  name="addhaar_no" value="<?php  echo $r['addhaar_no'] ?>"></td>
                    <td><input type="checkbox" value="present" name="status"> Present</td>
                    <td><input type="checkbox" name="status" value="absent">Absent</td>
                    <td><input type="checkbox" name="status" value="leave">Leave</td>
                </tr>
               <?php
                }           
                ?>   
            </table>  
            <input type="submit" name="submit" value="submit">
        </form>
    </body>
</html>

For submit.php code bellow

<?php
$user = 'root';
$pass = '';
$dbh = new PDO('mysql:host=localhost;dbname=education', $user, $pass);

$stmt = $dbh->prepare("INSERT INTO attandance (roll_no, student_name, father_name, addhaar_no, status) VALUES (?, ?, ?, ?, ?)");
$stmt->bindParam(1, $roll_no);
$stmt->bindParam(2, $student_name);
$stmt->bindParam(2, $father_name);
$stmt->bindParam(2, $addhaar_no);
$stmt->bindParam(2, $status);

//if you insert 2 time then
for($x=0; $x<2; $x++) {
    $roll_no = $_POST['roll_no'];
    $student_name = $_POST['student_name'];
    $father_name = $_POST['father_name'];
    $addhaar_no = $_POST['addhaar_no'];
    $status = $_POST['status'];

    $stmt->execute();
}
?>

How to insert multiple row to new table using single submit button

3 Answers3