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

Issue

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.

Solution

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.

Answered By - Nikola Matić

Answer Checked By - Marie Seifert (PHPFixing Admin)