Drupal performing a query against the database

Question

I wish to retrieve some nids from my drupal database. I have a query that I wish to run.

  SELECT node.nid AS projectnid
            FROM node node
            INNER JOIN content_type_project node_data_field_project_client ON node.vid = node_data_field_project_client.vid
            WHERE node_data_field_project_client.field_project_client_nid = (SELECT node_data_field_profile_company.field_profile_company_nid AS company_nid
            FROM node node  LEFT JOIN content_type_profile node_data_field_profile_company ON node.vid = node_data_field_profile_company.vid WHERE node.nid = 218)

I am calling the query by using:

$query =
        "
            SELECT node.nid AS projectnid
            FROM node node
            INNER JOIN content_type_project node_data_field_project_client ON node.vid = node_data_field_project_client.vid
            WHERE node_data_field_project_client.field_project_client_nid = (SELECT node_data_field_profile_company.field_profile_company_nid AS company_nid
            FROM node node  LEFT JOIN content_type_profile node_data_field_profile_company ON node.vid = node_data_field_profile_company.vid WHERE node.nid = 218)
        ";
$result = db_query($query);
dsm($result);

The dsm is giving me an empty object. When I run the SQL directly I get back a result.

So my question would be how would you get db_query to return you all your results as an object (I don't really mind if an object or array).

(The SQL was created by looking at the query output for views.)

This is a follow up to question: http://stackoverflow.com/questions/3718743/drupal-views-relationships-and-arguments

I have a Person content type. It has a node reference field of a company which is also a content type. I then have a content type called Project. A project has a node reference to a company content type. I want to list all the projects related to a person id (nid)id (nid)

Answer 1

The following works:

$query =
        "
            SELECT node.nid AS projectnid
            FROM node node
            INNER JOIN content_type_project node_data_field_project_client ON node.vid = node_data_field_project_client.vid
            WHERE node_data_field_project_client.field_project_client_nid = (SELECT node_data_field_profile_company.field_profile_company_nid AS company_nid
            FROM node node  LEFT JOIN content_type_profile node_data_field_profile_company ON node.vid = node_data_field_profile_company.vid WHERE node.nid = 218)
        ";
$results = db_query($query);
while ($result = db_result($results)) {
    dsm($result);
}

You need to use db_result() to get the results out. Worked this out by using http://drupal.org/node/259432#comment-846946