Skip to content

Instantly share code, notes, and snippets.

@iwata
Last active October 29, 2018 06:14
Show Gist options
  • Save iwata/291e66e312f670ff7085 to your computer and use it in GitHub Desktop.
Save iwata/291e66e312f670ff7085 to your computer and use it in GitHub Desktop.
INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder', '12,34,banana');
SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contacts_per_product
FROM Products;
CREATE TABLE Contacts (
product_id BIGINT UNSIGNED NOT NULL,
account_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (product_id, account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id),
FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
INSERT INTO Contacts (product_id, account_id)
VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);
SELECT product_id, COUNT(*) AS accounts_per_product FROM Contacts
GROUP BY product_id;
SELECT account_id, COUNT(*) AS products_per_account FROM Contacts
GROUP BY account_id;
SELECT c.product_id, c.accounts_per_product FROM (
SELECT product_id, COUNT(*) AS accounts_per_product FROM Contacts
GROUP BY product_id
) AS c
HAVING c.accounts_per_product = MAX(c.accounts_per_product)
SELECT p.*
FROM Products AS p INNER JOIN Contacts AS c ON p.product_id = c.product_id WHERE c.account_id = 34;
SELECT a.*
FROM Accounts AS a INNER JOIN Contacts AS c ON a.account_id = c.account_id WHERE c.product_id = 123;
UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46' WHERE product_id = 123;
UPDATE Products SET account_id = '101418,222630,343842,467790' WHERE product_id = 123;
SELECT * FROM Products AS p INNER JOIN Accounts AS a
ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.product_id = 123;
<?php
$stmt = $pdo->query(
"SELECT account_id FROM Products WHERE product_id = 123");
$row = $stmt->fetch();
$contact_list = $row['account_id'];
// PHP コードでの list の変更
$value_to_remove = "34";
$contact_list = split(",", $contact_list);
$key_to_remove = array_search($value_to_remove, $contact_list);
unset($contact_list[$key_to_remove]);
$contact_list = join(",", $contact_list);
$stmt = $pdo->prepare(
"UPDATE Products SET account_id = ?
WHERE product_id = 123");
$stmt->execute(array($contact_list));
INSERT INTO Contacts (product_id, account_id) VALUES (456, 34);
DELETE FROM Contacts WHERE product_id = 456 AND account_id = 34;
UPDATE Products
SET account_id = account_id || ',' || 56 WHERE product_id = 123;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment