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

[Jim R]

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

 

  Quote

umeta_id,user_id,meta_key,meta_value

16484,1117,wp_capabilities,a:1:{s:15:"s2member_level2";s:1:"1";}

16491,1117,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"2";}

 

 

How do I get both lines in my query to show up so I can use them?

[scootstah]

You should be splitting that data into separate tables instead of serializing it if you wish to perform searches and whatnot on it. It is loads more efficient for the database.

[Jim R]

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

[SergeiSS]

You have meta_key equals "wp_s2member_custom_fields" (your first condition) in the last line only but not in both lines! Why do you surprised?

 

PS. scootstah is quite right when say "You should be splitting that data into separate table...". I'd add to it "...or into separate fields in one table".

[scootstah]

  Quote

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

 

I didn't notice it was WP. Good luck with that bundle of joy.

[Jim R]

  Quote

You have meta_key equals "wp_s2member_custom_fields" (your first condition) in the last line only but not in both lines! Why do you surprised?

 

PS. scootstah is quite right when say "You should be splitting that data into separate table...". I'd add to it "...or into separate fields in one table".

 

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. 

[SergeiSS]

  Quote

I didn't say I was surprised.  I asked how do I go about querying that data. 

It seems that you surprised Let's say you go to the cafe and say "give me a tea with sugar". You get a tea with sugar... But you ask "why do you put in sugar???". Correct answer - it's because you ask about it.

 

If you need both rows you have to exclude the first condition.

[Jim R]

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

[SergeiSS]

Just try...

 

SELECT * 
FROM wp_usermeta 
WHERE user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%")

[Jim R]

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

[SergeiSS]

  Quote

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

In the beginning you told about 1 or 2 rows...  Why do you have now 19 rows???

[Jim R]

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

[SergeiSS]

OK... Try this

 

SELECT * 
FROM wp_usermeta 
WHERE (meta_key = 'wp_s2member_custom_fields' or meta_key = 'wp_capabilities')
AND user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value LIKE '%s2member_level%')

 

[Jim R]

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.

[SergeiSS]

Did you try it or just thinking? I just add one more condition to you first query (I mean first query in this topic).

[Jim R]

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

[SergeiSS]

Yes, it's true... It selects all rows where (meta_key = 'wp_s2member_custom_fields' or meta_key = 'wp_capabilities') - do you understand this condition??? AND in the same time user_id is contained in this selection "(SELECT user_id FROM wp_usermeta WHERE meta_value LIKE '%s2member_level%')".

 

It seems that you don't like to think... You even can't say what do you like to get. OK, I hope you will start to think and find a solution for you task. Now it seems you don't know what do you like I can't help such a person. Sorry, but I leave this topic.

[Jim R]

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

[mikosiko]

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

umeta_id,user_id,meta_key,meta_value
16484,1117,wp_capabilities,a:1:{s:15:"s2member_level2";s:1:"1";}
16491,1117,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"2";}

 

please explain again in plain English what exactly do you have and what exactly do you need.

 

to me looks like that you are asking for record that have meta_key = "wp_s2member_custom_fields" OR meta_value LIKE "%s2member_level%", but I'm just guessing... because could also be that you need

meta_key = "wp_s2member_custom_fields" AND meta_value LIKE "%s2member_level%"

[Jim R]

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

[mikosiko]

use one of the options in my previous answers (the one that apply to what you want).. or test both and pick

[Jim R]

"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.

[PFMaBiSmAd]

TRY the query in reply #12. It will give you the pairs of rows you state you want.

 

However, you probably want to do a (self) JOIN to basically get one row per each user_id with all the information in it from the pairs of rows that have a s2member_level in the wp_capabilities row. This (using a join) would result in less logic because with two rows per user_id (what you are attempting now) you will need to test which of the two rows for each user_id you are dealing with as you loop through the result set. You will also need an order by term in the query so that the two rows having the same user_id are together in the result set.

[Jim R]

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

[Jim R]

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