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">
		<tr>
			<td width="150">Asset Name:</td><td> <input type="text" name="asset_name" size="50" maxlength="50" value="' . $row[1] . '" /></td>
		</tr>
		<tr>
			<td>Asset Type:</td>
			<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>
			</select>
			</td>
		</tr>
		<tr>
		<td>Asset Description:</td>
		<td><textarea rows="9" cols="50" name="asset_desc" maxlength="500">'.$row[3].'</textarea></td>
		</tr>

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

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.

Help?
LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
rgranlundAuthor Commented:
@Ray, can you point me to a tutorial?  Preferably a video tutorial?
0
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 */
}
else
{
    $err = $obj->errors;
    /* SHOW CLIENT ERRORS HERE */
}

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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)";
       mysqli_query($query);
}

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

0
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
http://php.net/manual/en/ref.filter.php
0
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.
http://php.net/manual/en/language.oop5.php
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2626-Beginning-Object-Oriented-Programming-in-PHP.html
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2631-Advanced-Object-Oriented-Programming-in-PHP.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.