PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, username VARCHAR(100) UNIQUE NOT NULL ); CREATE TABLE contacts ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, contact_name VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE contact_phone_numbers ( id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, phone_number VARCHAR(20) NOT NULL ); CREATE TABLE contact_emails ( id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, email VARCHAR(100) NOT NULL ); INSERT INTO users (id, phone, email, username) VALUES (1, 'phone222', 'email', 'username'); INSERT INTO contacts (user_id, contact_name) VALUES (1, 'John Doe'); INSERT INTO contact_phone_numbers (contact_id, phone_number) VALUES (LAST_INSERT_ID(), '1234567890'); INSERT INTO contact_phone_numbers (contact_id, phone_number) VALUES (LAST_INSERT_ID(), '0987654321'); INSERT INTO contact_emails (contact_id, email) VALUES (LAST_INSERT_ID(), 'john.doe@example.com'); INSERT INTO contact_emails (contact_id, email) VALUES (LAST_INSERT_ID(), 'johndoe@example.net'); select * from users;
Copy Clear
Copy Format Clear
<?php $db = $pdo; $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $phoneNumbers = ['1234567890']; $emails = ['john.doe@example.com']; $phonePlaceholders = implode(', ', array_fill(0, count(['1234567890']), '?')); $emailPlaceholders = implode(', ', array_fill(0, count(['john.doe@example.com']), '?')); $query = " SELECT DISTINCT u.* FROM users u JOIN contacts c ON u.id = c.user_id LEFT JOIN contact_phone_numbers cpn ON c.id = cpn.contact_id LEFT JOIN contact_emails ce ON c.id = ce.contact_id WHERE cpn.phone_number IN ($phonePlaceholders) OR ce.email IN ($emailPlaceholders) "; $stmt = $db->prepare($query); $params = array_merge($phoneNumbers, $emails); $stmt->execute($params); print($stmt->fetchAll(PDO::FETCH_ASSOC));
Show:  
Copy Clear