Jim R

I changed the quotes in the Where line to double quotes. It didn't change anything.

It's just the unique key for that table. You asked me to show you the data, and I did. umeta_id | user_id | meta_key | meta_value 13068 | 439 | wp_s2member_custom_fields | a:1:{s:6:"county";s:1:"1";} 15195 | 439 | wp_capabilities | a:1:{s:15:"s2member_level1";s:1:"1";} 15569 | 439 | wp_s2member_custom_fields | a:1:{s:6:"county";s:1:"0";} 15195 | 439 | wp_capabilities | a:1:{s:15:"s2member_level1";s:1:"1";} Those are the columns. With each change a new row is shown. The query takes each pair of rows in the table and makes in one row in the query. I had a database issue switching from WP-multiuser to WP, and it duplicated some early users. I eliminated that data for you. What you see above now is the User switching what County they live in. I only want it to reflect the most recent, based on user_id.

This would be one example: (I assume um1.meta_id could be differentiated from um2.meta_id.) umeta_id,user_id,meta_key,meta_value,umeta_id,user_id,meta_key,meta_value 13068,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"1";},6680,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";} 13068,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"1";},15195,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";} 15569,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"0";},6680,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";} 15569,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"0";},15195,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";}

What appears to be happening is it's reflecting all the inputs, so if a User (such as my test accounts and a few others) have gone in and switched where they live. I'm getting their old and current locations. Is there a way to limit my search to the latest input from each ID?

That didn't have any effect. That's taken care of with: AND um2.meta_value LIKE "%s2member_level%" It would be: meta_key | meta_value wp_capabilities | %s2member_level%

Ok...I think this is getting what I'm wanting, but I'm getting repeats. It makes sense, but I don't want repeats since I'm eventually going to make a count of how many of each type of entry I have. The custom field shows where the User lives by a defined district. The s2member_level shows what subscription level the User has. I brought in the Join because it adds the user name and email address to the results. SELECT * FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 JOIN wp_users u ON um1.user_id=um2.user_id WHERE um1.meta_key = 'wp_s2member_custom_fields' AND um2.meta_value LIKE "%s2member_level%" AND um1.user_id = u.ID ORDER BY um1.user_id

Looking at Inner Joins, what is the "On" condition determining? Could that be my ultimate path?

Do you mean an inner join? I've not dealt with it, and the examples I see deal with different columns.

"AND" produced zero results. "OR" produced 201. It should be closer to 19 or so. They have to be of the same user_id. One row is noting what subscription level they have. The other tells what county they live in.

I'd like the two relevant rows of data for each ID that it applies to.

Uhm...I do like to think. The rows pulled have to have the same user_id. They don't.

I tried it. It pulled a number of rows that don't have the s2member_level value.

I'm not sure yet what that gave me. It appeared to pull anything with wp_capabilities, whether if it had s2member_level or not.

Yeah...both rows of data for each user that met the criteria.

Instead of 19 rows of data, it produced 1775 rows of data.

Not sure what excluding it will do. I need all that information linked to the user_id.

I didn't say I was surprised. I asked how do I go about querying that data. @Scootstah, I've never really had too many problems with it beyond just lacking a deeper knowledge of how to query a database. It's a learning process.

I can't really. It's how WordPress inputs their user information.

Not sure how to go about this, but I haven't been able to get the right query or even know where to look it up. Below are two rows of data. The following query (someone from here helped me with that query) only gives me the bottom of the two rows: SELECT * FROM wp_usermeta WHERE meta_key = "wp_s2member_custom_fields" AND user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%") How do I get both lines in my query to show up so I can use them?

bump

I'm dealing with data that WordPress creates for Users when they register and subscribe, and to use it for other purposes, I have to wrap my head around serialized data. It's not sinking in well. The below code works well for what I wanted at the time. It takes into consideration where the Subscriber lives (divided up into five areas) and counts how many I have. That is noted by wp_s2member_custom_fields and ANY s2member_level. There are three levels, and I need to actually note how many there are at each Member level. There is another line of data for each user_id meta_key = wp_capabilities meta_value = a:1:{s:15:"s2member_level2";s:1:"1";} $custom = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_s2member_custom_fields" AND user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%")'; $c_results = mysql_query($custom); $region = array(); while($line = mysql_fetch_assoc($c_results)) { $meta_value = unserialize($line['meta_value']); $region[$meta_value['county']]++; }; foreach ($region as $key => $value) { echo "Region $key: $value members<br>"; }

That worked. Thanks. And as usual, figuring something out means more work, and that means I'll likely be back in another topic for more help. I do appreciate all the help provided here.

I have a column with a lot of "wp_1_" in them. I want them to just be "wp_". I've backed up my table. Here is the code I'm looking at: UPDATE wp_usermeta SET meta_key LIKE "%wp_%" WHERE meta_key LIKE "%wp_1_%" Is that remotely close?

Crap, that easy enough. UPDATE jwrbloom_hhr.hhr_schools t1 JOIN jwrbloom_hhm.schools t2 ON t1.id = t2.id SET t1.email = t2.email WHERE t2.email IS NOT NULL

I'm hoping writing down my issue will help me find the logic in it. I have two tables in two different databases that are essentially the same. I've updated each separately at different times without remembering I had two of them. Now I'm trying to merge information from the email columns. These are coaching jobs which have changed since last year. There were around 90 changes. I'm trying to move the emails I have in t2 that I don't have in t1. I have NULL cells in both columns, and I don't want to overwrite emails I already have in t1 with NULL cells I have in t2. I need something that Sets t1.email Where t1.email isn't equal to t2.email and t2.email Is Not Null. (I should have done this before I changed the coaches' names. I could have used that as a trigger.) Would that be the logic? I definitely need help with the syntax. UPDATE jwrbloom_hhr.hhr_schools t1 JOIN jwrbloom_hhm.schools t2 ON t1.id = t2.id SET t1.email = t2.email WHERE ??????