Update records while looping

[Sigmond_1]

I have a problem that in my sql is always updating the last records. I need to loop in every row and update each record in that row i tried this code underneath but only last record is updating .

   <tr>
                    <td value="<?php echo $_SESSION['flowertypename'];?>"><?php echo $_SESSION['flowertypename']?></td>

                     <?php

                     $query7 = "SELECT * FROM tbl_try WHERE country='$_SESSION[country]' AND name='$_SESSION[flowertypename]'";
                     $result7= mysqli_query($conn, $query7) or die("Error in query: ". mysqli_error($conn));

                    while ($row = mysqli_fetch_assoc($result7))
                      {   
                     ?>
                    <td><input type="number" name="reg_smallprice" value="<?php echo $row['price_small'];?>"></td>
                    <td><input type="number" name="reg_mediumprice" value="<?php echo $row['price_medium'];?>"></td>
                    <td><input type="number" name="reg_largeprice" value="<?php echo $row['price_large'];?>"></td>

                 <?php

                   }
                    }
                     ?>
                 </tr></table>
        <div class="form-group">
          <div class="col-sm-offset-5 col-sm-10">
            <button type="submit" name="updated" class="btn btn-default">Save Changes</button>
          </div>
        </div>

       <?php

         if(isset($_POST['updated']))
                 {
             $query33 = "SELECT * FROM tbl_try WHERE country ='$_SESSION[country]'";
             $result33 = mysqli_query($conn, $query33) or die("Error in query: ". mysqli_error($conn));
            $result33_rows = mysqli_num_rows($result33);

          for($x = 0; $x <= $result33_rows; $x++){
             $x++;

           $smallprice= mysqli_real_escape_string($conn,$_POST["reg_smallprice"]);
           $mediumprice= mysqli_real_escape_string($conn,$_POST["reg_mediumprice"]);
           $largeprice= mysqli_real_escape_string($conn,$_POST["reg_largeprice"]);

           $query44 = "UPDATE tbl_try SET price_small = '$smallprice' ,price_medium='$mediumprice' ,price_large='$largeprice'  WHERE  name='$_SESSION[flowertypename]' ";

            $result44 = mysqli_query($conn, $query44)
                    or die("Error in query: ". mysqli_error($conn));

       }
            echo "Registered Successfully";
             header("location:trytest.php");
         }

       ?>
            <?php
            require_once("footer.php");
        ?>

Any suggestions ??

[rproffitt]

Something looks odd at lines 36 and 37. It looks like only the odd values would appear for the value x$ So that's odd on its own.

Next we have what is in the look from 37 to 47. I can't see where x$ is used or why anything other than one set/row would be affected. You have have to rewrite that.

[rproffitt]

Spelling error in my reply. I meant "the loop" in my second sentence.

[AssertNull]

I can't see where x$ is used

Same for the $result33 variable. It's used, but not inside the loop and for nothing but to figure out the number of times to go through the loop.

Hard to say what the solution is here since I'm unfamiliar with the database, but you are using WHERE in your sql update statement, which can update multiple records at a time. Your WHERE criteria does not seem to change during the loop so it appears to be the same update statement several times instead of once, which seems pointless. As far as I can tell, the update statement in the loop has nothing to do with the prior query statment, which sets up the number of times to go through this loop. Hence as far as I can tell, lines 32 to 48 end up as the same thing as lines 39 to 46. Thus you could delete lines 32 to 38 and lines 47 - 48.

This is also one of those perfect examples for the need to format code consistently. It was hard to follow the code and which lines were in the loop and which weren't. If it were formatted, I imagine the fact that the loop didn't use the variables would have simply jumped out at me. Instead I had to carefully go through the code line by line.

[rproffitt]

@Sigmond,
Consider this PHP code.

<?php
for($x = 0; $x <= 53; $x++){
    $x++;
    echo $x;
    echo ",";
}
?>

I ran it on a PHP fiddle and here's the output.
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,

I'd think over your code for this and the other reasons noted so far.

[diafol]

It seems you have a preoblem with the name property too:

<td><input type="number" name="reg_smallprice" value="<?php echo $row['price_small'];?>"></td>
<td><input type="number" name="reg_mediumprice" value="<?php echo $row['price_medium'];?>"></td>
<td><input type="number" name="reg_largeprice" value="<?php echo $row['price_large'];?>"></td>

If this is being looped you need to set the name as an array:

<td><input type="number" name="reg_smallprice[]" value="<?=$row['price_small']?>"></td>
<td><input type="number" name="reg_mediumprice[]" value="<?=$row['price_medium']?>"></td>
<td><input type="number" name="reg_largeprice[]" value="<?=$row['price_large']?>"></td>

However, this does not identify which DB row these banks of inputs represent. An alternative would be to placce the unique ID into the array, like so:

<td><input type="number" name="reg_smallprice[<?=$row['id']?>]" value="<?=$row['price_small']?>"></td>
<td><input type="number" name="reg_mediumprice[<?=$row['id']?>]" value="<?=$row['price_medium']?>"></td>
<td><input type="number" name="reg_largeprice[<?=$row['id']?>]" value="<?=$row['price_large']?>"></td>

You can then extract the id from the $_POST['reg_smallprice'], etc:

$sp = $_POST['reg_smallprice'];
$mp = $_POST['reg_mediumprice'];
$lp = $_POST['reg_largeprice'];
foreach($sp as $k=>$v){
//$k is now the row 'id' that you'd use to extract data from $sp, $mp and $lp and to use in your WHERE clause
}

You have three main options for updating:

1) Run each query individually in the loop having sanitized the inputs - probably not good as can be time consuming
2) Use a parameterized prepared statement and then bind values/execute in the loop
3) Create a string array of SQL statements in the loop having sanitized the inputs and then join them with implode(';',$array) and run as a multi_query

Not sure which of 2) or 3) would be quickest - some testing probably in order

[diafol]

Sorry forgot to mention - using transation/commit could also speed up your updates:

https://www.daniweb.com/programming/web-development/code/495371/insert-multiple-records-with-pdo-prepared-statement#post2167765

Although the example uses PDO, the same should be true for mysqli.