Is there any API to get corresponding field in a custom content type?

Question

I have drupal 7 and created a custom "content type" with several fields. just imagine i have:

1. title field
2. subtitle field

I need to search the data. I want to search titles and when i found my certain title I want to get corresponding subtitle (in the same row of that title)! I know I can do it by going to directly to my database and do it by db_query(and i have to know the structure of my database as well). but is there any API in this regard?
i need to get them inside my custom module

Answer 1

In this instance using db_query is appropriate...

$nids = db_query("SELECT nid FROM node WHERE title LIKE :title", array(':title' => '%'.$title.'%'))->fetchCol();

$nodes = node_load_multiple($nids);

foreach($nodes as $node) {
    $subtitle = $node->field_subtitle[LANGUAGE_NONE][0]['value'];
    // do things with the subtitle....
}

What has happened here is we are using db_query to search the node table for nodes with matching titles. Once we have the nids we call node_load_multiple and that loads the whole node(s). Drupal uses caching here so we don't have to necessarily connect to the DB at all to do this.

The resulting $node object has all of the fields already attached to it so knowing where they are actually stored in the DB at this point is irrelevant.

As Clive has pointed out we shouldn't really access field items directly as using field_get_times or EntityMetadataWrapper will ensure that we always load the field items in the correct language (the language of the current user).

To use field_get_items you could:

foreach($nodes as $node) {

    // using field_get_items here automatically loads the field in the correct language
    $field_items = field_get_items('node', $node, 'field_subtitle');
    $subtitle = $field_items[0]['safe_value'];

    // do things with the subtitle....
}