How to update multiple rows in mysql and php with a different values using variables and different files?

Question

I am trying to make a discord bot in PHP (which works, i know not the best choice of language but that is what i am most comfortable with for my own needs for now) with a dashboard. The idea was to be able to add and edit the commands on the website.

I am selecting my current commands using mysql for the form like this:

if ($result->num_rows > 0) {
                // output data of each row
                while($row = $result->fetch_assoc()) {
                  $disable = $row['Disabled'];
                  echo "<input type='hidden' name='id[]' value='";
                  echo $row['Id'];
                  echo "'>";
                  echo '<tr>';
                  echo '<td class="tg-515c"><input type="text" name="command[]" value="';
                  echo $row['Command'];
                  echo '" class="form-control"></td>';
                  echo '<td class="tg-wp8o"><input type="text" name="text[]" value="';
                  echo $row['Text'];
                  echo '" class="form-control"></td>';
                  echo '<td class="tg-wp8o"><select class="form-control" name="disabled[]">';
                  if($disable == '0')
                  {
                    echo "<option value='1'>Yes</option><option value='0' selected>No</option>";
                  }
                  else
                  {
                    echo "<option value='1' selected>Yes</option><option value='0'>No</option>";
                  }
                  echo "</select>";
                  echo '</td>';
                echo '</tr>';
                }

and i have the form outside of my php syntax. That seems to be working fine.

// Create connection
$command = $_POST['command'];
$text = $_POST['text'];
$id = $_POST['id'];
$disable = $_POST['disabled'];
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql="UPDATE dashboard SET Command='$command[0]', Text='$text[0]', Disabled='$disable[0]' WHERE Id='$id[0]'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
  } else {
    echo "Error updating record: " . $conn->error;
  }

header("Location: /");
$conn->close();

I have just copy/pasted this code few times and changed the array from [0] to [1], etc.

This now works, however, i have another file which adds more commands to database, so i need it to be automatized each time i add a new command and not manually type for each input.

Edited the code to updated version.

You are using a while loop to iterate over all the records to display the data input fields, but input fields of all the records are having the same names (id / command /text / disabled ) ?? Actually the proper way should be using array, i.e. id[] , command[], text[], disabled[] . See this [post](https://stackoverflow.com/questions/7880619/multiple-inputs-with-same-name-through-post-in-php) — Ken Lee, Dec 13 '21 at 01:00
Thank you @KenLee but i am not understanding what should i do in my update file in my instance in order for mysql to pick up different values without me having to manually type each one as i have another file for inserting more data, so i want to be able to automatically pull that data too. I have changed the names in my input but how do i put that into my update function without breaking it? — Nikola Matić, Dec 13 '21 at 01:06
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 13 '21 at 09:49

score 0 · Accepted Answer · answered Dec 13 '21 at 01:46

I was able to figure out a solution by doing this:

<?php
$servername = "xxxt";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";
$command = $_POST['command'];
$text = $_POST['text'];
$id = $_POST['id'];
$disable = $_POST['disabled'];
$count = count($id);
for ($x = 0; $x <= $count; $x++) {
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE dashboard SET Command='$command[$x]', Text='$text[$x]', Disabled='$disable[$x]' WHERE Id='$id[$x]'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
  } else {
    echo "Error updating record: " . $conn->error;
  }

$conn->close();
}
header("Location: /");
?>

So i decided to do a for loop and count how many inputs i have, so for each input, it automatically creates a new query updating it.

score -1 · Answer 2 · edited Dec 13 '21 at 09:49

I made an example for you that I think works for you and it works now and I tried it

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "createde";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


// When you press the change button, it will completely change the data
        if(isset($_POST["sub"])){
            $command = $_POST['command'];
            $text =  $_POST['text'];
            $id = "1";
            $disable = "0";
        
            $sql="UPDATE deew SET Command='$command', Text='$text', Disabled='$disable' WHERE id='$id'";
            $conn->query($sql);
        
        
        }

$conn->close();

?>

The second page is where the values are entered

<?php
include("sql.php");
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>

<form action="#" method="POST">
<textarea name="command" id="command" cols="30" rows="10"></textarea>
<input type="text" name="text" >
<input type="submit" name="sub">

</form>
    
</body>
</html>

I know that your code calls all the data and determines through them what data you want to change

Thank you! I was able to figure out a different way but this may help someone else! I have posted my own answer already in case anyone else needs it as i have multiple forms on a single page. Although your answer assumes $id is always one. In my case, it wasn't. — Nikola Matić, Dec 13 '21 at 01:57
You're welcome, but I think you need to organize your code only in order — عبدالله مصطفي, Dec 13 '21 at 02:00
Yes. I will be doing that. My main priority was functionality. Now that i managed to get it to work, i can work on securing it, organizing it and more. — Nikola Matić, Dec 13 '21 at 02:05

How to update multiple rows in mysql and php with a different values using variables and different files?

2 Answers2