ktyr report

public function get_std_by_array($arr,$include_keys = null)
{
    $tmp = new stdClass();
    foreach ($arr as $key => $v){
        if(empty($include_keys) || (!empty($include_keys) && in_array($key,$include_keys)))
            $tmp->$key = $v;
    }
    return $tmp;
}
public function get_datas
{
$sql = array();
$sql[] = "SELECT * FROM wp_posts";
$this->init_pgsql();
try{
    $res = ORM::raw_execute(implode(" ",$sql));
    $statement = ORM::get_last_statement();
    $rows = array();
    $results = array();
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
        $results[] = $this->get_std_by_array($row);
    }
}catch (PDOException $e){
}
return empty($results) ? null : $results;
}

try{
    $sql = "SELECT * FROM shops WHERE name LIKE ? OR name LIKE ?";
    $words = array("%銀座%","%御徒町%");
    $items = ORM::for_table('shops')->raw_query($sql,$words)->find_array();
}catch (PDOException $e){
    return null;
}
return $items;

$this->init_pgsql();
try{
    $orm = ORM::for_table('users')->select_expr("DISTINCT CONCAT(team,'_',job)","tj")->select('team')->select('job');
    $orm->where_in('team',$teams);
    $records = $orm->find_array();
}catch (PDOException $e){
    return null;
}
return $records;

public function init_pgsql()
{
if($this->_init_pgsql)return;
$this->_init_pgsql = true;
ORM::configure(‘pgsql:host=’.PDB_HOST.’;port=’.PDB_PORT.’;dbname=’.PDB_DB.’;’);
ORM::configure(‘username’, PDB_ID);
ORM::configure(‘password’, PDB_PW);
}
public function get_data_by_id($id)
{
$this->init_pgsql();
try{
$orm = ORM::for_table(‘wp_posts’)->join(‘wp_postmeta’, array(‘wp_posts.ID’, ‘=’, ‘wp_postmeta.post_id’))->select(‘*’)->where_equal(‘wp_posts.ID’,$id)->limit(1);
$data = $orm->find_array();
}catch (PDOException $e){
return null;
}
return empty($data) ? null : $data;
}

require_once 'idiorm.php';
ORM::configure('mysql:host=localhost;dbname=dev_db');
ORM::configure('username', 'root');
ORM::configure('password', '');
ORM::configure('driver_options', [
    PDO::MYSQL_ATTR_INIT_COMMAND       => 'SET NAMES utf8',
    PDO::ATTR_EMULATE_PREPARES         => false,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
]);
function echo_json($data)
{
    header("Content-Type: application/json; charset=utf-8");
    echo json_encode($data);
}
$words = mb_split('[\s,　]',addcslashes(trim($_POST['keywords']),'\_%'));
try{
    $orm = ORM::for_table('entries')->select('id')->where_equal('deleted','0')->order_by_asc('id');
    foreach ($words as $w) {
        $w = trim($w);
        if($w !== '')
            $orm->where_like('search', "%{$w}%");
    }
$recipes = $orm->find_many();
}catch (PDOException $e){
}
$result_data = array();
if(!empty($recipes)){
    foreach ($recipes as $recipe)
        $result_data[] = $recipe->html_id;
}
echo_json($result_data);
exit;

INSERT文を作る

echo getInsertQueryByKey('system_table', array('name','gender'), array( array('jeff','male'), array('pola','female') ) );

function getInsertQueryByKey($tableName,$columns,$datas){

	//SQLite古いバージョン
	$w=array();

	$column = implode(',', $columns);
	$query="INSERT INTO {$tableName} ({$column}) SELECT ";
	$tmp=array();
	foreach ($columns as $index => $columnName) {
		$tmp[] = "'". $datas[0][$index] . "'" . ' AS ' . $columnName;
	}
	$query .= implode(', ', $tmp);
	$w[]=$query;

	foreach ($datas as $index => $data) {
		if($index<1)continue;
		$tmp=array();
		foreach ($columns as $index2 => $columnName) {
			$tmp[]=$data[$index2];
		}
		$w[]="'".implode("', '", $tmp)."'";
	}

	return implode(' UNION ALL SELECT ', $w).';';

	/* MySQL or SQLite(バージョン 3.7.11以降)
	$queryValues=array();
	foreach ($datas as $index => $value) {
		$values=array();
		foreach ($value as $index2 => $value2) {
			$values[]="'".$value2."'";
		}
		$queryValues[]='('.implode(',', $values).')';
	}

	$column=implode(',', $columns);
	$value=implode(',', $queryValues);
	$sql="INSERT INTO {$tableName} ({$column}) VALUES {$value};";
	return $sql;
	*/
}

INSERTで複数のデータを挿入

INSERT INTO system_table (name,gender) SELECT 'jeff' AS name, 'male' AS gender UNION ALL SELECT 'pola', 'female';

MySQL or SQLite(バージョン 3.7.11以降)

INSERT INTO system_table (name,gender) VALUES ('jeff','male'),('pola','female');

バージョン取得

print_r( SQLite3::version() );

PHPでSQLite3 #6データ削除(DELETE)

$sql="DELETE FROM $tableName WHERE id = $id";
$result=$db->query($sql);

PHPでSQLite3 #5データ更新(UPDATE)

$sql="SELECT id,cnt FROM $tableName WHERE id='{$id}'";
$result=$db->query($sql);
$row = $result->fetchArray();
$id=$row['id'];
$cnt=$row['cnt']+1;

$sql="UPDATE $tableName SET cnt = $cnt WHERE id = $id";
$result=$db->query($sql);