Pull MySQL data from multiple tables and merge into 1 PHP array

Question

Appreciate help in advance.

I am dealing with a bit of a pickle with a formula. I am trying to create a page template that will display a list of users, specific metadata from usermeta table + additionally I want to pull data from table created by me. Here is a screenshot of the table structure: https://www.screencast.com/t/X07XDr3Gy.

I got it partially working to get user name, surname and company:

$sql = "
SELECT {$wpdb->users}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company, 
FROM {$wpdb->users}
INNER JOIN (SELECT user_id, meta_value 
FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->users}.ID = firstname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->users}.ID = lastname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'webaria_company') as webaria_company ON {$wpdb->users}.ID = webaria_company.user_id
";
$asuserlist = $wpdb->get_results($sql);

And that works perfectly

But then I try to get additional information from table attp2_as_score, column score_as

$sql = "
SELECT {$wpdb->users}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company, score_as
FROM {$wpdb->users}, wattp2_as_score
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->users}.ID = firstname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->users}.ID = lastname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'webaria_company') as webaria_company ON {$wpdb->users}.ID = webaria_company.user_id
INNER JOIN (SELECT score_as FROM wattp2_as_score WHERE approved=1) as scoreas ON {$wpdb->users}.ID = scoreas.user_id
 ";

 $asuserlist = $wpdb->get_results($sql);

And I get error:

WordPress database error: [Column 'score_as' in field list is ambiguous] SELECT wattp2_users.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company, score_as FROM wattp2_users, wattp2_as_score INNER JOIN (SELECT user_id, meta_value FROM wattp2_usermeta WHERE meta_key = 'first_name') as firstname ON wattp2_users.ID = firstname.user_id INNER JOIN (SELECT user_id, meta_value FROM wattp2_usermeta WHERE meta_key = 'last_name') as lastname ON wattp2_users.ID = lastname.user_id INNER JOIN (SELECT user_id, meta_value FROM wattp2_usermeta WHERE meta_key = 'webaria_company') as webaria_company ON wattp2_users.ID = webaria_company.user_id INNER JOIN (SELECT score_as FROM wattp2_as_score WHERE approved=1) as scoreas ON wattp2_users.ID = scoreas.user_id

Answer 1

Apparently both wattp_users and wattp2_as_score tables have a column named score_as.

Put a prefix with the table name before every mention to score_as column, just as you did with the ID (e.g. wattp2_users.ID) to avoid beeing ambiguous.