参考

Querying — Idiorm documentation

必要なもの : pdo,mysql

idiorm.phpだけあれば使える手軽さ

wget "https://raw.githubusercontent.com/j4mie/idiorm/master/idiorm.php" \
 --no-check-certificate

例 (mysql)

例1

<?php
require_once("./idiorm.php");

ORM::configure('mysql:host=localhost;port=3306;dbname=test');
ORM::configure('username', 'root');
ORM::configure('password', '');

//$record = ORM::for_table('テーブル名')
//           ->where('カラム名','検索したい値')
//           ->find_one();
$record = ORM::for_table('テーブル名')->find_one();

print_r($record->as_array());

$data = $record->カラム名;
print $data;

例1. クリック毎にinsert

create.sql

CREATE TABLE clicks (
  id int(11) NOT NULL AUTO_INCREMENT,
  count int(11) NOT NULL default '0',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='クリック数';

index.html(jquery)

<!DOCTYPE html>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<input type="button" id="btn1" value="click" />
<span id="counts"></span>
<script>
$(function() {
  var counts = 0;
  $('#btn1').click(function() {
    $.ajax('countup.php',
      {
        type: 'get',
        data: { count: counts++ }
      }
    )
    .done(function(data) {
      $("#counts").html('success');
    })
    .fail(function() {
      $("#counts").html('error');
    });
  });
});
</script>

mzabriskie/axios: Promise based HTTP client for the browser and node.js

index.html(axios)

<!DOCTYPE html>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<input type="button" value="click" onclick="countup();" />
<script>
var counts = 0;
function countup() {
  counts++;
  axios.get('./countup.php?count='+counts)
    .then(function (response) {
      console.log(response.statusText);
    })
    .catch(function (error) {
      console.log(error.statusText);
    });
}
</script>

http://idiorm.readthedocs.io/en/latest/models.html#creating-new-records

countup.php

<?php
require_once("./idiorm.php");
$DBNAME='test';
ORM::configure(array(
    'connection_string' => 'mysql:host=127.0.0.1;dbname='.$DBNAME,
    'username' => 'root',
    'password' => ''
));

# validation
$count = $_GET["count"];
if(!is_numeric($count)) {
        die("[quit] not number.");
}

# insert
$click = ORM::for_table('clicks')->create();
$click->count = $count;
$click->set_expr('created_at', 'NOW()');
$click->save();

echo "[success] 200";

例2. userテーブル

create.sql

CREATE TABLE clicks (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) NOT NULL default '0',
  count int(11) NOT NULL default '0',
  updated_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='クリック数';

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  sessid varchar(32) NOT NULL default '0',
  updated_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='user';

index.php

<?php session_start(); ?>
<!DOCTYPE html>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<input type="button" id="btn1" value="click" />
<span id="counts"></span>
<script>
$(function() {
  var counts = 0;
  var sessid = '<?php echo $_COOKIE["PHPSESSID"]; ?>';
  $('#btn1').click(function() {
    $.ajax('countup.php',
      {
        type: 'get',
        data: {
            sessid: sessid,
            count: ++counts,
        }
      }
    )
    .done(function(data) {
      $("#counts").html("クリック回数:"+counts);
    })
    .fail(function() {
      $("#counts").html('error');
    });
  });
});
</script>

countup.php

<?php
require_once("./idiorm.php");
# ------------------------------------------------------------------------------
class Counter {
    public $count;
    public $sessid;
    function __construct() {
        $this->count = $_GET["count"];
        $this->sessid = $_GET["sessid"];
    }
    function db_connect() {
        $DBNAME='test';
        ORM::configure(array(
            'connection_string' => 'mysql:host=127.0.0.1;dbname='.$DBNAME,
            'username' => 'root',
            'password' => '',
            'caching' => true,
            'caching_auto_clear' => true,
        ));
    }

    function validation() {
        if(!is_numeric($this->count)) {
        	die("[quit] not number.");
        }
        if(empty($this->sessid)) {
        	die("[quit] no sessid.");
        }
    }
    
    function up() {
        $this->validation();
        $this->db_connect();
        # user
        $user = ORM::for_table('users')->where('sessid', $this->sessid)->find_one();
        if(!$user) {
            # insert
            $user = ORM::for_table('users')->create();
            $user->sessid = $this->sessid;
            $user->set_expr('updated_at', 'NOW()');
            $user->save();
        }
        $user_id = $user->id();
        
        # click数
        $click = ORM::for_table('clicks')->where('user_id', $this->user_id)->find_one();
        if(!$click) {
            $click = ORM::for_table('clicks')->create();
        }
        $click->count = $this->count;
        $click->user_id = $user_id;
        $click->set_expr('updated_at', 'NOW()');
        $click->save();
        
        echo "[success] 200";
    }
}
# ------------------------------------------------------------------------------
$Counter = new Counter();
$Counter->up();

例 (sqlite3)

CREATE TABLE bbs (
 id INTEGER PRIMARY KEY
, body TEXT
, created_at datetime
);

<?php
require_once("vendor/idiorm.php");
ORM::configure('sqlite:a.sqlite3');
ORM::configure('id_column', 'id');

if($_GET["body"]) {
        $record = ORM::for_table('bbs')->create();
        $record->body = $_GET["body"];
        $record->created_at = date("Y-m-d H:i:s");
        $record->save();
}

sqliteを使う場合、親ディレクトリに書き込み権限が必要。
- 恐らく一旦tmpファイルに書き込んでからmvしているのでは。

書き方

SQL直書き

raw_query

//SQL直書き (raw_query)
$query = 'SELECT * FROM members WHERE name = :name';
$entries = ORM::for_table('members')
    ->raw_query($query, array('name' => $name))
    ->find_array();

Join

$records = ORM::for_table('addresses')
       ->left_outer_join('members', 'members.id=addresses.memid')
       ->find_many();

foreach ($records as $r) {
    echo sprintf("id: %s, name: %s, pref: %s<br>"
		,$r->id, $r->name, $r->pref);
}

複数DB接続

Multiple Connections — Idiorm documentation

<?php
require_once("vendor/idiorm.php");

// a.sqlite3
ORM::configure('sqlite:a.sqlite3', null, 'connection_A');
ORM::configure('id_column', 'id', 'connection_A');
if($_GET["body"]) {
        $record = ORM::for_table('bbs','connection_A')->create();
        $record->body = $_GET["body"];
        $record->save();
}

// b.sqlite3
ORM::configure('sqlite:b.sqlite3', null, 'connection_B');
ORM::configure('id_column', 'id', 'connection_B');
if($_GET["body"]) {
        $record = ORM::for_table('bbs','connection_B')->create();
        $record->body = $_GET["body"];
        $record->save();
}

show tables

Querying — Idiorm documentation

$res = ORM::raw_execute("SHOW TABLES LIKE 'sales_%'");
$statement = ORM::get_last_statement();
echo "<pre>";
while ($row = $statement->fetch(PDO::FETCH_NUM)) {
    echo $row[0]."<br>";
}