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();