Storing & Using Long Form Values : Best Practice?

[asprin]

Hello everyone. First, a little about me. I started off web development in .Net (C#) and found it to be not suiting my style. Then I moved on to PHP and instantly fell in love with it. I've been practicing it for the last 40-45 days and have covered up most of the topics with ease. I'm also past the beginner mode in AJAX and Jquery, which are awesome by the way. As a result, I'm in a position where I would not need to be spoon fed the solutions (I hope) xD

Back to topic, I'm currently working on a project which contains 3 forms - all huge forms in terms of number of fields. On a average, each form contains around 18-20 fields. Now the problem I'm facing is to decide how to hold and use the field values of the form with minimum effort. In other words, I'm looking for the best practices to follow while dealing with long forms. I can go the old fashioned way of storing each value in a variable and then put those values in the database with an equally long Mysql query.

So far, the closest I've come is to use the foreach loop. With this, I'm able to grab the field values in one line of code :

foreach($_POST as $key=>$value)
{
$$key = mysql_real_escape_string($value)
}

Now I'm wondering if there is any similar approach to store the values in the database with writing a small query instead of typing:

mysql_query("INSERT INTO tablename (col1, col2, col3, col4...., col18) VALUES ('$val1','$val2','$val3......','$val18')");

Now that is a huge query for a form with 18 fields. I'm assuming using OPPS approach in PHP will ease that for me, but I'm not that far yet. I'm yet to start learning the OPPS concept in PHP.

In a nutshell, I've accomplished holding the values of a form with a single line of code using foreach loop, but is there a similar way to make it store in the database without having to type in all the column names of the table as well as the variable names?

Thanks and Regards,
Nisar.

[deceptikon]

You can build the query string dynamically. It all depends on how the POST data is organized, but assuming everything in $_POST corresponds directly to table column names and values, here's an example:

$columns = array();

foreach ($_POST as $key => $value)
{
    $columns[$key] = "'" . mysql_real_escape_string($value) . "'";
}

mysql_query('INSERT INTO tablename (' . implode(',', array_keys($columns)) . ') VALUES (' . implode(',', $columns) . ')');

[asprin]

Thanks deceptikon. Your suggestion should be what I'm looking for. I'll give it a try and let you know. Thanks for introducing me to a new function - array_keys()

[asprin]

Alright, I tested this code and it works perfectly except there is one drawback. As we are looping through each $_POST, it is also storing a value for the submit button. As a result, my table is forced to have a column named 'submit'.

Any workaround this? I tried using unset() for the submit button, but that did not work.

Thanks in advance.

EDIT : Nevermind, I figured it out. For those who have a similar problem, I put unset($_POST['submit']) before the foreach loop instead of inside it.

$columns = array();
    unset($_POST['submit']); // excluding the value for the submit button
    foreach ($_POST as $key => $value)
    {
    $columns[$key] = "'" . mysql_real_escape_string($value) . "'";
    }
     
    mysql_query('INSERT INTO tablename (' . implode(',', array_keys($columns)) . ') VALUES (' . implode(',', $columns) . ')');

[pzuurveen]

simply test for it

if (!( $key=='submit')){
 $columns[$key] = "'" . mysql_real_escape_string($value) . "'";
}

[deceptikon]

Any workaround this?

Exclude any fields that don't correspond to a database column. If it were me, I'd store the columns in an array or object and just iterate over them and store anything in $_POST that matches:

$columns = array('column1', 'column2', 'column3', 'column4');
$fields = array();

foreach ($_POST as $key => $value)
{
    // Only use fields that are known to the database
    if (isset($columns[$key]))
    {
        $fields[$key] = "'" . mysql_real_escape_string($value) . "'";
    }
}

mysql_query('INSERT INTO tablename (' . implode(',', array_keys($fields)) . ') VALUES (' . implode(',', $fields) . ')');

[pritaeas]

Another option would be to do it the other way around. Use SHOW COLUMNS on the table you are using, and check whether your POST variable actually corresponds to an existing table column.

[diafol]

Using mysql_real_escape_string() as a magic bullet will not avoid errors. I suggest a robust validation routine FOR EACH ONE of your fields. If, say field3 expects an integer, but you try passing a mysql_real_escape_string-cleaned string to it - it's still gonna fail or insert bogus/unexpected data.

The examples given will work if you can trust the data, but by now we know that we can't. Simply cleaning strings and forcing them into a query will not ensure valid data.

The quick'n'dirty method may seem easy, but it's fraught with dangers. An array holding validation rules for each input variable - now that would be useful!

You can use a variety of validation tools to check input. Or build up your own functions/rules, e.g. below:

$rules = array(
   'id'  =>  array('type' => 'int', 'min' => 1, 'max' => 99999, 'required' => true),
   'fname'  =>  array('type' => 'string', 'min' => 2, 'max' => 20, 'required' => true),
...  
);

[asprin]

Using mysql_real_escape_string() as a magic bullet will not avoid errors. I suggest a robust validation routine FOR EACH ONE of your fields. If, say field3 expects an integer, but you try passing a mysql_real_escape_string-cleaned string to it - it's still gonna fail or insert bogus/unexpected data.

The examples given will work if you can trust the data, but by now we know that we can't. Simply cleaning strings and forcing them into a query will not ensure valid data.

You can use a variety of validation tools to check input.

I agree. I used mysql_real_escape_string() to prevent injection. I hope it is not an outdated practice. Of course, I'm adding validations on the input fields before they are submitted.

[diafol]

Sure, and mres is great at that. It's still 'in-practice' AFAIK, but consider using a more robust method for DB manipulation like PDO (if available to you) or MySQLi. I like PDO as you substitute placeholders in statements - some claim that it's safer, wrt injection.

[FreeGeezer]

My problem is very similar to this thread. The difference is that have to iterate thru a mysql_fetch_array() of data obtained from a combination of tables based on a user constructed search to display the data obtained. While reporting that data, I find myself needing to select data from another table each time thru the the upper mysql_fetch_array(). All that is not a problem as I have it working now. It is, however, necessary background to my question.

I need to store the primary key of each of the records retrieved in the lower mysql_fetch_array() in an array() in order to use it in a letter report.

Here's how it looks:

// The construction of $msg begins much earlier in a non-relavent portion to this process.
$svsql = "SELECT voper, vid, vtype, vname, vmaxpass, vmintime, vrate, vphoto, stype FROM Vehicles, Services WHERE Services.stype = '$service' AND Services.soper = Vehicles.voper AND Vehicles.vtype = '$vehicle'";
$result = mysql_query($svsql);
if (!$result) { exit ("No results for Operators offering BOTH Service: ".$service." AND Vehicle: ".$vehicle.""); }
$num = mysql_num_rows($result);
while ($vrow = mysql_fetch_array($result)) {
   $soper = $vrow[voper];
   $stype = $vrow[stype];
   $vtype = $vrow[vtype];
   $sdesc = $vrow[vname]; 
   $srate = $vrow[vrate];
   $smaxpass = $vrow[vmaxpass];
   $vmintime = $vrow[vmintime]; 
   $opsql = "SELECT `ocompany`, `oaddr1`, `oaddr2`, `ocity`, `ostate`, `ozip`, `ophone`, `oprefcontactemail`, `ocowebsite` FROM `Operators` WHERE `oid` = '$soper'";
   $oper = @mysql_query($opsql);
   if (!$oper) { exit ("No results for Operator: ".$soper.""); }
   $oprow = mysql_fetch_array($oper);
   $opcompany = $oprow[ocompany];
   $opphone   = $oprow[ophone];
   $opemail   = $oprow[oprefcontactemail];
   $opwebsite = $oprow[ocowebsite];
   $msg .= '<p><b>'.$opcompany.'</b>';
   $msg .= $opphone.'   '.$opemail.'';
   $msg .= '.$opwebsite.'';
   $msg .= TRIM($sdesc).'   (Max. '.$smaxpass.' passengers)';
   if ($vmintime > 0) { $msg .= '&nbsp;&nbsp;&nbsp;(Minimum '.$vmintime.' hours)'; }
   $msg .= '';
   // Compute Service Fees & Gratuity
   // Place it in the print line
   $msg .= $nhrs.' Hours '.TRIM($vcdesc).' service ($'.$srate.' per hour) $'.$tfee.' plus 20% gratuity ($'.$ttip.').</p>';
   } 
  
$msg .= "<hr width='100%' />";
/* Retrieve Advertisements in order of the oldest active ad served.  Limited to the 5 oldest ads. */
$adsql = "SELECT * FROM Advertisments LEFT JOIN  Advertisers ON Advertisments.`atiser` = Advertisers.`Atiser` ORDER BY `aimpressed` DESC LIMIT 3";
$ads = mysql_query($adsql);
if (!ads) { $msg .= "<p>No advertisements found.</p>"; } else {
$msg .= "<p>For additional ".TRIM($svcdesc)." needs, we suggest these wonderful merchants.</p>";
$msg .= "<TABLE BORDER='0'>";
while ($adrow = mysql_fetch_array($ads)) {
   $atiser     = $adrow[atiser];
   $aid        = $adrow[aid];
   $aimpressed = $adrow[aimpressed];
   $aimpress   = $adrow[aimpress];
   $aimage     = $adrow[aimage];
   $aname      = $adrow[Aname];
   $aaddr      = $adrow[Aaddr];
   $acity      = $adrow[Acity];
   $astate     = $adrow[Astate];
   $azip       = $adrow[Azip];
   $aphone     = $adrow[Aphone];
   $aemail     = $adrow[Aemail];
   $awebsite   = $adrow[Awebsite];
// This is where I would like to save $atiser.$aid into an array() so I will know, in a later script (same session) which advertisers were served.
   if (!$aemail) { $aemail = "[e-mail]"; }
   $msg .= "<tr><td align='top'><strong>".$aname."</strong>";
   $msg .= $aaddr."";   
   $msg .= $acity." ".$astate." ".$azip.""; 
   $msg .= $aphone."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
   $msg .= "<a href='http://".$awebsite."'>".$awebsite."</a>"; 
   $msg .= $aemail."</td>";
   $msg .= "<td align='top'><IMG SRC='http://domain.com/uploads/".$aimage."'></td></tr>";
   }