How to update database with entire html table (PHP, MySQL)

Question

I have been struggling with this for several days now. I have searched on how to update tables and have managed to get as far as to update rows, but only the last one in the table. So now i am trying to get a loop that loops through all the inputs and updates the database with the inputted values. I think the code that needs to be corrected is located near the end of the code

What i want to do:

Get/display database in html table
Change values of certain columns
Update the database table using a submit button which updates every row in database

Here is a picture of what the table looks like in web view:

<?php
//Connect to database
include '../db/connect.php';
?>
   <form action='test7.php' method="post">
      <table border='1'>
         <?php
            $result = $MySQLi_CON->query("SELECT * FROM users");
               echo "<tr>";
               echo "<td colspan='3'>CLASS 1</td>";
               echo "</tr>";
               //All table rows in database presented in html table
               while($row = $result->fetch_array()){
                  echo "<tr>";
                  echo "<td><input type='hidden' name='user_id[]' value='".$row['user_id']."' /></td>";
                  echo "<td>username  :<input type='text' name='username[]' value='".$row['username']."' /></td>";
                  echo "<td>email  :<input type='text' name='email[]' value='".$row['email']."' /></td>";
                  echo "<td>rank  :<input type='number' name='rank[]' value='".$row['rank']."' /></td>";
                  echo "</tr>";
               }
            echo "<input type='submit' name='update' value='UPDATE' />";
         ?>
      <table>
   </form>
<?php
   if(isset($_POST['update'])){ 
      $total = count($_POST['rank']); 
      $user_id_arr = $_POST['user_id']; 
      $rank_arr = $_POST['rank']; 
      for($i = 0; $i < $total; $i++){ 
         $user_id = $user_id_arr[$i]; 
         $rank = $rank_arr[$i]; 
         $query = "UPDATE users SET `rank`= '".$rank."' WHERE `user_id`= '".$user_id."'"; 
         $MySQLi_CON->query($query); 
         header('Location: test7.php');
      } 
   }
?>

When I press the UPDATE button, i get PHP Notice: Array to string conversion in....

It refers to line 30 which is this line:

$query = "UPDATE user SET rank=$_POST[rank][$row] WHERE user_id=$value ";

EDIT: Edited the code above to the working code. Thank you @Frayne Konok for your help.

you make a mistake here, you use the same resource to the foreach loop, so you face some unknown problem. — Murad Hasan
– Murad Hasan, Commented Apr 3, 2016 at 15:22

javier_domenech · Accepted Answer · 2016-04-03 12:34:48Z

3

You are very close.

The issue is that in this code $_POST[rank][$row] - rank is an undefined constant. You need it to be a string, like so $_POST['rank'][$row]. Also, pull the $POST variable out of the query directly to allow typecasting - you should always be very uncomfortable when you see a query that has $_POST data directly:

if(isset($_POST['update'])){
    foreach ($result as $row => $value) { 
        // typecast to a number with decimals below.  If you only need integers, than use (int)
        $rank = (float)$_POST['rank'][$row];
        $query = "UPDATE user SET rank={$rank} WHERE user_id={$value}";
        $MySQLi_CON->query($query);
    }
}

However, it would be better to use mysqli prepared statements rather than insert the variables directly - as it stand, the above code is vulnerable to SQL Injection attacks.

Your code should be modified to look something like so to prevent sql injection attacks:

if(isset($_POST['update'])) {
    $stmt = $MySQLi_CON->prepare("UPDATE user SET rank= ? WHERE user_id= ?");
    foreach ($result as $row => $value){      
        $stmt->bind_param('di', $_POST['rank'][$row], $value); 
        $stmt->execute(); 
    }
    $stmt->close();
}

edited Apr 3, 2016 at 12:34

javier_domenech

6,3036 gold badges41 silver badges60 bronze badges

answered Apr 3, 2016 at 11:32

random_user_name

26.3k7 gold badges81 silver badges119 bronze badges

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

2 Comments

Onret Over a year ago

I did what you said and modified the last chunk of code to: <br/>

if(isset($_POST['update'])) {     $stmt = $MySQLi_CON->prepare("$query = "UPDATE user SET rank= ? WHERE user_id= ?");      foreach ($result as $row => $value){                 $stmt->bind_param('di', $_POST['rank'][$row], $value);            $stmt->execute();       }       $stmt->close();  }

It now returns this error: Call to a member function bind_param() on boolean

Murad Hasan Over a year ago

@cale_b, did you notice? The user use the same $result to the loop which is a resource of query.

Murad Hasan · Accepted Answer · 2016-04-03 18:57:21Z

1

You did a great mistake here, Why you use the $result in foreach loop?? FRom where the $result comes?? The $result is the resource of the sql query.

Try this:

if(isset($_POST['update'])){
    $total = count($_POST['rank']);
    $user_id_arr = $_POST['user_id'];
    $rank_arr = $_POST['rank'];
    for($i = 0; $i < $total; $i++){
        $user_id = $user_id_arr[$i];
        $rank = $rank_arr[$i];
        $query = "UPDATE users SET `rank`= '".$rank."' WHERE `user_id`= '".$user_id."'";  
        $MySQLi_CON->query($query);
    }
}

Try with this and let me know if there is any problem.

edited Apr 3, 2016 at 18:57

answered Apr 3, 2016 at 15:21

Murad Hasan

9,5932 gold badges23 silver badges43 bronze badges

8 Comments

Onret Over a year ago

I tried it and it doesn't give any errors. However, when I press the update button, none of the fields are being updated in the database. When I press the button, all the values just return to what they were before I changed them.

Murad Hasan Over a year ago

if you can make a sqlfiddle then i can try or find out the real problem.

Onret Over a year ago

I have never used sqlfiddle before but I am trying now. I will write another comment if I manage to make one.

Onret Over a year ago

Okay I think I've made an sqlfiddle. The link is sqlfiddle.com/#!9/2d1cd/2/0. Hope you can help me find this problem

Onret Over a year ago

Thank you so much, I am staying tuned.

|

Collectives™ on Stack Overflow

How to update database with entire html table (PHP, MySQL)

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related