Large forms = large query strings PHP / mySql ... Is there a good solution?

Question

I have a CMS, I am building where I have 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']; 

here I am sql escape, check and send to my function (missing space check) ...

 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) )) 

code>

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()); } } 

We have got to do this. Who got the best method?

Thanks -J

Answer 1

Option number 1

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

Option number 2

If using ORM is too big to switch the paradigm, 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 to a function or class to handle different tables, columns, and SQL statements.

Answer 2

make foreach run across the entire params array so you can check the value. Do some magic inside the final function so you can check if any of them or something like that are there.

Answer 3

If you have 16 columns in your table, you will have a long insert.

You must use one of the database shell classes (e.g. PDO). Firstly, it gives you a convenient way to use prepared statements (avoiding SQL injection and adding type checking). Secondly, this makes adding parameters more readable, since you do not need to concatenate one huge line.

 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 are really worried about typing, you can use your database to generate code:

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

Answer 4

Something like this will 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 SAFE, BUT IT WAS TO WORK :)

Answer 5

Yes, it seems like it should be for the most part, but you can pretty much save your life 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 can simply write this line:

 extract($_POST, EXTR_SKIP); 

And now you have all the same variables as you, with so many lines, for example, now you can use them or echo them:

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

Add: I'm not sure that using extract is a good security practice, however I used it without any problems :)