Jump to content

Looping through $_POST array to filter out blank fields - need it to concatenate an sql string variable to include only columns with values


Recommended Posts

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 by nlomb_hydrogeo
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>';

 

 

  • Great Answer 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.