MYSQL - How do I use a SELECT as my WHERE

Question

I have two tables recordings and recording_metas and I want to select some metas and use the foreign keys from those metas to find the corresponding recording. This is what I have so far. $recording_sql outputs this

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 ?

But why do I get the following error?

Fatal error:  Uncaught Error: Call to a member function bind_param() on bool

at this line $stmt->bind_param(str_repeat('s', count($mixed)), ...$mixed); I'm sure that $meta_sql works because I tested it separately with the LIMIT pointer in there aswell so the pointers are working fine if I use them on $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";
    }
}

it suggests that the `prepared statement` failed - fork the logic to test for `if ( $stmt ){GOOD} else{BAD}` — Professor Abronsius, Dec 05 '19 at 20:05
do it right after `$stmt = $connection->prepare($recording_sql);` — Anatoliy R, Dec 05 '19 at 20:08
so something is wrong, actually user nbk already responded with the issue you have, select statement must match your where clause — Anatoliy R, Dec 05 '19 at 20:11
@MikeOttink did you see my answer to your [previous question](https://stackoverflow.com/questions/59186340/select-a-recording-based-on-the-meta)? — Nick, Dec 05 '19 at 20:21

score 2 · Accepted Answer · answered Dec 05 '19 at 20:09

2

you have to select only one columns in your where clause

WHERE recording_metas.recording_id IN 
  (SELECT recording_id FROM recording_metas WHERE meta_key=? AND meta_value=?) LIMIT ?

If you want to select more than one column you have to use tupels like shown here https://stackoverflow.com/a/44706402/5193536

answered Dec 05 '19 at 20:09

nbk

45,398
8
30
47

Yes this is the correct answer, you my good sir are a smart man! – anonymous-dev Dec 05 '19 at 20:20

MYSQL - How do I use a SELECT as my WHERE

1 Answers1