[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to create a MySQL view for wp_users and wp_usermeta

Posted on 2009-05-04
5
Medium Priority
?
3,063 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
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…
Suggested Courses

650 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