db_query or db_select for getting content type fields

Question

Originally I was doing something along the lines of $query = "SELECT {}" and then $result = db_query($query). This method works fine for most of my queries until I needed to get the field values and fid from a specific content type. And then be able to call these values when printing inside HTML. Can anyone provide an example of how I would do this?

I had something along the lines of:

<?php 
$query = "SELECT node.nid AS nid, 'node' AS field_data_field_news_node_entity_type, 'node' AS field_data_field_news_date_text_node_entity_type, 'node' AS field_data_field_news_teaser_node_entity_type, 'node' AS field_data_field_news_body_node_entity_type, 'node' AS field_data_field_news_video_node_entity_type, 'node' AS field_data_field_news_video_image_node_entity_type, 'node' AS field_data_field_mypdf_node_entity_type
    FROM node
    INNER JOIN field_data_field_news_is_featured ON node.nid = field_data_field_news_is_featured.entity_id AND (field_data_field_news_is_featured.entity_type = 'node' AND field_data_field_news_is_featured.deleted = '0')";
?>

$result = db_query($query);

<?php foreach ($result as $row) : ?>

    <div class="featuredArticle">
        <?php print $row->field_mypdf_value; ?>
        <?php print $row->field_mypdf_fid; ?>
    </div>

<?php endforeach; ?>

Answer 1

If you have your node object, you can use field_get_items to get the field values from the node.

$items = field_get_items( 'node', $node, 'field_name' );
// now $items is an array of values... you can manipulate...
// then use the field_view_value to build the render array
$specific_item = $items[0];
$item_build = field_view_value( 'node', $node, 'field_name', $specific_item );

Here's a link to the field_view_value API doc.

Use the devel module and sprinkle in some dpm()'s to understand the underlying data. It's pretty neat! :)

Answer 2

$result = db_query('SELECT field_1, field_2 FROM table_name WHERE bundle = :bundle AND entity_type = :entity_type', array(':bundle' => 'page', ':entity_type' => 'node' )->fetchAll(); 

Will give you an indexed array of objects with the queried values: $results will look like this

    array(
      [0] => [standardclass object](
        [field_1] => Value for field_1
        [field_2] => Value for field_2
      ) 
      // .. One entry for each result ...
      [n] => [standardclass object](
        [field_1] => Value for field_1
        [field_2] => Value for field_2
      ) 
   )

However querying the database directly is quite a strange thing to do so this might not at all be the best practice way for what you are are trying to achieve. Be sure to look into the field API as mentioned by Purdy.

Read more about the query object and resultsets(There is a lot to say about this but this is a good start).