MySQL

[MySQL]AES 256 CBC

ivはDBに保存しているが16進数文字列でファイルに記述してもよい

SET block_encryption_mode = 'aes-256-cbc'
SET @key_str = SHA2('XXXXXXX',512)
SET @init_vector = RANDOM_BYTES(16) ※INSERTの時だけ実行
INSERT INTO `table_name`(shop,token,iv,created) VALUES(HEX(AES_ENCRYPT('ABC', @key_str,@init_vector)),HEX(AES_ENCRYPT('DEF', @key_str,@init_vector)),HEX(@init_vector),'2024-05-01 11:12:13')
UPDATE `table_name` SET shop = HEX(AES_ENCRYPT(:shop, @key_str,@init_vector)),token = HEX(AES_ENCRYPT(:token, @key_str,@init_vector)),iv = HEX(@init_vector),modified = '2024-05-01 11:12:13' WHERE id = 5
SELECT convert(AES_DECRYPT(UNHEX(shop), @key_str,UNHEX(iv)) USING utf8) AS shop,convert(AES_DECRYPT(UNHEX(token), @key_str,UNHEX(iv)) USING utf8) AS token,created FROM `table_name` WHERE convert(AES_DECRYPT(UNHEX(shop), @key_str,UNHEX(iv)) USING utf8) = 'ABC'

[PHP]idiorm #7 raw_execute + AES_ENCRYPT

define('DB_ENCRYPT_KEY','aabbccddeeff');
require_once __DIR__ . '/idiorm.php';
ORM::configure('mysql:host=localhost;dbname=xxxx');
ORM::configure('username', 'yyy');
ORM::configure('password', 'zzz');
ORM::configure('driver_options', [
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
]);

$query = "INSERT INTO {$table_name}(`e_id`,`description`) VALUES(:e_id,AES_ENCRYPT(:description,'xxyyzz'))";
result = ORM::for_table('users')->raw_execute($query,['e_id'=>1,'description'=>'あいうえお']);

$query = "UPDATE {$table_name} SET `description`=AES_ENCRYPT(:description,'xxyyzz') WHERE `e_id` = :e_id";
result = ORM::for_table('users')->raw_execute($query,['e_id'=>1,'description'=>'あいうえお']);

[PHP]idiorm #6 select_expr + AES_DECRYPT

define('DB_ENCRYPT_KEY','aabbccddeeff');
require_once __DIR__ . '/idiorm.php';
ORM::configure('mysql:host=localhost;dbname=xxxx');
ORM::configure('username', 'yyy');
ORM::configure('password', 'zzz');
ORM::configure('driver_options', [
    PDO::MYSQL_ATTR_INIT_COMMAND       => 'SET NAMES utf8',
]);
$email = "xxxx@gmail.com";

ORM::configure('logging', true);
try{
    $orm = ORM::for_table('users')->select("id")->select_expr("AES_DECRYPT(`email`,'".DB_ENCRYPT_KEY."')","email")->select_expr("AES_DECRYPT(`name`,'".DB_ENCRYPT_KEY."')","name");
    $orm->where_raw("AES_DECRYPT(`email`,?) = ?",array(DB_ENCRYPT_KEY,$email));
    $record = $orm->find_one();
}catch (PDOException $e){
    return null;
}
echo "<pre>";
var_dump(ORM::get_last_query());
echo "</pre>";
if(!empty($record)) {
    echo "<pre>";
    echo $record->id;
    echo $record->email;
    echo $record->name;
    echo "</pre>";
}

[WordPress]アップロード画像の複数サイズ自動生成停止

下記実行

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

 
functions.phpに下記追加

function disable_image_sizes( $new_sizes ) {
    unset( $new_sizes['1536x1536'] );
    unset( $new_sizes['2048x2048'] );
    unset( $new_sizes['post-thumbnail'] );
    unset( $new_sizes['category-thumb'] );
    unset( $new_sizes['homepage-thumb'] );
    return $new_sizes;
}
add_filter( 'intermediate_image_sizes_advanced', 'disable_image_sizes' );

add_filter( 'big_image_size_threshold', '__return_false' );

[WordPress]get_post_by_post_name

public function get_post_by_post_name($post_name, $post_type = '', $post_status = 'publish')
{
    global $wpdb;
    $sql = array();
    $sql[] = "SELECT p.*";
    $sql[] = "FROM $wpdb->posts AS p";
    $sql[] = "WHERE p.post_name " . $this->__get_sql_for_in($post_name);
    if (!empty($post_type))
        $sql[] = "AND p.post_type = '$post_type'";
    if(!empty($post_status))
        $sql[] = "AND p.post_status = '$post_status'";
    $result = $wpdb->get_results(implode(' ', $sql));
    if (empty($result))
        return null;
    return is_array($post_name) ? $result : $result[0];
}
protected function __get_sql_for_in($values, $is_number = false)
{
    $quote    = $is_number ? "" : "'";
    $is_array = is_array($values);
    $not      = ($is_array && is_string($values[0]) && preg_match('/^.*\!=.+$/',$values[0])) || (!$is_array && is_string($values) && preg_match('/^.*\!=.+$/',$values));
    $in       = $not ? ' NOT IN' : ' IN';
    $eq       = $not ? '!=' : '=';
    if($is_array){
        foreach ($values as $i => $value) {
            $values[$i] = preg_replace('/^(\!=|=)/', '', esc_sql($value));
            $values[$i] = preg_replace('/^ /', '', esc_sql($value));
        }
    }else{
        $values = preg_replace('/^(\!=|=)/','',esc_sql($values));
        $values = preg_replace('/^ /','',esc_sql($values));
    }
    return $is_array && 1 < count($values) ? "{$in} ({$quote}" . implode("{$quote},{$quote}", $values) . "{$quote})" : ($is_array ? "{$eq} {$quote}{$values[0]}{$quote}" : "{$eq} {$quote}$values{$quote}");
}

[PHP]idiorm #5 raw_execute

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

[WordPress]タクソノミー、タームから記事数取得

public function get_posts_count_by_taxonomy_slug($taxonomy, $term = '')
{
    global $wpdb;
    $sql = array();
    $sql[] = "SELECT";
    $sql[] = "COUNT(tr.object_id) AS count";
    $sql[] = "FROM $wpdb->term_relationships AS tr";
    $sql[] = "LEFT JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id";
    $sql[] = "LEFT JOIN $wpdb->terms AS t ON tt.term_id = t.term_id";
    $sql[] = "LEFT JOIN $wpdb->posts AS p ON tr.object_id = p.ID";
    $sql[] = "WHERE p.post_status = 'publish'";
    if (!empty($term))
        $sql[] = "AND t.slug ='$term'";
    $sql[] = "AND tt.taxonomy = '$taxonomy'";
    if (empty($term))
        $sql[] = "GROUP BY tr.object_id";

    $posts = $wpdb->get_results(implode(' ', $sql));
    return empty($posts) ? 0 : $posts[0]->count;
}

[WordPress][プラグイン]WordPress Popular Posts

プラグインWordPress Popular Postsによって閲覧数が保存されている時
指定投稿タイプの記事IDを閲覧数の多い順に取得する

public function get_post_ids_order_by_views_wppp($post_type = 'post', $param = array())
{
    $param = array_merge(
        array(
            'posts_per_page' => 5,
            'range' => '1 MONTH',
            'return_posts' => false,
        ),
        $param
    );
    global $wpdb;
    $now = current_time('mysql');
    $interval = "";
    switch ($param['range']) {
        case "yesterday":
            $interval = "1 DAY";
            break;
        case "daily":
            $interval = "1 DAY";
            break;
        case "weekly":
            $interval = "1 WEEK";
            break;
        case "monthly":
            $interval = "1 MONTH";
            break;
        default:
            $interval = $param['range'];
            break;
    }
    $sql[] = "SELECT postid AS ID,SUM(pageviews) AS views,p.post_type";
    $sql[] = "FROM {$wpdb->prefix}popularpostssummary AS pps";
    $sql[] = "LEFT JOIN $wpdb->posts AS p ON pps.postid = p.ID";
    $sql[] = "WHERE view_datetime > DATE_SUB('{$now}', INTERVAL $interval)";
    $sql[] = "AND p.post_type " . (is_array($post_type) ? "IN ('" . implode("','", $post_type) . "')" : "= '$post_type'");
    $sql[] = "AND p.post_status = 'publish'";
    if (!empty($param['post_id'])) {
        $sql[] = "AND postid " . (is_array($param['post_id']) ? "IN (" . implode(",", $param['post_id']) . ")" : "= {$param['post_id']}");
    }
    if (!empty($param['not_post_id'])) {
        $sql[] = "AND postid " . (is_array($param['not_post_id']) ? "NOT IN (" . implode(",", $param['not_post_id']) . ")" : "!= {$param['not_post_id']}");
    }
    $sql[] = "GROUP BY postid";
    $sql[] = "ORDER BY views DESC";
    if (!empty($param['ppp'])) {
        $sql[] = "LIMIT 0,{$param['ppp']}";
    }elseif (!empty($param['posts_per_page'])) {
        $sql[] = "LIMIT 0,{$param['posts_per_page']}";
    }
    $posts = $wpdb->get_results(implode(' ', $sql));
    if ($param['return_posts'])
        return $posts;
    $ids = array();
    foreach ($posts as $post) {
        $ids[] = $post->ID;
    }
    return $ids;
}

[PHP]idiorm #4 raw_query

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;

[PHP]idiorm #3 select_expr

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