PHP MYSQL

STORE & RETRIEVE ARRAY IN DATABASE

PEOPLE TABLE ID | INT(11) PRIMARY NAME | VARCHAR(255) COLOR | VARCHAR(255)

ARRAY TO STRING (THE DATABASE)

01

ARRAY TO STRING $name = "John"; $colors = ["Red", "Green", "Blue"]; $colors =  json_encode($colors);

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

ARRAY TO STRING (STORE)

02

STORE INTO DATABASE $stmt = $pdo->prepare("INSERT INTO `people` (`name`, `color`) VALUES (?,?)"); $stmt->execute([$name, $colors]);

ARRAY TO STRING (STORE)

03

STRING TO ARRAY $colors = json_decode($row["color"]);

RETRIEVE FROM DATABASE $stmt = $pdo->prepare("SELECT * FROM `people`"); $stmt->execute(); $row = $stmt->fetch();

STRING TO ARRAY (RETRIEVE)

04

SEPARATE TABLE (THE DATABASE)

05

COLORS TABLE ID | INT(11) PRIMARY COLOR | VARCHAR(255) PRIMARY * EVERY COLOR WILL BE A SINGLE ENTRY

SQL INSERT QUERY $sql = "INSERT INTO `colors` (`id`, `color`) VALUES "; $data = []; foreach ($colors as $c) {   $sql .= "(?,?),"; $data[] = $id; $data[] = $c; } $sql = substr($sql, 0, -1) . ";";

ARRAY OF DATA $id = 123; $colors = ["Red", "Green", "Blue"];

06

SEPARATE TABLE (STORE)

RUN! $stmt = $pdo->prepare($sql); $stmt->execute($data);

07

SEPARATE TABLE (STORE)

08

SEPARATE TABLE (RETRIEVE)

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