sakura.ioのOutgoing WebhookでMySQLデータベースにデータを保存

やったこと

施設への人の出入りをカウントする装置を製作して
そのデータをsakura.ioのLTEモジュールを使って、自分のサーバーのMySQLデータベースに書き込む
(装置は出入り口2ヶ所に設置)

環境(ハードウェア) 2セット

• sakura.io LTEモジュール
• sakura.io Arduino用シールド
• Arduino UNO ボード
• 光電管(光センサー)

環境(サーバー)

• さくらのVPS
• Apache(2.4.28)
• MySQL(5.6.37)
• PHP(5.6.31)

sakura.io の Outgoing Webhookの設定

sakura.io のコントロールパネルにログインして サービス追加 ボタンをクリックします

追加サービスの選択で　Outgoing Webhook を選択します

Outgoing Webhook設定画面で
名前　何でも構いません
Payload URL　データを受け取るスクリプトのURL
を設定して　作成　ボタンを押します
(Secretは今回使わないので空白にします)

作成 ボタンを押すとトップ画面に戻るので 今回 追加して Outgoing Webhook サービスが追加されているかを確認してください

以上で sakura.io での設定は終わりです。

sakura.io から 送られるデータの構造

今回作ったシステムでは 以下のデータを各チャンネルに設定しています
(型はすべて "i"　データは全て "1"　です)
ch0 出入り口1 の in
ch1 出入り口1 の out
ch6 出入り口2 の in
ch7 出入り口2 の out

ペイロードデータは以下のようになっています

MySQL データベースの作成

自分は以下の構造でデータベースを作成しました
当然ですがご自身の取得しているデータに合わせてデータベースを作成してください。

counter_test.sql

-- phpMyAdmin SQL Dump
-- version 4.7.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: 2017 年 10 月 23 日 18:09
-- サーバのバージョン： 5.6.37
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `????????????????????`
--

-- --------------------------------------------------------

--
-- テーブルの構造 `counter_test`
--

CREATE TABLE `counter_test` (
  `fgn_id` bigint(20) UNSIGNED NOT NULL,
  `fgn_channel` tinyint(3) UNSIGNED NOT NULL,
  `fgn_type` varchar(1) NOT NULL,
  `fgn_value` varchar(10) NOT NULL,
  `fgn_datetime` varchar(30) NOT NULL,
  `fgn_write_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `counter_test`
--
ALTER TABLE `counter_test`
  ADD PRIMARY KEY (`fgn_id`),
  ADD KEY `fgn_channel` (`fgn_channel`),
  ADD KEY `fgn_write_datetime` (`fgn_write_datetime`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `counter_test`
--
ALTER TABLE `counter_test`
  MODIFY `fgn_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

PHPプログラムの作成

Outgoing Webhook設定画面の　Payload URL で設定したアドレスと同じ名前のPHPファイルを作成します。
処理の内容は　コメント から読み取れると思いますので別途解説は省きます

data_store.php

<?php
// エラー出力する場合(デバッグ時には有効にしておくとデバッグが楽です)
error_reporting(E_ALL);
ini_set('display_errors', '1');


$secret = "";    // Secret key は今回使っていません。

// json data の取得
$json = file_get_contents('php://input');

// 取り込んだ json data のデコード
$object = json_decode($json, true);

if (!empty($secret)) {
    $hash = hash_hmac('sha1', $json, $secret);
    if ($hash != $_SERVER["HTTP_X_SAKURA_SIGNATURE"]) exit;
}

// json data のパース
$module   = $object['module'];
$type     = $object['type'] ;
$datetime = $object['datetime'];
$payload_channel  = $object['payload']['channels'][0]['channel'];
$payload_type     = $object['payload']['channels'][0]['type'];
$payload_value    = $object['payload']['channels'][0]['value'];
$payload_datetime = $object['payload']['channels'][0]['datetime'];

// ログ書き込みはプログラムがバックエンドで処理されるためにデータを正常に受け取っているのが確認出来たら不要です
// ログ書き込み用データ作成
$str  = "module : ".$module."\n";
$str .= "type : ".$type."\n";
$str .= "datetime : ".$datetime."\n";
$str .= "payload_channel : ".$payload_channel."\n";
$str .= "payload_type : ".$payload_type."\n";
$str .= "payload_value : ".$payload_value."\n";
$str .= "payload_datetime : ".$payload_datetime."\n";

// ログ書き込み
$fp = fopen("data/data.txt", "a"); // dataフォルダのパーミッション は777(755)
fwrite($fp, $str.'\n');
fclose($fp);

// MySQLへの書き込み処理
$dsn = 'mysql:host=localhost;dbname=fukuokad_sakuraio;charset=utf8';
try {
$dbh = new PDO($dsn,'データベースユーザー名','パスワード',          // ご自身のデータベースに合わせてください。
array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e) {
 exit('データベース接続失敗。'.$e->getMessage());
}


$stmt = $dbh -> prepare("INSERT INTO counter_test (fgn_channel, fgn_type, fgn_value, fgn_datetime, fgn_write_datetime) VALUES (:fgn_channel, :fgn_type, :fgn_value, :fgn_datetime, now())");
$stmt->bindParam(':fgn_channel',   $payload_channel, PDO::PARAM_INT);
$stmt->bindParam(':fgn_type',      $payload_type, PDO::PARAM_STR);
$stmt->bindParam(':fgn_value',     $payload_value, PDO::PARAM_STR);
$stmt->bindParam(':fgn_datetime',  $payload_datetime, PDO::PARAM_STR);

$stmt->execute();

// このプログラムはバックエンドで処理されるので、エラーが発生していないかブラウザで直打ちして確認する場合は以下のecho文を有効にする
echo "END<br>\n";
echo $str ;
?>

このPHPプログラムのURLを直打ちすると以下の画面が表示されます
ここでエラーが表示されたら原因を調べてエラーをなくしてください

ここでエラーが無ければデータベースにデータが入っていると思います

今後の作業

MySQLにデータが入っているので、この後はデータを閲覧するUIを構築することになります
後はWEB屋さんに任せましょう(笑)

実は以下のような閲覧ページまで作っていますが非公開データなので数字をぼかしています。