Is there an easier way to do this?

[jeff5656]

I have about 270 fields in a form (!!).  When the user suibmits the form I want to update  that record in the database.  Below is an example (not the one with 270 fields) of the only way I know how to to update a database from a form.  If I do it this way I will have to type 270 fields in twice.

Is there a way to automatically update all the fields from the form without having to type it all in individually?

 

$rm_loc_pre = mysql_real_escape_string($_POST['rm_loc']);
$rm_loc = ereg_replace("[^A-Za-z0-9]", "", $rm_loc_pre);
$resident = mysql_real_escape_string($_POST['resident']);

$patient = mysql_real_escape_string($_POST['patient']);
$mrn = mysql_real_escape_string($_POST['mrn']);
$age =mysql_real_escape_string($_POST['age']);
$race = mysql_real_escape_string( $_POST['race']);
$gender = mysql_real_escape_string( $_POST['gender']);
$pod = mysql_real_escape_string( $_POST['pod']);
$rcf_date = mysql_real_escape_string($_POST['rcf_date']);
$dx = mysql_real_escape_string( $_POST['dx']);
$meds = mysql_real_escape_string( $_POST['meds']);
$pmhx = mysql_real_escape_string( $_POST['pmhx']);
$problist = mysql_real_escape_string( $_POST['problist']);
$anticipate = mysql_real_escape_string( $_POST['anticipate']);
$antic2 = mysql_real_escape_string( $_POST['antic2']);
$antic3 = mysql_real_escape_string( $_POST['antic3']);
$antic4 = mysql_real_escape_string( $_POST['antic4']);
$todo2 = mysql_real_escape_string( $_POST['todo2']);
$todo3 = mysql_real_escape_string( $_POST['todo3']);
$todo4 = mysql_real_escape_string( $_POST['todo4']);
$comments = mysql_real_escape_string( $_POST['comments']);
$code = mysql_real_escape_string( $_POST['code']);
$allergy = mysql_real_escape_string( $_POST['allergy']);
$todo = mysql_real_escape_string($_POST['todo']);
$signoff_status = mysql_real_escape_string( $_POST['signoff_status']);
$rcf_date2 = $newdate;



$sql = "UPDATE icu SET rm_loc = '$rm_loc', patient = '$patient', mrn = '$mrn', age = '$age',  icudays = '$icudays', race='$race', gender='$gender', pod='$pod', resident = '$resident',rcf_date='$rcf_date', dx='$dx', meds='$meds', pmhx='$pmhx', problist='$problist', problist_date= '$problist_date', anticipate='$anticipate', antic2='$antic2', antic3='$antic3', antic4='$antic4', anticipate_date = '$anticipate_date', antic2_date = '$antic2_date', antic3_date = '$antic3_date', antic4_date = '$antic4_date', comments='$comments', comments_date = '$comments_date', code='$code', allergy='$allergy', todo='$todo', todo2='$todo2', todo3='$todo3', todo4='$todo4', todo_date='$todo_date', todo2_date='$todo2_date', todo3_date='$todo3_date', todo4_date='$todo4_date', signoff_status='$signoff_status', rcf_date2='$rcf_date2' WHERE id_incr = '$id_incr'";

if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());

[Caesar]

Foreach loop.

[ratcateme]

if you made sure that the only elements in the form were database fields you could do

$query = "UPDATE `icu` SET ";
$set = array();
foreach($_POST as $field => $value){
    $field = mysql_real_escape_string($field);
    $value = mysql_real_escape_string($value);
    $set[] = "`{$field}` = '{$value}'";
}
$query .= implode(", ",$set);

 

Scott.

[jeff5656]

When I ran that foreach code, it did not update the databse.  Can't figure out why.

In sum I have many variables but they only differ by the number:

which_date1

icufellow1

icustaff1

f2staff1

intervent1

wb1

tx1

phtn1

warren1

 

The number goes all the way to 31

 

Is there an easy way to take all the POST variables and update the database knowing that all the variables are the same except for the number at the end?

[Psycho]

Based upon what you have I would first convert each POST array item to a variable with the same name as the key:

 

foreach ($_POST as $variable => $value)
{
    $$variable = $value;
}

//If $_POST['foo'] = "bar" the above will create a variable named $foo with a value of "bar"

 

Then for any post processing you currently have, you will need to incorporate, such as:

 

$rm_loc = ereg_replace("[^A-Za-z0-9]", "", $rm_loc);

 

Ass long as all the variables used in your query are the same as the POST index values, you shoudl be good to go.

[premiso]

I would honestly create an array of the columns you expect, or pull them from the database using SQL. Then do a check if $key is not in the array, then do not add it to the SQL, as it could break your sql and be an attempt of an attack on your server.

 

<?php
$cols = array("rm_loc", "patient"); // etc
foreach ($_POST as $variable => $value)
{
    if (in_array($variable, $cols)) {
        $$variable = $value;
    }
}
?>

[Psycho]

Well, if we're going to get into validation, there's a lot more than that wihch should be done. In fact that validation, although usefull, doesn't prevent values from beng added to the SQL. The SQL will only include the variables that are defined in the query. That extra step will prevent "other" variables from being defined that may be used in the script elsewhere.

 

But, to validate fully you should ensure that ALL values for the query are present and also run them through mysql_real_escape_string(). Some would also need to be validated that they are an appropriate value for the filed (e.g. number for numeric fields)

[jeff5656]

Ok my database table does not get updated with this code.  However, when I echo it out in teh foreach loop, the values are indeed there.  It's just that they don't end up being updated in the table.  I tried putting the $qquery both before and after the } but no change.

I'm sure it's something simple???

 

<?php

include "../connectdb.php";

$query = "UPDATE `staffsched` SET ";
$set = array();
foreach($_POST as $field => $value){
    $field = mysql_real_escape_string($field);
    $value = mysql_real_escape_string($value);
    $set[] = "`{$field}` = '{$value}'";

echo "field: " . $field . "<br>";
echo "value: " . $value . "<br>";
echo "set: " . $set . "<br>";

$query .= implode(", ",$set) or die ("Invalid query: " . mysql_error());;
}
?>

[Psycho]

$post_fields = array (
    'rm_loc', 'resident', 'patient', 'mrn', 'age', 'race', 'gender', 'pod', 'rcf_date',
    'dx', 'meds', 'pmhx', 'problist', 'anticipate', 'antic2', 'antic3', 'antic4',
    'todo', 'todo2', 'todo3', 'todo4', 'comments', 'code', 'allergy', 'signoff_status');

//Convert all the 'appropriate' post fields to variables
foreach ($post_fields as $field)
{
    $$field = mysql_real_escape_string(trim($_POST[$field]));
}

//Post processing of some POST vars and creation of others not from POST
$rm_loc = ereg_replace("[^A-Za-z0-9]", "", $rm_loc);
$rcf_date2 = $newdate;

//Create the query
$sql = "UPDATE icu
        SET rm_loc = '$rm_loc', patient = '$patient', mrn = '$mrn', age = '$age',
            icudays = '$icudays', race='$race', gender='$gender', pod='$pod', resident = '$resident',
            rcf_date='$rcf_date', dx='$dx', meds='$meds', pmhx='$pmhx', problist='$problist',
            problist_date= '$problist_date', anticipate='$anticipate', antic2='$antic2',
            antic3='$antic3', antic4='$antic4', anticipate_date = '$anticipate_date',
            antic2_date = '$antic2_date', antic3_date = '$antic3_date', antic4_date = '$antic4_date',
            comments='$comments', comments_date = '$comments_date', code='$code',
            allergy='$allergy', todo='$todo', todo2='$todo2', todo3='$todo3', todo4='$todo4',
            todo_date='$todo_date', todo2_date='$todo2_date', todo3_date='$todo3_date',
            todo4_date='$todo4_date', signoff_status='$signoff_status', rcf_date2='$rcf_date2'
        WHERE id_incr = '$id_incr'";

//Run the query
    //*** this check is not useful since $sql is explicitly set
    //if (isset($sql) && !empty($sql)) { 
    //***If any fields are required, that validation should occure when iterrating through the fields.
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());

[jeff5656]

To mjdamato: I do not want to do it that way because I have 279 vairables and with your method I would have to type each variable 3 times.  So that is alot of typing!  That's why I was hoping there was a foreach loop type solution.

[ratcateme]

i think you need a array of fields at the top because i could add any post field.

i have remade the code and included your where clause to

<?php
$post_fields = array('rm_loc', 'resident', 'patient', 'mrn', 'age', 'race', 'gender', 'pod', 'rcf_date', 'dx', 'meds', 'pmhx', 'problist', 'anticipate', 'antic2', 'antic3', 'antic4', 'todo', 'todo2',
'todo3', 'todo4', 'comments', 'code', 'allergy', 'signoff_status');

$query = "UPDATE `staffsched` SET ";
$set = array();
foreach ($_POST as $field => $value) {
$field = mysql_real_escape_string($field);
$value = mysql_real_escape_string($value);
if (in_array($field, $post_fields)) {
	$set[] = "`{$field}` = '{$value}'";

	echo "field: " . $field . "<br>";
	echo "value: " . $value . "<br>";
}
}

$query .= implode(", ", $set) . "WHERE id_incr = '{$id_incr}'";
mysql_query($query) or die("Invalid query: " . mysql_error());
?>

 

Scott.

 

[Psycho]

Sorry, I posted the wrong code

 

//List out the names for each field
$post_fields = array (
    'rm_loc', 'resident', 'patient', 'mrn', 'age', 'race', 'gender', 'pod', 'rcf_date',
    'dx', 'meds', 'pmhx', 'problist', 'anticipate', 'antic2', 'antic3', 'antic4',
    'todo', 'todo2', 'todo3', 'todo4', 'comments', 'code', 'allergy', 'signoff_status');

//Convert all the 'appropriate' post fields to query parts
foreach ($post_fields as $field)
{
    //Get value of current field from POST data
    $value = mysql_real_escape_string(trim($_POST[$field]));

    //If some fields include add'l processing/validation include it here
    switch ($field)
    {
        case 'rm_loc':
            $value = ereg_replace("[^A-Za-z0-9]", "", $value);
            break;
    }

    //Add partial query to array
    $set_parts[] = "{$field}='{$value}'";
}

//Don't know what the purpose is of this - should just use $newdate in the query
$rcf_date2 = $newdate;

//Create the query (include any SET's not from POST data explicity)
$sql = "UPDATE icu SET " . implode(', ', $set_parts) . ", rcf_date2='$rcf_date2' WHERE id_incr = '$id_incr'";

//Run the query
echo "<!--" . $sql . "-->";
mysql_query($sql) or die ("Invalid query: " . mysql_error());