-2

I've created a user form in which once the submit button is pressed I would like to send/insert the data to mysql database adding a new record. The form has over 100 input fields. How can I accomplish this. Here is my sample php code.

    <html>
<head>
</head>
<body>
<?php
if (isset($_POST['submit'])){


//Variables for connecting to your database.
            //These variable values come from your hosting account.
            $hostname = "hostname";
            $username = "username";
            $password = "password";
            $dbname = "dbname";
            $mystuff = "tenant_lname","tenant_fname","tenant_mname","ssn","dl_number","dl_state","birthday","tenant_hphone","tenant_wphone","tenant_cphone","curr_street","curr__unit","curr_city","curr_state","curr_zip","how_long_from","how_long_to","last_rent_mnt","last_rent_amt","own_man_name","own_man_tel","curr_reason","pre_street","pre_unit","pre_city","pre_state","pre_zip","pre_from","pre_to","pre_last_rent","pre_amt","pre_owner","pre_owner_tel","pre_reason","sec_pre_street","sec_pre_unit","sec_pre_city","sec_pre_state","sec_pre_zip","sec_pre_from","sec_pre_to","sec_pre_last_paid_mnt","sec_pre_amt","sec_pre_owner","sec_pre_owner_tel","sec_pre_reason","curr_emp_name","curr_emp_add","curr_emp_phone","curr_emp_pos","curr_emp_bus_type","curr_emp_sup","curr_emp_from","curr_emp_to","curr_emp_salary","pre_emp_name","pre_emp_add","pre_emp_phone","pre_emp_pos","pre_emp_bus_type","pre_emp_sup_name","pre_emp_from","pre_emp_to","pre_emp_salary","move_date","addntl_occ_name","addntl_occ_age","addntl_occ_relation","addntl_ft","addntl_pt","addntl_occ1_name","addntl_occ1_age","addntl_occ1_relation","addntl_occ1_ft","addntl_occ1_pt","addntl_occ2_name","addntl_occ2_age","addnt2_occ1_relation","addntl_occ2_ft","addntl_occ2_pt","addntl_occ3_name","addntl_occ3_age","addntl_occ3_relation","addntl_occ3_ft","addntl_occ3_pt","credit_yes","credit_no","det_yes","det_no","evict_yes","evict_no","bnkry_yes","bnkry_no","fel_yes","fel_no","pet_yes","pet_no","pet_numb","pet_type","furn_yes","furn_no","ins_cov_yes","ins_cov_no","ints_yes","ints_no","ints_type","smoke_yes","smoke_no","occ_smoke_yes","occ_smoke_no","explain_smoke","bnk_name","bnk_add","checking","checking_bal","saving","saving_bal","bnk_name1","bnk_add1","checking1","checking_bal1","saving1","saving_bal1","other_income","credit_name","credit_add","credit_city","credit_acct","credit_bal","credit_payment","credit_name1","credit_add1","credit_city1","credit_acct1","credit_bal1","credit_payment1","credit_acct2_name","credit_add2","credit_city2","credit_acc2","credit_bal2","credit_payment2","credit_acc3_name","credit_acc3_add","credit_acc3_city","credit_acc3_number","credit_acc3_bal","credit_acc3_payment","emer_contact_name","emer_contact_add","emer_relation","emer_phone","reg_owner_yes","reg_owner_no","reg_who","vehicle_year","vehicle_make","vehicle_model","vehicle_color","vehicle_license","veh_state","vehicle2_year","vehicle2_make","vehicle2_model","vehicle2_color","vehicle2_license","veh2_state";



$con = mysql_connect("$hostname","$username","$password");
if (!$con){
die ("Can not connect:" . mysql_error());   
}

mysql_select_db("dbname",$con);

$sql = "INSERT INTO dbname ($mystuff) VALUES ('$_POST[$mystuff]')";

mysql_query($sql,$con);

mysql_close($con);
}
?>
</body>
</html>
Spudley
  • 166,037
  • 39
  • 233
  • 307
Don Austin
  • 5
  • 4
  • I would like to do this without typing in all of the field names – Don Austin Apr 30 '13 at 01:48
  • 1. refrain from using mysql extension in php as it has been deprecated 2. use pdo for parameterized queries – Satya Apr 30 '13 at 01:49
  • @Don Austin: You are basically looking for something called an array: http://php.net/array and then something called [loops](http://php.net/control-structures.foreach) - the rest like the HTML form and the SQL query you seem to already know but take care that the database client library you use makes it easy to write unsafe code and is not under active development any longer. – M8R-1jmw5r Apr 30 '13 at 01:57
  • For safety's sake, you do need to type in all the field names. You will not be able to write [robust SQL code](http://weblogs.sqlteam.com/jamesw/archive/2011/10/03/a-list-of-sql-best-practices.aspx) otherwise. – Vince Bowdren Apr 30 '13 at 15:18

2 Answers2

0

$mystuff should be an array.

You can generate your query and form with an loop.

Do validation if these is for productive use!

$_POST is also an array, so $_POST["field1", "field2", ...] ist an syntax error. You can only access one key at once e.g. $_POST['field1'] . ',' . $_POST['field2']

You can join all values in an array by an char (e.g. ',') with implode()

rethink your Database schema!

untested:

    <html>
`enter code here`<head>
`enter code here`</head>
<body>
<?php
>if (isset($_POST['submit'])){

    //Variables for connecting to your database.
    //These variable values come from your hosting account.
    $hostname = "hostname";
    $username = "username";
    $password = "password";
    $dbname = "dbname";
    $mystuff = array(  "tenant_lname","tenant_fname","tenant_mname","ssn","dl_number","dl_state","birthday","tenant_hphone","tenant_wphone","tenant_cphone","curr_street","curr__unit","curr_city","curr_state","curr_zip","how_long_from","how_long_to","last_rent_mnt","last_rent_amt","own_man_name","own_man_tel","curr_reason","pre_street","pre_unit","pre_city","pre_state","pre_zip","pre_from","pre_to","pre_last_rent","pre_amt","pre_owner","pre_owner_tel","pre_reason","sec_pre_street","sec_pre_unit","sec_pre_city","sec_pre_state","sec_pre_zip","sec_pre_from","sec_pre_to","sec_pre_last_paid_mnt","sec_pre_amt","sec_pre_owner","sec_pre_owner_tel","sec_pre_reason","curr_emp_name","curr_emp_add","curr_emp_phone","curr_emp_pos","curr_emp_bus_type","curr_emp_sup","curr_emp_from","curr_emp_to","curr_emp_salary","pre_emp_name","pre_emp_add","pre_emp_phone","pre_emp_pos","pre_emp_bus_type","pre_emp_sup_name","pre_emp_from","pre_emp_to","pre_emp_salary","move_date","addntl_occ_name","addntl_occ_age","addntl_occ_relation","addntl_ft","addntl_pt","addntl_occ1_name","addntl_occ1_age","addntl_occ1_relation","addntl_occ1_ft","addntl_occ1_pt","addntl_occ2_name","addntl_occ2_age","addnt2_occ1_relation","addntl_occ2_ft","addntl_occ2_pt","addntl_occ3_name","addntl_occ3_age","addntl_occ3_relation","addntl_occ3_ft","addntl_occ3_pt","credit_yes","credit_no","det_yes","det_no","evict_yes","evict_no","bnkry_yes","bnkry_no","fel_yes","fel_no","pet_yes","pet_no","pet_numb","pet_type","furn_yes","furn_no","ins_cov_yes","ins_cov_no","ints_yes","ints_no","ints_type","smoke_yes","smoke_no","occ_smoke_yes","occ_smoke_no","explain_smoke","bnk_name","bnk_add","checking","checking_bal","saving","saving_bal","bnk_name1","bnk_add1","checking1","checking_bal1","saving1","saving_bal1","other_income","credit_name","credit_add","credit_city","credit_acct","credit_bal","credit_payment","credit_name1","credit_add1","credit_city1","credit_acct1","credit_bal1","credit_payment1","credit_acct2_name","credit_add2","credit_city2","credit_acc2","credit_bal2","credit_payment2","credit_acc3_name","credit_acc3_add","credit_acc3_city","credit_acc3_number","credit_acc3_bal","credit_acc3_payment","emer_contact_name","emer_contact_add","emer_relation","emer_phone","reg_owner_yes","reg_owner_no","reg_who","vehicle_year","vehicle_make","vehicle_model","vehicle_color","vehicle_license","veh_state","vehicle2_year","vehicle2_make","vehicle2_model","vehicle2_color","vehicle2_license","veh2_state");


    $sql_values=array();
    foreach($mystuff as $fieldname) {
        /* do validation! */

        $sql_values[$fieldname] = "'" . mysql_real_excape_stiring($_POST[$fieldname]) . "'";
    }


    $con = mysql_connect("$hostname","$username","$password");
    if (!$con){
        die ("Can not connect:" . mysql_error());
    }

    mysql_select_db("dbname",$con);

    $sql = "INSERT INTO dbname (".implode(',', $mystuff).") VALUES (" . implode(',', $sql_values) . ")";

    mysql_query($sql,$con);

    mysql_close($con);
}


foreach($mystuff as $fieldname) {
    echo "...an input field...";
}

?>



</body>
vszurma
  • 263
  • 1
  • 9
0

Create inputs something like :

<input type="text" name="datas[firstname]"/>
<input type="text" name="datas[lastname]"/>

You can process the data using :

<?php

$datas = $_POST['datas'];
$columns = implode(",",array_keys($datas));

//add ' since mysql use ' for strings
$values = implode("','",$datas);

$sql = "INSERT INTO dbname (".$columns.") VALUES ('".$values."')";

Hope this help.

Usman Tiono
  • 224
  • 1
  • 6
  • `$_POST` is already an array, why not just use `foreach($_POST as $k=>$v)`? – Niet the Dark Absol Apr 30 '13 at 02:26
  • Iterating the array is an alternative solution because we should build the column by concating $k as you defined above. Yes, you're right, $_POST['datas'] is already an array, so instead of iterating it, I'd prefer using implode which requires array to be the parameter too. – Usman Tiono Apr 30 '13 at 02:34
  • And here it comes the SQL Injection Company. – M8R-1jmw5r Apr 30 '13 at 03:17
  • Yeah, I'm aware that those codes are susceptible of being injected. I do recommend to use any libraries/frameworks but what I answered was the basic only. – Usman Tiono Apr 30 '13 at 03:45
  • At this point I don't believe that I can change my input names which are associated with my ccs. I'm new at all of this and now you've got me wondering how to do this using PDO to help prevent the SQL Injection I keep reading about. Is there another way to do this without changing the input names? Thank you in advance. @UsmanTiono – Don Austin May 01 '13 at 03:25
  • you've got me wondering how to do this using PDO to help prevent the SQL Injection I keep reading about. Is there another way to do this without changing the input names? Thank you in advance.@Kolink – Don Austin May 01 '13 at 03:25
  • Sorry for a late response. CSS depends on the element classes or id, not the name, so you can change the input name and let the class or id the same as previous. Here is a link how to prevent SQL Injection : [How To Prevent SQL Injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) – Usman Tiono May 07 '13 at 02:22