具有多个更新准备语句的PDO无法执行

Question

I am trying to construct a PDO multiple prepared statement that updates 2 tables.

I am having trouble and getting various errors when I try to get my code working, the latest: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

When looking on SO I have found various ways of doing this, however I can't figure out how to implement them.

Another concern is not being able use $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);. I've read online that is import to include for security. However I also understand that it doesn't work with Multi Queries PDO (?).

It is possible to do a Multi Update or should I have different scripts? (Also is my code secure from SQL Injection?)

This is the Code I am working with:

<?php
try {
$conn = new PDO('mysql:host=localhost;dbname=*', '*', '*');
$conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


$est_id = $_POST['est_id'];
$known_for = $_POST['known_for'];
$street_address = 'addressToAdd';

$sql = " UPDATE `theList`
 SET `known_for` = :known_for
 WHERE `id` = :est_id
    ";

$sql = " UPDATE `est_address`
  SET `street_address` = :street_address
  WHERE `id` = :est_id
     ";

 $params = array(
    ':est_id' => $est_id,
    ':known_for' => $known_for,
    ':street_address' => $street_address
);


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

  $statement->execute($params);


  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Answer 1

<?php
try {
$conn = new PDO('mysql:host=localhost;dbname=*', '*', '*');
$conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$est_id = $_POST['est_id'];
$known_for = $_POST['known_for'];
$street_address = 'addressToAdd';

$sqlKnown = "UPDATE `theList` SET `known_for` = :known_for WHERE `id` = :est_id";
$stKnown = $conn->prepare( $sqlKnown );
$stKnown->execute([
  ':est_id' => $est_id,
  ':known_for' => $known_for,
]);

$sqlStreet = "UPDATE `est_address` SET `street_address` = :street_address WHERE `id` = :est_id";
$stStreet = $conn->prepare( $sqlStreet );
$stStreet->execute([
  ':est_id' => $est_id,
  ':street_address' => $street_address
]);

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>