Best method of inserting form data

[Marked]

Hi guys,

This may seem like a strange question. I know there's several ways you can insert data from a form in a database. For example, I have this settings form right here: [img268.imageshack.us...]

I just this thing where I need the code to be efficient and done properly.. no shortcuts or hacks.

I was using a foreach loop to update each field, comparing the ID of the form element to an array, and if they match, update into the database. Like so:

<?php
class SettingsModel extends Model {
  public $fields = array(
    'avatar_location',
    'avatar_type',
    'facebook',
    'age',
    'twitter',
    'youtube',
    'website',
    'description',
    'name',
    'gender',
    'location',
  );
  public $integer_fields = array(
    'mainmenu_noicons'
  );
  public function doUpdate($args, $mid) {
    $data = array();
    //this takes $_POST variables and compares them with the
    // gdu_membersettings fields, compiling them into an array
    foreach ($args as $key => $value) {
      $field = str_replace('field_', '', $key);
      if (in_array($field, $this->fields)) {
        $data[$field] = $value;
      } elseif (in_array($field, $this->integer_fields)) {
        $value = (int) $value;
        $data[$field] = $value;
      }
    }
    //Update member settings
    $query = new Query("UPDATE");
    $query->table("gdu_membersettings");
    foreach ($data as $key => $value) {
      $query->set($key . " = ?", $value);
    }
    $query->where("settings_mid = ?", $mid);
    $query->limit(1);
    $stmt = $query->prepare();
    $stmt->execute();
  }
}
?>

Is an efficient way to do it? Perhaps a security risk by giving away the column names in the HTML.

I guess there's so many ways to do it, and I want to know the best way, if there is one.

Thanks in advance,
Mark.

[rocknbil]

Yes it is; I would use some sort of internal mapping of the publicly displayed fields and associate them with field names.

// forms left, db field names right

$flds = array (
'fname' => 'first_name',
'lname => 'last_name'
// etc
);

It may very well be that you can use this array to apply to multiple tables, that is, you may have a shipping table and a customer table both containing fields first_name, last_name.

The second thing I'd do is pass the list of the table names as one of your parameters of whatever functions you use them in. Then you don't have to modify your class every time you want to change the table structure.

[Marked]

I did what you said :) I'm using methods from a php5.3 framework called Hydrogen. It has excellent methods for sql management.

How's this looking? You can use multiple tables and everythig, you just add them to the $fields array and it will search for the form elements name, which returns the table name, column name, and the type of column

<?php

namespace gdu\models;

use hydrogen\model\Model;
use hydrogen\database\Query;

require_once(ROOT_PATH . "/lib/gdu/sqlbeans/PfieldsBean.php");

use gdu\sqlbeans\PfieldsBean;

class SettingsModel extends Model {

  protected static $modelID = "settingsmodel";

  /**
   *
   * @var array This array contains table names and their corresponing
   * column names, which are matched based the name of a form elememnt 
   * EG gfb is the form field where the user enters their facebook ID
   */
  public $fields = array('ibf_pfields_content' => array(
      'grn' => array('gdu_real_name', 0),
      'gfb' => array('gdu_facebook', 1),
      'gtw' => array('gdu_twitter', 0),
      'gyt' => array('gdu_youtube', 0),
      'gwb' => array('gdu_website', 0)
      ));

  /**
   * Returns an SQLBean: ibf_pfields_content
   * 
   * @param int $member_id
   * @return object 
   */
  public function getPfields($member_id) {
    $query = new Query("SELECT");
    $query->where("member_id = ?", $member_id);
    $pfields = PfieldsBean::select($query, true);
    //if member_id not found 
    if (count($pfields) == 0) {
      return false;
    } else {
      $pfields = $pfields[0];
      return $pfields;
    }
  }

  /**
   * This function will use the form elements name to see if there is a
   * match in the $fields array. A match will return a table and a column
   * name. It will then update those columns using the correct SQLBean by
   * calling the doUpdate() function.
   * 
   * @param array $args this is usually $_POST
   * @param object SQLBean for ibf_members 
   * @param object SQLBean for ibf_profile_portal
   * @param object SQLBean for ibf_pfields_content
   */
  public function prepareUpdate($args, $ibf_member, $ibf_profileportal, $ibf_pfields) {
    $args_ibf_member = array();
    $args_ibf_profileportal = array();
    $args_ibf_pfields = array();
    $count = 0;
    //start the sorting
    foreach ($_POST as $key => $p) {
      //catch all data for ibf_pfields
      if (array_key_exists($key, $this->fields['ibf_pfields_content'])) {
        $args_ibf_pfields[$count]['column'] = $this->fields['ibf_pfields_content'][$key][0];
        $args_ibf_pfields[$count]['value'] = $p;
        $args_ibf_pfields[$count]['int'] = $this->fields['ibf_pfields_content'][$key][1];
      }
      $count++;
    }
    //run the function to update if matches were found
    if (count($args_ibf_pfields) > 0) {
      $this->doUpdate($ibf_pfields, $args_ibf_pfields);
    }
  }

  private function doUpdate($bean, $args) {
    foreach ($args as $arg) {
      if ($arg['int'] == 0) {
        $bean->$arg['column'] = $arg['value'];
      } else {
        //if int column, add the int thing
        $bean->$arg['column'] = (int) $arg['value'];
      }
    }
    $bean->update();
  }

}

//end class
?>