通过SQL仅将填充的输入值发送到数据库

Question

I have dozens of inputs in an HTML table one can use to enter numerical values. When submit button is pressed all inputs values are added to their corresponding column in the SQL table via post method. Value of <input name="A1> will be sent to column A1 in SQL table, <input name="A2> to column A2, and so on.

I'm currently using something like this (but with dozens of parameters) to insert data in my table :

$sql = "INSERT INTO all_stats_table (A1, A2, A3) VALUES ($A1, $A2, $A3)";

Problem with this approach is that every input needs to be filled or it will result in an SQL error. I initially used php to set all empty inputs value to 0 before sending everything to database, but I don't think this method is the most efficient way to go.

I would rather like to dynamically check which inputs are actually filled and only send their values to the table instead of converting every empty input value to 0 and having to send everything to the database.

I've already set all default values to 0 in SQL, but I don't know how to only send filled input values via SQL. I tried using a php foreach loop but I'm definitely having trouble finding the right SQL syntax.

Is what I'm trying to do possible ? If not, what would be the best practice to make this process more efficient ?

Thank you for your help

---

EDIT : attempt to adapt akash raigade's great solution to non-numbered SQL columns :

HTML :

<input name='name'>
<input name='address'>
<input name='age'>

PHP :

$Field_list = array ('name','address','age');

$field_string = ''; 
$input_string = ''; 

foreach ($_POST as $userInfo=>$userInfo_value) {
    if (isset($userInfo)) {
        if ($field_string == '') {
            $field_string = $field_string.$userInfo; //problem here ?
            $input_string = $userInfo_value; //problem here ?
        }
        else {
            $field_string = $field_string.','.$userInfo; //problem here ?
            $input_string = $input_string.','.$userInfo_value; //problem here ?
        }
    }
}
$sql = "INSERT INTO protocole_test (".$field_string.") VALUES (".$input_string.")"; 
echo $sql ; //check query formed

Answer 1

[Upgraded version]

Basic idea is that we keep NAME attribute of INPUT same as table column-name where it is gonna be stored.Then with help of input tag name and value which are filled we prepare SQL statement which have only required (FILLED) columns and values.

For given example consider following MYSQL table :

 sr.no.|name|age|gender

CODE [Tested]:

<input name="name" >
<input name="age" >
<input name="gender" >
<input type='submit'>

<?php 
$field_string ='';
$input_string='';
foreach  ($_POST as $userInfo=>$userInfo_value){
    if($userInfo_value !=''){
        echo $userInfo."->".$userInfo_value;
        if ($field_string == '') {
            $field_string = $field_string.$userInfo; 
            $input_string = $userInfo_value; 
        }
        else {
            $field_string = $field_string.','.$userInfo; 
            $input_string = $input_string.','.$userInfo_value; 
        }
    }
}
$sql = "INSERT INTO protocole_test (".$field_string.") VALUES (".$input_string.")"; 
echo $sql ; //check query formed
?>

---

[original answer]Have a look at following code :

<input name='a1' id='input_for_name'>
<input name='a2' id='input_for_class'>
<input name='a3' id='input_for_seat.no'>
.
.
<input name='an' id='input_for_n'>

Now

    <?php

//you must be having field list to be inserted i.e
//INSERT INTO all_stats_table >>>(A1, A2, A3)<<< VALUES ($A1, $A2, $A3)
//A1,A2,A3 is field list here 

//so save them into an array.
$Field_list = array ('A1','A2','A3',.......'An');

//Now get which input_field is inputted by :
$i=0;
$field_string = '';
$input_string = '';
for($i<n){
if(isset($_POST['a'.$i])){

   if ($field_string == ''){
      $field_string = $field_string.$Field_list[$i];
      $input_string = $_POST['a'.$i];

}
   else {
   $field_string = $field_string.','.$Field_list[$i];
   $input_string = $input_string.','.$_POST['a'$i];
}}}
    $sql = "INSERT INTO (".$field_string.") VALUES (".$input_string.")";
//to check query formed 
echo $sql ;
    ?>

Explanation :

We check which input field is FILLED , if it is field we add its FIELD into FIELD LIST and ITS VALUE in INPUT LIST finally we GENERATE SQL STATEMENT.