PHP

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

[WordPress]Gutenberg下のプレビューURL変更

/**
 * Gutenbergによる投稿プレビューのURL(プレビュー生成中からのリダイレクト先)を変更する
 * WordPress 5.3.2で動作確認
 * @param $response
 * @return array
 */
function my_rest_pre_echo_response($response)
{
    if(!is_user_logged_in())
        return;
    if($response['status'] === 'draft'){
        $response['link'] = '任意のURL';
    }
    return $response;
}
add_filter( 'rest_pre_echo_response', 'my_rest_pre_echo_response', 100 );

[PHP]idiorm #2 join

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

[WordPress]独自設定画面で指定DBからデータ取得

functions.php

<?php
function my_menu()
{
    add_menu_page('独自設定画面','my menu','manage_options','my_menu','my_options_page','',58);
}
add_action('admin_menu', 'my_menu');

function my_options_page()
{
    $wpm = new ThisCaseModel();
    $wpdb = new wpdb('DBID','DBPW','DBNAME','DBHOST');

    $page = empty($_GET['_page']) ? 1 : $_GET['_page'];
    $sql = array();
    $sql[] = "SELECT u.id,u.name";
    $sql[] = "FROM users AS u";
    $sql[] = "LEFT JOIN others AS o ON u.id = o.user_id";
    $sql[] = "WHERE o.id IS NOT NULL";
    $sql[] = "ORDER BY u.id ASC";
    $sql[] = "LIMIT " . $wpm->get_sql_limit_value_by_page($page,20);
    $users = $wpdb->get_results(implode(" ",$sql));
    
    array_pop($sql);
    array_pop($sql);
    $sql[0] = "SELECT COUNT(*) AS count";
    $count = $wpdb->get_results(implode(" ",$sql));
    $page_info = $wpm->get_page_no_info($page,$count[0]->count,20);
    $page_numbers = $wpm->get_page_numbers_for_pager((int)$page_info['current'],(int)$page_info['last_page_no']);
    
    include TEMPLATEPATH . DIRECTORY_SEPARATOR . 'admin-my_menu.php';
}

admin-my_menu.php

<style>
    .users,.users th,.properties td{
        border-collapse: collapse;
        border:1px solid #ccc;
    }
    .users th,.users td{
        padding: 3px 5px;
    }
    .pager{
        display: flex;
    }
    .pager a{
        display: block;
        border:1px solid #ccc;
        margin-right: 5px;
        padding: 3px 10px;
    }
    .pager a:link{
        text-decoration: none;
    }
</style>
<div class="container">
    <h2>my menu</h2>
    <div>
        <div>件数:<?php echo $page_info['count'];?></div>
        <div><?php echo $page_info['current'];?>/<?php echo $page_info['last_page_no'];?></div>
    </div>
    <table class="users">
        <tr>
            <th>ID</th>
            <th>name</th>
        </tr>
        <?php foreach ($users as $user):?>
            <tr>
                <td><?php echo $wpm->esc_html($user->id);?></td>
                <td><?php echo $wpm->esc_html($user->name);?></td>
            </tr>
        <?php endforeach;?>
    </table>
    <div class="pager">
        <?php foreach ($page_numbers as $page_number):?>
            <?php if(empty($page_number)):?>
                …
            <?php else:?>
                <a href="<?php echo $wpm->get_url_by_current_url($page_number<2 ? array('exclude_keys'=>array('_page')) : array('exclude_keys'=>array('_page'),'add_values'=>array('_page'=>$page_number)));?>"><?php echo $page_number;?></a>
            <?php endif;?>
        <?php endforeach;?>
    </div>
</div>

[PHP]idiorm #1

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;

[PHP]remove_protocol

public function remove_protocol($url)
{
    return preg_replace('/^https?:\/\/[^\/]+/', '', $url);
}

[PHP]download_file

function download_file($filePath)
{
    header("Content-Disposition: inline; filename=\"" . basename($filePath) . "\"");
    header("Content-Length: " . filesize($filePath));
    header("Content-Type: application/octet-stream");
    readfile($filePath);
}