DBから取得するサンプルがなかったのでメモ
データベースからページネーションとソート
wp-content/plugins/my-list-table.php
<?php
/*
Plugin Name: My List Table
Version: 0.0.1
*/
if (!class_exists('WP_List_Table')) {
require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}
class My_List_Table extends WP_List_Table
{
public function prepare_items()
{
global $wpdb;
$per_page = 2;
$orderby = $_GET['orderby'] ?? 'price';
$order = $_GET['order'] ?? 'desc';
$order_by = sanitize_sql_orderby("$orderby $order");
$offset = ($this->get_pagenum() - 1) * $per_page;
$sql = $wpdb->prepare("SELECT SQL_CALC_FOUND_ROWS * FROM wp_fruit ORDER BY $order_by LIMIT %d, %d", $offset, $per_page);
$this->items = $wpdb->get_results($sql, ARRAY_A);
$this->set_pagination_args(
array(
'total_items' => $wpdb->get_var('SELECT FOUND_ROWS()'),
'per_page' => $per_page
)
);
}
public function get_columns()
{
return [
'name' => '名前',
'color' => '色',
'price' => '値段'
];
}
public function get_sortable_columns(): array
{
return array(
'name' => 'name',
'color' => 'color',
'price' => 'price'
);
}
public function column_default($item, $column_name)
{
return $item[$column_name];
}
}
function add_admin_menu()
{
$myListTable = new My_List_Table();
add_menu_page('My List Table', 'My List Table', 'activate_plugins', 'my_list_table', function () use ($myListTable) {
$myListTable->prepare_items();
$myListTable->display();
});
}
add_action('admin_menu', 'add_admin_menu');
// DB作成
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta("CREATE TABLE IF NOT EXISTS wp_fruit (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45),
color VARCHAR(45),
price INT,
PRIMARY KEY (id) )");
if ($wpdb->get_var('SELECT count(*) FROM wp_fruit') == 0) {
$wpdb->insert('wp_fruit', ['name' => 'りんご', 'color' => '赤', 'price' => 100]);
$wpdb->insert('wp_fruit', ['name' => 'バナナ', 'color' => '黄', 'price' => 60]);
$wpdb->insert('wp_fruit', ['name' => 'メロン', 'color' => '緑', 'price' => 200]);
}
検索もする
wp-content/plugins/my-list-table.php
<?php
/*
Plugin Name: My List Table
Version: 0.0.1
*/
if (!class_exists('WP_List_Table')) {
require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}
class My_List_Table extends WP_List_Table
{
public function prepare_items()
{
global $wpdb;
$per_page = 2;
$s = $_GET['s'] ?? '';
$orderby = $_GET['orderby'] ?? 'price';
$order = $_GET['order'] ?? 'desc';
$order_by = sanitize_sql_orderby("$orderby $order");
$offset = ($this->get_pagenum() - 1) * $per_page;
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM wp_fruit WHERE name LIKE %s ORDER BY $order_by LIMIT %d, %d";
$args = ['%' . $wpdb->esc_like($s) . '%', $offset, $per_page];
$this->items = $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A);
$this->set_pagination_args(
array(
'total_items' => $wpdb->get_var('SELECT FOUND_ROWS()'),
'per_page' => $per_page
)
);
}
public function get_columns()
{
return [
'name' => '名前',
'color' => '色',
'price' => '値段'
];
}
public function get_sortable_columns(): array
{
return array(
'name' => 'name',
'color' => 'color',
'price' => 'price'
);
}
public function column_default($item, $column_name)
{
return $item[$column_name];
}
}
function add_admin_menu()
{
$myListTable = new My_List_Table();
add_menu_page('My List Table', 'My List Table', 'activate_plugins', 'my_list_table', function () use ($myListTable) {
echo '<form>';
$myListTable->prepare_items();
$myListTable->search_box('検索する', 'items');
printf('<input type="hidden" name="page" value="%s" />', esc_attr($_GET['page']));
$myListTable->display();
echo '</form>';
});
}
add_action('admin_menu', 'add_admin_menu');
// DB作成
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta("CREATE TABLE IF NOT EXISTS wp_fruit (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45),
color VARCHAR(45),
price INT,
PRIMARY KEY (id) )");
if ($wpdb->get_var('SELECT count(*) FROM wp_fruit') == 0) {
$wpdb->insert('wp_fruit', ['name' => 'りんご', 'color' => '赤', 'price' => 100]);
$wpdb->insert('wp_fruit', ['name' => 'バナナ', 'color' => '黄', 'price' => 60]);
$wpdb->insert('wp_fruit', ['name' => 'メロン', 'color' => '緑', 'price' => 200]);
}