Skip to:
Content
Pages
Categories
Search
Top
Bottom

Search Query and MySQL help

  • Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    I’m attempting to make a custom search page that lets users browse through the members directory based on certain custom xprofile fields that they select in the search form. I have 3 custom xprofile fields: Member Type, Gender, and Location. With the way the database table (bp_xprofile_data) is setup, all of those values (under the ‘value’ column) are stored in different rows within the same table with corresponding xprofile ID’s (under the ‘field_id’ column).

    For the life of me, I can’t figure out how to filter results based on these multiple parameters when they aren’t listed within the same row, but are listed within the same table. For example, normally I would simply query “SELECT user_id FROM bp_xprofile_data WHERE xprofile1=’General’ AND xprofile2=’Male’ AND xprofile3=’Los Angeles’”…..but I can’t do that because of the way the table is setup and the info is organized.

    So how do I format the query? Do I need to use subqueries (if so…I’m lost on where to go with those!)? To sum up: How do I filter search results from bp_xprofile_data through a MySQL query matching multiple xprofile values?

    I’ve been working on this for 4 hours and I’ve done more research than I care to admit, especially because this is probably a much easier solution than I realize. Any help would make you the coolest person alive. Seriously!

Viewing 14 replies - 1 through 14 (of 14 total)
  • Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    Anything, anyone?

    Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    A tip or URL leading me in the right direction would be more than enough!

    Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    Since I’m being ignored, I’ll try adding more info…

    bp_xprofile_data table:

    id | field_id | userd_id | value


    1 | 2 | 30 | General
    2 | 3 | 30 | Female
    3 | 4 | 30 | Los Angeles
    4 | 2 | 32 | Premium
    5 | 3 | 32 | Female
    6 | 4 | 32 | Boston
    7 | 2 | 35 | General
    8 | 3 | 35 | Male
    9 | 4 | 35 | New York

    What I want to do is search for all user_id’s that are “Male” and “General”, meaning I need to query where (field_id=2 and value=’General’) AND where (field_id=3 and value=’Male’).

    Here is what I have and it doesn’t work:

    $sql = “SELECT user_id FROM bp_xprofile_data WHERE value IN(‘General’,’Male’) HAVING COUNT(user_id) > 1″

    This returns a value of “30″, corresponding to the first user in the table, however the first user is “Female”….not “Male” – when the search query SHOULD return user_id “35″.

    so….one last time, I’m really trying here people….any help?

    Avatar of @modemlooper
    @modemlooper
    Moderator

    @modemlooper

    Your not being ignored. At least give 24 hours on here for a response.

    Avatar of r-a-y
    r-a-y
    Moderator

    @r-a-y

    I’m not a SQL guy myself, but because the data is divided in separate tables, look into constructing a select join query.
    See how it’s done in /bp-xprofile/bp-xprofile-classes.php

    Also take a look at the BP Member Filter plugin or the BP Profile Search plugin.

    Avatar of Anne
    Anne
    Participant

    @ankka

    I am not sure if this helps; there is one good WP plugin called ‘Search everything’ (or ‘Search all’). The plugin has a great review. It many selectable settings e.g. search for words in comments, pages, posts and many more options. Could the code be tweaked for performing search also within BP created pages??? As an addition to the settings the plugin already has. It would be absolutely great!

    Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    @r-a-y Actually the problem is that the data is on the SAME table…not a different one…if it were different tables this would be easier :(

    Avatar of r-a-y
    r-a-y
    Moderator

    @r-a-y

    Sorry I misunderstood your question. I thought you meant you were looking to join wp_bp_xprofile_data and wp_bp_xprofile_fields.

    I’m thinking you’ll have to join the same table together in order to do what you want.

    Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    @r-a-y I’ve actually already tried that exact SQL query (it seems to make logical sense at first glance) but it doesn’t work because the “AND” statement is trying to make 2 different rows with different values match…which, obviously they don’t. They share a common user_id, but not field_id or value…thus being the problem.

    Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    my logic in my $sql statement above was that I want to return a table where all those cases are true, and then count the rows where there are more than 1 instance of the same user_id, meaning it matched both arguments…however, the code I wrote above didn’t work this way…perhaps I’m just writing it wrong?

    Avatar of r-a-y
    r-a-y
    Moderator

    @r-a-y

    Yeah, I amended my previous post after I typed it because I noticed the query would not work.
    You’ll have to join the same table, then you could get the result you need.

    Do a search on Google on self joins for more info.

    Avatar of Wythagy
    Wythagy
    Participant

    @wythagy

    I’ve been there all day lol!

    Avatar of Steven Word
    Steven Word
    Participant

    @stevenkword

    I came across this post today while researching using a similar method and I have found a work-around that is functional.
    It’s not uber-elegant, but it is functional. Here is how to do it.

    1.) Create a function that will update the wp-user usermeta to be the same as that of the xprofile.
    2.) Create a hook to update the wp-usermeta with the xprofile data everytime the profile is updated.
    3.) User the standard metakey / metavalue functions to pull the data back out.

    Here is a code sample that can go in functions.php in your theme:

    function copyUserMeta(){
    global $current_user;
    get_currentuserinfo();
    $user_id = $current_user->ID;

    $gender= xprofile_get_field_data( “Gender” ,$user_id);
    update_usermeta( $user_id, ‘gender’,$gender);

    $location = xprofile_get_field_data( “Location” ,$user_id);
    update_usermeta( $user_id, ‘location’,$location);
    }
    add_action( ‘bp_before_profile_edit_content’, ‘copyUserMeta’ );

    Then when you need to look up those values you can query using the standard user_meta calls.

    Avatar of milordk
    milordk
    Participant

    @milordk

    Hi to all!

    Please tell me what function and where – are responsible for searching for users xprofile .. really want to write a normal plug-in for multiple search capabilities (for instance: Moscow, Voronezh, St. Petersburg)

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

You must be logged in to reply to this topic.