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

General PHP Help
#61

I have complete it !!!
I have a headache!
<?php

	require_once("connection.php");
	

//verifie the fields on table
$result = $conn->query('DESCRIBE `table`');
$fields = array();
while ( $row = $result->fetch_assoc() ) {
    $fields[] = $row['Field'];	
}
//var_dump($fields);

	
// verify type of the fields integer, varchar, text, etc ... result in one code number... the codes are in post... after construct the bind_types array
$bind_types=array();	
$query = "SELECT * from table";	
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;
		}
	}
}




	
//create array with name of the columns in mysql table

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


	$columns=array();//names of columns in mysql table
	$values=array();//value data to insert

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

	
	
$a_bind_params=$values; //value data to insert
$a_param_type=$bind_types;// array of binds to check for example: "iddssddsis"
	
// bind parameters.
$param_type = '';
$n = count($a_param_type);

for($i = 0; $i < $n; $i++) {
    $param_type .= $a_param_type[$i];
}

/* with call_user_func_array, array params must be passed by reference */
$a_params = array();
$a_params[] = & $param_type;

for($i = 0; $i < $n; $i++) {
  /* with call_user_func_array, array params must be passed by reference */
  $a_params[] = & $a_bind_params[$i];
}	
     

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

$stmt = $conn->prepare($sql);

/* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
call_user_func_array(array($stmt, 'bind_param'), $a_params);	
$stmt->execute();

?>

#62

Last update!:rofl::rofl::rofl::rofl::rofl::smiley::smiley::smiley::smiley::sweat_smile::sweat_smile::sweat_smile::sweat_smile:
I change the way to check databinds, now i check inside foreach

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

$table_column_names=array();

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



	$columns=array();//names field tables
	$values=array();//data values
	$bind_types=array();// binds example for example: "iddsssiss"
// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST'){	
	/*start to receipt data from $_POST*/
	foreach ($_POST as $variable_name=>$variable_data) {		
		//check if is in array created before
		if(in_array($variable_name,$table_column_names)){
			
			
			//check one by one type of data binds
			$query = "SELECT " .$variable_name. " from table";	
			echo $query ."</br>";
					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 ."int"  ."<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;
							}
						}//end of select 
				}
			
			$columns[]=($variable_name);// array names of fields
			$values[]=($variable_data);// array data
		}
	
	}

	
	
$a_bind_params=$values; 
$a_param_type=$bind_types;
	
	// bind parameters.
$param_type = '';
$n = count($a_param_type);

for($i = 0; $i < $n; $i++) {
    $param_type .= $a_param_type[$i];
}

/* with call_user_func_array, array params must be passed by reference */
$a_params = array();
$a_params[] = & $param_type;

for($i = 0; $i < $n; $i++) {
  /* with call_user_func_array, array params must be passed by reference */
  $a_params[] = & $a_bind_params[$i];
}	
     

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

$stmt = $conn->prepare($sql);

/* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
call_user_func_array(array($stmt, 'bind_param'), $a_params);	
$stmt->execute();	

	$val=implode(',',$values);
}	$binds=implode('',$bind_types);
	echo $sql ."values  " .$val. "binds  ". $binds;
	?>
#63

That’s because you ignored the advice given in this thread and did this the hardest way possible. Your code is at least 10 times slower than the PDO version I posted, allows a hacker access to table columns that should not be accessible, doesn’t have any application validation in it, and has a problem that I didn’t previously mention (because the PDO version doesn’t have it.) By having php treat values as integers, you have limited the values to php’s maximum integer size, which is tied to the server’s register size (32/64 bits.) If you are doing this for real, it is easy to have data that exceeds php’s maximum integer size, so, your application can suddenly start storing truncated data. It is best to treat data only as strings within php and let the database engine parse and convert numbers, since the database server’s maximum data size is not tied to the server’s register size.

You also have a logical mistake in your code. The data type string is being built in the order that the columns are defined in the db table, but the columns in the sql query and the actual data values are being built in the order that the form fields are in. You can fix this by NOT looping over the $_POST array, but instead looping over the $table_column_names array (which is exactly the same as the $fields array it was built from, using an unnecessary loop) and building the $columns and $values arrays in the order that matches the db table definition.

#64

LOL. Do NOT run select queries inside of loops. The last code is at least 10 times + N times (N = number of form fields) slower than the PDO version I posted.

Sorry for the commentary, but you have created a mess of unnecessary and slow code.

#65

@phdr
I accept your comments and I have your code.
I´m learning and i not put nothing of this online, is only testing.
I have to learn to deal with pdo.

Sponsor our Newsletter | Privacy Policy | Terms of Service