0

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!

4
  • Your SQL subqueries don't make sense. Basically they read "SELECT NAME WHERE NAME = "abc". You don't need that select at all. You already have the name, "abc"
    – s_ha_dum
    Dec 3, 2015 at 3:14
  • Oh, you make a good point! I'll have to try that tomorrow, and see if it's the answer. I was only using the WHERE to compare strings and protect against selecting the wrong row. Is this the best way to do this kind of query? I'm pretty new to SQL.
    – mrClean
    Dec 3, 2015 at 3:46
  • However, I think I still need theSELECT, or comparable statement, because I'm trying to "copy" parts of data from two different tables into one table. That's why I was wondering about using JOIN and if that would make more sense, or if the way I have it makes the more sense. Either way, I want to be able to pull up information on a subscriber (subscriber table), and the methods (interests table) they used to subscribe with and put it in the subscriber_interest table.
    – mrClean
    Dec 3, 2015 at 6:16
  • You need to edit the question to reflect what you need in detail, and preferably explain what you want to do in addition to what you are trying to do to achieve the goal.
    – s_ha_dum
    Dec 3, 2015 at 13:32

1 Answer 1

0

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.

2
  • Thanks, that works great! Thank for you taking the time. How would I use the id column to link the interest table to the subscriber interest? Could you point me to some resources or show me some sample code? It sounds like it would be a JOIN to me, but I don't know if that's right.
    – mrClean
    Dec 3, 2015 at 23:49
  • By "link" I only meant to include the ID as it appears I the other table. The code above should serve you fine for your purposes. If you want to use a join look at this example Dec 4, 2015 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.