I am in the habit of destroying and recreating my custom fields. :-) Therefore I wrote a function that looks up the custom table name and field names, so that I can use them in the 'real' SQL statements. The function relies on the label for the custom field group and the fields. So as long as the labels stay the same, the code works.

Here is my function, which could be improved on:

function getCustomTableFieldNames(){

//*** Start of section to get table and column names ***/
# Change the next 3 variables to match the labels of the custom field group.
$custom_field_group_label = "Extended Date Information";
$custom_field_birthdate_sunset_label = "Birth Date Before Sunset";
$custom_field_deathdate_sunset_label = "Death Date Before Sunset" ;

/* rest of the function */
$return_custom_birth_sunset = '';
$return_custom_death_sunset = '';
$error_msg = '';

// figure out the table names and field names for custom fields.
$tablename_query = "SELECT civicrm_custom_group.table_name as tablename from civicrm_custom_group
where title = '$custom_field_group_label' and extends = 'Individual' ";

$extended_date_table = '';

$table_dao =& CRM_Core_DAO::executeQuery( $tablename_query );
if ( $table_dao->fetch( ) ) {
$extended_date_table = $table_dao->tablename;
}else{
$error_msg = "Cannot find table for custom field group '$custom_field_group_label'";
$return_values = array( $error_msg, $return_custom_birth_sunset , $return_custom_death_sunset );
return $return_values;
}
$table_dao->free( );

if( $extended_date_table == ''){

$error_msg = "extended_date_table variable is empty";
$return_values = array( $error_msg, $return_custom_birth_sunset , $return_custom_death_sunset );
return $return_values;
}

$date_fields_query = " SELECT civicrm_custom_field.column_name as column_name, civicrm_custom_field.label as label
FROM civicrm_custom_group left join civicrm_custom_field
on civicrm_custom_group.id = civicrm_custom_field.custom_group_id
where civicrm_custom_group.title = '$custom_field_group_label'
and civicrm_custom_group.extends = 'Individual'
and ( civicrm_custom_field.label = '$custom_field_birthdate_sunset_label' or
civicrm_custom_field.label = '$custom_field_deathdate_sunset_label' ) ";

//print "";

$fieldnames_dao =& CRM_Core_DAO::executeQuery( $date_fields_query );

while ( $fieldnames_dao->fetch( ) ) {

$tmp_label = $fieldnames_dao->label;
if($tmp_label == $custom_field_birthdate_sunset_label){
$extended_birth_date = $fieldnames_dao->column_name;
}else if($tmp_label == $custom_field_deathdate_sunset_label ){
$extended_death_date = $fieldnames_dao->column_name;
}
}

if($extended_birth_date == "" || $extended_death_date == ""){
$error_msg = "Cannot find custom field names for before sunset flags for date of birth or date of death";
$return_values = array( $error_msg, $return_custom_birth_sunset , $return_custom_death_sunset );
return $return_values;
}
$fieldnames_dao->free( );
//*** end of section to get table and column names ***/

$return_values = array( $error_msg, $extended_date_table, $extended_birth_date , $extended_death_date );
return $return_values;
}