php - 选择多行的 ID 并将它们用于更新查询中的 WHERE IN 子句

Question

已经尝试了各种解决方案来解决我的问题以选择 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     |
+---+-------------+------------+----------------+------------+

所以，例如如果 teacher_id = '2' 且 subject_ids 为 '2' 和 '3' 的老师选择 request_id = '19' 的请求，则 request_id = '19' 和 subject_ids '2' 和 '3' 的行应该更新为 teacher_id '2' 和 status_id = '2'。

编辑:哦，当然还有我目前得到的错误(显然它不适用于当前 WHERE 子句中的数组):

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

我很感谢你的每一个建议。

Answer 1

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

$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 的参数 check it out!

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set