WPDB - Read and write value from / to database

Question

I have a custom field in usermeta called vatnumber. If the field has a value, how do I read that specific value for user X?

Here's what I have so far:

<tr class="vatnumber">
    <th><?php _e( 'VAT-Number:', 'wpdb_usermeta' ); ?></th>
    <td><?php $wpdb->get_results("SELECT userid, vatnumber FROM usermeta"); ?></td>
</tr>

But I don't quite understand wpdb yet. I know it is used for reading and writing from and to the database, but if I need a value for a specific user, do I have to assign the user_id to a variable?

On the same side, how do I make sure a certain field writes into the correct place in the database?

I would be glad if you could give me an example code tidbit.

edit01:

from what I understand, I can change it to

<?php $wpdb->get_user_meta($user_id, vatnumber,"); ?>

But does $single = false give me an array for all users with their respective user_ids? Because user_id in this case is a variable.

I'm sorry that I haven't used the WordPress database in a while, but how do I tell the system which user to get the info from? I would need to read the current_user ID, wouldn't I?

Answer 1

This is still a valid question even if there is an easier way.

That easier way is get_user_meta($user_id,'vatnumber',true);

NOT $wpdb->get_user_meta($user_id, vatnumber,''); by the way.

Setting the $single flag to true is because although you can store multiple values in a single keyname but this is usually best avoided to avoid confusion.

Since you are getting a single value, the correct syntax for the database query would look something like this:

<?php 
global $wpdb; $metakey = "vatnumber";
$dbquery = $wpdb->prepare("SELECT meta_value FROM ".$wpdb->prefix."usermeta WHERE user_id = %d AND meta_key = %s", $user_id, $metakey);
$vatnumber = $wpdb->get_var($dbquery);
?>

(You need to call global $wpdb; only once inside each function.) Or, if you want to do it all at once:

<?php 
global $wpdb;
$vatnumber = $wpdb->get_var($wpdb->prepare("SELECT meta_value FROM ".$wpdb->prefix."usermeta WHERE user_id = %d AND meta_key = %s", $user_id, "vatnumber") );
?>

Note the use of $wpdb->prefix which generally will output wp_ since the standard table name is wp_usermeta but the prefix can be changed and it is good practice to support this in a plugin (but may not be needed for custom coding on your own site where you know it is wp_ anyway.)

Also note the action of $wpdb->prepare which tends to be needed these days to avoid MySQL injection attempts. The %d is replaced by the numeric value of $user_id. The %s is replaced by the string value of $meta_key.

If you do want to get ALL user_id's and corresponding meta_value's for those users with a VAT number stored, THEN you can use the get_results method to get them all, for example:

<?php 
global $wpdb; $metakey = "vatnumber";
$dbquery = $wpdb->prepare("SELECT user_id,meta_value FROM ".$wpdb->prefix."usermeta WHERE meta_key = %s", $metakey);
$vatdata = $wpdb->get_results($dbquery);
foreach ($vatdata as $data) {
    echo "VAT for User ".$data->user_id.": ".$data->meta_value."<br>";
}
?>

More examples: https://codex.wordpress.org/Class_Reference/wpdb