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

[nlomb_hydrogeo]

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

[requinix]

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.

[mac_gyver]

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