Query matching multiple columns in multiple rows??? (serialized data?)

[Jim R]

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

 

 

[PFMaBiSmAd]

You need to add -

 

AND um2.meta_key = 'wp_capabilities'

[Jim R]

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%

 

 

[Jim R]

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?

[PFMaBiSmAd]

Edit: I'm going to post this anyway, even if I started writing it before your last reply, and you have since indicated there is more than one value/matching rows.

 

If you were to post some sample results and indicate what part of it is duplicated, someone can probably help. We don't know what different types of data there are for any one user_id, don't have your database to test with, don't have or don't want to setup wp, and don't know what result you are getting.

 

Edit: As to your last post, of course. What value is present in the data that can be used to identify the latest value?

[Jim R]

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";}

 

[PFMaBiSmAd]

Since I don't know what the umeta_id value means, I don't think I can offer any further assistance. One could start making assumptions about it, but one could also make an assumption that wp would not store the same data in two different rows (the two sets of wp_capabilities are identical for that user, but with different umeta_id values.)

 

 

[Jim R]

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.

 

 

[Andy-H]

Don't know if anyone's said this (yeh, call me lazy, but 3 pages  ) but are you quoting that query with double quotes or single quotes? Because you either need to quote your query in single quotes, or quote your clause conditions in single quotes and quote the query in double quotes.

[Jim R]

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

[Andy-H]

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%")

 

$query = "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%')";

 

 

Like that?

[Jim R]

We are well beyond that query. 

[Andy-H]

Ok, I'll read :'(

[Jim R]

:-)

[Andy-H]

 

SELECT GROUP_CONCAT(CONCAT(umeta_id, '~', user_id, '~', meta_key, '~', meta_value) SEPARATOR '@') 
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%")
GROUP BY user_id

 

 

 

??

 

 

You could use 2 query's, otherwise, I need you to explain EXACTLY what you're expecting to get back from the query?

[Jim R]

The query I have works except for duplicates.  I just need the latest submission (change in their profile) from the User.

[Andy-H]

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

 

 

 

Is that the one?

 

 

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
GROUP BY um1.user_id
ORDER BY um1.user_id, um1.umeta_id DESC

 

//edit sorry group by before order by

[Jim R]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY um1.user_id' at line 7

[PFMaBiSmAd]

At this point, because the point of storing serialized data, is to store an array in a database, I suspect that there should only be one row with the wp_s2member_custom_fields per user_id and when you edit your profile, it reads that row, modifies the values, and writes the result back into that row.

[Jim R]

It doesn't write it back into the row.  It creates a separate row.  WP isn't really creating the entry, a plug-in is. 

[PFMaBiSmAd]

Any chance that the non-zero 'country' value is the 'active' one? (The current version of that plugin apparently stores a two character country code, not a number.)

 

The current version of that plugin actually calls a wp function to get the data. I doubt it expects more than one matching row. Someone (I don't have the time right now) might want to trace through the code for that plugin and the relevant function in wp to find out if it does anything special when a request for user information matches more than one row.

[Jim R]

It's a County code, not country, just one number, 1-5. I set up the custom field within the plugin. Zero is the default, and all new subscribers will have to declare in the future.

[PFMaBiSmAd]

I just installed the latest of wp and that s2member script and when you create a field in the profile and update the value (tested multiple times, different values, then back to a zero), it updates the one single row in the database. The row is initially created on the first edit/update of the value.

 

It would appear that your sets of two rows for wp_s2member_custom_fields with the same user_id are due to multiple data insertion in your database and are not the result of the operation of the script.

 

Edit: I would make the user_id/meta_key columns a composite and unique index to prevent duplicates.

[PFMaBiSmAd]

Slightly off topic, but now that I am somewhat up to speed on what that data means, if you were doing this query for security purposes, you would want the suggested AND um2.meta_key = 'wp_capabilities' in the query so that someone could not just store "administrator" or a higher user level string as a value in any one of their meta_value fields and magically become an administrator or gain a higher user level.

[Jim R]

OK...I tested it, and that appears to be the case now, which is good.  It doesn't appear that has always been the case.  I have only one custom field, and my test accounts clearly prompted separate rows of data when I changed its County.  The four most recent accounts don't have duplicates.

 

It could have been a change via an update in the last month.