insert all $_POST data into mysql using PHP?

Question

once again I have a question for the STACKOVERFLOW hivemind. Here's the deal, I'm trying to insert all of my $_POST data from a form into a mysql table. Before, I did:

    INSERT INTO forms (Place, Date, Find, username, who_sponsored, hours, comments, howdiditgo, studentleader_name, studentleader_email, description)
VALUES ('$place', '$date','$where', '$username', '$who', '$hours', '$comments', '$how', '$description',)");

where all the $values were declared as $_POST['Place'], $_POST['Date'], etc. Now, every time I add a new part to the form (like another textarea or something), I want to just have to create a new column in mysql instead of adding another $_POST['foo']. Here's what I have tried doing:

// In the previous form, I have set all input ids to "service[]", so all this data would be in a more specific array than just $POST.  Turns out all the $_POST['service'] stuff is null...  Here's an example: <input name="placeofservice" type="text" id="service[]">


$forms = serialize($_POST['service']);
var_dump($forms);

mysql_query("INSERT INTO forms VALUES('$forms')")
 or die(mysql_error()); 

The error I keep receiving is: Column count doesn't match value count at row 1. I realize this means that I am trying to put too much data into the database, because there are not enough columns to fit the data. I've checked back and forth to see if I have it right (which, I think I do). For Reference, here's my code for both the form and mysql table:

<form name="form1" method="post" action="process_form.php">
Place of Service</br>
<input name="placeofservice" type="text" id="service[]"></br>

Date of Service</br>
<input name="dateofservice" type="text" id="service[]"></br>

Where did you find this opportunity?</br>
<input name="where" type="text" id="service[]"></br>

What organization sponsored this opportunity?</br>
<input name="who_sponsored" type="text" id="service[]"></br>

How many hours did you work?</br>
<input name="hours" type="text" id="service[]"></br>

How did it go?</br>
<input type="text" id="service[]" name="howdiditgo" maxlength="100" /></br>

Description of Service:
<textarea name="description" id="service[]" COLS=40 ROWS=6></textarea></br>

Comments:
<textarea name="comments" id="service[]" COLS=40 ROWS=6></textarea></br>

Student Leader Name (If Applicable)</br>
<input name="studentleader_name" type="text" id="service[]"></br>

Student Leader Email(If Applicable)</br>
<input name="studentleader_email" type="text" id="service[]"></br>
<input type="submit" name="Submit" value="Submit">
</form>

Mysql Table:

Place | Date | Find |form_id | who_sponsored | hours | comments | howdiditgo | description | studentleader_name | studentleader_email | username

NOTE: I plan to sanitize my DB contents/$POST data as well, but for my purposes I left it out! If you have any questions feel free to ask and I'll post here with EDIT: tags :)

Answer 1

My function for this:

function i($table, $array) {
  $query = "INSERT INTO ".$table;
  $fis = array(); 
  $vas = array();
  foreach($array as $field=>$val) {
    $fis[] = "`$field`"; //you must verify keys of array outside of function;
                         //unknown keys will cause mysql errors;
                         //there is also sql injection risc;
    $vas[] = "'".mysql_real_escape_string($val)."'";
  }
  $query .= " (".implode(", ", $fis).") VALUES (".implode(", ", $vas).")";
  if (mysql_query($query))
    return mysql_insert_id();
  else return false;
}

Answer 2

Don't create a single field for all you data... it negates the entire value in having a database. You lose all the flexibility to search on specific fields (e.g. all records where hours worked is more than 25, or where date of service was 26th July 2010) You could easily write a function that built the insert statement from an array of values similar to the one Riateche has provided.

It could be improved by switching to mysqli and using bind variables.

Answer 3

Here's mine:

function incomingdump($array){
    $tablename="incomingdump";

    $currentID = generateID($tablename);

    $query = "INSERT INTO $tablename (ID) VALUES('$currentID');";  
    sendquery($query);
      foreach($array AS $key => $value){

        $query = "ALTER TABLE $tablename ADD `$key` VARCHAR(".strlen($value).");";  
        sendquery($query);
        $query = "ALTER TABLE $tablename MODIFY `$key`VARCHAR(".strlen($value).");";  
        sendquery($query);
        $query = "UPDATE $tablename SET `$key` = '$value' WHERE ID=$currentID";
        sendquery($query);
      }


}
function generateID($tablename){
    $query = "SELECT count(*) FROM $tablename"; 
    $result = sendquery($query);
    $row = mysql_fetch_row($result);
    return $row[0] + 1;

}

sendquery() is just a wrapper for executing a sql statement. Its called like so:

incomingdump($_POST);

Answer 4

Use json_encode($_POST)for saving data into JSON to MySql database

Answer 5

Sorry pal, but you have almost everything wrong.

1. I plan to sanitize my DB contents/$POST data - WRONG
   There is not a thing called sanitization. There is only syntax rules which you shouldn't "plan to use" but obey unconditionally. Or you'll end up with query error much sooner than with scaring SQL injection. And in terms of database, no DB contents nor POST data shouldn't be treated in any special way. It's DB query, not contents you prepare. And not only POST data but any data going to the query. The big difference.
   Also note that mysql_real_escape_string function itself do not "sanitize" anything.
   Comprehensive information on query building rules you can find in this my answer

2. every time I add a new part to the form - wrong.
   Adding new field to the database shouldn't be such a trivial task, but always exceptional issue. Database should be planned before you started to draw any forms. And, of course, not a mechanism should be invented to automate such a task. Always manually.

3. $forms = serialize($_POST['service']); - TERRIBLE WRONG idea.
   I wonder how such an idea can ever come. Mysql of course has nothing to do with PHP proprietary serializing format. And, even if it was, - how the database can tell there is ordinal data for single field or serialized row? Weird.

The only sensible point in your question is how to make query building ease.
Here is the function I am using:

function dbSet($fields, $data = array()) {
  if (!$data) $data = &$_POST;
  $set='';
  foreach ($fields as $field) {
    if (isset($data[$field])) {
      $set.="`$field`='".mysql_real_escape_string($data[$field])."', ";
    }
  }
  return substr($set, 0, -2); 
}

this will return you a SET statement, restricted to the previously defined set of fields.
Usage

$fields = explode(" ","name surname lastname address zip fax phone");
$query  = "INSERT INTO $table SET ".dbSet($fields);
$result = mysql_query($query) or trigger_error(mysql_error().$query);

or

$id     = intval($_POST['id']);
$fields = explode(" ","name surname lastname address zip fax phone");
$query  = "UPDATE $table SET ".dbSet($fields)." WHERE id=$id";
$result = mysql_query($query) or trigger_error(mysql_error().$query);

So, in your case you have to add just a single word to the field list