If you ever plan on using views with multiple tables that you wish to edit, I recommend you update the update_record function in business_logic.php with my modification. You'll have to update it everytime you add a new view that is comprised of multiple tables, but it will let you update a record with values from multiple tables. The only issue with this is that the last field position on dadabik cannot be a computated/concat field. Otherwise, you shouldn't run into any issues. I'm going to insert the entire function, but you should look at the places where it mentions [AVF], that'll be the parts that you'll be interested in. When adding new views to the list, you'll need to update AVF P1 and AVF P2. Hope it helps, and remember to make a backup before editing these files!
[pre]
// [UNU] Modified User name to update on any modifications
function update_record($_FILES_2, $_POST_2, $fields_labels_ar, $table_name, $table_internal_name, $where_field, $where_value, $update_type)
// goal: insert a new record in the main database
// input $_FILES_2 (needed for the name of the files), $_POST_2 (the array containing all the values inserted in the form, $fields_labels_ar, $table_name, $table_internal_name, $where_field, $where_value, $update_type (internal or external)
// output: nothing
// global: $ext_updated_field, the field in which we set if a field has been updated
{
global $conn, $ext_updated_field, $current_user, $quote, $upload_directory, $null_checkbox_prefix, $year_field_suffix, $month_field_suffix, $day_field_suffix, $hours_field_suffix, $minutes_field_suffix, $seconds_field_suffix, $prefix_internal_table, $field_type_for_date, $treat_blank_as_null;
$uploaded_file_names_count = 0;
/*
// get the post variables of the form
reset ($_POST_2);
while (list($key, $value) = each ($_POST_2)){
$$key = $value;
} // end while
*/
switch($update_type){
case "internal":
//$field_to_check = "present_insert_form_field";
$field_to_check = "present_edit_form_field";
break;
case "external":
$field_to_check = "present_ext_update_form_field";
break;
} // end switch
// build the update statement
/////////////////////////////
// [AVF] 7/21/2015 Adding functionality for views to update fields one by one to get around limitation of updating only one table at a time.
$sql = "";
$sql .= "UPDATE ".$quote.$table_name.$quote." SET ";
$count_temp = count($fields_labels_ar);
for ($i=0; $i<$count_temp; $i++){
if ($table_name == 'view_master_oct_file' || $table_name == 'view_oct_file_fit_sample' || $table_name == 'view_oct_file_pp_sample' || $table_name == 'view_oct_file_tests' || $table_name == 'view_oct_file_gcc_price_ticket' || $table_name == 'view_oct_file_endwork' || $table_name == 'view_oct_file_trim' || $table_name == 'view_oct_file_basic' || $table_name == 'factory_cost_edit') { // clearing the $sql and forming new query for [AVF]
$sql = "";
$sql .= "UPDATE ".$quote.$table_name.$quote." SET ";
display_sql('AVF Functionality Triggered: Iteration '.$i.' of '.$count_temp);
}
// [AVF] End P1/3 of modification
$field_name_temp = $fields_labels_ar[$i]["name_field"];
// I use isset for select_multiple because could be unset
//if ($fields_labels_ar[$i][$field_to_check] == "1" && isset($_POST_2[$field_name_temp]) or $fields_labels_ar[$i]["type_field"] == "update_date"){ // if the field is in the form or need to be inserted because it's an update data
// no, since I don't handle select_multiple anymore, I delete isset, and anyway it was not correct because for a file $_POST_2[$field_name_temp] is not set
if ($fields_labels_ar[$i][$field_to_check] == "1" or $fields_labels_ar[$i]["type_field"] == "update_date" or $fields_labels_ar[$i]["type_field"] == "ID_user"){ // if the field is in the form or need to be inserted because it's an update data
if (isset($_POST_2[$null_checkbox_prefix.$field_name_temp]) && $_POST_2[$null_checkbox_prefix.$field_name_temp] === '1') { // NULL checkbox selected
display_sql('null checkbox selected');
$sql .= $quote.$field_name_temp.$quote." = NULL, "; // add the NULL value to the sql statement
} // end if
elseif ( ( !isset($_POST_2[$field_name_temp]) || $_POST_2[$field_name_temp] == '') && $treat_blank_as_null === 1 && $fields_labels_ar[$i]["type_field"] !== 'generic_file' && $fields_labels_ar[$i]["type_field"] !== 'image_file' && $fields_labels_ar[$i]["type_field"] !== 'update_date' && $fields_labels_ar[$i]["type_field"] !== 'ID_user') { // is blank but treat it as NULL
display_sql('Blank but treat as null location');
$sql .= $quote.$field_name_temp.$quote." = NULL, "; // add the NULL value to the sql statement
} // end if
else {
switch ($fields_labels_ar[$i]["type_field"]){
case "generic_file":
case "image_file":
$file_name = unescape($_FILES_2[$field_name_temp]['name']);
if ($treat_blank_as_null === 1 && $file_name === ''){
if (isset($_POST_2[$field_name_temp.'_file_uploaded_delete'])) { // the user want to delete a file previoulsy uploaded
$sql .= $quote.$field_name_temp.$quote." = NULL, "; // add the NULL value to the sql statement
unlink($upload_directory.unescape($_POST_2[$field_name_temp.'_file_uploaded_delete']));
}
}
else{
if ( $file_name != '') { // the user has selected a new file to upload
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$file_name = get_valid_name_uploaded_file($file_name, 0);
$sql .= "'".escape($file_name)."', "; // add the field value to the sql statement
$uploaded_file_names_count++;
// rename the temp name of the uploaded file
copy ($upload_directory.'dadabik_tmp_file_'.$file_name, $upload_directory.$file_name);
unlink($upload_directory.'dadabik_tmp_file_'.$file_name);
if (isset($_POST_2[$field_name_temp.'_file_uploaded_delete'])) { // the user want to delete a file previoulsy uploaded
unlink($upload_directory.unescape($_POST_2[$field_name_temp.'_file_uploaded_delete']));
} // end if
}
elseif (isset($_POST_2[$field_name_temp.'_file_uploaded_delete'])) { // the user want to delete a file previoulsy uploaded
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$sql .= "'', "; // add the field value to the sql statement
unlink($upload_directory.unescape($_POST_2[$field_name_temp.'_file_uploaded_delete']));
}
}
break;
case "select_multiple_menu":
case "select_multiple_checkbox":
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$sql .= "'";
if (isset($_POST_2[$fields_labels_ar[$i]["name_field"]])){ // otherwise the user hasn't checked any options
$count_temp_2 = count($_POST_2[$field_name_temp]);
for ($j=0; $j<$count_temp_2; $j++){
$sql .= $fields_labels_ar[$i]["separator_field"].$_POST_2[$field_name_temp][$j];// add the field value to the sql statement
} // end for
$sql .= $fields_labels_ar[$i]["separator_field"]; // add the last separator
}
$sql .= "', ";
break;
case "update_date":
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
//$sql .= "'".date("Y-m-d H:i:s")."', "; // add the field name to the sql statement
$sql .= format_date_for_dbms(date('Y-m-d H:i:s')).", "; // add the field name to the sql statement
break;
case "ID_user":
$sql .= $quote.$field_name_temp.$quote." = ";// add the field name to the sql statement
$sql .= "'".$current_user."', "; // add the field name to the sql statement
break;
case "date":
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
if ($field_type_for_date === 'date_picker'){
$sql .= format_date_for_dbms($_POST_2[$field_name_temp]).", "; // add the field value to the sql statement
}
else{
$field_name_temp = $field_name_temp;
$year_field = $field_name_temp.$year_field_suffix;
$month_field = $field_name_temp.$month_field_suffix;
$day_field = $field_name_temp.$day_field_suffix;
$mysql_date_value = $_POST_2[$year_field]."-".$_POST_2[$month_field]."-".$_POST_2[$day_field];
//$sql .= "'".$mysql_date_value."', "; // add the field value to the sql statement
$sql .= format_date_for_dbms($mysql_date_value).", "; // add the field value to the sql statement
}
break;
case "date_time":
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
if ($field_type_for_date === 'date_picker'){
$sql .= format_date_time_for_dbms($_POST_2[$field_name_temp]).", "; // add the field value to the sql statement
}
else{
$field_name_temp = $field_name_temp;
$year_field = $field_name_temp.$year_field_suffix;
$month_field = $field_name_temp.$month_field_suffix;
$day_field = $field_name_temp.$day_field_suffix;
$hours_field = $field_name_temp.$hours_field_suffix;
$minutes_field = $field_name_temp.$minutes_field_suffix;
$seconds_field = $field_name_temp.$seconds_field_suffix;
$mysql_date_time_value = $_POST_2[$year_field]."-".$_POST_2[$month_field]."-".$_POST_2[$day_field]." ".$_POST_2[$hours_field].":".$_POST_2[$minutes_field].":".$_POST_2[$seconds_field];
//$sql .= "'".$mysql_date_value."', "; // add the field value to the sql statement
$sql .= format_date_time_for_dbms($mysql_date_time_value).", "; // add the field value to the sql statement
}
break;
case 'select_single':
$field_name_other_temp = $field_name_temp."_other____";
if ($fields_labels_ar[$i]["other_choices_field"] == "1" and $_POST_2[$field_name_temp] == "......" and $_POST_2[$field_name_other_temp] != ""){ // insert the "other...." choice
$primary_key_field_field = $fields_labels_ar[$i]["primary_key_field_field"];
if ($primary_key_field_field != ""){
$linked_fields_ar = explode($fields_labels_ar[$i]["separator_field"], $fields_labels_ar[$i]["linked_fields_field"]);
$primary_key_field_field = insert_other_field($fields_labels_ar[$i]["primary_key_db_field"], $fields_labels_ar[$i]["primary_key_table_field"], $linked_fields_ar[0], $_POST_2[$field_name_other_temp]);
/*
if (substr_custom($foreign_key_temp, 0, 4) != "SQL:"){ // with arbitrary sql statement the insert in the primary key table is not supported yet
insert_other_field($foreign_key_temp, $_POST_2[$field_name_other_temp]);
} // end if
*/
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$sql .= "'".$primary_key_field_field."', "; // add the field value to the sql statement
} // end if ($foreign_key_temp != "")
else{ // no foreign key field
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$sql .= "'".$_POST_2[$field_name_other_temp]."', "; // add the field value to the sql statement
if (strpos_custom($fields_labels_ar[$i]["select_options_field"], $fields_labels_ar[$i]["separator_field"].unescape($_POST_2[$field_name_other_temp]).$fields_labels_ar[$i]["separator_field"]) === false){ // the other field inserted is not already present in the $fields_labels_ar[$i]["select_options_field"] so we have to add it
update_options($fields_labels_ar[$i], $field_name_temp, $_POST_2[$field_name_other_temp]);
// re-get the array containg label ant other information about the fields changed with the above instruction
$fields_labels_ar = build_fields_labels_array($table_internal_name, "1");
} // end if
} // end else
} // end if
else{
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$sql .= "'".$_POST_2[$field_name_temp]."', "; // add the field value to the sql statement
} // end else
break;
default: // textual field
$sql .= $quote.$field_name_temp.$quote." = "; // add the field name to the sql statement
$sql .= "'".$_POST_2[$field_name_temp]."', "; // add the field value to the sql statement
break;
} // end switch
} // end else
} // end if
//Functionality for [AVF]
if ($table_name == 'view_master_oct_file' || $table_name == 'view_oct_file_fit_sample' || $table_name == 'view_oct_file_pp_sample' || $table_name == 'view_oct_file_tests' || $table_name == 'view_oct_file_gcc_price_ticket' || $table_name == 'view_oct_file_endwork' || $table_name == 'view_oct_file_trim' || $table_name == 'view_oct_file_basic' || $table_name == 'factory_cost_edit')
if ($sql != 'UPDATE `view_master_oct_file` SET ' && $sql != 'UPDATE `view_oct_file_fit_sample` SET ' && $sql != 'UPDATE `view_oct_file_pp_sample` SET ' && $sql != 'UPDATE `view_oct_file_tests` SET ' && $sql != 'UPDATE `view_oct_file_gcc_price_ticket` SET ' && $sql != 'UPDATE `view_oct_file_endwork` SET ' && $sql != 'UPDATE `view_oct_file_trim` SET ' && $sql != 'UPDATE `view_oct_file_basic` SET ' && $sql != 'UPDATE `factory_cost_edit` SET ') {
$sql = substr_custom($sql, 0, -2); // delete the last two characters: ", "
$sql .= " where ".$quote.$where_field.$quote." = '".$where_value."'";
display_sql($sql);
$res_update = execute_db($sql, $conn);
}
// End Part 2/3 of [AVF]
} // end for
display_sql('Ending for loop');
// Clean up if statement functionality for [AVF]
if ($table_name != 'view_master_oct_file' && $table_name != 'view_oct_file_fit_sample' && $table_name != 'view_oct_file_pp_sample' && $table_name != 'view_oct_file_tests' && $table_name != 'view_oct_file_gcc_price_ticket' && $table_name != 'view_oct_file_endwork' && $table_name != 'view_oct_file_trim' && $table_name != 'view_oct_file_basic' && $table_name != 'factory_cost_edit') {
$sql = substr_custom($sql, 0, -2); // delete the last two characters: ", "
$sql .= " where ".$quote.$where_field.$quote." = '".$where_value."'";
// End Part 3/3 of [AVF]
/////////////////////////////
// end build the update statement
display_sql($sql);
// hack for static pages
if ($table_name === $prefix_internal_table.'static_pages' && $_POST_2['is_homepage_static_page'] === 'y'){
$sql_2 = "update ".$quote.$prefix_internal_table."static_pages".$quote." set is_homepage_static_page = 'n'";
$res_update = execute_db($sql_2, $conn);
}
}
// update the record
$res_update = execute_db($sql, $conn);
// enterprise
log_operation('update', $sql);
if ($update_type == "external"){
$sql = "UPDATE ".$quote.$table_name.$quote." SET ".$quote.$ext_updated_field.$quote." = '1' WHERE ".$quote.$where_field.$quote." = '".$where_value."'";
display_sql($sql);
// update the record
$res_update = execute_db($sql, $conn);
} // end if
} // end function update_record
[/pre]
