Drupal - how to get the absolute path for files based on fid

Drupal 7

This is a two part process, using file_load() and file_create_url()

First, you need to turn the $fid into a Drupal URI:

$file = file_load($fid);
$uri = $file->uri;

Now, you can turn this into a URL

$url = file_create_url($uri);

file_create_url() always creates an absolute URL, either using the global $base_path that you have defined, or using the one that Drupal guessed during bootstrap.

Drupal 8

$file = \Drupal\file\Entity\File::load($file_id);
$uri = $file->getFileUri();
$url = \Drupal\Core\Url::fromUri(file_create_url($uri))->toString();

In Drupal 7 you can also use MYSQL query, if you don't want to load all the fields of the file

  $fid = 1; //your file ID
  $uri = db_select('file_managed', 'f')
    ->condition('f.fid', $fid, '=')
    ->fields('f', array('uri'))
    ->execute()->fetchField();
  echo file_create_url($uri);