How to get usermeta meta_key and meta_value

prestonc1986
(@prestonc1986)

7 years, 10 months ago
Great plugin! However, I am stumped with populating some data within the table. I would like to populate the table with WooCommerce ‘Customers’ and get 2 meta_values from 2 different meta_keys.

So far, I have queried the customers.
My query looks like this:

$this->data = $wpdb->get_results( "SELECT * FROM {$table_prefix}usermeta WHERE meta_key = 'billing_phone'", ARRAY_A );

But when I get here:
```
function column_default($item, $column_name){
        switch($column_name){
            case 'rating':
            case 'director':
                return $item[$column_name];
            default:
                return print_r($item,true); //Show the whole array for troubleshooting purposes
        }
    }
```
Changing case ‘rating’ to case ‘meta_key’ it displays the key ‘billing_phone’, when I need the meta_value of the ‘billing_phone’ which would be 205-xxx-xxxx. I need 2 values, the meta_key of ‘billing_phone’ and ‘billing_first_name’

Can you please help?

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

Plugin Author Dutch van Andel
(@veraxus)

7 years, 10 months ago

The class is looping through database columns for each record as a convenience. You then use the switch statement to detect which column you are in for the current loop and output.

Setting the case to meta_key means you are outputting on the wrong column, based on what you explained for your requirements. Change it to meta_value and you’ll be able to output with the data instead of the key.
Plugin Author Dutch van Andel
(@veraxus)

7 years, 10 months ago
A sidenote: Since you mentioned wanting multiple pieces of metadata, you’re going to need to use joins in your query, so I’d strongly recommend updating your select statement to use specific aliased column names instead of the * wildcard.
- This reply was modified 7 years, 10 months ago by Dutch van Andel.
Thread Starter prestonc1986
(@prestonc1986)

7 years, 10 months ago
Matt, please see screenshot:

Here is part of my current code:
```
function __construct() {
		global $status, $page, $wpdb, $table_prefix;

		//Set parent defaults
		parent::__construct( array(
			'singular' => 'ID',     //singular name of the listed records
			'plural'   => 'ID',    //plural name of the listed records
			'ajax'     => false        //does this table support ajax?
		) );

		$this->data = $wpdb->get_results( "SELECT * FROM {$table_prefix}usermeta JOIN {$table_prefix}_users ON {$table_prefix}usermeta.umeta_id = {$table_prefix}users.ID", ARRAY_A );
	}

	function column_default( $item, $column_name ) {
		switch ( $column_name ) {
			case 'display_name':
			case 'meta_value':
				return $item[ $column_name ];

			default:
				return print_r( $item, true ); //Show the whole array for troubleshooting purposes
		}
	}
```
I think I am getting close as this queries only the users or ‘customers’. In other words, there are 5 users and 5 results are coming back. Thats good.

I am not quite sure what to replace “*” with. According to the screenshot provided above, what would you replace “*” with? Would it be meta_key? Would it be meta_value? would it be meta_key = ‘billing_phone’? Would it be meta_key = ‘billing_first_name’? Would it be any of those? Am I using the JOIN correctly?

Also in the CASE values how would I use meta_value TWICE? Again, I am trying to get the meta_value for both meta_key = billing_phone and meta_key = billing_first_name
- This reply was modified 7 years, 10 months ago by prestonc1986.
Thread Starter prestonc1986
(@prestonc1986)

7 years, 10 months ago
@veraxus, does my previous reply make sense to you?
- This reply was modified 7 years, 10 months ago by prestonc1986.
Thread Starter prestonc1986
(@prestonc1986)

7 years, 10 months ago

@veraxus, Hello?
Plugin Author Dutch van Andel
(@veraxus)

7 years, 9 months ago
A query that includes multiple pieces of metadata might look like…
```
$sql = "SELECT user_login, m1.meta_value as foo, m2.meta_value as bar 
FROM {$wpdb->users} AS u
JOIN {$wpdb->usermeta} AS m1
  ON m1.user_id = u.ID
JOIN {$wpdb->usermeta} AS m2
  ON m2.user_id = u.ID
WHERE m1.key = 'foo'
AND m2.key = 'bar';"
```
This query selects and returns (all at once) the username, the first meta value (which matches a field with a meta_key of ‘foo’) and the second meta value (which matches a field with a meta_key of ‘bar’). This is fairly basic SQL, but in WordPress we have to remember that the meta tables relate to their parent table by ID. Select the parent table first, since that is your canonical reference, and then filter the record by your meta keys. You also need to be aware that this will skip parent records where either metakey is not present… that’s the tradeoff for its efficiency.

At this point, you can now switch on the columns ‘user_login’, ‘foo’, and ‘bar’, the latter two we aliased in the query.

FYI, this is one of the reasons no actual queries are included in the example… the use cases are infinite.

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

The topic ‘How to get usermeta meta_key and meta_value’ is closed to new replies.