Solved

How to create a MySQL view for wp_users and wp_usermeta

Posted on 2009-05-04
5
2,887 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
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…

737 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