How can I use multiple for loops to create an sql statement in php

I am working on a website where I can accept user data through multiple different forms to update tables in a database and instead of writing separate functions for each I thought it would be a good idea to use string concatenation and for loops to write the SQL statements for me. Basically it takes in 4 parameters a table, id, the columns that need to be updated (params) and an array of user input. I believe that I am pretty close to what I need but it fails to execute and gives me an error of

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(name,comment) VALUES ( ‘sadf’,’asdf’) WHERE music_work_ID=14′ at line 1

This is what is displayed when I print out $sql,

sql = UPDATE music_work SET (name,comment) VALUES ( :name,:comment) WHERE music_work_ID=14

I don’t know if theres a better way of creating something like this or if i’m not using the correct syntax but for now I am at a deadlock.

function music_work_update($userInput, $userID_selection){
  foreach ($userInput as $k => $v) {
    if($userInput[$k] === '') {
      unset($userInput[$k]);
    }
  }
  update("music_work", $userID_selection, ["name", "comment"], $userInput);
}

function update($table, $userID_selection, $params, $input){
  $conn = connect();
  try {
    $sql = "UPDATE ".$table." SET (";
    for ($i = 0; $i < sizeof($params); $i++) {
      $sql .= "`".$params[$i]."`,";
    }
    $sql = substr_replace($sql ,"", -1); 
    $sql .= ") VALUES ( ";
    
    for ($i = 0; $i < sizeof($params); $i++) {
      $sql .= ":".$params[$i].",";
    }
    $sql = substr_replace($sql ,"", -1); 
    $sql .= ") WHERE `music_work_ID`=$userID_selection";
    echo ("sql = $sql <br>");

    $command = $conn->prepare($sql);

    for ($i = 0; $i < sizeof($params); $i++) {
      $command->bindParam(':'.$params[$i], $input[$params[$i]], PDO::PARAM_STR);
    }


    if ($command->execute()) {
      echo "<script type= 'text/javascript'>alert('New Record Inserted Successfully');</script>";
    }
    else{
      echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";
    }
    echo "failed before execute";
    $conn = null;
  }
  catch(PDOException $e)
  {
    echo ($e->getMessage());
  }
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You confuse multiple INSERT INTO and multiple UPDATE.

For multiple UPDATE use:

UPDATE music_work SET name = x, comment = y WHERE id = z;

For multiple INSERT INTO:

INSERT INTO table_name (column_list)
VALUES
  (value_list_1),
  (value_list_2);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0