Solved

How to create a MySQL view for wp_users and wp_usermeta

Posted on 2009-05-04
5
2,777 Views
Last Modified: 2012-05-06
Does anyone have an idea on how I can create a view in MySQL off a WordPress database so I can query wp_users and get a recordset back with all the wp_usermeta data as well?
If you are not familiar, wp_users contains the main record and wp_usermeta contains extra information with user_id as the key to wp_users. wp_usermeta contains a field meta_key and meta_value which would be the extra field name in the value of that field.

Some values in the wp_usermeta.meta_key for your example view are first_name, last_name.

I also have another table called wp_cimy_uef_fields and wp_cimy_uef_data I would like to do the same thing with. This is from a plug-in with even more custom fields. wp_cimy_uef_fields contains an ID column and a NAME column which would be the field value in the view and wp_cimy_uef_data contains USER_ID which is the key to wp_users, FIELD_ID which is the key to wp_cimy_uef_fields and VALUE which is the value of the field.

Some field in wp_cimy_uef_fields.NAME for the example view are ADDRESS, CITY, STATE

Thanks
CREATE TABLE IF NOT EXISTS `wp_users` (

  `ID` bigint(20) unsigned NOT NULL auto_increment,

  `user_login` varchar(60) NOT NULL default '',

  `user_pass` varchar(64) NOT NULL default '',

  `user_nicename` varchar(50) NOT NULL default '',

  `user_email` varchar(100) NOT NULL default '',

  `user_url` varchar(100) NOT NULL default '',

  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',

  `user_activation_key` varchar(60) NOT NULL default '',

  `user_status` int(11) NOT NULL default '0',

  `display_name` varchar(250) NOT NULL default '',

  PRIMARY KEY  (`ID`),

  KEY `user_login_key` (`user_login`),

  KEY `user_nicename` (`user_nicename`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=118 ;
 
 

CREATE TABLE IF NOT EXISTS `wp_usermeta` (

  `umeta_id` bigint(20) NOT NULL auto_increment,

  `user_id` bigint(20) NOT NULL default '0',

  `meta_key` varchar(255) default NULL,

  `meta_value` longtext,

  PRIMARY KEY  (`umeta_id`),

  KEY `user_id` (`user_id`),

  KEY `meta_key` (`meta_key`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1127 ;
 

CREATE TABLE IF NOT EXISTS `wp_cimy_uef_fields` (

  `ID` bigint(20) NOT NULL auto_increment,

  `F_ORDER` bigint(20) NOT NULL,

  `NAME` varchar(20) default NULL,

  `LABEL` text,

  `DESCRIPTION` text,

  `TYPE` varchar(20) default NULL,

  `RULES` text,

  `VALUE` text,

  PRIMARY KEY  (`ID`),

  KEY `F_ORDER` (`F_ORDER`),

  KEY `NAME` (`NAME`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
 

CREATE TABLE IF NOT EXISTS `wp_cimy_uef_data` (

  `ID` bigint(20) NOT NULL auto_increment,

  `USER_ID` bigint(20) NOT NULL,

  `FIELD_ID` bigint(20) NOT NULL,

  `VALUE` text NOT NULL,

  PRIMARY KEY  (`ID`),

  KEY `USER_ID` (`USER_ID`),

  KEY `FIELD_ID` (`FIELD_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=963 ;

Open in new window

0
Comment
Question by:marms676
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 24304081
you do realize that a join on these two tables will return multiple rows per single record?

what are you trying to achieve with this? there is probably a better way.

0
 

Author Comment

by:marms676
ID: 24304861
I need one main "table" that I can query from that contains all this data. Right now it is very difficult to work with. The only way currently to see everything is to loop over the main table wp_users in a WordPress PHP page. Then on each iteration I have to use a function pf_get_vars() to get all the values in wp_usermeta for that record. At the same time I need to use another function get_cimyFieldValue($user_id, 'ADDRESS') in which like the example I enter the user_id from the first table and the key value that I want returned.

In MS SQL Server I have used subqueries in the SELECT statement to do something similar.

Example:
select a.id, (select top 1 meta_value from wp_usermeta where user_id = a.id and meta_key = 'first_name') as first_name
from wp_users a

In this case it returns each of the main table records and then it returns only one result from wp_usermeta for that user_id, meta_key combination and then I alias it as that first_name. Technically there should be only one record for that combination, but I use the TOP 1 just in case.

I was thinking of a large query in which each value I need from the foreign tables would be return.

After it is a VIEW, then I could do searches like

SELECT *
FROM user_view
WHERE Address like '%123 NW 34 St%'
AND State = 'OK'
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 24306827
ok you don't say which plugin you are using but this
get_usermeta(userid,'metakey');
will get you any thing from the user meta table as ling as you have the id, let me know which plugin you are using, they should have something similar
0
 

Author Comment

by:marms676
ID: 24306888
The other plug is Cimy extra fields
http://www.marcocimmino.net/cimy-wordpress-plugins/

Yes, they do have something like that as I stated in my last post
get_cimyFieldValue($user_id, 'ADDRESS')

I use this now, but my post question is how do have a view of all this data that is external from WordPress.
0
 

Accepted Solution

by:
marms676 earned 0 total points
ID: 24316607
I figured out my question. I didn't realize you could do a subquery in the select statement. I reference each field I need out of the other tables and user the user_id key. I then do a LIMIT 1 just to make sure there are not more than one result returned. (There should be only one.)
CREATE VIEW user_view AS

select a.id as user_id, a.user_email,a.user_url, 

(select meta_value from wp_usermeta where user_id = a.id and meta_key = 'first_name' limit 1) as first_name,

(select meta_value from wp_usermeta where user_id = a.id and meta_key = 'last_name' limit 1) as last_name,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'BUSINESSNAME' limit 1) as BUSINESSNAME,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'ADDRESS' limit 1) as ADDRESS,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'CITY' limit 1) as CITY,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'STATE' limit 1) as STATE,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'ZIP' limit 1) as ZIP,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'CELLPHONE' limit 1) as CELLPHONE,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'BUSINESSPHONE' limit 1) as BUSINESSPHONE,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'HOMEPHONE' limit 1) as HOMEPHONE,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'TITLE' limit 1) as TITLE,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'BIRTHDAY' limit 1) as BIRTHDAY,

(select b.value from wp_cimy_uef_data b  inner join wp_cimy_uef_fields c on (b.FIELD_ID = c.ID) WHERE b.USER_ID = a.id AND c.NAME = 'GROUPNUMBER' limit 1) as GROUPNUMBER 

from wp_users a

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
This video teaches users how to migrate an existing Wordpress website to a new domain.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now