$30
How do I find out how many unique users we have, across databases

I would like to know how many unique users Codewi.se has across its sites. I'm happy to measure unique email addresses as a reasonable proxy. We now have 5 sites but each is in a separate database and each has its own user system. Our sites are:

WP Questions

Symfony Experts

Javascript Questions

PHP Emergency

plus this site that you are reading now.

There is a lot of overlap among the users of these sites. I'd like to find the total of unique email addresses.

We use the sfGuardUserPlugin to manage users, but we use this custom table to collect such information as email addresses:

CREATE TABLE IF NOT EXISTS `sf_guard_user_profile` (

  `id` int(11) NOT NULL auto_increment,

  `user_id` int(11) NOT NULL,

  `status` varchar(255) character set utf8 NOT NULL default '',

  `first_name` varchar(255) character set utf8 NOT NULL default '',

  `last_name` varchar(255) character set utf8 NOT NULL default '',

  `email` varchar(255) character set utf8 NOT NULL default '',

  `url` varchar(255) character set utf8 NOT NULL default '',

  `biography` text character set utf8 NOT NULL,

  `created_at` datetime default NULL,

  `updated_at` datetime default NULL,

  `cash_total` float default NULL,

  `image` varchar(255) character set utf8 NOT NULL default '',

  `secret_signup_key` varchar(255) character set utf8 NOT NULL default '',

  `twitter_address` varchar(255) character set utf8 NOT NULL default '',

  `landing_page` varchar(255) character set utf8 NOT NULL default '',

  `subscription_paid` datetime NOT NULL,

  `paypal_email_address` varchar(255) character set utf8 NOT NULL default '',

  `subscribe_to_questions_via_email` varchar(1) character set utf8 NOT NULL default '',

  `clients_can_contact` char(1) NOT NULL,

  PRIMARY KEY  (`id`),

  KEY `sf_guard_user_profile_FI_1` (`user_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

What is the best way of querying this table in 5 separate databases to get the number of unique email addresses?

By the way, many users have asked for single-signon across the sites. To be sure, we plan to implement RPX just as soon as I have some free time to do it.

Lawrence Krubner | 05/31/10 at 1:20pm | Edit

multiple databases, rpx, sum, unique, users

(1) Possible Answers Submitted...

Last edited:
06/03/10
11:41am
Oleg Butuzov says:
get all db in auto mode
```
SELECT 	TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sf_guard_user_profile'
```
select all
```
SELECT email

FROM development_wp_plugins.sf_guard_user_profile

UNION SELECT email

FROM development_wp_tuning.sf_guard_user_profile

UNION SELECT email

FROM reviewer_parsers.sf_guard_user_profile

GROUP BY email 
```
count for results?
---------------------

one more way to create a tmp folder insert data into it, and select from it. witch is actualy same stuff as prevoius query.

Previous versions of this answer: 05/31/10 at 4:00pm | 05/31/10 at 6:51pm
- 06/03/10 11:41am
  
  Lawrence Krubner says:
  Thanks, Oleg.