• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Turn an array into a query update string

Is there a way to turn form input information into an array and the create an insert statement so I don't have to write numerous UPDATE / SET statements in an UPDATE Query?
I'm trying to write less and I'm not sure I'm asking the question very well.

Here is my code to UPDATE data in my DB:
// Check if the form has been submitted:
if (isset($_POST['submitted'])) {
require_once (MYSQL);
	$errors = array();
	// Check for a first name:
	if (empty($_POST['asset_name'])) {
		$errors[] = 'You forgot to enter an asset name.';
	} else {
		$an = mysqli_real_escape_string($dbc, trim($_POST['asset_name']));
	// Check for a asset type:
	if (empty($_POST['asset_type'])) {
		$errors[] = 'You forgot to enter an asset_type.';
	} else {
		$at = mysqli_real_escape_string($dbc, trim($_POST['asset_type']));
	// Check for an Description:
	if (!empty($_POST['street_address'])) {
		$ad = mysqli_real_escape_string($dbc, trim($_POST['asset_desc']));


	if (empty($errors)) { // If everything's OK.
		//  Test for unique email address:
		$q = "SELECT workorder_id FROM workorders WHERE workorder_id = $id";
		$r = @mysqli_query($dbc, $q);
		if (mysqli_num_rows($r) == 1) {

			// Make the query:
			$q = "UPDATE workorders SET THE ARRAY VAIABLE HERE  WHERE workorder_id=$id LIMIT 1";
			$r = @mysqli_query ($dbc, $q);
			if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.
				// Print a message:
				echo '<p class="success">This workorder has been edited.</p>';	
			} else { // If it did not run OK.
				echo '<p class="error">Warning:<br /> 1. Either no information has been changed.<br />2. Or this asset could not be edited due to a system error.<br /><br />The system apologizes for any inconvenience.</p>'; // Public message.
				//echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.

Open in new window

Here is My FORM:
// Retrieve the user's information:
$q = "SELECT * FROM assets WHERE asset_id=$id";		
$r = @mysqli_query ($dbc, $q);

if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form.

	// Get the user's information:
	$row = mysqli_fetch_array ($r, MYSQLI_NUM);
	// Create the form:
	echo '<form action="edit_asset.php?id='.$id.'" method="post">
	<table class="content">
			<td width="150">Asset Name:</td><td> <input type="text" name="asset_name" size="50" maxlength="50" value="' . $row[1] . '" /></td>
			<td>Asset Type:</td>
			<select name="asset_type">
				<option name="asset_type" value="truck"';
				if($row[2]=='truck') {echo 'selected="selected"';} echo '>Truck</option>
				<option name="asset_type" value="car"';
				if($row[2]=='car') { echo 'selected="selected"';} echo '>Car</option>
				<option name="asset_type" value="other"';
				if($row[2]=='other') {echo 'selected="selected"';} echo '>Other</option>
		<td>Asset Description:</td>
		<td><textarea rows="9" cols="50" name="asset_desc" maxlength="500">'.$row[3].'</textarea></td>

	<div class="submit-cont-sml">
		<input type="image" src="img/buttons/submit.png" name="submit" value="Submit" />
		<input type="hidden" name="submitted" value="TRUE" />

Open in new window

So, is there a way to turn all of my if(empty($_POST ELSE statments in to a single array and then use that as the SET cause in my MY SQL query.

I know there are only three entries, but if I had an update form the had 50 - 100 fields that can be updated.

  • 4
  • 2
2 Solutions
Ray PaseurCommented:
This is a classic task for a bit of object-oriented programming.  You really need to sanitize and escape the data in these inputs before you use the data in a query, so write a class that handles each of the fields, building a class property for each field that is something acceptable in the query.  When the class is instantiated, it will access $_POST and prepare the properties, as well as any error messages.  If there are errors you can show the client, and if there are no errors, you can call a method on the class to retrieve the query string.
rgranlundAuthor Commented:
@Ray, can you point me to a tutorial?  Preferably a video tutorial?
Ray PaseurCommented:
I don't know of any tutorial that matches this exactly.  I can describe the design.

Each expected input will have its own class method that checks / validates / sanitizes / escapes an element of the POST array.

The class constructor will have a list of the expected elements of the POST array, and will dispatch the appropriate methods for each of the expected elements.

Each of the methods will raise a signal indicating that it has useful information or that it has added error information because an element of the POST array failed validation.

There will be a method to return the error information or FALSE.  It the error information is FALSE, there are no errors and the query can be run.  If there is error information, it can be displayed to the client so that the inputs can be corrected.

There will be a method to return the query string.  It will check for the error information and "protect" the caller by refusing to return a query string if the error information method indicates errors.

Once this class is developed, there will be a simple calling sequence that looks something like this:

$obj = new PostValidator;
if ($obj->errors) === FALSE
    $sql = $obj->queryString;
    /* RUN QUERY HERE */
    $err = $obj->errors;

Open in new window

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Julian HansenCommented:
Here is how I do it (although this might be overkill for your situation given the relatively few fields you are processing)

1. Create the form variables with array names like so

<input type="text" name="formdata[asset_name]" />
<input type="text" name="formdata[asset_type]" />

2. Fetch the data from the post like so

$formdata = $_POST['formdata'];

//To do an insert
add_record($formdata, 'mytable');

// Assumes the index names in the formdata array exactly
// match the table names in your table

function add_record(&data, $table)
    $fields = "";  // String to store field names
    $values = "";  // String to store values
    foreach($data as $k => $v) {
                // if strings are not empty we add commas
        if ($fields != '') {
            $fields .= ',';
            $values .= ",";

                // sanitize your input IMPORTANT
        $fields .= "`" . mysqli_real_escape_string($k) . "`";

                // This is so you can insert MYSQL functions into the
                // query like NOW() - make the value %%NOW()%%
                // - tells the function to not put quotes around the value

        if (preg_match('/^%%(.*)%%$/', $v, $matches) > 0) {
            $values .= $matches[1];
                // Otherwise add the quoted value to the value string
        else {
            $values .= "'" . mysql_real_escape_string($v) . "'";
    $query = "INSERT INTO $table ($fields) VALUES($values)";

Open in new window

To update
update_record('mytable','id=1', $formdata);

function update_record($table, $filter, & $data)
    $updatestr = "";  // String to hold the update
    $rv = 0;
    foreach($data as $f => $v) {
        // Add commas if not empty
        if ($updatestr != '') $updatestr .= ',';
        // Check for delimiter to exclude quotes
        if (preg_match('/^%%(.*)%%$/', $v, $matches) > 0) {
            $updatestr .= "`$f`=" . $matches[1];
        else {
            // Sanitize - IMPORTANT
            $updatestr .= "`$f`='" . mysqli_real_escape_string($v) . "'";
    if ($updatestr != '') {
        $query = "UPDATE $table SET $updatestr";
        // Add a filter to limit to the record(s) you want to update
        if ($filter != '') $query .= " WHERE $filter";
        $result = mysqli_query($query);
        if (!$result) {
            // set some error condition here
            $rv = false;
        else {
            // Otherwise return the number of rows that were affected by the query
            $rv = mysqli_affected_rows();
    return $rv;

Open in new window

If you want to exclude blank items from the form from an update then simply do this before calling

foreach($formdata as $k => $v) {
  if (empty($v)) unset($formdata[$k]);

Open in new window

Julian HansenCommented:
The other part of your question deals with validation - which can be hanlded using the same form array
// Setup a validation array. This is made up of
// An index that matches the index of your formdata
// An array with a regular expression (regx) that determines valid input
// An error message to display if the field fails validation
// Leave out fields that don't require validation

$validation = array (
    'asset_name' => array('regx' => '.+', 'error' => 'You must enter an assetname'),
    'asset_type' => array('regx' => '[0-9]+', 'error => 'Youmust enter an assettype')

// Call the validate function with your formdata and valiation array

$errors = validate($formdata, $validation);

if (!empty($errors)) {
    // send back to form
else {
     add_record($formdata, 'mytable');
     // OR
    // update_record('mytable', $filter, $formdata );

function & validate(&$data, &$validation)
    // Setup your return array for the errors
    $errors = array();

    // For each value in the formdata

    foreach($formdata as $k => $v) {

        // If a validation rule exists

        if (!empty($validation[$k])) {

            // Validate field

            if (preg_match($validation[$k]['regx'], $v) == 0) {

                // If the field fails validation add the error
                // to the error array using the field name
                // as the index
                $errors[$k] = $validation[$k]['error'];

    // return errors
    return $errors;

Open in new window

Ray PaseurCommented:
For filtering, validation, and sanitize functions PHP has some built-in capabilities.  It's not flawless, but it's usually better than trying to outsmart attackers with regular expressions.
Ray PaseurCommented:
The object-oriented solution is going to be better for you in the long run.  Among other things it's extensible, and processing the POST input is a classic opportunity to write and extend a base class, with custom methods for each of the POST data elements.  Here are some learning resources for the object-oriented notation, which you should learn before you try to learn object-oriented design patterns.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now