View Full Version : massive form. sql input advice pls

07-05-2011, 03:22 PM
Ok guys.

i am updating our Subcontractor system and i would like to know if there is a better method that im using to inset/update the sql table.

the SQL table!

tracking TEXT NOT NULL,
uniquecreationid VARCHAR(50) NOT NULL,
trading_name VARCHAR(50) NOT NULL,
md_name VARCHAR(50) NOT NULL,
contact_name VARCHAR(50) NOT NULL,
address_1 VARCHAR(50) NOT NULL,
address_2 VARCHAR(50) NOT NULL,
address_3 VARCHAR(50) NOT NULL,
postcode VARCHAR(10) NOT NULL,
business_telephone VARCHAR(15) NOT NULL,
business_fax VARCHAR(15) NOT NULL,
website VARCHAR(50) NOT NULL,
dir_mobile VARCHAR(15) NOT NULL,
reg_address_1 VARCHAR(50) NOT NULL,
reg_address_2 VARCHAR(50) NOT NULL,
reg_address_3 VARCHAR(50) NOT NULL,
reg_city VARCHAR(20) NOT NULL,
reg_postcode VARCHAR(10) NOT NULL,
design_service VARCHAR(3) NOT NULL,
design_service_notes TEXT NOT NULL,
trade_org_assoc VARCHAR(3) NOT NULL,
trade_org_assoc_notes TEXT NOT NULL,
total_employees integer not null,
attach_3yr_accounts VARCHAR(3) NOT NULL,
registration_no VARCHAR(20) NOT NULL,
tax_ref VARCHAR(20) NOT NULL,
verification VARCHAR(20) NOT NULL,
tax_treatment VARCHAR(20) NOT NULL,
vat_no VARCHAR(20) NOT NULL,
account_name VARCHAR(50) NOT NULL,
account_no VARCHAR(20) NOT NULL,
sort_code VARCHAR(10) NOT NULL,
insurance_signer VARCHAR(20) NOT NULL,
insurance_sign_date date NOT NULL,
insurance_policy_date date NOT NULL,
attach_insurance VARCHAR(3) NOT NULL,
quality_policy VARCHAR(3) NOT NULL,
attach_quality_policy VARCHAR(3) NOT NULL,
iso9001 VARCHAR(3) NOT NULL,
attach_iso9001 VARCHAR(3) NOT NULL,
quality_service VARCHAR(3) NOT NULL,
quality_service_notes TEXT NOT NULL,
hs_policy VARCHAR(3) NOT NULL,
attach_hs_policy VARCHAR(3) NOT NULL,
hs_dir_name VARCHAR(20) NOT NULL,
hs_dir_position VARCHAR(20) NOT NULL,
hs_advice VARCHAR(3) NOT NULL,
hs_advice_name VARCHAR(20) NOT NULL,
hs_advice_position VARCHAR(20) NOT NULL,
hs_advice_quals VARCHAR(80) NOT NULL,
hs_advice_profmem VARCHAR(50) NOT NULL,
hs_advice_employee VARCHAR(10) NOT NULL,
hs_responsibilities VARCHAR(3) NOT NULL,
attach_hs_responsibilities VARCHAR(3) NOT NULL,
hs_certified VARCHAR(3) NOT NULL,
attach_hs_certified VARCHAR(3) NOT NULL,
attach_hs_arrangements VARCHAR(3) NOT NULL,
attach_hs_ras VARCHAR(3) NOT NULL,
attach_hs_coshh VARCHAR(3) NOT NULL,
attach_hs_inspections VARCHAR(3) NOT NULL,
attach_hs_training_certs VARCHAR(3) NOT NULL,
attach_hs_consultation VARCHAR(3) NOT NULL,
attach_hs_accident_report VARCHAR(3) NOT NULL,
attach_hs_monitoring VARCHAR(3) NOT NULL,
hs_subcontractors VARCHAR(3) NOT NULL,
attach_hs_subcontractors VARCHAR(3) NOT NULL,
hs_subcontractors_competence TEXT NOT NULL,
cscs_cards VARCHAR(3) NOT NULL,
cscs_cards_pc double NOT NULL,
firstaid_name1 VARCHAR(50) NOT NULL,
firstaid_qual1 VARCHAR(100) NOT NULL,
firstaid_qual_exp1 date NOT NULL,
attach_firstaid_qual1 VARCHAR(3) NOT NULL,
firstaid_name2 VARCHAR(50) NOT NULL,
firstaid_qual2 VARCHAR(100) NOT NULL,
firstaid_qual_exp2 date NOT NULL,
attach_firstaid_qual2 VARCHAR(3) NOT NULL,
firstaid_name3 VARCHAR(50) NOT NULL,
firstaid_qual3 VARCHAR(100) NOT NULL,
firstaid_qual_exp3 date NOT NULL,
attach_firstaid_qual3 VARCHAR(3) NOT NULL,
other_name1 VARCHAR(50) NOT NULL,
other_course1 VARCHAR(100) NOT NULL,
other_qual_exp1 date NOT NULL,
attach_other_qual1 VARCHAR(3) NOT NULL,
other_name2 VARCHAR(50) NOT NULL,
other_course2 VARCHAR(100) NOT NULL,
other_qual_exp2 date NOT NULL,
attach_other_qual2 VARCHAR(3) NOT NULL,
other_name3 VARCHAR(50) NOT NULL,
other_course3 VARCHAR(100) NOT NULL,
other_qual_exp3 date NOT NULL,
attach_other_qual3 VARCHAR(3) NOT NULL,
other_name4 VARCHAR(50) NOT NULL,
other_course4 VARCHAR(100) NOT NULL,
other_qual_exp4 date NOT NULL,
attach_other_qual4 VARCHAR(3) NOT NULL,
other_name5 VARCHAR(50) NOT NULL,
other_course5 VARCHAR(100) NOT NULL,
other_qual_exp5 date NOT NULL,
attach_other_qual5 VARCHAR(3) NOT NULL,
other_name6 VARCHAR(50) NOT NULL,
other_course6 VARCHAR(100) NOT NULL,
other_qual_exp6 date NOT NULL,
attach_other_qual6 VARCHAR(3) NOT NULL,
other_name7 VARCHAR(50) NOT NULL,
other_course7 VARCHAR(100) NOT NULL,
other_qual_exp7 date NOT NULL,
attach_other_qual7 VARCHAR(3) NOT NULL,
other_name8 VARCHAR(50) NOT NULL,
other_course8 VARCHAR(100) NOT NULL,
other_qual_exp8 date NOT NULL,
attach_other_qual8 VARCHAR(3) NOT NULL,
hsp_curr_year DOUBLE NOT NULL,
hsp_prev_year DOUBLE NOT NULL,
hsp_ybl_year DOUBLE NOT NULL,
hsp_curr_year_employees DOUBLE NOT NULL,
hsp_prev_year_employees DOUBLE NOT NULL,
hsp_ybl_year_employees DOUBLE NOT NULL,
hsp_curr_year_afr DOUBLE NOT NULL,
hsp_prev_year_afr DOUBLE NOT NULL,
hsp_ybl_year_afr DOUBLE NOT NULL,
hsp_curr_year_hsein DOUBLE NOT NULL,
hsp_prev_year_hsein DOUBLE NOT NULL,
hsp_ybl_year_hsein DOUBLE NOT NULL,
hsp_curr_year_hsepn DOUBLE NOT NULL,
hsp_prev_year_hsepn DOUBLE NOT NULL,
hsp_ybl_year_hsepn DOUBLE NOT NULL,
hsp_curr_year_hsep DOUBLE NOT NULL,
hsp_prev_year_hsep DOUBLE NOT NULL,
hsp_ybl_year_hsep DOUBLE NOT NULL,
hsp_curr_year_fatalities DOUBLE NOT NULL,
hsp_prev_year_fatalities DOUBLE NOT NULL,
hsp_ybl_year_fatalities DOUBLE NOT NULL,
hsp_curr_year_injuries DOUBLE NOT NULL,
hsp_prev_year_injuries DOUBLE NOT NULL,
hsp_ybl_year_injuries DOUBLE NOT NULL,
hsp_curr_year_absences DOUBLE NOT NULL,
hsp_prev_year_absences DOUBLE NOT NULL,
hsp_ybl_year_absences DOUBLE NOT NULL,
hsp_curr_year_dangerous DOUBLE NOT NULL,
hsp_prev_year_dangerous DOUBLE NOT NULL,
hsp_ybl_year_dangerous DOUBLE NOT NULL,
hsp_curr_year_nonriddor DOUBLE NOT NULL,
hsp_prev_year_nonriddor DOUBLE NOT NULL,
hsp_ybl_year_nonriddor DOUBLE NOT NULL,
hsp_enforcement_action TEXT NOT NULL,
env_policy VARCHAR(3) NOT NULL,
attach_env_policy VARCHAR(3) NOT NULL,
env_iso14001 VARCHAR(3) NOT NULL,
attach_env_iso14001 VARCHAR(3) NOT NULL,
env_iso14001_notes TEXT NOT NULL,
env_objectives VARCHAR(3) NOT NULL,
env_objectives_notes TEXT NOT NULL,
envp_curr_year DOUBLE NOT NULL,
envp_prev_year DOUBLE NOT NULL,
envp_ybl_year DOUBLE NOT NULL,
envp_curr_year_in DOUBLE NOT NULL,
envp_prev_year_in DOUBLE NOT NULL,
envp_ybl_year_in DOUBLE NOT NULL,
envp_curr_year_en DOUBLE NOT NULL,
envp_prev_year_en DOUBLE NOT NULL,
envp_ybl_year_en DOUBLE NOT NULL,
envp_curr_year_p DOUBLE NOT NULL,
envp_prev_year_p DOUBLE NOT NULL,
envp_ybl_year_p DOUBLE NOT NULL,
envp_curr_year_minor DOUBLE NOT NULL,
envp_prev_year_minor DOUBLE NOT NULL,
envp_ybl_year_minor DOUBLE NOT NULL,
env_waste VARCHAR(3) NOT NULL,
attach_env_waste VARCHAR(3) NOT NULL,
company_year1 VARCHAR(15) NOT NULL,
company_project1 VARCHAR(100) NOT NULL,
company_value1 VARCHAR(10) NOT NULL,
company_year2 VARCHAR(15) NOT NULL,
company_project2 VARCHAR(100) NOT NULL,
company_value2 VARCHAR(10) NOT NULL,
company_year3 VARCHAR(15) NOT NULL,
company_project3 VARCHAR(100) NOT NULL,
company_value3 VARCHAR(10) NOT NULL,
company_year4 VARCHAR(15) NOT NULL,
company_project4 VARCHAR(100) NOT NULL,
company_value4 VARCHAR(10) NOT NULL,
company_year5 VARCHAR(15) NOT NULL,
company_project5 VARCHAR(100) NOT NULL,
company_value5 VARCHAR(10) NOT NULL,
company_year6 VARCHAR(15) NOT NULL,
company_project6 VARCHAR(100) NOT NULL,
company_value6 VARCHAR(10) NOT NULL,
company_year7 VARCHAR(15) NOT NULL,
company_project7 VARCHAR(100) NOT NULL,
company_value7 VARCHAR(10) NOT NULL,
previous_year1 VARCHAR(15) NOT NULL,
previous_project1 VARCHAR(100) NOT NULL,
previous_value1 VARCHAR(10) NOT NULL,
previous_year2 VARCHAR(15) NOT NULL,
previous_project2 VARCHAR(100) NOT NULL,
previous_value2 VARCHAR(10) NOT NULL,
previous_year3 VARCHAR(15) NOT NULL,
previous_project3 VARCHAR(100) NOT NULL,
previous_value3 VARCHAR(10) NOT NULL,
previous_year4 VARCHAR(15) NOT NULL,
previous_project4 VARCHAR(100) NOT NULL,
previous_value4 VARCHAR(10) NOT NULL,
previous_year5 VARCHAR(15) NOT NULL,
previous_project5 VARCHAR(100) NOT NULL,
previous_value5 VARCHAR(10) NOT NULL,
competence_name1 VARCHAR(50) NOT NULL,
competence_trade1 VARCHAR(50) NOT NULL,
competence_quals1 VARCHAR(100) NOT NULL,
competence_service1 VARCHAR(20) NOT NULL,
competence_name2 VARCHAR(50) NOT NULL,
competence_trade2 VARCHAR(50) NOT NULL,
competence_quals2 VARCHAR(100) NOT NULL,
competence_service2 VARCHAR(20) NOT NULL,
competence_name3 VARCHAR(50) NOT NULL,
competence_trade3 VARCHAR(50) NOT NULL,
competence_quals3 VARCHAR(100) NOT NULL,
competence_service3 VARCHAR(20) NOT NULL,
competence_name4 VARCHAR(50) NOT NULL,
competence_trade4 VARCHAR(50) NOT NULL,
competence_quals4 VARCHAR(100) NOT NULL,
competence_service4 VARCHAR(20) NOT NULL,
competence_name5 VARCHAR(50) NOT NULL,
competence_trade5 VARCHAR(50) NOT NULL,
competence_quals5 VARCHAR(100) NOT NULL,
competence_service5 VARCHAR(20) NOT NULL,
ref_nature_1 VARCHAR(50) NOT NULL,
ref_value_1 VARCHAR(20) NOT NULL,
ref_client_1 VARCHAR(20) NOT NULL,
ref_project_1 VARCHAR(50) NOT NULL,
ref_projectno_1 VARCHAR(20) NOT NULL,
ref_email_1 VARCHAR(50) NOT NULL,
ref_nature_2 VARCHAR(50) NOT NULL,
ref_value_2 VARCHAR(20) NOT NULL,
ref_client_2 VARCHAR(20) NOT NULL,
ref_project_2 VARCHAR(50) NOT NULL,
ref_projectno_2 VARCHAR(20) NOT NULL,
ref_email_2 VARCHAR(50) NOT NULL,
ref_nature_3 VARCHAR(50) NOT NULL,
ref_value_3 VARCHAR(20) NOT NULL,
ref_client_3 VARCHAR(20) NOT NULL,
ref_project_3 VARCHAR(50) NOT NULL,
ref_projectno_3 VARCHAR(20) NOT NULL,
ref_email_3 VARCHAR(50) NOT NULL,
safety_signer VARCHAR(50) NOT NULL,
safety_sign_date DATE NOT NULL,
primary key(id));

now, i generally do soemthing like this:

if(!isset($_SESSION)) {session_start();}
include 'config.php';
$_SESSION['linkedtopage'] = curPageURL();
if($_SESSION['authorised'] != true) {
header('Location: login.php');
//basic constants
$form_name_friendly = 'Subcontractor';
$table_name = 'subcontractors';
$page_name = 'subcontractor';

if(isset($_GET['id'])) {
$id = mysql_real_escape_string(urldecode($_GET['id']));

if($id!="") { //use values if obtained from URL via id=
$query = "SELECT * FROM $table_name WHERE id = $id" ;
$result = mysql_query($query) or die('<h3>Error - ' . $query . '</h3>');

if (extract(mysql_fetch_array($result, MYSQL_ASSOC)) == 0) {
header("Location: $page_name.php");// used if cant find id in db.

} else {

if (!isset($uniquecreationid)) {
$uniquecreationid = md5(str_replace(" ", "-", $_SESSION['authusername']) . "-". date("y-n-j-H-i-s"));
$datetimer = date("D d\.m\.y \@ H\:i");
$tracking = "&rsaquo; $form_name_friendly created by " . $_SESSION['authusername'] . " - $datetimer";

if($_POST) {
import_request_variables("p"); // p = post, g = get, pg does both

$query = "SELECT * FROM $table_name WHERE uniquecreationid = '$uniquecreationid'";
$result = mysql_query($query) or die('<h3>Error - ' . $query . '</h3>');
$row = mysql_fetch_array($result, MYSQL_ASSOC);

if($row) { //updates record
$datetimer = date("D d\.m\.y \@ H\:i");
$oldtracking = $tracking;
$tracking = "&rsaquo; $form_name_friendly edited by " . $_SESSION['authusername'] . " - $datetimer<br />$oldtracking";

$query = "UPDATE $table_name SET uniquecreationid='$uniquecreationid', tracking='$tracking' WHERE uniquecreationid='$uniquecreationid'";
mysql_query($query) or die('<h3>Error - ' . $query . '</h3>');
$id = $row['id'];

} else { //create new record /// check _copy_subcontractorder.php as duplicated there!
$query = "INSERT INTO $table_name (tracking, uniquecreationid) " .
"VALUES ('$tracking', '$uniquecreationid')";
mysql_query($query) or die('<h3>Error - ' . $query . '</h3>');
$id = mysql_insert_id();

//open up the page via get, so stop caching errors!
header("Location: $page_name.php?id={$id}");

but the thought of having to manually sort all those field names into the INSERT/UPDATE (why doesnt sql have a similar syntax for both? seems silly to me!) makes me want to cry lol.

any advice on how to automate that bit?

07-05-2011, 04:33 PM
Generally speaking, if you are not requiring every field to be used than it can be factored down and flattened to a more structured 3NF level in your database design. These can often be identified by the usage of suffixes on your column names: _1, _2, ... where these can typically be broken into a table consisting of a one to many or two tables to create a many to many relationship (I'm guessing that the address columns for example are not always all filled in?).
Beyond this, you can set default values for your columns by flagging the DEFAULT during table creation which allows you to only insert data you intend to add.

SQL itself does not share a commonality between INSERT and UPDATE. These are different types of DMS' and must be treated as such; however, MySQL itself does have a combined statement called a REPLACE which acts as an INSERT and onconflict issues a DELETE followed by an INSERT. MySQL also allows the SET syntax to be used within an INSERT query instead of the traditional COLUMNS/VALUES calls.

Overall, I would not suggest using the REPLACE syntax as its non-standard SQL. Insetead, a SELECT to determine an INSERT or UPDATE, or an INSERT to an error catch is the best way to determine if you should INSERT or UPDATE.

07-05-2011, 04:38 PM
thanks mate, i will look into this. to be honest im not sure which fields will and wont be filled out. they are supposed to fill them all out (with maybe the exception of registered_address etc but thats just 5 fields)

each subcontractor should fill out one of these forms.

are you saying that i can use set for both insert and update? if so thats just saved me a fair bit of work! :)

07-05-2011, 04:43 PM
That's correct. SET can be used in MySQL UPDATE and INSERT statements (http://dev.mysql.com/doc/refman/5.5/en/insert.html note the second example).

INSERT INTO MyTABLE SET column1 = value1, column2 = value2, ...

INTO is also optional according to the API. So the only real difference between the INSERT using the SET and the UPDATE would be that the INSERT will not accept a WHERE clause.

07-05-2011, 04:52 PM
excellent. this alone had made me happy! :) i find it hard to cope with the 2 sets of bracketted values when you have hundreds of fields, the x=$x seems much more logical and easy to read.