MYSQL - 如何使用 SELECT 作为我的 WHERE
我有两个表recordings，recording_metas我想选择一些元数据并使用这些元数据中的外键来查找相应的记录。这就是我到目前为止所拥有的。$recording_sql输出这个
SELECT recording FROM recordings
INNER JOIN recording_metas
ON recordings.id = recording_metas.recording_id
WHERE recording_metas.recording_id IN (SELECT recording_id, meta_key, meta_value FROM recording_metas WHERE meta_key=? AND meta_value=?) LIMIT ?
但是为什么我会收到以下错误？
Fatal error: Uncaught Error: Call to a member function bind_param() on bool
在这一行$stmt->bind_param(str_repeat('s', count($mixed)), ...$mixed);，我确信它$meta_sql可以工作，因为我也用 LIMIT 指针单独测试了它，所以如果我使用它们，指针工作正常$meta_sql
PHP
function retrieveRecordingsByMetaData($connection, $config, $metas, $limit)
{
$where = "";
for ($i = 0; $i < count($metas); $i++) {
$where .= "meta_key=? AND meta_value=? AND ";
}
$where = preg_replace('/ AND $/', '', $where);
$meta_sql = "SELECT recording_id, meta_key, meta_value FROM $config->meta_table WHERE $where";
$recording_sql = "SELECT recording FROM $config->recording_table
INNER JOIN $config->meta_table
ON $config->recording_table.id = $config->meta_table.recording_id
WHERE $config->meta_table.recording_id IN ($meta_sql) LIMIT ?";
echo ($recording_sql);
$stmt = $connection->prepare($recording_sql);
$mixed = associativeToArrayMixed($metas);
array_push($mixed, $limit);
$stmt->bind_param(str_repeat('s', count($mixed)), ...$mixed);
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error . " \r\n";
die();
}
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "recording found";
}
} else {
echo "0 results \r\n";
}
}
POPMUISE1回答
-
婷婷同学_
您必须在 where 子句中只选择一列WHERE recording_metas.recording_id IN (SELECT recording_id FROM recording_metas WHERE meta_key=? AND meta_value=?) LIMIT ?如果您想选择多于一列，则必须使用此处所示的元组 https://stackoverflow.com/a/44706402/5193536
随时随地看视频慕课网APP
PHP