WordPress外部数据库连接插件查询无数据问题求助

我开发了一款连接同服务器外部数据库的WordPress插件，插件能成功连接到外部数据库，但提示：

Successfully connected to the external database. No data found in the "ali" column.

但我的数据库中确实存在ali列（表结构截图：）。插件代码如下：

<?php
/*
Plugin Name: External DB Connector
Description: Connects to an external WordPress database and retrieves data.
Version: 1.0
Author: Your Name
*/

// Exit if accessed directly.
if (!defined('ABSPATH')) {
    exit;
}

// Hook into WordPress
add_action('admin_menu', 'external_db_connector_menu');

function external_db_connector_menu()
{
    add_menu_page(
        'External DB Connector',
        'External DB Connector',
        'manage_options',
        'external_db_connector',
        'external_db_connector_page'
    );
}

function external_db_connector_page()
{
    ?>
    <div class="wrap">
        <h2>External DB Connector</h2>
        <?php
        // Connect to the external database
        $external_db = new wpdb('root', '', 'students', 'localhost');

        // Check the connection
        if ($external_db->last_error) {
            echo '<p>Error connecting to the external database: ' . esc_html($external_db->last_error) . '</p>';
        } else {
            echo '<p>Successfully connected to the external database.</p>';

            // Retrieve data from the 'student' table and 'ali' column
            $table_name = $external_db->prefix . 'students';
            $column_name = 'ali';

            // Perform the query
            $result = $external_db->get_results("SELECT $column_name FROM $table_name");

            // Check if the query was successful
            if ($result === null) {
                echo '<p>No data found in the "ali" column.</p>';
            } elseif ($external_db->last_error) {
                echo '<p>Error retrieving data: ' . esc_html($external_db->last_error) . '</p>';
            } else {
                echo '<p>Data from column "ali":</p>';
                foreach ($result as $row) {
                    echo '<p>' . esc_html($row->$column_name) . '</p>';
                }
            }
        }
        ?>
    </div>
    <?php
}

问题原因分析 #

1. 表名错误：从截图可以看到，数据库中的表名是student（单数），但代码中使用$external_db->prefix . 'students'（复数）生成表名。如果外部数据库没有WordPress的表前缀（比如默认的wp_），那么实际查询的表会是students，而你的数据库里只有student表，导致查询返回空结果。

2. 错误判断逻辑缺陷：当前代码中$result === null时直接提示“无数据”，但这种情况也可能是表不存在、SQL语法错误导致的，而非真的没有数据。

解决方案 #

1. 修正表名 #

直接使用实际的表名student，去掉不必要的表前缀拼接：

$table_name = 'student'; // 替换原有的$external_db->prefix . 'students'

2. 添加调试信息 #

在查询后输出实际执行的SQL语句，方便排查问题：

// Perform the query
$result = $external_db->get_results("SELECT $column_name FROM $table_name");
// 添加调试：输出实际执行的SQL
echo '<p>执行的SQL语句：' . esc_html($external_db->last_query) . '</p>';

3. 优化错误判断 #

先检查SQL是否有错误，再判断是否有数据：

// Check if the query was successful
if ($external_db->last_error) {
    echo '<p>Error retrieving data: ' . esc_html($external_db->last_error) . '</p>';
} elseif (empty($result)) {
    echo '<p>No data found in the "ali" column.</p>';
} else {
    echo '<p>Data from column "ali":</p>';
    foreach ($result as $row) {
        echo '<p>' . esc_html($row->$column_name) . '</p>';
    }
}

修改后的完整代码 #

<?php
/*
Plugin Name: External DB Connector
Description: Connects to an external WordPress database and retrieves data.
Version: 1.0
Author: Your Name
*/

// Exit if accessed directly.
if (!defined('ABSPATH')) {
    exit;
}

// Hook into WordPress
add_action('admin_menu', 'external_db_connector_menu');

function external_db_connector_menu()
{
    add_menu_page(
        'External DB Connector',
        'External DB Connector',
        'manage_options',
        'external_db_connector',
        'external_db_connector_page'
    );
}

function external_db_connector_page()
{
    ?>
    <div class="wrap">
        <h2>External DB Connector</h2>
        <?php
        // Connect to the external database
        $external_db = new wpdb('root', '', 'students', 'localhost');

        // Check the connection
        if ($external_db->last_error) {
            echo '<p>Error connecting to the external database: ' . esc_html($external_db->last_error) . '</p>';
        } else {
            echo '<p>Successfully connected to the external database.</p>';

            // Retrieve data from the 'student' table and 'ali' column
            $table_name = 'student';
            $column_name = 'ali';

            // Perform the query
            $result = $external_db->get_results("SELECT $column_name FROM $table_name");
            
            // 调试：输出执行的SQL
            echo '<p>执行的SQL语句：' . esc_html($external_db->last_query) . '</p>';

            // Check if the query was successful
            if ($external_db->last_error) {
                echo '<p>Error retrieving data: ' . esc_html($external_db->last_error) . '</p>';
            } elseif (empty($result)) {
                echo '<p>No data found in the "ali" column.</p>';
            } else {
                echo '<p>Data from column "ali":</p>';
                foreach ($result as $row) {
                    echo '<p>' . esc_html($row->$column_name) . '</p>';
                }
            }
        }
        ?>
    </div>
    <?php
}

内容的提问来源于stack exchange，提问作者Mohammad Mehmandoost