Printing fields from View using query

Question

The query below is taken from the related View. How can I get the field value for 'field_data_field_news_link', 'field_data_field_news_source', and field_data_field_news_date' for each row (news content type that is published and featured)? I prefer to use what I already have, which probably means changes are needed to the JOIN statements? Right now, all that is printing are the node titles.

If there is a more straight forward approach, I'm open to that too.

<?php 
    $query = "SELECT node.nid AS nid, node.title AS node_title, field_data_field_news_date.field_news_date_value AS field_data_field_news_date_field_news_date_value, 'node' AS field_data_field_news_date_node_entity_type, 'node' AS field_data_field_news_link_node_entity_type, 'node' AS field_data_field_news_source_node_entity_type
        FROM {node} node
        INNER JOIN {field_data_field_news_is_featured} 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')
        LEFT JOIN {field_data_field_news_date} field_data_field_news_date ON node.nid = field_data_field_news_date.entity_id AND (field_data_field_news_date.entity_type = 'node' AND field_data_field_news_date.deleted = '0')
        WHERE (( (node.type IN  ('news')) )AND(( (node.status = '1') AND (field_data_field_news_is_featured.field_news_is_featured_value = '1') )))
        ORDER BY field_data_field_news_date_field_news_date_value DESC
        LIMIT 5 OFFSET 0";

?>

<?php $results = db_query($query); ?>

<?php $i = 0; ?>
<?php foreach ($results as $row) : ?>   
    <!-- Custom HTML here -->

<?php endforeach; ?>

Some background. I am upgrading a project that I inherited to Drupal 7. The Drupal 6 version has all hard coded templates (not my doing!). The specfic page I am working on is of Page content type. This is the News node. On this node, I need to render all News content types and the selected fields. I also need to render this into custom HTML. I prefer not to go the Rewrite results route. Changing anything in Manage Display would be changing the Page content type which I do not want to do. Additionally, it seems print render($content['field_news_link']) would only render that field from one News content type? So if I already have a template file for the News node (of Page content type), what would be my approach? Would I create a template for the view and then just print the view in my existing News template?

Answer 1

This is not good approach for Drupal. Try to make the query in VIEWS UI, after that you have 2 options:

1. In the view add this option Show:Content | Teaser. Create tpl file for that specific node (node--news.tpl.php), add if statement for the DISPLAY(view) MODE of the content type (full or teaser). In the correct display mode you have all node fields that are enabled in the content "Manage display" tab (as admin structure->content type->news->manage display) and use " print render($content['field_news_link']) ".

2. In the view add this option Show:Fields. Rewrite the option of the field "Rewrite results"->"Rewrite the output of this field" in the view and add there html + token of the field.