insert all $_POST data into mysql using PHP?-Collection of common programming errors

2 years ago

admin

5 minutes

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: 


$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:


Place of Service




Date of Service




Where did you find this opportunity?




What organization sponsored this opportunity?




How many hours did you work?




How did it go?




Description of Service:



Comments:



Student Leader Name (If Applicable)




Student Leader Email(If Applicable)

Mysql Table:

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 🙂

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

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.

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

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

Originally posted 2013-11-09 23:31:23.