Trying to program my way out of repetitive form POST capture/MySQL insert!

[luke3]

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.

Edited January 8, 2015 by luke3

[hansford]

 

 

perfect I thought, now I just need to bind the values from $_POST.

 

Don't know what you mean .. put them in there.

Pass the db connection or array returned from the db call - whatever - let the function do the work.

Edited January 8, 2015 by hansford

[CroNiX]

$columns = array();
$fields = array();
foreach($this->getColumnNames("table") as $columnName)  //cycle through the valid columns
{
  //see if there was a posted field with this key
  if (array_key_exists($columnName, $_POST)) 
  {
    $columns[] = $columnName; //store the column name
    $fields[] = $_POST[$columnName]; //store the field value from POST
  }
}

Now you can use your implode on $columns and $fields to build $columnList and $paramList

Edited January 8, 2015 by CroNiX

[Psycho]

I think your process is flawed. I too have tried to build methods to do such things automatically for any scenario. It seems like you are making things easier, but ultimately you end up painting yourself into a corner. You then end up adding more complexity to account for functionality that you need for one instance that you don't need for others. In the end, you're left with a huge mass of code that becomes difficult to maintain and can be buggy. Plus, how would you handle situations when you need to multiple records with associations? E.g. creating a client with multiple phone numbers? You should store the basic client data in one table (get the ID of that record) and then store the phone numbers in the associative table.

 

For something such as forms which, typically, are used for different entities I think it is always best to build the logic for each one to be specific to the needs for each entity. That doesn't mean you can't build common functionality to handle certain pieces of the functionality, which is re-purposed in each form handling logic. You absolutely should do that. For example, you might build different validation functions for certain types of data (e.g. Phone, email, etc.).

 

For example, your function assumes that the first field in the tables will always be the ID and that the last three will always be default fields. Plus, you need to match up the POST values to the DB fields. You could ensure the form fields are in the same order as the DB fields, which is a huge pain. Just changing a form to put things in a different order would require you to make an alter in the DB. Or, you could name the input fields exactly the same as the DB fields. It is a bad practice to purposefully expose any information about your DB schema.

[luke3]

Thanks for input, all appreciated.