MySQL-如何将两个表中的数据合并为一个表？

时间：2015-12-02 作者：mrClean

所以我想我正在尝试做一些超出我知识基础的事情。以下是我想做的：

我想将表单中的值插入subscriber 表（first_name, last_name, email). 在这个表中，ID是自动递增的，我有一个名为is\\u subscribed的布尔值，在用户注册时设置为trueinterest 表(name, description). 例如，如果他们使用RSVP表单注册，我想在兴趣表中记录该表单，然后，我想id 从subscriber 表，以及name 从interest 并将其插入另一个名为subscriber_interest 桌子因此，如果订阅者在多个表单上注册，则情况将是1:n。这个id此表上的不是唯一的Thesubscriber 包含测试数据的表如下所示：

+----+------------+-------------+------------------------+---------------+
| id | first_name | last_name   | email                  | is_subscribed |
+----+------------+-------------+------------------------+---------------+
|  1 | Katheryn   | Moleready   | [email protected]    |             1 |
|  2 | Robert     | DeLong      | [email protected]     |             1 |
|  3 | Carina     | Slovikyna   | [email protected] |             1 |
|  4 | Frederick  | Wilmington  | [email protected]         |             1 |
|  5 | Jackson    | Galaxy      | [email protected]      |             1 |
|  6 | Red        | Foreman     | [email protected]   |             1 |
|  7 | Richard    | Worthington | [email protected]    |             1 |
|  8 | Simonne    | DaKova      | [email protected]  |             1 |
|  9 | Howard     | Remmington  | [email protected] |             1 |
| 10 | Benjamin   | Ratfield    | [email protected]  |             1 |
| 23 | Bill       | Pollman     | [email protected]       |             1 |
+----+------------+-------------+------------------------+---------------+
Theinterest 包含测试数据的表如下所示：

+----+--------------------+--------------------------------------------------+---------------+
| id | name               | description                                      | interest_date |
+----+--------------------+--------------------------------------------------+---------------+
|  5 | Classes            | The subscriber has shown an interest in classes. | NULL          |
|  4 | Free Stuff Sign Up | Sign up for receiving offers and free stuff      | NULL          |
|  3 | Meditation Sign Up | Sign up for Meditation things                    | NULL          |
|  1 | RSVP               | Reserve a spot in one of our Events.             | NULL          |
|  2 | Sign Up            | General sign up for nothing in particular        | NULL          |
| 25 | Some Free Stuff    | Its like regular free stuff, but only different  | NULL    |
| 27 | Some Free Stuff2   | Its like regular free stuff, but only different  | NULL    |
+----+--------------------+--------------------------------------------------+---------------+
Thesubscriber_interest 包含测试数据的表如下所示：

    +----+---------+
    | id | name    |
    +----+---------+
    |  3 | RSVP    |
    |  3 | Sign Up |
    | 10 | RSVP    |
    | 23 | Sign Up |
    +----+---------+
当我通过SQL终端运行SQL时，它的工作方式与我预期的一样：它需要id 的subscriber 以及name 的interest 并将其复制到subscriber_interest 桌子

好消息！在我通过WordPress运行之前：

public function update_subscriber_interest($subscriber_id, $interest_name) {
    global $wpdb;
    $select_subscriber_id = $subscriber_id;
    $select_interest_name = $interest_name;

    $sql = $wpdb->query( 
        $wpdb->prepare(
            "
            INSERT INTO {$wpdb->prefix}subscriber_interest (id, name)
            VALUES 
                (
                    (
                        SELECT 
                            {$wpdb->prefix}subscriber.id
                        FROM 
                            {$wpdb->prefix}subscriber
                        WHERE 
                            {$wpdb->prefix}subscriber.id = %d
                    ),
                    (
                        SELECT 
                            {$wpdb->prefix}interest.name
                        FROM 
                            {$wpdb->prefix}interest
                        WHERE 
                            {$wpdb->prefix}interest.name = %s
                    )
                )
            ",
            $select_subscriber_id,
            $select_interest_name
        )
    );
    return $sql;
}
当实例化并运行时，我得到以下错误：

WordPress database error: [You have an error in your SQL syntax; check the     manual that corresponds to your MySQL server version for the right syntax to use near \'js_subscriber.id FROM  js_subscriber WHERE  \' at line 6]
INSERT INTO js_subscriber_interest (id, name) VALUES ( ( SELECT  js_subscriber.id FROM  js_subscriber WHERE  js_subscriber.id = 2 ), ( SELECT  js_interest.name FROM  js_interest WHERE  js_interest.name = \'Sign Up\' ) )
无论我做了什么尝试，我都无法找出select语句的错误。因此，我尝试了一种更注重文字的方法：

public function update_subscriber_interest($subscriber_id, $interest_name) {
    global $wpdb;

    $select_subscriber_id = $subscriber_id;
    $select_interest_name = $interest_name;

    $get_subscriber_id = $wpdb->get_var(
        "SELECT * FROM {$wpdb->prefix}subscriber WHERE id =   ".$select_subscriber_id.""
    );

    $get_interest_name = $wpdb->get_var(
        "SELECT * FROM {$wpdb->prefix}interest WHERE name = \'".$select_interest_name."\'"
    );

    $sql = $wpdb->insert(
        "{$wpdb->prefix}subscriber_interest",
        array(
            "id"    => $get_subscriber_id[\'id\'],
            "name"  => $get_interest_name[\'name\']
        ),
        array(
            \'%d\',
            \'%s\'
        )
    );
    return $sql;
}
令人惊讶的是，我在select语句周围遇到了与之前非常相似的错误：

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'SELECT * FROM js_subscriber WHERE id = 2\' at line 1]
SELECT * FROM js_subscriber WHERE id = 2

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'SELECT * FROM js_interest WHERE name = \'Sign Up\'\' at line 1]
SELECT * FROM js_interest WHERE name = \'Sign Up\'

WordPress database error: [Duplicate entry \'0-\' for key \'PRIMARY\']
INSERT INTO `js_subscriber_interest` (`id`, `name`) VALUES (0, \'\')
也是一个新的INSERT INTO 错误，但这是意料之中的，因为select语句没有求值，它正在尝试插入无意义。

以下是我的表创建代码：

register_activation_hook( __FILE__, \'create_subscriber_db\' );

function create_subscriber_db() {
    global $wpdb;

    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . "subscriber";

    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        first_name varchar(25) NOT NULL,
        last_name varchar(20) NOT NULL,
        email varchar(100) NOT NULL,
        is_subscribed BOOLEAN NOT NULL,
        UNIQUE KEY id (id),
        PRIMARY KEY (id)
    ) $charset_collate;";

    require_once( ABSPATH . \'wp-admin/includes/upgrade.php\' );
    dbDelta( $sql );
}

register_activation_hook( __FILE__, \'create_interest_db\' );

function create_interest_db() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . "interest";

    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        name varchar(25) NOT NULL,
        description text,
        interest_date date NULL,
        UNIQUE KEY id (id),
        PRIMARY KEY (name)
    ) $charset_collate;";

    require_once( ABSPATH . \'wp-admin/includes/upgrade.php\' );
    dbDelta( $sql );
}

register_activation_hook( __FILE__, \'create_subscriber_interest\' );

function create_subscriber_interest() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . "subscriber_interest";

    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL,
        name varchar(25) NOT NULL,
        PRIMARY KEY (id, name)
    ) $charset_collate;";

    require_once( ABSPATH . \'wp-admin/includes/upgrade.php\' );
    dbDelta( $sql );
}
My Question is: 我应该如何使用WordPress和MySQL正确组合来自两个不同表的数据？我的数据结构是否良好，但问题出在我的查询上？这方面的任何帮助都会很好，因为我是MySQL新手<谢谢你抽出时间！

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

语法你的Wordpress方法的问题是get_var 用于获取订户id和兴趣名称的调用。get_var 仅用于从数据库中获取一个变量，如表中的行数或表中数字列的总和，请参阅the Wordpress Codex

您要做的是使用get_row

public function update_subscriber_interest($subscriber_id, $interest_name) {
global $wpdb;

$select_subscriber_id = $subscriber_id;
$select_interest_name = $interest_name;

$get_subscriber_id = $wpdb->get_row(
    "SELECT * FROM {$wpdb->prefix}subscriber WHERE id =   ".$select_subscriber_id.""
);

$get_interest_name = $wpdb->get_row(
    "SELECT * FROM {$wpdb->prefix}interest WHERE name = \'".$select_interest_name."\'"
);

$sql = $wpdb->insert(
    "{$wpdb->prefix}subscriber_interest",
    array(
        "id"    => $get_subscriber_id->id,
        "name"  => $get_interest_name->name
    ),
    array(
        \'%d\',
        \'%s\'
    )
);
return $sql;
}

表结构

您描述的表结构将为您提供一个subscriber\\u利息表，该表使用一个非唯一的列名称链接到利息表，因此如果有两个单独的利息行都命名为RSVP，那么事情就会失控。

我建议使用id列将利息表链接到订阅者利息。

标签： mysql   小码农  

相关推荐

如何将CURRENT_TIME(“MySQL”，FALSE)形成的WordPress时间加2小时？

如何在使用已形成的wordpress时间基础上增加2小时current_time(\"mysql\", false)?