ktyr report

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;
}

CREATE TABLE `users` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARBINARY(100) NULL DEFAULT NULL,
	`email` VARBINARY(200) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id` (`id`)
)
ENGINE=InnoDB
;

crypto_saltは任意の文字列

SET @key_str = SHA2('crypto_salt',512);
INSERT INTO users(name,email) VALUES(AES_ENCRYPT('佐藤鈴木',@key_str),AES_ENCRYPT('satosuzuki@nullmail.com',@key_str));

SET @key_str = SHA2('crypto_salt',512);
SELECT id,AES_DECRYPT(`name`,@key_str) AS name,AES_DECRYPT(`email`,@key_str) AS email FROM users;

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;

update wp_options set option_value='/manage' where option_name = 'siteurl';
update wp_options set option_value='/' where option_name = 'home';
update wp_options set option_value='/uploads' where option_name = 'upload_url_path';
update wp_options set option_value='../uploads' where option_name = 'upload_path';
update wp_options set option_value='' where option_name = 'blogdescription';
update wp_options set option_value='0' where option_name = 'start_of_week';
update wp_options set option_value='Y/m/d' where option_name = 'date_format';
update wp_options set option_value='H:i' where option_name = 'time_format';
update wp_options set option_value='closed' where option_name = 'default_comment_status';
update wp_options set option_value='closed' where option_name = 'default_ping_status';
update wp_options set option_value='0' where option_name = 'thumbnail_size_w';
update wp_options set option_value='0' where option_name = 'thumbnail_size_h';
update wp_options set option_value='0' where option_name = 'medium_size_w';
update wp_options set option_value='0' where option_name = 'medium_size_h';
update wp_options set option_value='0' where option_name = 'large_size_w';
update wp_options set option_value='0' where option_name = 'large_size_h';
update wp_options set option_value='0' where option_name = 'medium_large_size_w';
update wp_options set option_value='0' where option_name = 'medium_large_size_h';
update wp_options set option_value='0' where option_name = 'uploads_use_yearmonth_folders';
update wp_options set option_value='/%postname%/' where option_name = 'permalink_structure';

plugin require

Advanced Custom Fields PRO
Custom Post Type UI
Scheduled Post Trigger
User Role Editor
WP Term Order
Duplicate Post

plugin option

Radio Buttons for Taxonomies
Parent Category Toggler
Adjust Admin Categories

REGEXPはマルチバイトに対応していないので、FIND_IN_SETを使用。

INSERT INTO xxx(initial_chara,gyo)
(
SELECT
SUBSTRING(tmp.name,1,1),
(CASE
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'あ,い,う,え,お') THEN 'あ'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'か,き,く,け,こ,が,ぎ,ぐ,げ,ご') THEN 'か'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'さ,し,す,せ,そ,ざ,じ,ず,ぜ,ぞ') THEN 'さ'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'た,ち,つ,て,と,だ,ぢ,づ,で,ど') THEN 'た'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'な,に,ぬ,ね,の') THEN 'な'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'は,ひ,ふ,へ,ほ,ば,び,ぶ,べ,ぼ') THEN 'は'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'ま,み,む,め,も') THEN 'ま'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'や,ゐ,ゆ,ゑ,よ') THEN 'や'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'ら,り,る,れ,ろ') THEN 'ら'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'わ,を,ん') THEN 'わ'
ELSE 'ん' END
)
FROM yyy AS tmp

AmazonRDSは仕様上FILE権限は付与出来ない。
故に

LOAD DATA INFILE xxxx.csv INTO TABLE wp_names FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES;

を

LOAD DATA LOCAL INFILE xxxx.csv INTO TABLE wp_names FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES;

へ変更するとよい

※余談
windows上ではパスのセパレータを/に変換しないと動かないことも

$filepath = preg_replace('/\\\\/','/',$filepath);

生年月日から年齢を算出する

■PHP

$birth = '1952-01-26 00:00:00';
$age = (int) ((date('Ymd') - preg_replace('/([\- ]|[0-9]{2}:[0-9]{2}:[0-9]{2})/','',$birth)) / 10000);
echo $age;

結果

65

■MySQL

new_users

SQL

SELECT
id,
name,
birth,
DATE_FORMAT(birth, '%Y%m%d') AS tmp_birth,
DATE_FORMAT(NOW(), '%Y%m%d') AS now_date,
FLOOR((DATE_FORMAT(NOW(), '%Y%m%d') - DATE_FORMAT(birth, '%Y%m%d')) / 10000) AS age
FROM new_users
ORDER BY id ASC

結果

開始位置は1からなので注意

all_test

SQL

SELECT id,name,tel,zip,SUBSTRING(zip,1,3) AS zip1,SUBSTRING(zip,4,4) AS zip2
FROM all_test
ORDER BY id ASC;

結果

下記2つのテーブルをtelカラムでjoinしたい

all_test

sub_infos

SQL

SELECT a.id,a.name,a.tel,s.id AS sub_id,s.tel AS sub_tel
FROM all_test AS a
LEFT JOIN sub_infos AS s
ON FIND_IN_SET(a.tel,REPLACE(s.tel,'-',''))
ORDER BY id ASC;

結果

検索結果ファイル出力

SELECT id,ken_id,ken_name FROM ad_address GROUP BY ken_name ORDER BY ken_id ASC LIMIT 55 INTO OUTFILE "tmp/test.csv" FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';