Jump to content

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


luke3
Go to solution Solved by CroNiX,

Recommended Posts

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 by luke3
Link to comment
Share on other sites

  • Solution
$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 by CroNiX
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.