Large Forms = Large PHP/mySql Query Strings... Is There a Good Solution?

I've got a CMS I'm building where I've got a rather large form full of data to add to my database. This is where I collect my variables....

$orgName = $_POST['orgName'];
$impact = $_POST['impact'];
$headline = $_POST['headline'];
$content = $_POST['content'];
$subContent = $_POST['subContent'];
$meterText = $_POST['meterText'];
$month = $_POST['month'];
$shopLink = $_POST['shopLink'];
$blurbTitle = $_POST['blurbTitle'];
$blurb = $_POST['blurb'];
$logoURL = $_POST['logoURL'];
$buttonURL = $_POST['buttonURL'];
$blurbURL = $_POST['blurbURL'];
$POMURL = $_POST['POMURL'];
$horizontalURL = $_POST['horizontalURL'];
$statURL = $_POST['statURL'];
$stats = $_POST['stats'];

require_once 'DB_Connect.php';

$connection = new DB_Connect();    

$connection->insertPartner(
    $index,
    mysql_real_escape_string($orgName),
    mysql_real_escape_string($impact),
    mysql_real_escape_string($headline),
    mysql_real_escape_string($content),
    mysql_real_escape_string($subContent),
    $month,
    mysql_real_escape_string($shopLink),
    mysql_real_escape_string($blurbTitle),
    mysql_real_escape_string($meterText),
    mysql_real_escape_string($blurb),
    mysql_real_escape_string($stats),
    mysql_real_escape_string($logoURL),
    mysql_real_escape_string($buttonURL),
    mysql_real_escape_string($blurbURL),
    mysql_real_escape_string($POMURL),
    mysql_real_escape_string($horizontalURL),
    mysql_real_escape_string($statURL)
    ))

and finally the function...

public function insertPartner(
    $orgName = '',
    $impact = '',
    $headline = '',
    $content = '',
    $subContent = '',
    $month = '',
    $shopLink = '',
    $blurbTitle = '',
    $blurb = '',
    $stats = '',
    $logoURL = '',
    $buttonURL = '',
    $blurbURL = '',
    $POMURL = '',
    $horizontalURL = '',
    $statURL = '')
    {
        $query="INSERT INTO `hupcap_FCE`.`fce_partners` (
        `index`,
        `organization_name`,
        `impact`,
        `headline`,
        `content`,
        `sub_content`,
        `blurb_title`,
        `blurb`,
        `stats`,
        `month`,
        `meter_number`,
        `meter_text`,
        `shop_link`,
        `button_img_url`,
        `blurb_img_url`,
        `logo_url`,
        `month_img_url`,
        `horizontal_logo_url`,
        `stat_img_url`,
        `util`
        ) VALUES (
        '',
        '$orgName',
        '$impact',
        '$headline',
        '$content',
        '$subContent',
        '$blurbTitle',
        '$blurb',
        '$stats',
        '$month',
        0,
        '',
        '$shopLink',
        '$buttonURL',
        '$blurbURL',
        '$logoURL',
        '$POMURL',
        '$horizontalURL',
 开发者_开发知识库       '$statURL',
        0)";
        if(mysql_query($query)){
            return true;
        }else{
            die("failed to insert record" . mysql_error());
        }
    }

There has GOT to be a slicker way of doing this. Who's got the best method?

Thanks -J

Option #1

Use an ORM like Doctrine to handle CRUD in your PHP apps.

Option #2

If using an ORM is too big of a paradigm shift try something like this:

// Alias $_POST fields to SQL columns
$sql_columns= array(
    'post_field1'=> 'sql_column1',
    'post_field2'=> 'sql_column2',
    'post_field3'=> 'sql_column3');

// Encode $_POST data for use in SQL
$sql_a= array();
foreach ($sql_columns as $k=> $k2) {
 if (isset($_POST[$k])) {
  $sql_a[]= sprintf("`%s` = '%s'", $k2, mysql_real_escape_string($_POST[$k]));
 }
}

// Build SQL string to execute
$sql= sprintf('INSERT INTO table_name SET %s', implode(', ', $sql_a));
var_dump($sql);

This can easily be extended into a function or a class to handle different tables, columns and SQL statements.

do a foreach to run all over the params array, so you can check the value. Do some magic inside the final function so you can check if any of them is empty or something...

If you have 16 columns in your table, you're going to have a long insert statement.

You should use one of the database wrapper classes (like PDO). Firstly, it gives you a convenient way use prepared statements (avoiding SQL injection, and adding type checking). Secondly, it makes adding parameters more readable, since you don't have to concatenate one huge string.

function insert_stuff($col1, $col2, $col3) {
    $conn = new PDO($connectionString);
    $query = "insert into my_table (col1, col2, col3) values (:col1, :col2, :col3)";
    $statement = $conn->prepare($query);

    $statement->bindValue(":col1", $col1);
    $statement->bindValue(":col2", $col2);
    $statement->bindValue(":col3", $col3);

    $statement->execute();
    // etc. 
}

If you're really bothered by all the typing, you can use your database to generate some of the code for you:

select 
    concat('$statement->bindValue(":', column_name, '", $', column_name, ');' 
from
    information_schema.columns
where
    table_schema = 'my_database_name'
and table_name = 'my_table_name';

Something like this would work:

$insertArray() = array();
foreach ($_POST as $key=> $name)
{
    $insertArray[$name] = mysql_real_escape_string($_POST[$name]);
}
$query = "INSERT INTO `hupcap_FCE`.`fce_partners` (" . implode(',', array_keys($insertArray)) VALUES '" . implode("','", $insertArray) . "'";

//...

THIS IS NOT SECURE BUT IT WOULD WORK :)

Yes it seems to be how it should be for the most part, however, you can save your life to a great extent by doing this:

Instead of writing:

$orgName = $_POST['orgName'];
$impact = $_POST['impact'];
$headline = $_POST['headline'];
$content = $_POST['content'];
$subContent = $_POST['subContent'];
$meterText = $_POST['meterText'];
$month = $_POST['month'];
$shopLink = $_POST['shopLink'];
$blurbTitle = $_POST['blurbTitle'];
$blurb = $_POST['blurb'];
$logoURL = $_POST['logoURL'];
$buttonURL = $_POST['buttonURL'];
$blurbURL = $_POST['blurbURL'];
$POMURL = $_POST['POMURL'];
$horizontalURL = $_POST['horizontalURL'];
$statURL = $_POST['statURL'];
$stats = $_POST['stats'];

You could simply write this line:

extract($_POST, EXTR_SKIP);

And now you have all the same variables available like what you did with so many lines above, for example, now you can use them or echo them:

echo $orgName;
echo $impact;
echo $headline;

To Add: I am not sure whether using extract is good practice in terms of security, however, i have been using this without any problems so far :)