THE DATABASE TABLE

In this example, we will be creating a simple people table and their favorite colors.

CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `fav_color` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `people`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);

ALTER TABLE `people`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

THE CONFIG FILE

Next, we create a config file to store all the settings and stuff – Please remember to change the database settings to your own.

DATABASE LIBRARY

pdo = new PDO(
        $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
        ]
      );
      return true;
    }

    // ERROR - DO SOMETHING HERE
    // THROW ERROR MESSAGE OR SOMETHING
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct() {
  // __destruct() : close connection when done

    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  function start() {
  // start() : auto-commit off

    $this->pdo->beginTransaction();
  }

  function end($commit=1) {
  // end() : commit or roll back?

    if ($commit) { $this->pdo->commit(); }
    else { $this->pdo->rollBack(); }
  }

  function exec($sql, $data=null) {
  // exec() : run insert, replace, update, delete query
  // PARAM $sql : SQL query
  //       $data : array of data

    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      $this->lastID = $this->pdo->lastInsertId();
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return true;
  }

  function fetch($sql, $cond=null, $key=null, $value=null, $col=null) {
  // fetch() : perform select query
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $key : sort in this $key=>data order, optional
  //       $value : $key must be provided, sort in $key=>$value order
  //       $col : get a "flat" array with data from this column

    $result = false;
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      if (isset($key)) {
        $result = array();
        if (isset($value)) {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row[$value];
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row;
          }
        }
      } else if (isset($col)) {
        while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
          $result[] = $row[$col];
        }
      } else {
        $result = $this->stmt->fetchAll();
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return $result;
  }
}
?>

Moving on, we create a database library, so we don’t have to repeat the same pieces of database code…

STORING DATA

exec($sql, $data) ? "OK" : $libDB->error;

Now that we have all the groundworks ready, all that is left is to write the SQL and push the data in – Take note that we use json_encode here to “convert” the array into a string.

RETRIEVING DATA

fetch($sql, ["[email protected]"]);

// THE RETRIEVED PERSON
if (count($person)==1) {
  $person = $person[0];
  print_r($person);
  echo "


";

  // We can get back the favorite colors with JSON decode
  $colors = json_decode($person['fav_color']);
  print_r($colors);
}

To retrieve the array back, we simply do the opposite of using json_decode.

THE DATABASE TABLE

CREATE TABLE `fav_color` (
  `id` int(11) NOT NULL,
  `color` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `fav_color`
  ADD PRIMARY KEY (`id`,`color`);

Yep, we are creating another table here to hold the favorite colors one-by-one.

STORING DATA

start();

// CREATE MAIN ENTRY FIRST
$sql = "INSERT INTO `people` (`name`, `email`) VALUES (?, ?)";
$data = [$name, $email];
$pass = $libDB->exec($sql, $data);

// CREATE COLOR ENTRIES
if ($pass) {
  $id = $libDB->lastID;
  $sql = "INSERT INTO `fav_color` (`id`, `color`) VALUES ";
  $data = [];
  foreach ($colors as $c) {
    $sql .= "(?, ?),";
    $data[] = $id;
    $data[] = $c;
  }
  $sql = substr($sql, 0, -1) . ";";
  $pass = $libDB->exec($sql, $data);
}

// END - COMMIT OR ROLLBACK
$libDB->end($pass);
echo $pass ? "OK" : $libDB->error ;

Storing the data becomes a little more tedious now, but we can simply loop through the array to create the entries and the SQL.

RETRIEVING THE ARRAY

fetch($sql, ["[email protected]"]);

// THE RETRIEVED PERSON
if (count($person)==1) {
  $person = $person[0];
  print_r($person);
  echo "


";

  // GET FAVORITE COLORS
  $sql = "SELECT * FROM `fav_color` WHERE `id`=?";
  $colors = $libDB->fetch($sql, [$person['id']], null, null, "color");
  print_r($colors);
}

There is no need for any decoding here – Just pull all the data back out from the database.

WHY THIS METHOD MAKES SENSE

Some of you guys may be thinking that this method is totally dumb, roundabout, and wastes more system resources. Well, no, and let us walk through why this method is preferred in most cases. Let us create some more dummy data entries first.

 "Joe Doe", "email" => "[email protected]", "col" => ["red", "blue"]],
    ["name" => "Jin Doe", "email" => "[email protected]", "col" => ["blue", "cyan"]],
    ["name" => "Jon Doe", "email" => "[email protected]", "col" => ["yellow", "blue"]],
    ["name" => "Jen Doe", "email" => "[email protected]", "col" => ["blue", "magenta"]],
    ["name" => "Jan Doe", "email" => "[email protected]", "col" => ["cyan", "yellow"]]
];

// DATABASE ACTION
$libDB->start();
foreach ($people as $p) {
  $sql = "INSERT INTO `people` (`name`, `email`) VALUES (?, ?)";
  $data = [$p['name'], $p['email']];
  $pass = $libDB->exec($sql, $data);
  if ($pass) {
    $id = $libDB->lastID;
    $sql = "INSERT INTO `fav_color` (`id`, `color`) VALUES ";
    $data = [];
    foreach ($p['col'] as $c) {
      $sql .= "(?, ?),";
      $data[] = $id;
      $data[] = $c;
    }
    $sql = substr($sql, 0, -1) . ";";
    $pass = $libDB->exec($sql, $data);
  }
}
$libDB->end($pass);
echo $pass ? "OK" : $libDB->error ;
echo "

";

With that, let us now do some data mining and find out which are the most and least common favorite colors.

fetch($sql, null, "color", "count");
print_r($data);

For those of you who don’t catch the drift – It is impossible or very difficult to do this with JSON encoded strings. The value of creating a separate table is not in the convenience of coding, but the power to do data analysis. So always weigh your pro and cons carefully, that laziness to cut a few lines of code comes at the cost of losing the ability to do proper studies.

JSON

Just what the heck is JSON? Find out in my other guide.

Simple PHP JSON For Beginners (Examples and Tutorial)

SOURCE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
  

The post How to Store Arrays In MYSQL With PHP appeared first on Code Boxx.