SQL Query Help
I have an interesting question, and haven't found any answers during my searching of drupal.org.
I set up a new field in the user profile called "Newsletter", it's simply a checkbox whether the user wants to receive a newsletter or not. I am planning on writing a module to export all of the user email addresses who have "opted in" (checked the box, it's optional) to the newsletter. There is already a newsletter mailing list we use, and I would like to find a way to export all the user email addresses and user names so I can add them to my other list.
I've run into a bit of a hitch though, and it has to do with my knowledge of SQL. I've browsed the PHPmyAdmin, and I noticed a table called "profile_fields" which lists the extra fields I have added. However, the users table doesn't include any (from what I can tell) flags or information about whether or not a particular user has that field checked or not.
Is there a way I can somehow do a join on the two tables to achieve the desired results? I'm not too familiar with advanced queries, especially across tables. If there isn't, how can I go about reaching my goal? Would I have to have a custom module import data fields from both tables and then run a query on that new table? If so, I'll do some searching into how to copy single columns from multiple tables into one new table.
Thanks in advance for any help and guidance.
You need to join three
You need to join three tables:
(c) users - Each row contains a user's info and most importantly the user's ID (uid). But you can't connect this directly with any profile field, because it doesn't contain any profile field information.
(a)profile_fields - Each row contains general info about a field that you defined (not values entered by users). Its name etc, and most importantly its ID (fid). But you can't connect this directly with any user, because it doesn't contain any user information.
(b) profile_values - Each row contains a value that a user added to a field, and also the info needed to associate it with a user and a field (uid and fid). It doesn't contain the username or the field's name, but any connections to get those must go through this table.
To get the users who have checked a box that its machine-readable name is "profile_my_checkbox", you can do roughly something like this:
$profile_field_name = "profile_my_checkbox";
$query_string =
"SELECT u.name, u.mail, pf.title
FROM profile_values AS pv
JOIN profile_fields AS pf ON pf.fid=pv.fid
JOIN users AS u ON u.uid=pv.uid
WHERE pv.value = 1 AND pf.name = '%s' ";
$result = db_query($query_string, $profile_field_name);
$output = 'Newsletter subscriptions' . '<br />';
$output .= '------------------------' . '<br />';
while ($subscription = db_fetch_object($result) ) {
$output .= $subscription->name . ' ';
$output .= $subscription->mail . ' ';
$output .= $subscription->title . '<br />';
}
print $output;
Brilliant!
Thanks for the SQL query help, I can now see all the information I need. Now I can keep poking around with trying to get a nicely exported Excel file with this information :)