参考
必要なもの : 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>
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>
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接続
<?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
$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>";
}