选择多行'；id，并将它们用于更新查询中的WHERE IN子句 - Select multiple rows' ids and use them for the WHERE IN clause in an update query

已经尝试了各种解决方案来解决我选择ID（从多行中）的问题，然后这些ID用于更新与这些ID匹配的特定行。

我目前已经部署了以下内容。首先是ID的简单SELECT查询，然后用于下一个UPDATE查询中的WHERE子句：

$SelectTSubjectsQuery = "
SELECT subject_id FROM teachers_subjects
WHERE teacher_id = :teacher_id";
$statement = $pdo->prepare($SelectTSubjectsQuery);
$statement->bindParam(':teacher_id', $_SESSION['teacher_id']);
$statement->execute();
$FetchedTSubjects = Array();
$FetchedTSubjects = $statement->fetchAll();

现在是UPDATE查询，它应该更新表中与请求id和前面选择的subject_id:匹配的相应行的teacher_id和status_id

$StatusUpdateQuery = "UPDATE requests_subjects SET teacher_id = :teacher_id, status_id = '2' WHERE request_id = :request_id AND requests_subjects.subject_id IN (".implode(',', $FetchedTSubjects).")";
$statement = $pdo->prepare($StatusUpdateQuery);
$statement->bindParam(':request_id', $_GET['id']);
$statement->bindParam(':teacher_id', $_SESSION['teacher_id']);
$statement->execute();

我的requests_subjects表应该更新，它看起来像：

+---+-------------+------------+----------------+------------+
|id | request_id  | subject_id |   teacher_id   |  status_id |
+---+-------------+------------+----------------+------------+
| 1 |     19      |      1     |       0        |      1     |
| 2 |     19      |      2     |       0        |      1     |
| 2 |     19      |      3     |       0        |      1     |
| 2 |     20      |      1     |       4        |      3     |
| 2 |     21      |      1     |       5        |      3     |
+---+-------------+------------+----------------+------------+

因此，例如，如果教师id为'2'，subject_id为'2''和'3'的教师选择了request_id为'19'的请求，则应使用教师id为2'和status_id='2'更新请求id为'19'和subject_id'为2'的行。

edit：哦，当然还有我现在得到的错误（显然它不适用于当前WHERE子句中的Array）：

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause'' in /var/www/xxx/html/teacher.php:227 Stack trace: #0 /var/www/xxx/html/teacher.php(227): PDOStatement->execute() #1 {main} thrown in /var/www/xxx/html/teacher.php on line 227

我感谢你的每一个建议。

问题是，您需要正确地将数据放在数组中。。。

$SelectTSubjectsQuery = "SELECT subject_id FROM teachers_subjects WHERE teacher_id = :teacher_id";
$statement = $pdo->prepare($SelectTSubjectsQuery);
$statement->bindParam(':teacher_id', $_SESSION['teacher_id']);
$statement->execute();
$FetchedTSubjects = Array();
$FetchedTSubjects = $statement->fetchAll()

从您的代码中，如果您print_r($FetchedTSubjects)将获得以下结果或类似结果：

Array
(
    [0] => Array
        (
            [subject_id] => 1
            [0] => 1
        )
    [1] => Array
        (
            [subject_id] => 2
            [0] => 2
        )
)

如果你尝试implode(',',$FetchedTSubjects)，你实际上是在尝试将数组转换为字符串，这就是的意义

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause''

您需要执行以下操作：

$SelectTSubjectsQuery = "SELECT subject_id FROM teachers_subjects WHERE teacher_id = :teacher_id";
$statement = $pdo->prepare($SelectTSubjectsQuery);
$statement->bindParam(':teacher_id', $_SESSION['teacher_id']);
$statement->execute();
$FetchedTSubjects = Array();
// iterate all the rows to get the subject_id of each and put in an independent array
foreach($statement->fetchAll('PDO::FETCH_ASSOC) as $subject) {
    array_push($FetchedTSubjects,$subject['subject_id']);
}

现在，如果您print_r($FetchedTSubjects)，您将得到以下输出：

Array
(
    [0] => 1
    [1] => 2
)

你现在可以成功地使用implode(',', $FetchedTSubjects)，它将按照你的期望返回（1,2）

注意：'PDO::FETCH_ASSOC是PDOStatement::fetchAll和PDOStatement::fetch的参数，请检查！

PDO:：FETCH_ASSOC:返回按结果集中返回的列名索引的数组

我相信您不需要在这里指定表名

requests_subjects.subject_id IN

只需将其更改为

subject_id IN

尝试一下。