将搜索栏添加到WordPress仪表板中的自定义数据库表

时间：2017-06-28 作者：DigitalDesigner

我在WordPress数据库中构建了一个自定义表。在此基础上，我创建了一个插件，它创建了一个仪表板页面，允许您通过扩展WP\\u List\\u Table函数来查看数据库中的各种行。我现在需要做的是添加搜索栏功能。我已经能够添加搜索栏并加载结果页面，但是没有给出任何结果。

我需要能够搜索自定义数据库中标题为“Card”的特定列并显示结果。

这是我到目前为止的情况。如有任何指示和具体情况的样品，将不胜感激。

 <?php

 if ( ! class_exists( \'WP_List_Table\' ) ) {
    require_once( ABSPATH . \'wp-admin/includes/class-wp-list-table.php\' );
 }

 class Members_List extends WP_List_Table {

    /** Class constructor */
    public function __construct() {

        parent::__construct( [
            \'singular\' => __( \'Member\', \'red\' ), //singular name of the listed records
            \'plural\'   => __( \'Members\', \'red\' ), //plural name of the listed records
            \'ajax\'     => false //does this table support ajax?
        ] );

    }


    /**
     * Retrieve members data from the database
     *
     * @param int $per_page
     * @param int $page_number
     *
     * @return mixed
     */
    public static function get_members( $per_page = 5, $page_number = 1 ) {

        global $wpdb;

        $sql = "SELECT * FROM  my_members";

        if ( ! empty( $_REQUEST[\'orderby\'] ) ) {
            $sql .= \' ORDER BY \' . esc_sql( $_REQUEST[\'orderby\'] );
            $sql .= ! empty( $_REQUEST[\'order\'] ) ? \' \' . esc_sql( $_REQUEST[\'order\'] ) : \' ASC\';
        }

        $sql .= " LIMIT $per_page";
        $sql .= \' OFFSET \' . ( $page_number - 1 ) * $per_page;

        if( ! empty( $_REQUEST[\'s\'] ) ){
        $search = esc_sql( $_REQUEST[\'s\'] );
        $sql .= " WHERE card LIKE \'%{$search}%\'";
    }

        $result = $wpdb->get_results( $sql, \'ARRAY_A\' );

        return $result;
    }


    /**
     * Delete a members record.
     *
     * @param int $id member ID
     */
    public static function delete_member( $id ) {
        global $wpdb;

        $wpdb->delete(
            "my_members",
            [ \'ID\' => $id ],
            [ \'%d\' ]
        );
    }

    /**
     * Returns the count of records in the database.
     *
     * @return null|string
     */
    public static function record_count() {
        global $wpdb;

        $sql = "SELECT COUNT(*) FROM my_members";

        return $wpdb->get_var( $sql );
      }


    /** Text displayed when no member data is available */
    public function no_items() {
        _e( \'The Members Database is currently empty.\', \'red\' );
    }


/**
 * Render a column when no column specific method exist.
 *
 * @param array $item
 * @param string $column_name
 *
 * @return mixed
 */
public function column_default( $item, $column_name ) {
    switch ( $column_name ) {
        case \'Member_ID\':
        case \'Card\':
        case \'First\':
        case \'Last\':
        case \'Class\':
            return $item[ $column_name ];
        default:
            return print_r( $item, true ); //Show the whole array for troubleshooting purposes
    }
}

/**
 * Render the bulk edit checkbox
 *
 * @param array $item
 *
 * @return string
 */
function column_cb( $item ) {
    return sprintf(
        \'<input type="checkbox" name="bulk-delete[]" value="%s" />\', $item[\'ID\']
    );
}


/**
 * Method for name column
 *
 * @param array $item an array of DB data
 *
 * @return string
 */
function column_name( $item ) {

    $delete_nonce = wp_create_nonce( \'red_delete_member\' );

    $title = \'<strong>\' . $item[\'name\'] . \'</strong>\';

    $actions = [
        \'delete\' => sprintf( \'<a href="?page=%s&action=%s&customer=%s&_wpnonce=%s">Delete</a>\', esc_attr( $_REQUEST[\'page\'] ), \'delete\', absint( $item[\'ID\'] ), $delete_nonce )
    ];

    return $title . $this->row_actions( $actions );
}


/**
 *  Associative array of columns
 *
 * @return array
 */
function get_columns() {
    $columns = [
        \'cb\'      => \'<input type="checkbox" />\',
        \'Member_ID\' => __( \'Member ID\', \'red\' ),
        \'Card\'    => __( \'Card\', \'red\' ),
        \'First\' => __( \'First Name\', \'red\' ),
        \'Last\'    => __( \'Last Name\', \'red\' )
    ];

    return $columns;
}


/**
 * Columns to make sortable.
 *
 * @return array
 */
public function get_sortable_columns() {
    $sortable_columns = array(
        \'Member_ID\' => array( \'Member_ID\', true ),
        \'Card\' => array ( \'Card\', true)
    );

    return $sortable_columns;
}

/**
 * Returns an associative array containing the bulk action
 *
 * @return array
 */
public function get_bulk_actions() {
    $actions = [
        \'bulk-delete\' => \'Delete\'
    ];

    return $actions;
}


/**
 * Handles data query and filter, sorting, and pagination.
 */
public function prepare_items() {

    $this->_column_headers = $this->get_column_info();

    /** Process bulk action */
    $this->process_bulk_action();

    $per_page     = $this->get_items_per_page( \'members_per_page\', 7 );
    $current_page = $this->get_pagenum();
    $total_items  = self::record_count();

    $this->set_pagination_args( [
        \'total_items\' => $total_items, //WE have to calculate the total number of items
        \'per_page\'    => $per_page //WE have to determine how many items to show on a page
    ] );

    $this->items = self::get_members( $per_page, $current_page );
}

public function process_bulk_action() {

    //Detect when a bulk action is being triggered...
    if ( \'delete\' === $this->current_action() ) {

        // In our file that handles the request, verify the nonce.
        $nonce = esc_attr( $_REQUEST[\'_wpnonce\'] );

        if ( ! wp_verify_nonce( $nonce, \'red_delete_member\' ) ) {
            die( \'\' );
        }
        else {
            self::delete_member( absint( $_GET[\'member\'] ) );

                    // esc_url_raw() is used to prevent converting ampersand in url to "#038;"
                    // add_query_arg() return the current url
                    wp_redirect( esc_url_raw(add_query_arg()) );
            exit;
        }

    }

    // If the delete bulk action is triggered
    if ( ( isset( $_POST[\'action\'] ) && $_POST[\'action\'] == \'bulk-delete\' )
         || ( isset( $_POST[\'action2\'] ) && $_POST[\'action2\'] == \'bulk-delete\' )
    ) {

        $delete_ids = esc_sql( $_POST[\'bulk-delete\'] );

        // loop over the array of record IDs and delete them
        foreach ( $delete_ids as $id ) {
            self::delete_member( $id );

        }

        // esc_url_raw() is used to prevent converting ampersand in url to "#038;"
            // add_query_arg() return the current url
            wp_redirect( esc_url_raw(add_query_arg()) );
        exit;
    }
    }

 }

 class Member_Management_Plugin {

    // class instance
    static $instance;

    // member WP_List_Table object
    public $members_obj;

    // class constructor
    public function __construct() {
    add_filter( \'set-screen-option\', [ __CLASS__, \'set_screen\' ], 10, 3 );
    add_action( \'admin_menu\', [ $this, \'plugin_menu\' ] );
}


public static function set_screen( $status, $option, $value ) {
    return $value;
}

public function plugin_menu() {

    $hook = add_menu_page(
        \'Manage Members\',
        \'Manage Members\',
        \'manage_options\',
        \'wp_list_table_class\',
        [ $this, \'plugin_settings_page\' ]
    );

    add_action( "load-$hook", [ $this, \'screen_option\' ] );

}


/**
 * Plugin page
 */
public function plugin_settings_page() {
    ?>
    <style>
      table {display: block;overflow-x: scroll;max-width:98%;}
      th {min-width:100px;font-size:10px;}
    </style>
        <h2>IBEW 353 Member Management Portal</h2>
                <div id="post-body-content">
                    <div class="meta-box-sortables ui-sortable">
                        <form method="post">
   <input type="hidden" name="page" value="wp_list_table_class" />
                            <?php
                            $this->members_obj->prepare_items();
                            $this->members_obj->search_box(\'Search\', \'search\');
                            $this->members_obj->display(); ?>
                        </form>
                    </div>
                </div>
            <br class="clear">
<?php
}

/**
 * Screen options
 */
public function screen_option() {

    $option = \'per_page\';
    $args   = [
        \'label\'   => \'Members\',
        \'default\' => 7,
        \'option\'  => \'members_per_page\'
    ];

    add_screen_option( $option, $args );

    $this->members_obj = new Members_List();
}


/** Singleton instance */
public static function get_instance() {
    if ( ! isset( self::$instance ) ) {
        self::$instance = new self();
    }

    return self::$instance;
    }

 }


 add_action( \'plugins_loaded\', function () {
    Member_Management_Plugin::get_instance();
 } );
更新：如您所见，我现在可以看到搜索栏，但无法正确获取结果。我应该添加什么来搜索卡片列并显示结果，以及如何添加。

如何添加搜索表单，以便从搜索栏加载包含新结果的页面？任何代码示例都将不胜感激。

我尝试了提供的建议并查看了参考链接，但是链接教程似乎破坏了内容中的代码，因此我犹豫了，我已经在尝试调试一个解决方案。

我的双手悬空，依靠社区的进一步了解。

1 个回复
最合适的回答,由SO网友:sMyles 整理而成

只需将其添加到正在执行由“准备项目”调用的自定义查询的位置：

public static function get_members( $per_page = 5, $page_number = 1 ) {

    global $wpdb;

    $sql = \'SELECT * FROM  my_members\';

    if ( ! empty( $_REQUEST[\'orderby\'] ) ) {
        $sql .= \' ORDER BY \' . esc_sql( $_REQUEST[\'orderby\'] );
        $sql .= ! empty( $_REQUEST[\'order\'] ) ? \' \' . esc_sql( $_REQUEST[\'order\'] ) : \' ASC\';
    }

    $sql .= " LIMIT $per_page";
    $sql .= \' OFFSET \' . ( $page_number - 1 ) * $per_page;

    if( ! empty( $_REQUEST[\'s\'] ) ){
        $search = esc_sql( $_REQUEST[\'s\'] );
        $sql .= " WHERE card LIKE \'%{$search}%\'";
    }

    $result = $wpdb->get_results( $sql, \'ARRAY_A\' );

    return $result;
}
我还没有测试过这个，但这应该是你想要的。您还应该考虑使用$wpdb 包括以下功能$wpdb->prepare 使用WordPress内置的处理、转义等来制定SQL查询

https://developer.wordpress.org/reference/classes/wpdb/prepare/

UPDATE:您还需要在进行自定义数据库查询的任何地方进行更新，包括WHERE语句、计数等。

我建议您查看此示例，并根据本教程重写整个列表表类，该教程使用自定义数据库表数据，并集成了搜索处理：https://webkul.com/blog/create-admin-tables-using-wp_list_table-class/

结束
标签： database   search   dashboard   wp-list-table   小码农  

相关推荐

Search one custom field?

在帖子中，有一个名为“color”的自定义字段。我有自定义搜索页面模板，我想只显示来自该自定义字段的搜索结果。如何限制搜索只在帖子内的一个自定义字段中查看？