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

General PHP Help
#41

I have to first rebuild all querys i make in mysqli and try to make it in pdo.

Please not be surprise with very beginner code.

#42

Ok I fix connection. Just one more question

can i use the script made by @phdr

this way

// 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['field1'] = array('label'=>'field1','required'=>true,'insert'=>true,'update'=>'set');
$fields['field2'] = array('label'=>'seguradora','required'=>true,'insert'=>true,'update'=>'set');

/////////////////// can i use it this way or is insecure????
foreach ($_POST as $variable_name=>$variable_data) {
	if($variable_data!=""&&$variable_name!='action'&&$variable_name!='submit'&&$variable_name!='field1'&& $variable_name!='field2'){
		
	$fields[$variable_name] = array('label'=>$variable_name,'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 ...
#43

Nobody here would steer you wrong! Yes, you can use that code. He did explain how it works in comments.
Basically, the “fields” arrays set up options that adjusts the posted data depending on what they are.
This is a little advanced for a beginner, but, should work for you if you understand how it works.

You will need to create $fields[‘some-field-name’] entries for each of the fields you have on your page.
( He showed you examples just for a couple test examples in the HTML near the bottom. )
That is the biggest part of that code, making sure you mark all of your fields. His also has an option in the
array that marks the field as required or not. This is handy as it can mark it as missing and not process
the insert or update if it is missing and is needed.

Read all of his comments and it explains his code. Should help you understand how it works. Again, it is
a bit advanced for a beginner, but, will do the job.

1 Like
#44

DON’T. External data can be anything and cannot be trusted. You are somehow stuck on trying to loop over the submitted form data, rather than to loop over a ‘definition’ of the expected form data to control what your form processing code does.

What issue are you trying to solve by the code in your last post? If it is to initially create the $fields array for db table(s), it shouldn’t be much work to make a blank array entry, then keep copying it and filling in the information for each field. You could also query for the columns in any/each database table, use php code to build the $fields array, use var_export() to output the result in your browser, then you can copy and paste the array syntax into your php code as a starting point.

1 Like
#45

Mysql query to get the fields and validate is great idea.
Because the start of this post is not write more 1000 variables.

#46

I can get the fields name from db table, and in vardump show me if is string or int.
how I can validate the part of information?

<?php
$result = $con->query('DESCRIBE `table`');
$fields = array();
while ( $row = $result->fetch_assoc() ) {
    $fields[] = $row['Field'];                   	
}
var_dump($fields);

$names=array();
foreach($fields as $key=>$name){
	echo $name."<br>";
 $names[]=$name;
}

foreach ($_POST as $variable_name=>$variable_data) {

  }if(in_array($variable_name,$names)){
  echo($variable_name).',';

?>

My idea is validate if $_post field is in table, and if it is what type of data he have? for after i can filter that data.
In_array not found the exist fields? why?

#47

The data type of a column in the db table doesn’t tell you the meaning of the data, who (user type) can insert or update any particular column, or even if the column should be included in a query.

These are things that require knowledge about the application and the different types of users who will use the application.

The ‘defining’ $fields array in the example code I posted, that controls what the code does for each submitted form field, must be created or filled in (if you automate their creation) by a person with knowledge about the application, knowledge about what form fields are expected, knowledge about the meaning of the data in each field, and knowledge about if a field is used in any particular query.

You are trying to automate something that requires human knowledge to determine and by unconditionally allowing access to columns based on their existence in a db table, will allow hackers to insert and update data they shouldn’t have access to.

What you are doing would be okay (except the part where you are still unconditionally looping over the $_POST data) if you were writing a general purpose db management tool, like phpmyadmin, but not for application code.

#48

My idea is verify if field exist and if int or string and use mysqli prepare staments
If I found a way to catch that info from table structure maybe i could filter data

$result = $conn->query('DESCRIBE `table`');
$fields = array();
while ( $row = $result->fetch_assoc() ) {
    $fields[] = $row['Field'];	
}//fim do verificar dos campos 	

//array with sql table fields names
	$table_column_names=array();

foreach($fields as $key=>$name){
	//echo $name."<br>";
	$table_column_names[]=$name;
}


	$columns=array();//names of fields to insert
	$values=array();//data to insert

	foreach ($_POST as $variable_name=>$variable_data) {		
		
		if(in_array($variable_name,$table_column_names)){
			$columns[]=($variable_name);

// here I want to filter data before make the array, I just try to know how i can make after the prepare stament
//imagine something like if(int_data){ prepare statement use “i” if(string) prepare statement use “s”

			$values[]=($variable_data);
		}
	
	}
     

	
	$sql = "INSERT INTO apolicesoutras (" .implode(',',$columns). ") VALUE (" .implode(',',array_fill(0,count($columns),'?')). ")";
	echo $sql ."Values  " . implode(',',$values);
?>
#49

I think i found a way to do it.
$query=“select * from table”;
if($result = $segjar->query($query)){
// Get field information for all columns
while ($column_info = $result->fetch_field()){
echo $column_info->type ."
";
}
}

Here i found the codes:

numerics

BIT: 16
TINYINT: 1
BOOL: 1
SMALLINT: 2
MEDIUMINT: 9
INTEGER: 3
BIGINT: 8
SERIAL: 8
FLOAT: 4
DOUBLE: 5
DECIMAL: 246
NUMERIC: 246
FIXED: 246

dates

DATE: 10
DATETIME: 12
TIMESTAMP: 7
TIME: 11
YEAR: 13

strings & binary

CHAR: 254
VARCHAR: 253
ENUM: 254
SET: 254
BINARY: 254
VARBINARY: 253
TINYBLOB: 252
BLOB: 252
MEDIUMBLOB: 252
TINYTEXT: 252
TEXT: 252
MEDIUMTEXT: 252
LONGTEXT: 252

Now i can control data if is int, string, bolb, double

#50

That is a good way if you want to validate each different types of strings and binary data.
But, you could just use the filter_input() function which would take out most of hacker crap and is easy to use.
Then, just use PDO and prepared statements and you will be safe.

Validating individual posted inputs is good and makes inputs safer. It also a good way to limit inputs for the
various fields. The code that @phdr gave you with the fields[ ] array was a way to handle this. It lets you
build an array of all input fields with the needed options such as the fields that are required and would let
you add in validation as needed. But, he only gave you the general layout of the process, not a complete
one for your webpage. You would need to use the data you just posted and combine it into his routines.
Then, you would have a solid and safe was to acquire and save your data.

#51

My problem is have all more difficult to work with pdo than with mysqli and prepare statements

#52

You shouldn’t be using mysqli for this. The example code I posted uses the much simpler PDO extension. Writing a general purpose prepared query function/method using mysqli will take a bunch of slow code and is not practical.

#53

@phdr
your code is awesome and i try it!
in my begginner world. i have difficult to deal with it.
How I can use it if I have query to update with multiple WHERE?, ? ;?

#54

When you are at the point of editing/updating data, you should be referencing it using an auto-increment integer index, in which case you will have a single column in the WHERE clause.

To modify the code to use more than one column, change the logic to instead use an array for the UPDATE where terms and use implode() to build that part of the sql query syntax, similar to how several other parts of the sql queries are being built now.

1 Like
#55

I try other way, but i´m having error; mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables

I count everything and have 20 fields on each. help me found error on end.

<?php
	require_once("connect.php");
	

//check names of fields
$result = $conn->query('DESCRIBE `apolicesoutras`');
$fields = array();
while ( $row = $result->fetch_assoc() ) {
    $fields[] = $row['Field'];	
}//fim do verificar dos campos 	
//var_dump($fields);


$_bind_types=array();	
$query = "SELECT * from apolicesoutras";	
if($result = $conn->query($query)){
	
    // Get field information for all columns
    while ($column_info = $result->fetch_field()){
      
		if($column_info->type <=3){
			 // echo $column_info->type ."inteiro"  ."<br>";
			$bind_type="i";
			$_bind_types[]=$bind_type;
		}
			if($column_info->type ==5){
			 // echo $column_info->type ."double"  ."<br>";
				$bind_type="d";
				$_bind_types[]=$bind_type;
		}
		
			if($column_info->type >=7 && $column_info->type <=10){
			 // echo $column_info->type ."text"  ."<br>";
				$bind_type="s";
				$_bind_types[]=$bind_type;
		}
			if($column_info->type >251){
			  //echo $column_info->type ."text"  ."<br>";
				$bind_type="s";
				$_bind_types[]=$bind_type;
		}
	}
}	

$table_column_names=array();

foreach($fields as $key=>$name){
	//echo $name."<br>";
	$table_column_names[]=$name;
}


	$columns=array();//nomes dos campos da tabela
	$values=array();//valores dos campos

	foreach ($_POST as $variable_name=>$variable_data) {		
		
		if(in_array($variable_name,$table_column_names)){
			$columns[]=($variable_name);
			$values[]=($variable_data);
		}
	
	}     
	

	
	$sql = "INSERT INTO apolicesoutras (" .implode(',',$columns). ") VALUE (" .implode(',',array_fill(0,count($columns),'?')). ")";

	
	$a=count($columns);
	$b=count($values);
	$c=count($_bind_types);
	//echo "colunas = ". $a ."valores=  ".$b. "binds=  " .$c;
	
	
$bind_types= implode($_bind_types);
$values=implode(',',$values);	
	
	// prepare and bind
$stmt = $conn->prepare($sql);
$stmt->bind_param($bind_types, $values);
	
	
	
$stmt->execute();	
	
?>
#56

Remove the underscore in the bind_param line. The values is $values not $values_ !!!
Also, you have an underscore in your $stmt=mysqli_stmt_init($conn) line also!

#57

I edit code again in upper post, but still not work.

$bind_types= implode($_bind_types);
$values=implode(',',$values);	
	
	// prepare and bind
$stmt = $conn->prepare($sql);
$stmt->bind_param($bind_types, $values);
	
$stmt->execute();

how i can echo $stmt full query for check?

#58

I would echo your $values variable first and see if that is correct.
The $stmt variable is an object and not really displayable. It can be if needed, but, it most likely is your query or values. You also should echo the $sql as that may be bad, too.

Just before: $stmt = $conn->prepare($sql);
echo $sql;
and
echo $values;

And verify that they look correct. Also note that you have another underscore for $_bind_types[] which you use a few places. You should remove the first underscore. That was used decades ago to indicate an OOP object. Nobody really uses it now as far as I know. Not needed…

#59

The mysqli bind_parm() doesn’t work like that. Please read the php.net documentation. The 1st parameter is a string consisting of the data types. The 2nd through Nth parameters are a variable for each value. The 2nd parameter is NOT a string consisting of the values separated by commas.

To do this dynamically using mysqli, you can either use -

  1. call_user_func()
  2. reflection (dynamically gets and calls class methods)
  3. argument unpacking via the … splat operator (the three … are the actual php syntax to use)

Or you can use the much simpler php PDO extension, where the ->execute() method call directly accepts an array of the values.

1 Like
#60

@phdr
Thank you for your help! :slight_smile:
I found this article https://www.pontikis.net/blog/dynamically-bind_param-array-mysqli

in my code my /* Bind parameters. Types: s = string, i = integer, d = double, b = blob */ are in

$bind_types=implode('',$bind_types);
The values I want to insert are in 
$values=implode(','.$values);

for use call_user_func_array( array ( $stmt , 'bind_param' ), $a_params );

my $a_params should be one combined array?

I try to combine the 2 arrays WITHOUT PUT IMPLODE ON VAR but I always have error in 2 argument is not array.

array_merge(
   array_combine(
       $bind_types,
       array_fill(0,count($values),NULL)
   ),
   $a_params);


I debug the 2 variables i have 2 arrays with 20 lines each . I not know what is wrong. Thank you for all help
Sponsor our Newsletter | Privacy Policy | Terms of Service