db_query(); -- list of files attached to a node?

Question

I have a DRUPAL_BOOTSTRAP_DATABASE .php -script that loads info based on the nid it gets via POST request.

I need to list files attached to the requested node, and I know how to do this in D6, but after spending 30min trying to understand the new table structure of Drupal 7, I cannot fathom how to pull the fid (or any other file info related to the node) via a db_query();

I've been trying to read Devel query log to get a hang of how Drupal handles this to no avail. Any insight into my problem would be greatly appreciated. :)

Answer 1

replace uppercase by your data:

define('DRUPAL_ROOT', getcwd());

require_once DRUPAL_ROOT . '/includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);

$sql = 'SELECT f.* FROM {field_data_field_YOURFIELDFILENAME} ff INNER JOIN {file_managed} f ON ff.field_file_fid=f.fid WHERE ff.entity_type = :etype AND entity_id=:nid';
$result = db_query($sql, array(':etype'=>'node', ':nid' => YOURNID));

foreach($result as $row) {
  print_r($row);
}

Create custom function for processing uri: public://FILENAME.EXT and getting right path from this.

Answer 2

Here's the final solution to pull the data from {file_managed} and another field to be returned as json based on GET parameter nid.

<?php
  // only take action on GET parameters
  if ( !empty($_GET) ) {

    // escape and map GET parameters
    $post = array_map('mysql_escape_string', $_GET);
    $nid  = $post['nid'];

    // we have a numerical nid
    if ( is_numeric($nid) && !empty($nid) ) {
        // switch to drupal root, as relative to our module path
        chdir('../../../../../../');
        // define root dir
        define('DRUPAL_ROOT', getcwd());

        // load drupal db layer
        require_once './includes/bootstrap.inc';
        drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);

        // regardless of is audio data found, we return the edit path of the node
        $temp = new stdClass();
        $temp->path = "node/$nid/edit/";
        $temp->nid  = $nid;

        // grab audio urls and slot states
        $params = array(':nid' => $nid);
        $sql    = "SELECT      f.filename,
                               f.uri,
                               fss.field_slot_state_value state
                    FROM       field_revision_field_related_audio AS fra
                    INNER JOIN field_revision_field_audio_file AS faf
                            ON faf.entity_id = fra.field_related_audio_value
                    INNER JOIN file_managed f
                            ON faf.field_audio_file_fid = f.fid
                    INNER JOIN field_revision_field_slot_state fss
                            ON fss.entity_id = fra.entity_id
                    JOIN       node n
                            ON (fra.revision_id = n.vid AND fss.revision_id = n.vid)
                    WHERE      n.nid = :nid
                    AND        fra.entity_type = 'node'";

        $results = db_query($sql, $params);

        // if we have results, display them in json format
        if (!empty($results)) {
          $i = 0;

          foreach ($results AS $result) {
            // add the state just once, as it's the same for each file
            if ( $i == 0 ) {
                  $temp->state = $result->state;
            }

            // add all audios related to production
            $temp->audio->{$i++} = $result->uri;
          }
        }

        // encode to json and spit out
        $json = json_encode($temp);
        print $json;
      }
    }

?>