nlomb_hydrogeo Posted January 26 Share Posted January 26 (edited) Hello PHP freaks I'm a newbie so bear with me. I have a generic problem and I'm sure code already exists to solve it. I need to have a dynamic sql string variable that will only produce an insert statement with database column names corresponding to input fields on the form that contain values, ignoring the blank ones. I know some looping function like foreach might be used, but if I use it on the $_POST array I'll get the values, not the keys. (the first field, well_no, is a primary key and is held in a session variable, so I know I'll be inserting a value for that column) $ins = "well_no,"; foreach($_POST as $field) if (!empty($field)){ $ins .=$_POST[$field].","; } And I get a warning "Warning: Undefined array key "production in... Here "production" is actually a form input. the problem is my insert statement is hard-coded: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, ... How can I adjust the sql statement and also the $data array holding the variables. Here's the context: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, :purpose, :country, :admin1, :admin2, :admin3, :settlement, :orig_wellno, :date_completed, :coord_sys, :elev, :status)"; $stmnt = $pdo->prepare($sql); $data = [':well_no'=>$well_no, ':easting'=>$easting, ':northing'=>$northing, ':purpose'=>$purpose, ':country'=>$country, ':admin1'=>$admin1, ':admin2'=> $admin2, ':admin3'=>$admin3, ':settlement'=>$settlement, ':orig_wellno'=>$orig_wellno, ':date_completed'=> $date_completed, ':coord_sys'=> $coord_sys, ':elev'=>$elev, ':status'=>$status]; $stmnt->execute($data); Edited January 26 by nlomb_hydrogeo Quote Link to comment Share on other sites More sharing options...
requinix Posted January 26 Share Posted January 26 40 minutes ago, nlomb_hydrogeo said: I know some looping function like foreach might be used, but if I use it on the $_POST array I'll get the values, not the keys. Is it possible you're not aware that foreach can give you both the keys and the values? That said, you shouldn't be going through every single entry in $_POST without verifying that it's good to add to your SQL. Really, you should be starting with the list of columns you want to support and then looking in $_POST to see what each one's value is. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted January 26 Share Posted January 26 don't unconditionally loop over $_POST data. hackers/bots can submit 100's or 1000's of fields (php had to add a setting to limit the number of fields), with their own field names (which will allow sql injection in the query), not yours. you should instead have an array the defines the expected fields, then use this definition to control what your code does. this is referred to as a data-driven design. see this example - // define the expected form fields $fields = ['easting', 'northing', 'purpose', 'country', 'admin1', 'admin2', 'admin3', 'settlement', 'orig_wellno', 'date_completed', 'coord_sys', 'elev', 'status']; $col = []; // array of columns $data = []; // array of prepared query input values // add the well no $col[] = '`well_no`'; $data[] = $_SESSION['well_no']; // use whatever the actual session variable is // loop over the defining array foreach($fields as $field) { // note: empty considers 0 or '0' to be an empty value. this will prohibit a numerical zero value being used. // you should instead test if the value is or is not an empty string. if($_POST[$field] !== '') { $col[] = "`$field`"; $data[] = $_POST[$field]; } } // build the sql query $sql = "INSERT INTO well_parent (".implode(',',$col).") VALUES (".implode(',',array_fill(0,count($col),'?')).")"; // examine the result echo $sql; echo '<pre>'; print_r($data); echo '</pre>'; 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.