My php form is only saving the last row of my table.

Question

I have a table within a form (new_tear.php) that takes data from a single user. The first cloumn is populated from a database table (ProfileTable) with names. The user has to select an option from a dropdown for each row (each name). When the form is submitted (POST to tear_done.php), I want to store each row in my database. However, I am only seeing the last row posted each time

new_ter.php

<?php
$sqlT1 = "SELECT * FROM ProfileTable WHERE dept = 'T1'";
$queryT1 = mysqli_query($conn, $sqlT1);
if (!$queryT1) {
  die ('SQL Error: ' . mysqli_error($conn));
}
?>
<form action="tear_done.php" method="post">
<table>
<?php
    $no     = 1;
    while ($row = mysqli_fetch_array($queryT1))
    {
        echo '<tr>
                <td>'.$no.'</td>
                <td>'.$row['name'].'</td>
                <td><select id="Dz_M" name="Dz_M">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_T" name="Dz_T">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_W" name="Dz_W">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_F" name="Dz_F">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_S" name="Dz_S">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>                   
                </tr>';
        $no++;
        $no_cont = $no;
    }?> 
</table>
<br>
<button value="save" type="submit" name="save">Save Tear</button>       
</form>

tear_done.php

<?php
$sqlT1 = "SELECT * FROM ProfileTable WHERE dept = 'T1'";
$queryT1 = mysqli_query($conn, $sqlT1);
if (!$queryT1) {
    die ('SQL Error: ' . mysqli_error($conn));
}
$no = 1;
while ($row = mysqli_fetch_array($queryT1))
{
$Name = $row ['name'];
$Dept = $row ['dept'];
$DZ_M = $_POST ["Dz_M"];
$DZ_T = $_POST ["Dz_T"];
$DZ_W = $_POST ["Dz_W"];
$DZ_F = $_POST ["Dz_F"];
$DZ_S = $_POST ["Dz_S"];

$sql = "INSERT INTO TearTable (name, Mdy, Tdy, Wdy, Fdy, Sdy, dept)
VALUES ('$Name', '$DZ_M', '$DZ_T', '$DZ_W', '$DZ_F', '$DZ_S', '$Dept')";    

echo $no;
echo '<br>';
echo $sql; //for testing
echo '<br>';
$no++;
}   
?>

What I am getting from the echo is the sql with all the names and dept correct, however the other fields $DZ_M, $DZ_T etc only repeat the last row entered from the table in new_tear.php. What is it am I missing please?

Unless I'm overlooking something, the code you've shared defines a variable with `INSERT INTO` code but never executes it. — Álvaro González, May 10 '18 at 17:38
Could not see the sql code that does the insert. How are you getting last row inserted — Indra Kumar S, May 10 '18 at 17:39
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, May 10 '18 at 18:51
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, May 10 '18 at 18:51
Note: Try and get out of the habit of declaring SQL statements in throw-away variables that are used only once. It's a lot easier to follow code where the query is supplied directly to the function, and there's no longer a chance of messing up and sending in `$sql3` instead of the visually similar `$sql8`. — tadman, May 10 '18 at 18:51
Alvaro Gonzalez and Indra Kumar, Firstly, thanks for the reply. I edited my code to make it shorter, I'm sorry for the confusion. I removed the execution for testing purposes. The line 'echo $sql' is just for testing if its being written properly. — GraysonSoft, May 10 '18 at 23:22
Tadman,Thanks for the reply, Its for an internal network, never going online outside. — GraysonSoft, May 10 '18 at 23:26
I would not use iterated INSERT queries. I'd use [INSERT with SELECT](https://stackoverflow.com/q/5391344/2943403). — mickmackusa, Jan 12 '22 at 01:13

score 0 · Answer 1 · answered May 10 '18 at 17:43

0

Inside While loop Just concat the insert query strings

while ($row = mysqli_fetch_array($queryT1))
{
......

$sql .= "INSERT INTO TearTable (name, Mdy, Tdy, Wdy, Fdy, Sdy, dept)
VALUES ('$Name', '$DZ_M', '$DZ_T', '$DZ_W', '$DZ_F', '$DZ_S', '$Dept');";    // Note .= and semicolon at the end 

   .....
    } 
// Outside the loop
execute your query here..

You should use prepared statements for security reasons

answered May 10 '18 at 17:43

Indra Kumar S

2,818
2
16
27

Don't use multi-query. You need to ensure those `INSERT` statements run correctly, and that requires firing them off individually and checking for errors. This just makes an awful mess of things. – tadman May 10 '18 at 18:52
@tadman Yes you are right. Individual queries will help us know whats happening with each insert. Since the OP asked "why only last one gets executed" i posted the above answer – Indra Kumar S May 10 '18 at 18:55
@Indra Kumar, when I execute my query outside of the loop, only the last row of my table is shown. When I execute it within the loop, all the rows are shown, however, only data from the last row in the table is shown. – GraysonSoft May 10 '18 at 23:33

score 0 · Answer 2 · answered May 11 '18 at 12:35

I was able to solve my problem by adding a var ($no) to the new_tear.php select id and name.

new_tear.php

<?php
    $no     = 1;
    while ($row = mysqli_fetch_array($queryT1))
    {
        echo '<tr>
                <td>'.$no.'</td>
                <td>'.$row['name'].'</td>
                <td><select id="Dz_M'  . $no . '" name="Dz_M'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_T'  . $no . '" name="Dz_T'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_W'  . $no . '" name="Dz_W'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_F'  . $no . '" name="Dz_F'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>

                </td>
                <td><select id="Dz_S'  . $no . '" name="Dz_S'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>                   
                </tr>';
        $no++;
        $no_cont = $no;
    }?>

And adding the var ($no) to the $_POST in tear_done.php

tear_done.php

$Name = $row ['name'];
$Dept = $row ['dept'];
$DZ_M = $_POST ['Dz_M'.$no]; 
$DZ_T = $_POST ['Dz_T'.$no];
$DZ_W = $_POST ['Dz_W'.$no];
$DZ_F = $_POST ['Dz_F'.$no];
$DZ_S = $_POST ['Dz_S'.$no];

This solved my problem and now in the echo (again, only for testing) I see the sql looks correct. Thanks Much for your help everyone.

Paulo Hgo · Answer 3 · 2018-05-11T14:33:20.567

Best would be to create one single big query statement.

$sql .= "INSERT INTO TearTable (name, Mdy, Tdy, Wdy, Fdy, Sdy, dept) VALUES"
while ($row = mysqli_fetch_array($queryT1)) {  
  $Name = $row ['name'];
  $Dept = $row ['dept'];
  $DZ_M = $_POST ["Dz_M"];
  $DZ_T = $_POST ["Dz_T"];
  $DZ_W = $_POST ["Dz_W"];
  $DZ_F = $_POST ["Dz_F"];
  $DZ_S = $_POST ["Dz_S"]; 
  $sql .= "('$Name', '$DZ_M', '$DZ_T', '$DZ_W', '$DZ_F', '$DZ_S', '$Dept'),"
}
$result = mysqli_query($conn, $sql); //run your INSERT query.

This single query inserts all that data to the DB. The issue with this way of doing it is that it becomes harder to debug if your INSERT fails.

Thanks for the reply. I have tried that but the result is the same. Could you clarify a bit please. Thank You. — GraysonSoft, May 11 '18 at 02:54

My php form is only saving the last row of my table.

3 Answers3