So I am trying to do something a little above my knowledge base, i think. Here's what I am trying to do:
- 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. - 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 - Then, I want to take the
id
from thesubscriber
table, and thename
from theinterest
table and insert it into another table called thesubscriber_interest
table. So if a subscriber signs up on more than one form, it will be a 1:n situation. Theid
s 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!
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.SELECT
, 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 usingJOIN
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 thesubscriber_interest
table.