How to update multiple rows in mysql and php with a different values using variables and different f-CodePudding

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 ><input type="text" name="command[]" value="';
                  echo $row['Command'];
                  echo '" ></td>';
                  echo '<td ><input type="text" name="text[]" value="';
                  echo $row['Text'];
                  echo '" ></td>';
                  echo '<td ><select  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.

CodePudding user response：

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.

CodePudding user response：

Post the full code

Because I think that the variables that do not appear in Database, there is a problem in storing them

CodePudding user response：

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

I think this example will help you