如何使用PHP PDO通过数组元素方便地更新DB表中的3行？

Question

I’m learning PHP and PDO. A bit confused with updating 3 rows in database by elements of array. I have an array of data $dateArray[$index], obtained by fetching from DB and calculating with another data. So finally, this array contains elements in such form:

var_dump($dateArray[$index]);

outputs

string(10) "2018-07-24" string(10) "2018-07-11" string(10) "2018-07-23"

A DB table looks like this:

Now I need to write them to DB (UPDATE stored values) in 3 columns: day_remind1, day_remind2 and day_remind3 and then display in the HTML table.

I suppose I should:

1. Make PDO prepared statement with UPDATE query;

2. Organize loop of bindParam statements assigning elements of $dateArray[$index] to fields.

3. Include execute in this loop to alternately update values of DB fields.

So this is how I do:

  $id = $_POST['id'];
  $day = $dateArray[$index];
  $sql = "UPDATE birthday_table SET day_remind1=:day, day_remind2=:day, day_remind3=:day WHERE id=:id";
  $stmt3 = $pdo->prepare($sql);

  foreach ($day as $dates=>$v) {
    $stmt3->bindParam(':day_remind1', $day);
    $stmt3->bindParam(':day_remind2', $day);
    $stmt3->bindParam(':day_remind3', $day);
    $stmt3->bindParam(':id', $id);
    $id = $v['id'];
    $stmt3->execute();
  }
  $db_handle = new DBController();
  // use AJAX variables column, editval, id containig user input 
  $result = $db_handle->executeUpdate("UPDATE birthday_table SET " . $_POST["column"] . " = '".$_POST["editval"]."' WHERE  id=".$_POST["id"]);

`

This doesn’t work, any data updated in DB, any error seen in console. Need clarification on how to fix my loop to solve my task.

Answer 1

You didn't bind the query params correctly.

when you do $stmt3->bindParam(':day_remind1', $day);, that means you are trying to inject the $day variable in a placeholder called :day_remind1.

But your 3 placeholders in your query are all named :day. Then you should edit your query to $sql = "UPDATE birthday_table SET day_remind1=:day_remind1, day_remind2=:day_remind2, day_remind3=:day_remind3 WHERE id=:id";

and remove the : when biding params:

$stmt3->bindParam('day_remind1', $day);
$stmt3->bindParam('day_remind2', $day);
$stmt3->bindParam('day_remind3', $day);
$stmt3->bindParam('id', $id);