Issue with database insert comparing dual meta_keys

  • Not sure if its the right area, but I’m stumped with what I have wrong in this wpdb query.

    I have this:

    $testresults = $wpdb->get_results(

    “SELECT user_id
    FROM {$wpdb->base_prefix}usermeta
    WHERE (((meta_key = ‘skill_role_name’
    AND meta_value LIKE ‘%%%$search_terms%%’)
    OR (meta_key = ‘description’
    AND meta_value LIKE ‘%%%$search_terms%%’))
    AND
    (meta_key = ‘zip’
    AND meta_value IN (” . implode(‘,’, array_map(‘intval’, $zips_array)) . “)” . “))
    “

    );

    I’ve echo’d this out as a test, and ran it in the database through MySQL, but it doesn’t seem to like it. This is meant to grab where (‘skill_role_name’ OR ‘description’ = $search_terms) AND (‘zip’ = in($array)).

    What I get when I echo it out is this:

    SELECT user_id FROM wp_usermeta WHERE (((meta_key = ‘skill_role_name’ AND meta_value LIKE ‘%%%director%%’) OR (meta_key = ‘description’ AND meta_value LIKE ‘%%%director%%’)) AND (meta_key = ‘zip’ AND meta_value IN (90026,90057,90081,90084,90086,90051,90087,90088,90012,90017,90071,90030,90074,90099,90029,90039)))

    Any ideas?

Viewing 4 replies - 1 through 4 (of 4 total)

  • The only things that stand out as far as the syntax goes, are the LIKE fields. There’s no reasonwhy you would have multiple %’s on each side. One is what is called for in the MySQL syntax.

    I do have one observation though. Your WHERE statement looks like it’s wrong.

    What you’re asking is this:

    WHERE (
    (
        (meta_key = 'skill_role_name' ...) OR (meta_key = 'description' ...)
    )
    AND (meta_key = 'zip' ...))

    You’re asking to get a value where the meta_key name is (skill-role_name or description) AND zip which means that you’re asking for one record with two values for one field at the same time. That’s just not possible, so you’ll never get any results back.

    Just thinking… Are you trying to get the values from two different meta fields for the same user? If so, that’s a very different query.

    Thread Starter powermaniac

    (@powermaniac)

    It’s kind of hard to explain. Basically, I’m allowing them to do a standard search, which searching through skill_role and description. I’m adding the ability to limit it by zip. So, say your search term was for “Director” and you chose the zip code of 90026. It would look for the word “Director” in Skill_role or Description. Then, it will check if the “zip” metakey is 90026. If it is, it returns the userid. All three fields are meta keys under wp_usermeta.

    I guess I could just split them up into two queries, though I assumed I should try to get it done under one.

    Basically, check for director in the first statement and return the userids. Then run another statement looking for people with 90026 from that array of users.

    I assume the second one is probably easier.

    Ah OK. I get it now. That makes the query a fair bit more difficult.

    The problem is that you can’t use two keys to select two different items from the same table the way that you’re doing it. You’ll be looking for two different values in the same field, and like I said, you can’t store two values in one field, so you’ll never get any results. What you are looking for is a JOIN. Or in your case probably two. That’s where you select data from one table joined to another table so that you can query for more then one thing at once.

    As an example from your code…

    u.ID AS user_id

FROM wp_users AS u

INNER JOIN wp_usermeta AS um1
    ON um1.user_id = u.ID
    AND (um1.meta_key = 'Skill_role' OR um1.meta_key = 'Description')
    AND um1.meta_value = 'Director'

INNER JOIN wp_usermeta AS um2
    ON um2.user_id = u.ID
    AND um2.meta_key = 'zip'
    AND um2.meta_value IN ('90000','90001','90002')

    There’s different types of joins that you can use, but the main ones that you’d use 99% of the time are LEFT, RIGHT and INNER. It’s worth reading up about those and seeing what each one does and how they are different.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Issue with database insert comparing dual meta_keys’ is closed to new replies.