Solved

How to create a MySQL view for wp_users and wp_usermeta

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Passing value to a stored procedure 8 65
mysql Encryption with PHP 8 45
WP WooCom Prob 7 25
sql calculate averages 18 21
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 …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
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…

763 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

9 Experts available now in Live!

Get 1:1 Help Now