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

Question

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());
  }
}

That query is widely open for SQL injection. Why do you bind all but one user supplied variables? — Nico Haase
– Nico Haase, Commented Oct 8, 2021 at 14:51

JC Cavalca · Accepted Answer · 2021-10-08 14:33:45Z

2

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);

answered Oct 8, 2021 at 14:33

JC Cavalca

1448 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related