In this article, We will learn about how to create dynamic columns in the MySQL table.
Normally, all work with static table column fields but this article learns about creating a dynamic column in MySQL.
Now, Let’s start to create a dynamic table column.
First of all, Create an index.php file and put the below HTML structure into this file for creating forms.
This HTML structure on the top has shown a column add button stands for cloning an existing column and appending it with a new column in form.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Blog Form</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.js"></script>
<script src="js/custom.js"></script>
</head>
<body>
<!-- ADD Input Modal -->
<div class="modal fade" id="inputModal" tabindex="-1" aria-labelledby="inputModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="inputModalLabel">Add Input Field</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="form-group mb-2">
<label for="field_type" class="form-label">Field Type</label><span class="text-danger">*</span>
<select name="select" id="field_type" class="form-control">
<option value="" selected>select</option>
<option value="text">Text</option>
<option value="number">Number</option>
<option value="color">Color</option>
<option value="checkbox">Checkbox</option>
<option value="image">Image</option>
</select>
<div id="field_type_error" style="display:none"><font color="red">Please select field type.</font></div>
</div>
<div class="form-group mb-2">
<label for="field_name" class="form-label">Field Name</label><span class="text-danger">*</span>
<div class="input-group">
<input name="field_name" id="field_name" placeholder="Enter Field Name" type="text" aria-describedby="fieldNamePrepend" class="form-control">
<div class="input-group-prepend d-flex">
<span class="input-group-text fieldname_prefix rounded-0 rounded-end" id="fieldNamePrepend">Select</span>
</div>
</div>
<div id="field_name_error" style="display:none"><font color="red">Please enter field name.</font></div>
</div>
<div class="form-group">
<input id="add-block-data-id" name="block-data-id" type="hidden" class="form-control">
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="button" id="add_input_field" name="add_input_field" class="btn btn-primary">Insert</button>
</div>
</div>
</div>
</div>
<!-- ADD Column Modal -->
<div class="modal fade" id="addColModal" tabindex="-1" aria-labelledby="addColModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="addColModalLabel">Add Column</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="form-group mb-2">
<label for="form_type" class="form-label">Form Type</label><span class="text-danger">*</span>
<select name="select" id="form_type" class="form-control">
<option value="" selected>select</option>
<option value="google_admob">Google Admob</option>
<option value="adx_one">ADX ONE</option>
</select>
<div id="form_type_error" style="display:none" class="text-danger">Please select form type.</div>
</div>
<div class="form-group">
<label for="form_name" class="form-label">Form Name</label><span class="text-danger">*</span>
<input name="form_name" id="form_name" placeholder="Enter Field Name" type="text" class="form-control">
<div id="form_name_error" style="display:none" class="text-danger">Please enter form name.</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="button" id="add_ads_col_box" name="add_ads_col_box" class="btn btn-primary">Insert</button>
</div>
</div>
</div>
</div>
<!-- ADD COlUMN BUTTON -->
<div class="app-page-title bg-light p-4">
<div class="page-title-wrapper text-center">
<button type="button" class="btn btn-primary add-new-col-box" data-bs-toggle="modal" data-bs-target="#addColModal">
Add New Column
</button>
</div>
</div>
<!-- Form -->
<div class="form-main-wrap pt-5 px-3">
<form role="form" method="post" action="" id="add_app_ads_master" enctype="multipart/form-data">
<div class="row justify-content-center">
<div class="col-xl-4 col-lg-6 col-md-6">
<div class="card">
<div class="card-header d-flex justify-content-between">
<h5 class="card-title">Google Admob</h5>
<div class="add-btn">
<button type="button" class="btn btn-primary addbtn" data-id="google_admob" data-bs-toggle="modal" data-bs-target="#inputModal">
Add Input
</button>
</div>
</div>
<div class="card-body" id="google_admob">
<div class="form-group app-main-field mb-2">
<label for="app_open_admob" class="form-label">App Open Admob</label>
<input name="app_open_admob" id="app_open_admob" placeholder="Enter App Open Admob" type="text" class="form-control">
</div>
<div class="form-group app-main-field mb-2">
<label for="banner_admob" class="form-label">Banner Admob</label>
<input name="banner_admob" id="banner_admob" placeholder="Enter Banner Admob" type="text" class="form-control">
</div>
<div class="form-group app-main-field mb-2">
<label for="interstitial_admob" class="form-label">Interstitial Admob</label>
<input name="interstitial_admob" id="interstitial_admob" placeholder="Enter Interstitial Admob" type="text" class="form-control">
</div>
<div class="form-group app-main-field mb-2">
<label for="interstitial_video_admob" class="form-label">Interstitial Video Admob</label>
<input name="interstitial_video_admob" id="interstitial_video_admob" placeholder="Enter Interstitial Video Admob" type="text" class="form-control">
</div>
</div>
</div>
</div>
<div class="col-xl-4 col-lg-6 col-md-6">
<div class="main-card mb-3 card">
<div class="card-header d-flex justify-content-between">
<h5 class="card-title">Interstitial actvity flag</h5>
<div class="add-btn">
<button type="button" class="btn btn-primary addbtn" data-id="interstitial_actvity_flag" data-bs-toggle="modal" data-bs-target="#inputModal">
Add Input
</button>
</div>
</div>
<div class="card-body" id="interstitial_actvity_flag">
<div class="form-group app-main-field mb-2">
<label for="activity_name" class="form-label">Activity Name</label>
<input name="activity_name" id="activity_name" placeholder="Enter Activity Name" type="text" class="form-control">
</div>
<div class="row app-main-field" class="form-label">
<label for="activity_flag"><input type="checkbox" name="activity_flag" id="activity_flag"> Activity Flag</label>
</div>
</div>
</div>
</div>
</div>
</form>
</div>
</body>
</html>
After creating HTML, a js folder and create a custom.js file in the js folder. Now, add the below js in the custom js file.
This js file adds code for creating dynamic input fields and columns.
Notes:
To only create a dynamic input field go here.
jQuery(document).ready(function(){
//Add data-id in input hidden field of form and Add the title in modal
jQuery(document).on('click',".addbtn",function(event) {
event.preventDefault();
var dataid = jQuery(this).attr('data-id');
jQuery('#'+dataid).slideDown();
jQuery('#add-block-data-id').attr('value', dataid);
var title_text = dataid.replace(/_/g,' ');
var title_text = title_text.replace(/(^\w{1})|(\s+\w{1})/g, letter => letter.toUpperCase());
jQuery('#inputModal #inputModalLabel').html("Add "+ title_text +" Input Field");
});
//On Change Append Type Of Field
jQuery(document).on('change', '#field_type', function(event) {
event.preventDefault();
var typeVal = jQuery(this).val();
let typeName;
switch ( typeVal ) {
case 'text':
typeName = "";
break;
case 'number':
typeName = "Count";
break;
case 'color':
typeName = "Color";
break;
case 'checkbox':
typeName = "Flag";
break;
case 'image':
typeName = 'Image';
break;
default:
typeName = "";
break;
}
jQuery('#fieldNamePrepend').html( typeName );
});
// Create And Add A Dynamic Input Field into Form
jQuery(document).on('click',"#add_input_field",function(event) {
event.preventDefault();
var field_type = jQuery('#field_type').val();
var fieldNamePrefix = jQuery('#fieldNamePrepend').html();
var fieldName = jQuery('#field_name').val();
var fielddata_id = jQuery('#add-block-data-id').val();
var field_name_joint = $.trim(fieldName).concat(" ", fieldNamePrefix);
const field_name = field_name_joint.replace(/(^\w{1})|(\s+\w{1})/g, letter => letter.toUpperCase());
var ret = true;
if (!(field_type)){
$( "#field_type_error" ).show();
ret = false;
} else {
$( "#field_type_error" ).hide();
}
if (!(fieldName)){
$( "#field_name_error" ).show();
ret = false;
} else {
$( "#field_name_error" ).hide();
}
if( ret == true ){
//convert string to lowercase and add ( - )
var fieldname_lower = field_name.toLowerCase();
field_name_split = fieldname_lower.split(' ').join('_');
let fieldscript;
switch ( field_type ) {
case 'text':
fieldscript = "<div class='form-group mb-2'><label for="+field_name_split+" class='form-label'>"+field_name+"</label><input name="+field_name_split+" id="+field_name_split+" placeholder='Enter "+field_name+"' type='text' class='form-control'></div>";
break;
case 'number':
fieldscript = "<div class='form-group mb-2'><label for="+field_name_split+" class='form-label'>"+field_name+"</label><input name="+field_name_split+" id="+field_name_split+" placeholder='Enter "+field_name+"' type='number' class='form-control'></div>";
break;
case 'color':
fieldscript = "<div class='form-group mb-2'><label for="+field_name_split+" class='form-label'>"+field_name+"</label><input name="+field_name_split+" id="+field_name_split+" placeholder='Enter "+field_name+"' type='color' class='form-control'></div>";
break;
case 'checkbox':
fieldscript = "<div class='form-group mb-2'><label for="+field_name_split+" class='form-label'><input type='checkbox' name="+field_name_split+" id="+field_name_split+"> "+field_name+"</label></div>";
break;
case 'image':
fieldscript = "<div class='form-group mb-2'><label for="+field_name_split+" class='form-label pe-2'>"+field_name+"</label><input class='form-control-file' type='file' name="+field_name_split+" id="+field_name_split+" accept='image/png ,image/PNG, image/gif, image/jpeg, image/webp' /></div>";
break;
}
if( fieldscript != '' ){
jQuery('#inputModal').modal('toggle');
jQuery('#field_type, #field_name, #add-block-data-id').val('');
jQuery('#fieldNamePrepend').html('Select');
var addfield_id = jQuery('#'+fielddata_id);
addfield_id.append(fieldscript);
}
}
});
//on click add form type of available into page
jQuery(document).on('click', '.add-new-col-box', function(event) {
event.preventDefault();
jQuery("#form_type").empty();
jQuery("#form_type").append('<option value="" selected="">select</option>');
jQuery("#add_app_ads_master .card-body").each(function( index ) {
var formtype = jQuery(this).attr('id');
var form_name_split = formtype.split('_').join(' ');
const form_name = form_name_split.replace(/(^\w{1})|(\s+\w{1})/g, letter => letter.toUpperCase());
var fieldvalue = "<option value="+formtype+">"+form_name+"</option>";
jQuery("#form_type").append( fieldvalue );
});
});
// create and add a dynamic form
jQuery(document).on('click',"#add_ads_col_box",function(event) {
event.preventDefault();
var formType = jQuery('#form_type').val();
var formNameVal = jQuery('#form_name').val();
var ret = true;
if (!(formType)){
$( "#form_type_error" ).show();
ret = false;
} else {
$( "#form_type_error" ).hide();
}
if (!(formNameVal)){
$( "#form_name_error" ).show();
ret = false;
} else {
$( "#form_name_error" ).hide();
}
if( ret == true ){
//convert string to lowercase and add -
var fieldname_lower = formNameVal.toLowerCase();
var formName = fieldname_lower.split(' ').join('_');
var formID = '#'+formName;
jQuery.ajax({
url : 'addData.php',
type : 'POST',
dataType: 'JSON',
data: {
'formName' : formName,
},
success : function(response) {
if ( response.statuscode == '001' ) {
var formStructure = "<div class='col-xl-4 col-lg-6 col-md-6'><div class='main-card mb-3 card'><div class='card-header d-flex justify-content-between'><h5 class='card-title mb-0'>"+formNameVal+"</h5><div class='add-btn'><button type='button' class='btn btn-primary addbtn' data-id="+formName+" data-bs-toggle='modal' data-bs-target='#inputModal'>Add Input</button></div></div><div class='card-body' id="+formName+"></div></div></div>"
jQuery("#add_app_ads_master > .row").append(formStructure);
jQuery('#add_app_ads_master').find('#'+formType).children().clone().appendTo(formID);
jQuery(formID).find('.app-main-field').each( function(){
var labelfield = jQuery(this).find('label');
var labeloldname = labelfield.html();
var labelnewname = labeloldname+' '+formNameVal;
labelfield.html(labelnewname);
var inputfield = jQuery(this).find('input');
var fieldtype = inputfield.attr('type');
var inputoldname = inputfield.attr('name');
var inputnewname = inputoldname+'_'+formName;
labelfield.attr('for', inputnewname);
if( fieldtype == 'file' ){
var nextinputfile = inputfield.next('input');
var fieldname = nextinputfile.attr('name')+'_'+formName;
inputfield.attr({id: fieldname });
nextinputfile.attr({name: fieldname , id: '', value:''});
jQuery('.blashport').attr('src', '#').hide();
} else if( fieldtype == 'checkbox' ) {
var inputnewname_split = inputnewname.split('_').join(' ');
const label_name = inputnewname_split.replace(/(^\w{1})|(\s+\w{1})/g, letter => letter.toUpperCase());
inputfield.attr({ name: inputnewname, id: inputnewname, placeholder: 'Enter '+label_name, value:'' });
inputfield.removeAttr('checked');
} else {
inputfield.attr({name: inputnewname, id: inputnewname, placeholder: 'Enter '+labelnewname, value:''});
}
});
jQuery('#addColModal').modal('toggle');
jQuery(".fade:not(.show), .app-main__outer, .header-shadow, .modal-dialog, .app-main").removeAttr("style");
jQuery('#form_type, #form_name').val('');
} else {
jQuery('#addColModal').modal('toggle');
jQuery('#form_type, #form_name').val('');
var msg_data = '<div id="delete" class="alert alert-danger alert-dismissible">The form has not been created due to this form already existing or the row size being too large.</i></div>';
$('#delete').remove();
$('.form-main-wrap').before(msg_data);
}
},
error: function(errorThrown){
console.log(errorThrown);
}
});
event.stopImmediatePropagation();
return false;
}
});
});
SQL ALTER TABLE Statement:
The ALTER TABLE statement stands for adding, deleting, or modifying columns in an existing table.
The ALTER TABLE statement also stands for add and drops various constraints on an existing table.
ALTER TABLE – ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
Then, create an addData.php and put it in the below code.
Following code to create a column in the table using “SQL ALTER TABLE Statement“.
<?php
define ("HOST","localhost");
define ("USER","root");
define ("PASSWORD","");
define ("DATABASE","codehubs");
error_reporting(E_ALL ^ E_NOTICE);
error_reporting(1);
//..........database connection............
$con = mysqli_connect (HOST,USER,PASSWORD,DATABASE) or die ("Couldn't connect with database!");
// Add Column
if( isset( $_POST['formName'] ) && $con ){
$formName = $_POST['formName'];
$qry = "ALTER TABLE `blogform` ADD $formName varchar(1000)";
$err = mysqli_query( $con, $qry );
if( !$err ){
$array = array(
'statuscode' => '002',
);
echo json_encode( $array );
exit;
} else {
$array = array(
'statuscode' => '001',
);
echo json_encode( $array );
exit;
}
}
?>
Now you can see the output that creates a dynamic column in the table and creates a new column with the input data field div in form.
Output:
Thank you, I hope you find something helpful.😃
