PHP MYSQL

STORE & RETRIEVE ARRAY IN DATABASE

(simple examples)

FAV COLORS TABLE  id | bigint(20) PRIMARY KEY name | varchar(255) colors | JSON

JSON STRING (A)

01

CONNECT TO DATABASE $pdo = new PDO("mysql:host=HOST; dbname=NAME;charset=utf8mb4", USER, PASSWORD);

STORE (ARRAY TO STRING) $colors = json_encode(["Red", "Blue"]); $stmt = $pdo->prepare("INSERT INTO `people` (`id`, `name`, `colors`) VALUES (?,?,?)"; $stmt->execute([99, "Jon", $colors]);

JSON  STRING (B)

02

RETRIEVE (STRING TO ARRAY) $stmt = $pdo->prepare("SELECT `colors` FROM `people` WHERE `id`=?"); $stmt->execute([99]); $colors = json_decode($stmt->fetchColumn());

JSON  STRING (C)

03

SEPARATE TABLE (A)

04

FAV COLORS TABLE id | bigint(20) PRIMARY KEY color | varchar(255) * EVERY COLOR WILL BE A SINGLE ENTRY

SEPARATE TABLE (B)

05

STORE $sql = "INSERT INTO `colors` (`id`, `color`) VALUES (?,?), (?,?), (?,?);"; $data = [99, "Red", 99, "Green", 99, "Blue"]; $stmt = $pdo->prepare($sql); $stmt->execute($data);

SEPARATE TABLE (C)

06

RETRIEVE $stmt = $pdo->prepare("SELECT `color` FROM `colors` WHERE `id`=?"); $stmt->execute([99]); $colors = $stmt->fetchAll();