Solved

How to create a MySQL view for wp_users and wp_usermeta

Posted on 2009-05-04
5
2,812 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

776 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