MySQL - How to combine data from two tables into one?

Question

So I am trying to do something a little above my knowledge base, i think. Here's what I am trying to do:

1. I want to insert values from a form into a subscriber table ( first_name, last_name, email). In this table the ID is auto-increment and I have a Boolean called is_subscribed, that is set to true at the moment the user signs up.
2. I also want to insert their subscription type into an interest table (name, description). For example, if they sign up using an RSVP form I want to capture that in the interest table
3. Then, I want to take the id from the subscriber table, and the name from the interest table and insert it into another table called the subscriber_interest table. So if a subscriber signs up on more than one form, it will be a 1:n situation. The ids on this table are not unique.

The subscriber table with test data will look like this:

+----+------------+-------------+------------------------+---------------+
| 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 |
+----+------------+-------------+------------------------+---------------+

The interest table with test data will look like this:

+----+--------------------+--------------------------------------------------+---------------+
| 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    |
+----+--------------------+--------------------------------------------------+---------------+

The subscriber_interest table with test data will look like this:

    +----+---------+
    | id | name    |
    +----+---------+
    |  3 | RSVP    |
    |  3 | Sign Up |
    | 10 | RSVP    |
    | 23 | Sign Up |
    +----+---------+

When I run the SQL through a SQL terminal it works as I would expect it to: it takes the id of the subscriber and the name of the interest and copies it over to the subscriber_interest table.

Great news! Well, until I ran it through 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;
}

When that is instantiated and ran I get this error:

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' ) )

No matter what I tried I couldn't figure out what was wrong with the select statement. So I tried a more WordPress-heavy approach:

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

Surprisingly, I get a very similar error as before, surrounding my select statements:

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, '')

Also a new INSERT INTO error, but that's to be expected since the select statements didn't evaluate and it's trying to insert non-sense.

Here is my table creation code:

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: How should I/do I properly combine data from two different tables using WordPress and MySQL? Is my data structure fine, but my queries are the problem? Any help on this would be great, as I am new to MySQL. Thank you for your time!

Answer 1

Syntax

The issue with your Wordpress approach is the get_var calls used to get the subscriber id and interest name. get_var is intended to get just a variable from the database like the number of rows in the table or sum of a numeric column in the table, see the Wordpress Codex

What your looking to do is select a row, using 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;

}

Table Structure

The table structure you describe is going to give you an subscriber_interest table that links to the interest table using a non-unique column the name, so if there are two seperate interest rows that are both named RSVP things are going haywire.

I'd recommend using the id column to link the interest table to the subscriber interest.