Using GROUP CONCAT in my-sql query with wp_usermeta table

Question

I have a query that is meant to return user details from Wordpress tables. It would be fine if I was just SELECTing column names that I want, but within Wordpress there is a usermeta table which has 2 columns - 1 called metakey and 1 called meta value.

I want to get certain bits of user info from meta keys such as first_name and last_name, but they are all within the same column - metavalue.

Here is what I have:

    $allquery="SELECT $comma_separated, wp_usermeta.meta_value, wp_usermeta.meta_key, 
//comma_seperated are a list of values to search for seperate by ",". this is name, email
    GROUP_CONCAT(
        wp_usermeta.meta_value
        ORDER BY wp_usermeta.meta_key
    ) AS name
    FROM wp_users
    LEFT JOIN wp_usermeta
    ON wp_users.ID = wp_usermeta.user_id
    WHERE  (wp_usermeta.meta_key = 'first_name'
        OR wp_usermeta.meta_key = 'last_name')
        AND wp_users.user_login = '$spec_user'
    GROUP BY wp_users.ID";

    $names = array();
    $allresult=mysql_query($allquery) or die(mysql_error()); 

    while($rows=mysql_fetch_array($allresult)){  

        $names[] = $rows['name']; //name is from the group_concat in query
        $emails[] = $rows['user_email']; 
    }

The problem is, this just returns a single row. I think it could be something to do with the line:

AND wp_users.user_login = '$spec_user'

Where $spec_user is a user entered value.

What I want ideally is all rows returned where the user_login column equals what the user has entered. Not just from that table, but from the usermeta table.

EDIT @nnichols, I probably didnt go about explaining it properely...

This is the user table:

ID    userlogin    password    email        date-registered
2     jay          xxxxxx      xxx@xxx.xcom 1/2/12
5     pete         xxxxxx      xxxx@xxx.com 2/2/12

And this is the usermeta table:

umetaid     userid    meta_key     meta_value
122         2         first_name   james
123         5         first_name   peter
155         2         last_name    jones
167         5         last_name    gould
168         2         DOB          16/8/89
190         5         DOB          23/5/70

So I am trying to acheive pulling the values in a single query into a PHP loop so I can add them to an array like so:

$names = array();
$dob = array();

$allresult=mysql_query($allquery) or die(mysql_error()); 

while($rows=mysql_fetch_array($allresult)){  

    $names[] = $rows['name']; //name is from the group_concat in query
    $emails[] = $rows['user_email']; 
    $dob[] = $rows['custom_field_dob']; 

}

I have the first and last name already put into one array, names[]. But how would I acheive it so essentially the usermeta table treated the DOB metakey for example, as a column, so I could pull all content to the array, and be in the samme "row" as the other user data.

Answer 1

You can use the following sql to achieve your goal, may need to change table name, field name etc......

SELECT distinct(user.id),

(SELECT GROUP_CONCAT(u2.meta_value SEPARATOR ' ') FROM usermeta as u2 where u2.user_id=user.id and (u2.meta_key='first_name' OR u2.meta_key='last_name')) AS name,

user.email as user_email,(SELECT u3.meta_value from usermeta u3 where u3.user_id=user.id and u3.meta_key='DOB') AS custom_field_dob

FROM user, usermeta WHERE usermeta.user_id = user.id

See the image below...