Jump to content

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


Jim R

Recommended Posts

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

 

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?

Link to comment
Share on other sites

  • Replies 51
  • Created
  • Last Reply

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.