0

I'm running a conditional statement to return two different sets of data depending on whether certain ids are present.

$emailContent = $emailDataResource->getEmailResourceIDs($date, $country_id, $category_id);
    foreach($emailContent as $content) {
        $edrIds[] = $content;
    }
$a = array_fill(0, count($edrIds), '?');
    $b = implode(', ', $a);
    $db = DB::getInstance();
    // get the resources
    if(count($countryId) == 1) {
        $query = "
        SELECT
            edr.id AS data_id, r.id, r.title, r.friendly_url, edr.position
        FROM
            `twinkl_email_data_resource` edr
        JOIN
            `twinkl_resource` r ON r.id = edr.resource_id 
        WHERE
            edr.`date` = ?
            AND edr.`country_id` = ?
            AND edr.`category_id` = ?
            AND edr.id NOT IN ($b)";
        $params = [$date, $country_id, $category_id, implode(',', $edrIds)];
        $result = $db->query($query, $params);
    } else if (count($countryId) > 1) {
            $query = "
        SELECT
            edr.id AS data_id, r.id, r.title, r.friendly_url, edr.position
        FROM
            `twinkl_email_data_resource` edr
        JOIN
            `twinkl_resource` r ON r.id = edr.resource_id 
        WHERE edr.id IN ($b)";
            $params = [implode(',', $edrIds)];
            $result = $db->query($query, $params);
    }

Introducing multiple placeholders before the array of ids can be generated and passed in halts the running of the page. This means I can't make all the selections necessary to pass in all the parameters (country_id and category_id) to find the ids.

anon
  • 3
  • 2
  • Are you getting any errors at all? – Jay Blanchard Aug 19 '20 at 15:30
  • 2
    You cannot do IN clauses with 1 placeholder. If this is MySQL have a look at https://stackoverflow.com/questions/3703180/a-prepared-statement-where-in-query-and-sorting-with-mysql. I'm not sure about SQL Server (as tagged). – Nigel Ren Aug 19 '20 at 15:33
  • 1
    Your `IN` might as well be an `=` there; it's expecting an scalar value. I suspect you think that `SomeColumn IN ('a,delimited,string')` would returns rows where a column has the value `'a', `'delimited'` or `'string'`, where as it would for a row where the column has the value `'a,delimited,string'`. Ergo, it would be equivilent to `SomeColumn = 'a,delimited,string'`. – Thom A Aug 19 '20 at 15:33
  • 1
    Your IN clause needs as many `?` placeholders as there are entries in the `$erIds` array. e.g. if the array contains two items then the IN clause would need to be `IN (?, ?)`. That's because you have two distinct values you want to test for. Therefore you need to write some code which will check the length of the array, and generate the correct SQL string accordingly. At the moment only the first value in the array is being considered in the query, because you only added a placeholder for one value. – ADyson Aug 19 '20 at 16:00
  • @ADyson that makes sense with the current results. So does the SQL read the imploded array as one item then? And how can I generate a certain number of placeholders? – anon Aug 20 '20 at 08:54
  • "does the SQL read the imploded array as one item then"...no, it does what I said it does - it only reads the first item and ignores the rest, because there's only one placeholder `?`. As another example, if you have 4 `?` placeholders and 6 array items, then the last 2 array items will be ignored. – ADyson Aug 20 '20 at 09:04
  • "how can I generate a certain number of placeholders"...again do what I said - check the length of the array and create the right number of placeholders based on that. (Hint: use a loop to append to your SQL string). – ADyson Aug 20 '20 at 09:05
  • if you really can't get that to work then add your attempt at coding it to the question then I can see if there's something you've done wrong. – ADyson Aug 20 '20 at 09:31
  • @ADyson Updated with what I've tried to do. Tried my best to explain what's happening. – anon Aug 20 '20 at 09:52
  • ok so you mention still can't do something. But what _specifically_ is the problem? Have you debugged this to work out what errors / unexpected behaviour you've got? – ADyson Aug 20 '20 at 10:23
  • P.S. I just noticed that `implode(',', $edrIds)` inside `$params` makes no sense. $params should be an array, but implode returns a string. If you want to join the $erIds array to the $params array then use array_merge: https://www.php.net/manual/en/function.array-merge.php – ADyson Aug 20 '20 at 10:27

0 Answers0