Hi Folks,
Firstly I am new, I have read several topics here and learned a lot, I would class myself as 'slightly better than basic', but my knowledge is mostly gained from reading code.
I am making a simple POST form for work, the data gets inserted into MySQL, nice and easy, I can make it work if I write out the statement completely, BUT I need to make a new form, it will have HUNDREDS of input fields, I really don't want to write the code, and I figured programmatically is a good way to go anyway as forms change and new forms may be required, so I set about building a function to completely handle my post data, bind it to a statement and insert it into a table, I have scrapped it a half dozen times already because something fundamentally doesn't work, but I am very close! The function can write the statement, but I need to bind the POST values before I can insert, something going wrong here and I would appreciate some help, I have a feeling it's a problem with an array, but anyway I will show you what I have, give you some comments as to my reasoning, and hopefully you can help me with the last bit
public function getColumnNames($table){
$sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table";
try {
$stmt = $this->dbh->prepare($sql);
$stmt->bindValue(':table', $table, PDO::PARAM_STR);
$stmt->execute();
$output = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$output[] = $row['COLUMN_NAME'];
}
$all = array($output);
$a1 = array_slice($output, 1); // I don't want column 1, it contains the ID, its auto incremented.
$a2 = array_slice($a1, 0, -3); // I don't want the last 3 columns as they have default values
$selected = array($a2); // contains all the columns except those excluded by array_slice, columns now match all of the input fields on the form
foreach ($selected as $row){
$fields = "`" . implode('`, `', $row) . "`"; // I'm making `fields` here,
$bind = ":" . implode(', :', $row); // And making :values here
}
return array (
"raw" => $all,
"fields" => $fields,
"bind" => $bind
);
}
catch(PDOException $pe) {
trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
}
}
public function addRecord(){
$col = array();
$col = $this->getColumnNames("table");
$raw = array($col['raw']);
$fields = array($col['fields']);
$bind = array($col['bind']);
$columnList = implode('`, `', $fields);
$paramList = implode(', ', $bind);
$sql = "INSERT INTO `{$this->dbtable}` ($columnList) VALUES ($paramList)";
return $sql; // this returns something like:
INSERT INTO `table` (`field1`, `field2`, `field3`) VALUES (:field1, :field2, :field3)";
perfect I thought, now I just need to bind the values from $_POST... then I get stuck.