function &JB_schema_get_fields($form_id, $result_type = JB_FIELD_LIST)
{
$form_id = (int) $form_id;
$fields = JB_schema_get_static_fields($form_id, $result_type);
if ($result_type == JB_DB_MAP) {
$sql_exclude = " AND field_type != 'BLANK' AND field_type !='SEPERATOR' AND field_type !='NOTE' ";
}
$sql = "SELECT t1.*, t2.field_label AS NAME FROM `form_fields` as t1, form_field_translations as t2 where t1.field_id = t2.field_id AND t2.lang='" . jb_escape_sql($_SESSION['LANG']) . "' AND form_id='" . jb_escape_sql($form_id) . "' {$sql_exclude} ORDER BY field_sort ";
$result = JB_mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$fields[$row['field_id']]['field_id'] = $row['field_id'];
$fields[$row['field_id']]['field_type'] = $row['field_type'];
$fields[$row['field_id']]['field_label'] = $row['NAME'];
$fields[$row['field_id']]['template_tag'] = $row['template_tag'];
$fields[$row['field_id']]['is_hidden'] = $row['is_hidden'];
// Is hidden from website. Only visibile on the editing form.
// sometimes, template tag can be blank
if ($fields[$row['field_id']]['template_tag'] == '') {
$fields[$row['field_id']]['template_tag'] = $form_id . '_' . $row['field_id'];
}
switch ($form_id) {
case 1:
$fields[$row['field_id']]['is_member'] = $row['is_member'];
break;
case 2:
$fields[$row['field_id']]['is_anon'] = $row['is_anon'];
// resumes can have anonymous fiields
$fields[$row['field_id']]['is_blocked'] = $row['is_blocked'];
$fields[$row['field_id']]['is_member'] = $row['is_member'];
break;
case 3:
$fields[$row['field_id']]['is_member'] = $row['is_member'];
break;
case 4:
break;
case 5:
break;
default:
break;
}
}
JBPLUG_do_callback('schema_get_fields', $fields, $form_id, $result_type);
return $fields;
}
$error .= "Only text fields, text editor fields and HTML editor fields can be truncated - this field is a " . $field_row['field_type'] . "<br>";
}
$sql = "SELECT * from form_fields WHERE form_id=5 AND field_id='" . jb_escape_sql($_REQUEST['field_id']) . "' ";
$result = JB_mysql_query($sql);
$field_row = mysql_fetch_array($result, MYSQL_ASSOC);
if ($field_row['field_type'] != 'TEXT' && $field_row['field_type'] != 'TEXTAREA' && $field_row['field_type'] != 'EDITOR') {
if ($_REQUEST['truncate_length'] > 0) {
$error .= "Only text fields, text editor fields and HTML editor fields can be truncated - this field is a " . $field_row['field_type'] . "<br>";
}
}
} else {
$field_row['field_type'] = 'TEXT';
// default storage type.
$field_row['field_id'] = $_REQUEST['field_id'];
// set the template tag for the field
$fields = JB_schema_get_static_fields(5);
$field_row['template_tag'] = $fields[$_REQUEST['field_id']]['template_tag'];
if ($fields[$_REQUEST['field_id']]['field_type']) {
$field_row['field_type'] = $fields[$_REQUEST['field_id']]['field_type'];
} else {
$field_row['field_type'] = 'TEXT';
// default storage type.
}
}
if ($field_row['template_tag'] == '') {
// need to fix the template tag!
$field_row['template_tag'] = JB_generate_template_tag(5);
// update form field
$sql = "UPDATE form_fields SET `template_tag`='" . jb_escape_sql($field_row['template_tag']) . "' WHERE form_id=5 AND field_id='" . jb_escape_sql($_REQUEST['field_id']) . "'";
JB_mysql_query($sql);
}
function get_sql_update_values($table_name, $primary_key_name, $primary_key_id, $user_id, &$assign)
{
$fields =& JB_schema_get_static_fields($this->form_id, JB_DB_MAP);
foreach ($fields as $field) {
if ($field['field_type'] == 'ID') {
continue;
// do not update the id
}
if (isset($assign[$field['field_id']])) {
$str .= "{$comma} `" . $field['field_id'] . "` = '" . JB_escape_sql($assign[$field['field_id']]) . "' ";
$comma = ',';
}
}
foreach ($this->tag_to_field_id as $tag => $field) {
if (!is_numeric($field['field_id']) || $field['field_type'] == 'BLANK' || $field['field_type'] == 'SEPERATOR' || $field['field_type'] == 'NOTE') {
continue;
}
$tmp = '';
$comma = '';
switch ($field['field_type']) {
case 'GMAP':
$str .= ", `" . $field['field_id'] . "_lat` = '" . JB_escape_sql($_REQUEST[$field['field_id'] . '_lat']) . "', `" . $field['field_id'] . "_lng` = '" . JB_escape_sql($_REQUEST[$field['field_id'] . '_lng']) . "', `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id'] . '_zoom']) . "' ";
break;
case 'IMAGE':
if ($_FILES[$field['field_id']]['name'] != '') {
$_REQUEST[$field['field_id']] = $file_name;
// delete the old image
if ($primary_key_id != '') {
JB_delete_image_from_field_id($table_name, $primary_key_name, $primary_key_id, $field['field_id']);
}
$file_name = JB_saveImage($field['field_id'], $user_id);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($file_name) . "' ";
}
break;
case 'FILE':
if ($_FILES[$field['field_id']]['name'] != '') {
// delete the old file
if ($primary_key_id != '') {
JB_delete_file_from_field_id($table_name, $primary_key_name, $primary_key_id, $field['field_id']);
}
$file_name = JB_saveFile($field['field_id'], $user_id);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($file_name) . "' ";
}
break;
case 'DATE':
$day = $_REQUEST[$field['field_id'] . "d"];
$month = $_REQUEST[$field['field_id'] . "m"];
$year = $_REQUEST[$field['field_id'] . "y"];
$temp_date = $year . "-" . $month . "-" . $day;
if ($temp_time = strtotime($temp_date . ' 00:00:00')) {
// convert the date timezone to GMT
$temp_time = $temp_time - 3600 * JB_GMT_DIF;
$temp_date = gmdate('Y-m-d H:i:s', $temp_time);
}
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($temp_date) . "' ";
break;
case 'DATE_CAL':
// Convert SCW Date to ISO Date format before saving in the DB
$temp_date = JB_SCWDate_to_ISODate($_REQUEST[$field['field_id']]);
$temp_date = trim($_REQUEST[$field['field_id']]);
if (strlen($temp_date) > 0) {
$temp_date = JB_SCWDate_to_ISODate($temp_date);
if ($temp_time = strtotime($temp_date . ' 23:59:59')) {
// convert the date timezone to GMT
$temp_time = $temp_time - 3600 * JB_GMT_DIF;
$temp_date = gmdate('Y-m-d H:i:s', $temp_time);
} else {
$temp_date = '';
}
}
$str .= ", `" . JB_escape_sql($field['field_id']) . "` = '" . JB_escape_sql($temp_date) . "' ";
break;
case 'CHECK':
$comma = '';
$tmp = '';
$selected_codes = array();
$selected_codes = $_REQUEST[$field['field_id']];
// the field comes in as an array
for ($i = 0; $i < sizeof($selected_codes); $i++) {
if ($i > 0) {
$comma = ',';
}
$tmp .= $comma . $selected_codes[$i] . "";
}
$_REQUEST[$field['field_id']] = $tmp;
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
break;
case 'MSELECT':
$tmp = '';
$comma = '';
$selected_codes = array();
$selected_codes = $_REQUEST[$field['field_id']];
// the field comes in as an array
for ($i = 0; $i < sizeof($selected_codes); $i++) {
if ($i > 0) {
$comma = ',';
}
$tmp .= $comma . $selected_codes[$i] . "";
}
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($tmp) . "' ";
break;
case 'SKILL_MATRIX':
JB_save_skill_matrix_data($field['field_id'], $primary_key_id, $user_id);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
break;
case 'TEXT':
case 'EDITOR':
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
break;
case 'URL':
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql(strip_tags($_REQUEST[$field['field_id']])) . "' ";
break;
case 'NUMERIC':
case 'CURRENCY':
case 'INTEGER':
// featch only the numerical part
preg_match('/[\\+-]?(\\d+(\\.)?(\\d+)?)/', $_REQUEST[$field['field_id']], $m);
if ($m[1] === '0') {
// string zero
$str .= ", `" . $field['field_id'] . "` = '0' ";
} elseif (!$m[1]) {
// empty
$str .= ", `" . $field['field_id'] . "` = NULL ";
} else {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
}
break;
case 'YOUTUBE':
// extract the video ID form the URL
// eg. http://www.youtube.com/watch?v=iuTNdHadwbk - extract iuTNdHadwbk
if (preg_match('/watch\\?v=([a-z0-9\\-_]+)/i', $_REQUEST[$field['field_id']], $m)) {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
} elseif (preg_match('/src="http:\\/\\/www\\.youtube\\.com\\/v\\/([a-z0-9\\-_]+)/i', $_REQUEST[$field['field_id']], $m)) {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
} elseif (preg_match('#http:\\/\\/youtu\\.be\\/([a-z0-9\\-_]+)\\/?#i', $_REQUEST[$field['field_id']], $m)) {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
} else {
preg_match('/([a-z0-9\\-_]+)/i', $_REQUEST[$field['field_id']], $m);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
}
break;
default:
$custom_sql = false;
// your plugin would have to generate $custom_sql string like the one after the else { starement
JBPLUG_do_callback('append_sql_update_values', $custom_sql, $field, $table_name, $primary_key_name, $primary_key_id, $user_id);
if ($custom_sql !== false) {
$str .= $custom_sql;
} else {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
}
break;
}
}
return $str;
}