根据中继选择录音 [英] Select a recording based on the meta

问题描述

我有下表

元表

id | recording_id | meta_key | meta_value

记录表

id | recording

meta_table上的recording_id是指向记录表上的记录的外键.

The recording_id on the meta_table is a foreign key that points towards a recording on the recording table.

现在，我有一个来自$_GET的关联数组$metas，其中包含元键和值，并且我想SELECT与所有元键和值匹配的记录.我该怎么办?

Now I have a associative array $metas from my $_GET with meta keys and values and I want to SELECT the recordings that match all the meta keys and values. How would I do that?

这是我到目前为止所拥有的.如何将数组添加到绑定参数中?而且我在正确的轨道上吗?

This is what I have so far. How do I add an array into my bind param? And am I on the right track?

我只是无法解决这个问题.

I just cannot wrap my head around this.

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);

    $sql = "SELECT recording_id 
    FROM $config->meta_table 
    WHERE " . $where . " 
    INNER JOIN $config->recording_table 
    ON $config->meta_table.id=$config->recording_table.id 
    LIMIT ?";

    $stmt = $connection->prepare($sql);

    foreach ($metas as $key => $value) {
        $stmt->bind_param("s", $key);
        $stmt->bind_param("s", $value);
    }
    $stmt->bind_param("i", $limit);

    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";
            //$recording = $row["recording"];
            //$hex = bin2hex($recording);
            //echo ("response=recording" . $id . "=" . $hex . "\r\n");
        }
    } else {
        echo "0 results \r\n";
    }
}

推荐答案

要仅使用SQL编写此查询，您将编写类似

To write this query just in SQL, you would write something like

SELECT r.id
FROM recording r
JOIN meta m ON m.recording_id = r.id
           AND (m.meta_key = 'key1' AND m.meta_value = 'value1'
             OR m.meta_key = 'key2' AND m.meta_value = 'value2'
             OR m.meta_key = 'key3' AND m.meta_value = 'value3'
                ...)
GROUP BY r.id
HAVING COUNT(*) = <count of all key/value pairs>
LIMIT 10

HAVING子句断言记录具有所有指定的元键和值对.

The HAVING clause is what asserts that a recording has all of the specified meta key and value pairs.

要将其转换为PHP代码，您需要以类似的方式构建$where子句；我更喜欢使用数组和内爆来省去后跟AND之类的麻烦.在构建该子句的同时，我们可以构建对bind_param的输入:

To translate that into your PHP code, you need to build up your $where clause in a similar manner; I prefer using an array and imploding to save worrying about trailing AND and the like. At the same time as we are building that clause, we can build the inputs to bind_param:

$join = array();
$params = array();
$types = '';
foreach ($metas as $key => $value) {
    $join[] = 'm.meta_key=? AND m.meta_value=?';
    $params[] = $key;
    $params[] = $value;
    $types .= 'ss';
}
// add the parameter for the `HAVING` check
$params[] = count($metas);
$types .= 'i';
// add the limit
$params[] = $limit;
$types .= 'i';
// make the query string
$sql = "SELECT recording_id 
        FROM {$config->recording_table} r
        JOIN {$config->meta_table} m ON m.recording_id = r.id
         AND (" . implode(' OR ', $join) . ")
        GROUP BY r.id
        HAVING COUNT(*) = ?
        LIMIT ?";
$stmt = $connection->prepare($sql);
if (!$stmt) {
    echo "Prepare failed: " . $conn->error . "\n";
    die();
}
$stmt->bind_param($types, ...$params);
if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error . " \r\n";
    die();
}

可以在此处找到有关查询形成和参数生成的演示.

A demo of the query formation and params generation can be found here.

这篇关于根据中继选择录音的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！