mysql_fetch_assoc data insert into mysql database

Question

Below is my php form:

<form method="POST" action="finalc.php" name="sing">
  <table>
  <tr>
<td>Sl.No.</td>  <td>Date</td>  <td>Cus Id</td>  <td>Name</td>
<td>Sm1</td>  <td>Sm2</td>  <td>Sm3</td>  <td>Sm4</td> <td>Total</td> <td>Min</td>
  </tr>         
                    <?php                           
                     $i =0;
                     $sql = mysql_query("select date cus_id, name, sm1, sm2, sm3, sm4,
                            sum(sm1 + sm2 + sm3 + sm4) as total,
                            least(sm1, sm2, sm3, sm4) as min
                            from final");

                        $count = mysql_num_rows($sql);
                        if($count == 0){
                        ?>
                        <tr> 
                            <th><?php echo 'No Records Founds!' .mysql_error(); ?></th> 
                        </tr>
                        <?php 
                        }else{
                            while($sql_result = mysql_fetch_assoc($sql)){
                        ?>
<tr>
<td><?php echo $i += 1; ?></td>
<td><input type="text" name="date" value="<?php echo $sql_result['date']; ?>" ></td>
<td><input type="text" name="cus_id" value="<?php echo $sql_result['cus_id']; ?>" ></td>
<td><input type="text" name="name" value="<?php echo $sql_result['name']; ?>" ></td>
<td><input type="text" name="sm1" value="<?php echo $sql_result['sm1']; ?>" ></td>
<td><input type="text" name="sm2" value="<?php echo $sql_result['sm2']; ?>"</td>
<td><input type="text" name="sm3" value="<?php echo $sql_result['sm3']; ?>"></td>
<td><input type="text" name="sm4" value="<?php echo $sql_result['sm4']; ?>"></td>
<td><input type="text" name="total" value="<?php echo $sql_result['total']; ?>"></td>
<td><input type="text" name="min" value="<?php echo $sql_result['min']; ?>"></td>
</tr>
    <?php
    }
    }
    ?>
 </table>
<input name="submit" type="submit" id="submit" value="Submit"  />
</form>

My report table is as like :

Id  Date        Name   sm1  sm2  sm3  sm4  total  min
1   15/10/2017  aman   2    3    5    7    17     2
2   16/10/2017  anil   23   24   17   34   98     17
3
4
++

final table data comes from customer login. all the data from customer login shows here.

Question : In the finalc.php what will be the insert query to insert all the mysql_fetch_assoc multiple row data into mysql database with single submit button?

Answer 1

You really shouldn't user mysql_* functions. They are deprecated and vulnerable to sql injection attacks. They are completely removed from php 7 and forward, so if you use them your code will not be easily upgraded to future php versions.

Then there are some logical issues you will have to resolve yourself. You have the min and total values as input fields, however they can't be updated into the database directly, as they are not stored in your database but are only calculated when you select them. You are also fetching the mobile field while never using it, which is a sign (as in, it might be or it might not be) of a logical bug in your code.

Your html text inputs are also wrong. They all have the same name, no matter which row they are in, so the last row will always override all the other values. Try to submit your form and inspect the web traffic and var_dump() the $_POST superglobal, and you will see.

You can combat this by naming them as an array like

<input type="text" name="sm1[<?php echo $i; ?>]" value="<?php ... ?>" />

This will make each variable an associative array in php.

Furthermore, there seems to be an issue with your query. You never fetch date or name from the table, but you use them in your form.

It is also impossible to give you a solution when we don't know how your table looks like. But in general, you need to update your rows using the primary key of the table as a where clause. I'm going out on a limb here assuming cus_id is the primary key. In that cause, you could do something like:

$mysqli = new mysqli($host, $user, $pass, $database);

if (mysqli_connect_errno()) {
    // log & handle connection errors
    die();
}

$statement = $mysqli->prepare('INSERT INTO final (cus_id, name, date, sm1, sm2, sm3, sm4) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE');

foreach ($_POST['cus_id'] as $i => $cus_id) {
    $name = $_POST['name'][$i];
    $date = $_POST['date'][$i];
    $sm1  = $_POST['sm1'][$i];
    $sm2  = $_POST['sm2'][$i];
    $sm3  = $_POST['sm3'][$i];
    $sm4  = $_POST['sm4'][$i];
    $statement->bind_param('dssdddd', $cus_id, $name, $date, $sm1, $sm2, $sm3, $sm4);
    $statement->execute();
}

$statement->close();
$mysqli->close();

Though, please be advised this solution is not really suited if you intend to update thousands of rows. Then you should think about testing which rows to update first, or concatenating the insert like the most popular answer in this question.

Also, you may want to read through the documenation on mysqli, especially mysqli_result and mysqli_stmt.