PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE `pivot` ( id INT AUTO_INCREMENT, table_1_id INT, table_2_id INT, is_active INT, PRIMARY KEY (id) ); INSERT INTO `pivot` (id, table_1_id, table_2_id, is_active) VALUES (1, 5, 1, 1), (2, 5, 2, 1), (3, 5, 3, 1), (4, 1, 1, 1), (5, 3, 1, 1), (6, 3, 2, 1);
Copy Clear
Copy Format Clear
<?php // $mysqli is already declared as a working database connection by this sandbox $table1Ids = [5, 5, 1, 3, 3]; $table2Ids = [2, 3, 1, 1, 2]; // assumed to always have identical length as $table1Ids $zipFlattened = []; foreach ($table1Ids as $index => $zipFlattened[]) { $zipFlattened[] = $table2Ids[$index]; } // $zipFlattened = [5, 2, 5, 3, 1, 1, 3, 1, 3, 2] $uniqueIds2 = array_unique($table2Ids); $count1 = count($table1Ids); $count2 = count($uniqueIds2); $placeholders1 = implode(',', array_fill(0, $count1, '(?,?)')); $placeholders2 = implode(',', array_fill(0, $count2, '?')); $sql = <<<SQL SELECT * FROM `pivot` WHERE (table_1_id, table_2_id) NOT IN ($placeholders1) AND table_2_id IN ($placeholders2) AND is_active = 1 SQL; $stmt = $pdo->prepare($sql); $stmt->execute(array_merge($zipFlattened, $uniqueIds2)); echo json_encode($stmt->fetchAll(), JSON_PRETTY_PRINT);
Show:  
Copy Clear