I'm using the below SQL to create a table from wordpress user information joining data in u3g_users and u3g_metavalue.

SELECT u3g_users.`ID`,
       u3g_users.`display_name` AS `Name`,
       u3g_users.`user_email` AS `Email`,
       u3g_users.`user_login` AS `Login (Email)`,
       u3g_usermeta.`meta_value` AS `Address #1`
FROM u3g_users

INNER JOIN u3g_usermeta
     ON u3g_users.`ID` = u3g_usermeta.`user_id`
WHERE meta_key = '#address1'

The code as it is shows 2 users out of 183 that have actual values for the meta_key set as #address1

I'm trying to expand the code to show the following but can't seem to get there.

  1. The current code shows me the results of just two user where the meta_value has a value. I need it to show ALL Users even if the meta_value is blank.

  2. The meta_key also contains additional sections for which I need to expand on for example #address #2 I've tried to make a repetition to show a sixth column which should be '#Address2' for example the column headings would be Name, Email, Login(Email), Address #1, Address #2

The goal is to create an SQL code that combines u3g_users & u3g_meta_value with repeating columns of meta_value so that the user can see a better defined user database on the front end even is the data is blank

Any pointers would be great

Thanks in Advanced

  • I may be misunderstanding, but if your SELECT is asking for users with a 'meta_value', then you're specifically requesting to NOT return those that have a blank in the column. – Tony Djukic Oct 18 at 1:37
  • Correct, but I do not know how to get around that or show the table with additional columns for each 'metal_value' – Dan Hardy Oct 18 at 9:11
  • Dan, why not get all the data first using SELECT * and then rename the columns during your output? You’d put it all in a $results variable and then run a foreach on the data that is returned. Like ‘foreach( $results as $result )’ and then write a pattern within the loop to organize the data into the correct columns. Before running the loop though you could just establish the columns in a table or as headings in a list, depending on your desired output. – Tony Djukic Oct 18 at 16:01
  • Hi Tony, I'm brand new to SQL I've been avoiding it for so long but working in wordpress now forces it. I managed the following work around CREATE OR REPLACE VIEW Associate_All AS SELECT u3g_users.id AS Line, (select meta_value from u3g_usermeta where user_id = u3g_users.id and meta_key = 'u3g_capabilities' limit 1) as 'Role', (select meta_value from u3g_usermeta where user_id = u3g_users.id and FROM u3g_users Which works as a read only option for the moment, I tried CREATE but this didn't refresh data when updated in the webite – Dan Hardy Oct 20 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.