The update_user_meta and get_user_meta functions are what you want, without having to fool around with any database connections or SQL. All it takes is one include statement in your theme, and a bit of PHP magic to call the meta functions.
Check out my code, which accomplished what you are trying to do, on Github (https://github.com/yasyf/WP-Subscriptions/blob/master/subs.php). It should serve as a good example. I also have a description with embedded code on my blog: Using WordPress As A Pay-For-Access System.
Alternatively, you can check out how I added code to my footer to show text based on custom fields (in this case, hours remaining in a free trial). Looking at what you tried to do, you might add similar code to your theme’s header.php instead.
<?php
$current_user = wp_get_current_user();
$subscription = get_user_meta($current_user->ID, 'subscription', true);
if($subscription != "true")
{
$time = (80000 - (intval(time()) - intval($_COOKIE['seshCurrent'])))/3600;
if(intval($time) < 0)
{
$time = 22.3;
}
echo("$time hours remaining in this session");
}
?>