What's wrong with this code?

I have 3 tables: wp_users, wp_usermeta and wp_bp_xprofile_data.

They all have one column in common – user_id (called ID in the wp_users table).

I want to

  • find users who have the value like $teamNameWithoutCityUppercase in field_id 166
  • pull the URL of their avatar (bp_core_avatar_v1)

This code works:

"SELECT wp_users.user_login FROM wp_users INNER JOIN wp_bp_xprofile_data ON wp_bp_xprofile_data.user_id=wp_users.ID WHERE wp_bp_xprofile_data.field_id=166 AND UPPER(wp_bp_xprofile_data.value) LIKE '%%" . $teamNameWithoutCityUppercase . "%%' ORDER BY RAND()"

This code does not:

"SELECT wp_users.user_login FROM wp_users INNER JOIN wp_usermeta ON wp_usermeta.user_id=wp_users.ID WHERE wp_usermeta.meta_key=bp_core_avatar_v1 INNER JOIN wp_bp_xprofile_data ON wp_bp_xprofile_data.user_id= wp_usermeta.user_id WHERE wp_bp_xprofile_data.field_id=166 AND UPPER(wp_bp_xprofile_data.value) LIKE '%%" . $teamNameWithoutCityUppercase . "%%' ORDER BY RAND()"

(I am storing this as a variable and the first code outputs correctly.)

What’s wrong with the 2nd code?

you’re not allowed to shoehorn an extra WHERE clause in between two INNER JOINs

you’re not allowed to have more than one WHERE clause, and it must come after the FROM clause

all the joins are part of the FROM clause

helps?

:slight_smile:

I take it those are all separate issues.

  1. Which extra WHERE is shoehorned?

  2. What’s the best option where logic like 2 WHEREs are neded?

  3. So the joins should be separated with parenths?

Thanks for your help. Sometimes this logic stuff makes my head spin.(:

the one marked in red here…


SELECT wp_users.user_login
FROM wp_users
INNER
JOIN wp_usermeta
ON wp_usermeta.user_id = wp_users.ID
WHERE wp_usermeta.meta_key = bp_core_avatar_v1
INNER
JOIN wp_bp_xprofile_data
ON wp_bp_xprofile_data.user_id = wp_usermeta.user_id

WHERE wp_bp_xprofile_data.field_id = 166
AND UPPER(wp_bp_xprofile_data.value) LIKE ‘%%" . $teamNameWithoutCityUppercase . "%%’
ORDER
BY RAND()





[quote="jungerpants,post:3,topic:73042"]
2) What's the best option where logic like 2 WHEREs are neded?
[/quote]
combine them with AND or OR


[quote="jungerpants,post:3,topic:73042"]
3) So the joins should be separated with parenths?
[/quote]
no

:)

I’m getting closer – but am having issues with the location of the second WHERE statement.

SELECT wp_users.user_login 
     FROM wp_users 
  INNER 
     JOIN wp_usermeta 
     ON wp_usermeta.user_id=wp_users.ID 
   INNER JOIN wp_bp_xprofile_data 
     ON wp_bp_xprofile_data.user_id=wp_usermeta.user_id 
    WHERE  wp_bp_xprofile_data.field_id=166 
     AND UPPER(wp_bp_xprofile_data.value) LIKE '%%" . $teamNameWithoutCityUppercase . "%%' 
ORDER BY RAND()"

This is the only thing left to insert – the 2nd “where”:

 wp_usermeta.meta_key=bp_core_avatar_v1

I’ve tried placing it after the WHERE and using an AND in between it and wp_bp_xprofile_data.field_id=166, as well as at the end of the statement with an AND, but no luck.

Any thoughts? Thanks r937.

Never mind! It’s a quote issue.

This seems to work:

SELECT wp_users.user_login FROM wp_users INNER JOIN wp_usermeta ON wp_usermeta.user_id=wp_users.ID INNER JOIN wp_bp_xprofile_data ON wp_bp_xprofile_data.user_id=wp_usermeta.user_id WHERE  wp_bp_xprofile_data.field_id=166 AND wp_usermeta.meta_key='bp_core_avatar_v1' AND UPPER(wp_bp_xprofile_data.value) LIKE '%%" . $teamNameWithoutCityUppercase . "%%' ORDER BY RAND()

Thanks for your help!

what table is bp_core_avatar_v1 coming from?

oh, i just saw your update, it’s a string :slight_smile:

i thought it was a colulmn and should have been part of one of the joins

Yup. Of course, now I realize that I (think I) also need to SELECT wp_usermeta.bp_core_avatar_v1 FROM wp_usermeta.

I know that selecting two items is as simple as commas, but now the 2nd SELECT is subject to all of the WHERE … right?

So this does not work.

SELECT wp_users.user_login, wp_usermeta.bp_core_avatar_v1 FROM wp_users, wp_usermeta INNER JOIN wp_usermeta ON wp_usermeta.user_id=wp_users.ID INNER JOIN wp_bp_xprofile_data ON wp_bp_xprofile_data.user_id=wp_usermeta.user_id WHERE  wp_bp_xprofile_data.field_id=166 AND wp_usermeta.meta_key='bp_core_avatar_v1' AND UPPER(wp_bp_xprofile_data.value) LIKE '%%" . $teamNameWithoutCityUppercase . "%%' ORDER BY RAND()

How do I select “wp_usermeta.bp_core_avatar_v1” without subjecting it to the WHERE statement?

Or … maybe not. I need to be able to display the value of bp_core_avatar_v1, but it doesn’t seem to be storing anything as it.

And … I’m solving my own problems. Love it when that happens.

Don’t need the second FROM wp_usermeta. Just the additional SELECT.

i’m lost

now it’s a table column?

wp_usermeta is a table
meta_value is a column
meta_key is a column
bp_core_avatar_v1 is a string in meta_key

I needed to pull the meta_value string when the meta_key string is bp_core_avatar_v1. It’s working now!

:slight_smile:

Thanks for your help on this one!