Dynamic create Php Variables for insert or update reading mysql table structure

General PHP Help
#21

Ok I have to learn how to do it.

For now I just fix the last comma error.

My Idea is learn how to do it after with staments,

<?php
	$i=0;
	foreach ($_POST as $k=>$v) {		
		++$i;		
	}
	$count=$i-1;
	$count_data=$i-2;
	echo "\$sql= INSERT INTO apolices(";	
	$z=0;	
	foreach ($_POST as $variable_name=>$variable_data) {		
		++$z;
	
		if($variable_name=="submit"){
			$variable_name="";
		}if($z<$count){
		echo($variable_name).',';
		}else{
			echo($variable_name);
		}
	}
		
		
	$x=0;
	echo ")" . "Values (";
   	foreach ($_POST as $variable_name=>$variable_data){		
		++$x;
			
		
	    if($variable_data==""){
		  $variable_data="NULL";
			
		}if($x<$count_data){
		echo($variable_data).',';
			
		}if($x==$count){
			echo($variable_data);
		}
		
	}echo ")";
	
?>
#22

If you use an array to hold the values making up a list, then use implode() to build the result, you won’t have a problem with trailing commas.

The following example code shows how you can safely build and execute dynamic INSERT/UPDATE PDO prepared queries, just by creating a defining array ($fields) in the code -

<?php

// dynamic Create/insert and Update CrUd examples

// define some 'helper' functions - these would typically be defined in an external .php file and 'required' when needed
// apply htmlentities to a value
function _ent($val){
	return htmlentities($val); // this uses the current/default character encoding setting
}

// return an element from an array - used to reference array elements that might not be set
function _element($arr,$index){
	return isset($arr[$index]) ? $arr[$index] : '';
}

// recursive function to trim data
function _trim($val){
	if(is_array($val)){
		return array_map('_trim',$val);
	} else {
		return trim($val);
	}
}

// use an array to define the field names and any validation (required, regex, call-backs) and query attributes (update - set/where, insert)
// the main array index 'field_nameN' values get replaced with your actual field/column names
// the label element is a meaningful text label/name for each field
// the rest should be self-explanatory after reading the code using the array values
$fields = []; 
$fields['field_name1'] = array('label'=>'Field 1','required'=>true,'insert'=>true,'update'=>'set');
$fields['field_name2'] = array('label'=>'Field 2','required'=>true,'insert'=>true,'update'=>'set');
$fields['id'] = array('label'=>'ID','required_update'=>true,'update'=>'where'); // the WHERE field value would be passed using a hidden form field
// add definitions for the remainder of the fields/columns ...


// create the db connection at the earliest point it is needed
require 'pdo_connection.php';

$table_name = 'your_table'; // set which db table to operate on

$errors = []; // define an array to hold application error messages
$post = []; // define an array to hold a working, trimmed, copy of the submitted data (also used to hold the initial data when editing existing data)

// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	// get a trimmed copy of the form data
	$post = array_map('_trim',$_POST);
	
	// perform any common validation
	// check required fields
	foreach($fields as $field=>$arr)
	{
		if(isset($arr['required']) && $arr['required'] && $post[$field] == '')
		{
			$errors[$field] = "{$arr['label']} is required.";
		}
	}

	// if no errors, use the submitted data
	if(empty($errors))
	{
		// determine which action to perform
		switch($post['action'])
		{
			case 'insert':
				// perform any specific validation - none for an insert
				
				$columns = []; // column names
				$params = []; // prepared query parameters
				foreach($fields as $field=>$arr)
				{
					if(isset($arr['insert']) && $arr['insert'])
					{
						$columns[] = "`$field`";
						$params[] = $post[$field];
					}
				}
				$sql = "INSERT INTO `$table_name` (" .implode(',',$columns). ") VALUE (" .implode(',',array_fill(0,count($columns),'?')). ")";
				echo $sql; print_r($params);
				$stmt = $pdo->prepare($sql);
				$stmt->execute($params);
			break;

			case 'update':
				// perform any specific validation
				foreach($fields as $field=>$arr)
				{
					if(isset($arr['required_update']) && $arr['required_update'] && $post[$field] == '')
					{
						$errors[$field] = "{$arr['label']} is required.";
					}
				}
				
				if(empty($errors))
				{
					$set_terms = []; // SET col=? terms
					$where_term = '';
					$params = []; // prepared query parameters
					foreach($fields as $field=>$arr)
					{
						if(isset($arr['update']) && $arr['update'] == 'set')
						{
							$set_terms[] = "`$field`=?";
							$params[] = $post[$field];
						}
						if(isset($arr['update']) && $arr['update'] == 'where')
						{
							$where_term = " WHERE `$field`=?";
							$params[] = $post[$field];
						}
					}
					$sql = "UPDATE `$table_name` SET " .implode(',',$set_terms). "$where_term";
					echo $sql; print_r($params);
					$stmt = $pdo->prepare($sql);
					$stmt->execute($params);
				}
			break;
		}
	}
}

// if editing existing data, you would retrieve that here, into the $post variable
if(empty($post))
{
	// query for and retrieve existing data to edit
}


//html document starts here...
?>


<?php
// display any errors
if(!empty($errors))
{
	echo implode('<br>',$errors);
}
?>
<h3>Insert example</h3>
<form method='post'>
<input type='text' name='field_name1' value='<?php echo _ent(_element($post,'field_name1'))?>'><br>
<input type='text' name='field_name2' value='<?php echo _ent(_element($post,'field_name2'))?>'><br>
<input type='hidden' name='action' value='insert'>
<input type='submit'>
</form>

<h3>Update example</h3>
<form method='post'>
<input type='text' name='field_name1' value='<?php echo _ent(_element($post,'field_name1'))?>'><br>
<input type='text' name='field_name2' value='<?php echo _ent(_element($post,'field_name2'))?>'><br>
<input type='hidden' name='id' value='123'> <!-- the record you are editing is usually specified via a get parameter. modify code as needed to use the actual value -->
<input type='hidden' name='action' value='update'>
<input type='submit'>
</form>
#23

No it doesn’t. It could, but, that doesn’t automatically make it a bad design.

And since this is a legacy system, exactly how much of the system will be rewritten? If the database scheme changes drastically, that translates to a whole lot of code that needs to change too. Which in business translates to needing a cost benefit analysis before you start doing anything.

#24

The Db not have exactly 25 fields for table.
I have tables with 8 fields and tables with 70 or more.

shematic;

Clientes have: Id, Name,Phone, fiscal number, etc…

Insurance Companys have: Id:, Number, Name, fiscal number , adrress, etc;

Area is like(health, insurance, car insurance, house) have: Company Id, Number, name of insurance area, etc.
For each area we have one form model and one specific table.
For example; Cars have the table model 1 is the CarTable
We have 15 different models. 15 different tables. one for houses, one for cars, one for work acidents, one for industry, etc.

Some areas like insurance health have other sub table, like for the name of each person in policy, you could have all family in one policy.

The policy table have the basic data of the policy: Id client, Id insurance, Id Area, Date of start, date of end, price comercial ammount, price total ammount, comission, etc, And the sub tables are the one i reffer upper.

Every table have when is created date, when is modified, who creates, who modified.
We also have table for receipts, with, client id, Client insurance company id, policy id, area id, price, commision, over comission, and lots more details.

We have other table with daily money in and out

We have other table for Acidents occured.
Other table for Payment we make to insurance company
Other table for payment company comission receipts

I not is is really bad design. I think is very extensive and could have lots of detailed data.

Policys. have : Client Id, Policy Number,

#25

Sounds like I would expect it to be designed honestly. The issue I see, and it is a big one, is migrating the code base over to something more current like PDO and switching dangerous queries to prepared statements and getting away from the theory that “cleaning” the value will do anything useful.

That would be a start…

#26

Rhandyx, in my humble opinion, you have a lot to do to get this system up to today’s more secure versions.
Also, I feel that the looping thru the $_POST array is still valid. You would need to put in further security in that routine to clean up the inputs to be safe from hackers. Also, @astonecipher mentioned PDO and prepared statements and that is the best way to improve security on the system. All of this info is correct and valid.

As far as the last code you posted, you run thru a loop to count values to get the total count of the array.
That is not needed at all. There is a function for that. The foreach loop to create a $count variable can be done just by using the count function. $count = count($_POST); will do it for you. You do not even need to assign it to a variable, just use count($_POST) instead wherever you would use the $count variable.

To add a first-level protection of the data, wherever you use the $variable_data, you would need to filter it for protection. Where you “echo($variable_data);” use “echo filter_input(INPUT_POST, $variable_data);” instead. This will protect the data from hackers putting invalid info in it. And, as @astonecipher mentioned, you would use PDO with prepared statements to save this data to your database. Please read the “PHP the right way” that he posted a link for you. It covers most of PDO and security.

#27

I know I have lots to learn and to read how to do it.
I still have lots of questions and i hope get more and more help and learn.

I to thank you all.

#28

We are available for whatever questions you have. There is a ton of knowledge between us in various professional settings.

#29

There´s lot of code to do and to learn.
let me take a breath and try it! :slight_smile:

#30 
```
<?php

// dynamic Create/insert and Update CrUd examples

// define some 'helper' functions - these would typically be defined in an external .php file and 'required' when needed
// apply htmlentities to a value
function _ent($val){
	return htmlentities($val); // this uses the current/default character encoding setting
}

// return an element from an array - used to reference array elements that might not be set
function _element($arr,$index){
	return isset($arr[$index]) ? $arr[$index] : '';
}

// recursive function to trim data
function _trim($val){
	if(is_array($val)){
		return array_map('_trim',$val);
	} else {
		return trim($val);
	}
}

// use an array to define the field names and any validation (required, regex, call-backs) and query attributes (update - set/where, insert)
// the main array index 'field_nameN' values get replaced with your actual field/column names
// the label element is a meaningful text label/name for each field
// the rest should be self-explanatory after reading the code using the array values
$fields = []; 
$fields['field_name1'] = array('label'=>'Field 1','required'=>true,'insert'=>true,'update'=>'set');
$fields['field_name2'] = array('label'=>'Field 2','required'=>true,'insert'=>true,'update'=>'set');
$fields['id'] = array('label'=>'ID','required_update'=>true,'update'=>'where'); // the WHERE field value would be passed using a hidden form field
// add definitions for the remainder of the fields/columns ...


// create the db connection at the earliest point it is needed
require 'pdo_connection.php';

$table_name = 'your_table'; // set which db table to operate on

$errors = []; // define an array to hold application error messages
$post = []; // define an array to hold a working, trimmed, copy of the submitted data (also used to hold the initial data when editing existing data)

// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	// get a trimmed copy of the form data
	$post = array_map('_trim',$_POST);
	
	// perform any common validation
	// check required fields
	foreach($fields as $field=>$arr)
	{
		if(isset($arr['required']) && $arr['required'] && $post[$field] == '')
		{
			$errors[$field] = "{$arr['label']} is required.";
		}
	}

	// if no errors, use the submitted data
	if(empty($errors))
	{
		// determine which action to perform
		switch($post['action'])
		{
			case 'insert':
				// perform any specific validation - none for an insert
				
				$columns = []; // column names
				$params = []; // prepared query parameters
				foreach($fields as $field=>$arr)
				{
					if(isset($arr['insert']) && $arr['insert'])
					{
						$columns[] = "`$field`";
						$params[] = $post[$field];
					}
				}
				$sql = "INSERT INTO `$table_name` (" .implode(',',$columns). ") VALUE (" .implode(',',array_fill(0,count($columns),'?')). ")";
				echo $sql; print_r($params);
				$stmt = $pdo->prepare($sql);
				$stmt->execute($params);
			break;

			case 'update':
				// perform any specific validation
				foreach($fields as $field=>$arr)
				{
					if(isset($arr['required_update']) && $arr['required_update'] && $post[$field] == '')
					{
						$errors[$field] = "{$arr['label']} is required.";
					}
				}
				
				if(empty($errors))
				{
					$set_terms = []; // SET col=? terms
					$where_term = '';
					$params = []; // prepared query parameters
					foreach($fields as $field=>$arr)
					{
						if(isset($arr['update']) && $arr['update'] == 'set')
						{
							$set_terms[] = "`$field`=?";
							$params[] = $post[$field];
						}
						if(isset($arr['update']) && $arr['update'] == 'where')
						{
							$where_term = " WHERE `$field`=?";
							$params[] = $post[$field];
						}
					}
					$sql = "UPDATE `$table_name` SET " .implode(',',$set_terms). "$where_term";
					echo $sql; print_r($params);
					$stmt = $pdo->prepare($sql);
					$stmt->execute($params);
				}
			break;
		}
	}
}

// if editing existing data, you would retrieve that here, into the $post variable
if(empty($post))
{
	// query for and retrieve existing data to edit
}


//html document starts here...
?>


<?php
// display any errors
if(!empty($errors))
{
	echo implode('<br>',$errors);
}
?>

i´m trying this code with my forms.
i have set the hidden input in form.

Undefined variable: pdo line 83

                                   $stmt = $pdo->prepare($sql);
					$stmt->execute($params);
#31

I took a look at the OP’s DB and it’s just as I suspected. It is a very bad DB structure with numerous duplication’s. Any code written against the DB as is will for the most part be a hack. The DB needs to be fixed first.

#32

I will try to translate it. for you can give some ideas to fix it.

#33

As @astonecipher so wisely pointed out, with as much modification of the DB that is required, it will also require re-writing all your code. This is why I always preach (when it comes up) to make sure your DB design is correct before you even think about writing a line of code. The DB is the foundation of the application. If the DB is wrong, all the code you write against it will be wrong.

If you want to get this correct and have the resources to do it then you will want to create a duplicate of your application and work on the copy so as to not interfere with your current use of the app. The first step will be to normalize your database and make sure you have the proper foreign keys in place.

By the way, where did this DB come from? Did you download it from somewhere or did someone write it for you?

#34

This is actually where stored procedures, wrappers, and interfaces come in handy. It is far less hard hitting when there is a massive change. Though, those also require a knowledgeable development and architecture team during initial development or it causes a lot more headaches later when a change needs to be made.

#35

@benanamen

My actual software managment is one software I buy lots years ago wroten in delphi with paradox tables. I use one software and convert all paradox tables to mysql tables.

I´m trying to wrote one webapp to work now with mysql.

#36

@phdr

I´m trying t use your code. but I have error undefined variable $pdo line 83.

Other thing, It only display in sql query the required fields i put.

// use an array to define the field names and any validation (required, regex, call-backs) and query attributes (update - set/where, insert)
// the main array index 'field_nameN' values get replaced with your actual field/column names
// the label element is a meaningful text label/name for each field
// the rest should be self-explanatory after reading the code using the array values
$fields = []; 
$fields['client'] = array('label'=>'Field 1','required'=>true,'insert'=>true,'update'=>'set');
$fields['insurance'] = array('label'=>'Field 2','required'=>true,'insert'=>true,'update'=>'set');
$fields['tree'] = array('label'=>'Field 3','required'=>true,'insert'=>true,'update'=>'set');
$fields['policy'] = array('label'=>'Field 4','required'=>true,'insert'=>true,'update'=>'set');
$fields['id'] = array('label'=>'ID','required_update'=>true,'update'=>'where'); // the WHERE field value would be passed using a hidden form field
// add definitions for the remainder of the fields/columns ...
#37

If would be helpful if you posted all the code to a repository such as GitHub.

#38

@benanamen

sorry, i not understand. the entire site code?

I´m just starting.

#39

rhandyx, using PDO, you create a connection to the database. You give it a variable name.
It is common to call it $pdo so you will know it is the PDO connection.

It appears you did not create the connection and that is causing you to get the error saying it does not exist.
Also, when you post a line number where an error exists, please tell us what that line says.
There are no line numbers in your posted code.

#40

Yes, the entire site code. Then we can review it as a whole. Otherwise this is likely to be a very long thread. You could also provide a zip file.

Sponsor our Newsletter | Privacy Policy | Terms of Service