Real time Java script calculation for update Row & column total

Senthilkumar · May 30, 2023

Dear Team,

I am creating a table with dynamic rows based on the data available on my databse. In that table having input fields on oall the columns. I want to sum of entire row and update on total colum while typing the input. Then sum entire column of total automatically. Then i have to get % of rowtotal & columntotal.

image.png.0ff7edfcf624d545f005304c38830e5a.png

In this image the Total column will update the sum of entire row. Out put is 4. Then in colum Total 1260 is the sum of category Excavator. So my for row SM% is (4/1260)*100. Then in 2nd row SM% is (59/1260)*100. For all the category Excavator the SM% should update with divided by 1260. Also for Wheel loader it should update (108/108)*100 and for Grader it should update (21/21)*100.

I want the calculation should happen like this.

My table code is

<table style="margin-top:40px; height:15px" id="example" cellpadding="0" cellspacing="0" border="0" class="datatable table-sm table-hover table-striped table-bordered">
								<thead>
									<tr>
                                        <?PHP
										$Year = date("y");
										$previousYear = $Year - 1;
                                        ?>
										<th style="text-align:center; font-weight:bold;width:2%">S.No</th>
										<th style="text-align:center; font-weight:bold; width:10%">Category</th>
										<th style="text-align:center; font-weight:bold; width:10%">Model</th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Jan<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Feb<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Mar<?PHP echo $previousYear; ?></th>
                                        <th style="text-align:center; font-weight:bold;width:4.875%"hidden>Q1</th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Apr<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">May<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Jun<?PHP echo $previousYear; ?></th>
                                        <th style="text-align:center; font-weight:bold;width:4.875%"hidden>Q2</th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Jul<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Aug<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Sep<?PHP echo $previousYear; ?></th>
                                        <th style="text-align:center; font-weight:bold;width:4.875%"hidden>Q3</th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Oct<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Nov<?PHP echo $previousYear; ?></th>
										<th style="text-align:center; font-weight:bold;width:4.5%">Dec<?PHP echo $previousYear; ?></th>
                                        <th style="text-align:center; font-weight:bold;width:4.5%"hidden>Q4</th>
										<th style="text-align:center; font-weight:bold;width:4.875%">Total</th>
                                       	<th style="text-align:center; font-weight:bold;width:4.875%">MS %</th>

									</tr>
                                  
                                       
                              
                                    
  								</thead>
							 <tbody>
                                <?php

                            // $qry=mysqli_query($conn,"SELECT * FROM users where Approver1_Name = '$Name' OR Approver2_Name = '$Name' ");
							 $qry=mysqli_query($conn,"SELECT * FROM model WHERE Brand_ID = '1' order by Category_Name ASC");

                            $n  =   1;
                            while($row = $qry->fetch_assoc()){
                                $Category_Name = $row['Category_Name'];
                                $Model_Name = $row['Model_Name'];
                                ?>

                                <tr>
                                    <td align='center'>
                                        <?php echo $n++ ?>
                                    </td>
                                    <td style="text-align:center">
                                        <?php echo $Category_Name; ?>
                                    </td>
                                    <td hidden>
                                        <input type="text" name="Category_Name[]" id="Category_Name" value=" <?php echo $Category_Name; ?> " style="width:100px" readonly />
                                    </td>
                                    <td style="text-align:center">
                                        <?php echo $Model_Name; ?>
                                    </td>
                                    <td hidden>
                                        <input type="text" name="Model_Name[]" id="Model_Name" value=" <?php echo $Model_Name; ?> " style="width:100px" readonly />
                                    </td>                                
									<td >
										<input type="number" class="form-control Jan" name="Jan[]" id="Jan" value="" style="width:100%" required/>
									</td>
									<td >
										<input type="number" class="form-control Feb" name="Feb[]" id="Feb" value="" style="width:100%" required/>
									</td>
									<td >
										<input type="number" class="form-control Mar" name="Mar[]" id="Mar" value="" style="width:100%"  required/>
									</td>
                                     <td hidden>
                                            <input type="number" name="Q1[]" id="Q1"  class="form-control Q1" value="Q1" style="width:100%" readonly />
                                        </td>
									<td>
										<input type="number" class="form-control Apr" name="Apr[]" id="Apr" value="" style="width:100%" required/>
									</td>
                                    <td>
                                        <input type="number" class="form-control May" name="May[]" id="May" value="" style="width:100%"  required/>
                                    </td>
                                    <td>
                                        <input type="number" class="form-control Jun" name="Jun[]" id="Jun" value="" style="width:100%"  required/>
                                    </td>
                                    <td hidden>
                                            <input type="number" name="Q2[]" id="Q2" class="form-control Q2" value="" style="width:100%"  readonly/>
                                        </td>
                                    <td>
                                        <input type="number" class="form-control Jul" name="Jul[]" id="Jul" value="" style="width:100%" required/>
                                    </td>
                                    <td>
                                        <input type="number" class="form-control Aug" name="Aug[]" id="Aug" value="" style="width:100%" required/>
                                    </td>
                                    <td>
                                        <input type="number" class="form-control Sep" name="Sep[]" id="Sep" value="" style="width:100%"  required/>
                                    </td>
                                    <td hidden>
                                            <input type="number" name="Q3[]" id="Q3" class="form-control Q3" value="" style="width:100%" readonly/>
                                        </td>
                                    <td >
                                        <input type="number" class="form-control Oct" name="Oct[]" id="Oct" value="" style="width:100%" required/>
                                    </td>
                                      <td>
                                        <input type="number" class="form-control Nov" name="Nov[]" id="Nov" value="" style="width:100%" required/>
                                    </td>
                                      <td>
                                        <input type="number" class="form-control Dec" name="Dec[]" id="Dec" value="" style="width:100%" required/>
                                    </td>
                                    <td hidden>
                                           <input type="number" name="Q4[]" id="Q4" class="form-control Q4" value="" style="width:100%" readonly/>
                                        </td>

									 <td>
                                           <input type="number" name="Total[]" id="Total" class="form-control Total" value="" style="width:100%" readonly/>
                                        </td>
                                     <td>
                                           <input type="number" name="MS%[]" id="MS%" class="form-control MS%" value="" style="width:100%" readonly/>
                                        </td>

                                    <td hidden align='center'>
                                        <input type="checkbox" checked="checked" class="checkbox" name='lang[]' value="1" />
                                    </td>
                                </tr>
                              

                                <?php
                            }
                                ?>
                            
                           </tbody>
							
							</table>

I am using the following script for calculating Row total and column total

<script>
      $(document).ready(function () {
           
            $(".Jan, .Feb, .Mar, .Apr, .May, .Jun, .Jul, .Aug, .Sep, .Oct, .Nov, .Dec ").keyup(function () {
                    $("input[name='Dec[]']").each(function (index) {
                        var Jan = $("input[name='Jan[]']").eq(index).val();
                        var Feb = $("input[name='Feb[]']").eq(index).val();
                        var Mar = $("input[name='Mar[]']").eq(index).val();
                        var Apr = $("input[name='Apr[]']").eq(index).val();
                        var May = $("input[name='May[]']").eq(index).val();
                        var Jun = $("input[name='Jun[]']").eq(index).val();
                        var Jul = $("input[name='Jul[]']").eq(index).val();
                        var Aug = $("input[name='Aug[]']").eq(index).val();
                        var Sep = $("input[name='Sep[]']").eq(index).val();
                        var Oct = $("input[name='Oct[]']").eq(index).val();
                        var Nov = $("input[name='Nov[]']").eq(index).val();
                        var Dec = $("input[name='Dec[]']").eq(index).val();

                        var Row_Total =parseInt(Jan) + parseInt(Feb) + parseInt(Mar) + parseInt(Apr) + parseInt(May) + parseInt(Jun) + parseInt(Jul) + parseInt(Aug) + parseInt(Sep) +  parseInt(Oct) + parseInt(Nov) + parseInt(Dec);
                         if (!isNaN(Row_Total)) {
                            $("input[name='Total[]']").eq(index).val(Row_Total);
                             
                        }
                        
                        
                    });                

                            var Column_Total = 0.0;
                            $.each($(".Total"), function (key, input) {
                                if (input.value && !isNaN(input.value)) {
                                    Column_Total += parseFloat(input.value);
                                }                            
                            })               
            });                    
      });
  </script>

I was struckup with this position. I dont know how to update MS% row wise. Can any one help me to complete this process.

requinix · May 30, 2023

When you look at most sorts of Javascript UI frameworks, you see a common design: they keep an internal "model" of what the UI looks like, do work on that, and then update the actual UI with the changes. While it seems like a lot of extra work, it makes the design a lot simpler as you can manage the internal model whatever way you want instead of being forced to observe how the page is laid out.

Repeating that concept here would be helpful.

1. For each row that you need to update, keep an internal array of the values of the 12 input boxes. Each one starts with a value of zero (apparently).
2. When an input is changed, go into that row's array and update the month's value.

That's one half of the functionality: keeping track of when the value changes. Every time this process completes you'll have an array of the current values for the row.

3. Take that array and update the row's total.
4. Update the MS% value too? I don't know what that's supposed to be.

That's the other half. Each of those halves can be done more-or-less independently of each other.

Here is where I would say "Before I continue, let's clean up that HTML some because it's painful to look at" but that would take quite a bit of effort. So I won't. 😓
But there are some changes that would be really, really helpful:

* Change the markup for the input boxes so that you don't have to list out the months so often by adding a data-month attribute for the month number (counting from 0)
* Get rid of their IDs because (a) they're more often a bad thing than a good thing and (b) you aren't using them anyways

<input type="number" class="form-control" name="Jan[]" value="" style="width:100%" data-month="0" required/>

Then add some Javascript.

* Don't use keyup. There's a perfectly good event exactly for this sort of thing: input
* Instead of adding event handlers for every single month input, make use of the fact that Javascript "bubbles up" events and add a single event handler to cover all of them

$(function() {
	// for each row in the table,
	$("#example tbody tr").each(function() {
		// the 12 values for the months
		const values = Array(12).fill(0);
		// a running total that can be updated every time a value changes
		let total = 0;

		// arrow function means "this" is inherited from the current scope (ie. this == the table row)
		const updateTotal = () => {
			$("[name='Total[]']", this).val(total.toString());
		};

		// every time something with a data-month changes its value,
		$(this).on("input", "[data-month]", function() {
			// get the new value
			const newvalue = parseInt(this.value || "0", 10);

			// update the running total
			total += newvalue - values[this.dataset.month];

			// put the new value into the array
			values[this.dataset.month] = newvalue;

			// update the total
			updateTotal();
		});
	});
});

Demo: https://jsfiddle.net/gyhu2k57/

mac_gyver · May 30, 2023

in order to produce the "total" row for each category section, you need separate sections. you can use separate tbodys for this.

in order to detect when each category name changes, you need to remember the last category, then detect each time the value changes, to conditionally close out the previous section (only after a section has been output) and start a new section. this requires that you repeat the code to close out the previous section, at the end, to close the last section. as a simpler alternative, you can index/pivot the data from the query using the category name as the main array index, then use two nested foreach loops. since i/we don't know what other data you are selecting and using from that query, i didn't bother showing this.

i also recommend that you dynamically produce any series of values, such as the month abbreviations and the output from them. this will result in DRY (Don't Repeat Yourself) code, where there's only one instance of the output written in the code, so that if you need to change or fix anything, you only have one place to make the changes.

once you do that, you will end up with something that looks like this (tested only with some faked query data) -

<?php
// generate month labels
foreach(range(1,12) as $m)
{
	$mon[] = ucfirst(date('M',strtotime("2023-$m-01")));
}
?>
<table style="margin-top:40px; height:15px" id="example" cellpadding="0" cellspacing="0" border="0" class="datatable table-sm table-hover table-striped table-bordered">
<thead>
<tr>
<?php
$Year = date("y");
$previousYear = $Year - 1;
?>
<th style="text-align:center; font-weight:bold;width:2%">S.No</th>
<th style="text-align:center; font-weight:bold;width:10%">Category</th>
<th style="text-align:center; font-weight:bold;width:10%">Model</th>
<?php
foreach($mon as $m)
{?>
<th style="text-align:center; font-weight:bold;width:4.5%"><?=$m.$previousYear?></th>
<?php
}
?>
<th style="text-align:center; font-weight:bold;width:4.875%">Total</th>
<th style="text-align:center; font-weight:bold;width:4.875%">MS %</th>
</tr>
</thead>
<?php
$qry=mysqli_query($conn,"SELECT * FROM model WHERE Brand_ID = '1' order by Category_Name ASC");
$n  =   1;
// remember last category, to detect when it changes
$last_cat = '';

while($row = $qry->fetch_assoc())
{
	// detect if the category changed
	if($last_cat !== $row['Category_Name'])
	{
		// test if not the first output
		if($last_cat !== '')
		{
			// close the previous section - output a total row
			?>
			<tr>
			<td colspan="2">&nbsp;</td>
			<td style="text-align:center">TOTAL</td>
			<?php
			foreach($mon as $m)
			{?>
			<td><input type="number" class="form-control <?=$m?>" name="<?=$m?>[]" style="width:100%" required></td>
			<?php } ?>
			<td><input type="number" name="Total[]" class="form-control cat_Total" style="width:100%" readonly></td>
			<td>&nbsp;</td>
			</tr>
			</tbody>
			<?php
		}
		// remember the new category
		$last_cat = $row['Category_Name'];
		// start a new section
		?>
		<tbody>
		<?php
	}
?>
<tr>
<td align='center'><?=$n++?></td>
<td style="text-align:center"><?=$row['Category_Name']?></td>
<td style="text-align:center"><?=$row['Model_Name']?></td>
<?php
foreach($mon as $m)
{?>
<td><input type="number" class="form-control calc <?=$m?>" name="<?=$m?>[]" style="width:100%" required></td>
<?php } ?>
<td><input type="number" name="Total[]" class="form-control Total" style="width:100%" readonly></td>
<td><input type="number" name="MS[]" class="form-control MS" style="width:100%" readonly></td>
</tr>
<?php
} // end of data loop

// if there was any output, close out the last section
if($last_cat !== '')
{
	// close the previous section - output a total row
	?>
	<tr>
	<td colspan="2">&nbsp;</td>
	<td style="text-align:center">TOTAL</td>
	<?php
	foreach($mon as $m)
	{?>
	<td><input type="number" class="form-control <?=$m?>" name="<?=$m?>[]" style="width:100%" required></td>
	<?php } ?>
	<td><input type="number" name="Total[]" class="form-control cat_Total" style="width:100%" readonly></td>
	<td>&nbsp;</td>
	</tr>
	</tbody>
	<?php
}
?>
</table>

note: you cannot use the % character in field names or css class names. this example uses just 'MS' in these cases.

once you do that, the javascript (not including each month's column total) becomes -

<script>
$(document).ready(function () {
	$(".calc").on('input', function () {
		var row_total = 0;
		var cat_total = 0;
		var x;
		var ms;
		
		// current row total
		$(this).closest('tr').find('.calc').each(function () {
			x = parseInt($(this).val());
			row_total += isNaN(x) ? 0 : x;
		});
		$(this).closest('tr').find('.Total').val(row_total);
		
		// category total
		$(this).closest('tbody').find('.Total').each(function () {
			x = parseInt($(this).val());
			cat_total += isNaN(x) ? 0 : x;
		});
		$(this).closest('tbody').find('.cat_Total').val(cat_total);
		
		// ms calc = each row total/cat_Total * 100
		$(this).closest('tbody').find('.Total').each(function (index) {
			x = parseInt($(this).val());
			ms = (isNaN(x) ? 0 : x)/cat_total*100;
			$(this).closest('tbody').find('.MS').eq(index).val(ms.toFixed(2));
		});
	});
});
</script>

Edited May 30, 2023 by mac_gyver

Senthilkumar · June 1, 2023

Dear Mac_Gyver,

Thanks for your reply and this is working exactly as per my requirement. But in this i am facing some issues.

I tried for each month's column total with the following code. But only December month is passing on the script.

// Column total month wise
		            $(this).closest('tbody').find('.<?=$m?>').each(function () {
			            y = parseInt($(this).val());
			            col_total += isNaN(y) ? 0 : y;
		            });
		            $(this).closest('tbody').find('.col_Total<?=$m?>').val(col_total);

So i have tried to calculate month wise like bellow code and it is updating the month's column toltal.

// Column total Jan
		            $(this).closest('tbody').find('.Jan').each(function () {
			            y = parseInt($(this).val());
			            col_total += isNaN(y) ? 0 : y;
		            });
		            $(this).closest('tbody').find('.col_TotalJan').val(col_total);

If i use the above code, I have to create for all the 12 month separatly.

My final script is

    <script>
            $(document).ready(function () {
	            $(".calc").on('input', function () {
		            var row_total = 0;
                    var cat_total = 0;
                    var col_total = 0;
                    var col_total1 = 0,col_total2 = 0,col_total3 = 0,col_total4 = 0,col_total5 = 0,col_total6 = 0,col_total7 = 0,col_total8 = 0,col_total9 = 0,col_total10 = 0,col_total11 = 0;
                 

		            var x;
                    var ms;
					var y, a, b, c, d, e, f,g,h,i,j,k;
                   
		
		            // current row total
		            $(this).closest('tr').find('.calc').each(function () {
			            x = parseInt($(this).val());
			            row_total += isNaN(x) ? 0 : x;
		            });
		            $(this).closest('tr').find('.Total').val(row_total);
		
		            // category total
		            $(this).closest('tbody').find('.Total').each(function () {
			            x = parseInt($(this).val());
			            cat_total += isNaN(x) ? 0 : x;
		            });
                    $(this).closest('tbody').find('.cat_Total').val(cat_total);

                    // Column total Jan
		            $(this).closest('tbody').find('.Jan').each(function () {
			            y = parseInt($(this).val());
			            col_total += isNaN(y) ? 0 : y;
		            });
		            $(this).closest('tbody').find('.col_TotalJan').val(col_total);

                    // Column total Feb
		            $(this).closest('tbody').find('.Feb').each(function () {
			            a = parseInt($(this).val());
			            col_total1 += isNaN(a) ? 0 : a;
		            });
                    $(this).closest('tbody').find('.col_TotalFeb').val(col_total1);

                    
                    // Column total Mar
		            $(this).closest('tbody').find('.Mar').each(function () {
			            b = parseInt($(this).val());
			            col_total2 += isNaN(b) ? 0 : b;
		            });
                    $(this).closest('tbody').find('.col_TotalMar').val(col_total2);

                     // Column total Apr
		            $(this).closest('tbody').find('.Apr').each(function () {
			            c = parseInt($(this).val());
			            col_total3 += isNaN(c) ? 0 : c;
		            });
		            $(this).closest('tbody').find('.col_TotalApr').val(col_total3);

                     // Column total May
		            $(this).closest('tbody').find('.May').each(function () {
			            d = parseInt($(this).val());
			            col_total4 += isNaN(d) ? 0 : d;
		            });
					$(this).closest('tbody').find('.col_TotalMay').val(col_total4);

					  // Column total Jun
		            $(this).closest('tbody').find('.Jun').each(function () {
			            e = parseInt($(this).val());
						col_total5 += isNaN(e) ? 0 : e;
		            });
					$(this).closest('tbody').find('.col_TotalJun').val(col_total5);

					  // Column total Jul
		            $(this).closest('tbody').find('.Jul').each(function () {
			            f = parseInt($(this).val());
						col_total6 += isNaN(f) ? 0 : f;
		            });
					$(this).closest('tbody').find('.col_TotalJul').val(col_total6);

					 // Column total Aug
		            $(this).closest('tbody').find('.Aug').each(function () {
			            g = parseInt($(this).val());
						col_total7 += isNaN(g) ? 0 : g;
		            });
					$(this).closest('tbody').find('.col_TotalAug').val(col_total7);

					 // Column total Sep
		            $(this).closest('tbody').find('.Sep').each(function () {
			            h = parseInt($(this).val());
						col_total8 += isNaN(h) ? 0 : h;
		            });
					$(this).closest('tbody').find('.col_TotalSep').val(col_total8);

					 // Column total Oct
		            $(this).closest('tbody').find('.Oct').each(function () {
			            i = parseInt($(this).val());
						col_total9 += isNaN(i) ? 0 : i;
		            });
					$(this).closest('tbody').find('.col_TotalOct').val(col_total9);

					 // Column total Nov
		            $(this).closest('tbody').find('.Nov').each(function () {
			            j = parseInt($(this).val());
						col_total10 += isNaN(j) ? 0 : j;
		            });
					$(this).closest('tbody').find('.col_TotalNov').val(col_total10);

					 // Column total Dec
		            $(this).closest('tbody').find('.Dec').each(function () {
			            k = parseInt($(this).val());
						col_total11 += isNaN(k) ? 0 : k;
		            });
					$(this).closest('tbody').find('.col_TotalDec').val(col_total11);

		            // ms calc = each row total/cat_Total * 100
		            $(this).closest('tbody').find('.Total').each(function (index) {
			            x = parseInt($(this).val());
			            ms = (isNaN(x) ? 0 : x)/cat_total*100;
			            $(this).closest('tbody').find('.MS').eq(index).val(ms.toFixed(2));
		            });
	            });
            });
    </script>

Next problem i am facing is,

When i am insert the values to database, Once the first category is completed, the month wise total value is updating on next category because of we merged on first three columns. My inserting code is

    <?php

   if(isset($_POST['addInvoice'])){


		$Dealer_ID = $_POST['Dealer_Name'];

		$dealname = mysqli_query($conn, "SELECT * from users WHERE Emp_No = '$Dealer_ID'");
		$dealerrow = $dealname->fetch_assoc();
		$Dealername = $dealerrow['Name'];

		$Current_Year = date("Y");
     
    	foreach ($_POST['lang'] as $ID => $VAL) {	

		        $Category = $_POST['Category_Name'][$ID];
		        $Model= $_POST['Model_Name'][$ID];       	        
		        $Jan = $_POST['Jan'][$ID];
		        $Feb = $_POST['Feb'][$ID];
		        $Mar = $_POST['Mar'][$ID];
		        $Apr = $_POST['Apr'][$ID];
		        $May = $_POST['May'][$ID];
		        $Jun = $_POST['Jun'][$ID];
		        $Jul = $_POST['Jul'][$ID];
		        $Aug = $_POST['Aug'][$ID];
		        $Sep = $_POST['Sep'][$ID];
		        $Oct = $_POST['Oct'][$ID];
		        $Nov = $_POST['Nov'][$ID];
		        $December= $_POST['Dec'][$ID];
		        $Total = $_POST['Total'][$ID];
			    $MS = $_POST['MS'][$ID];


			//$query = "UPDATE users SET Branch ='$Branch', Posting_Location ='$Posted_Location', Department='$Department', Divison='$Division', Emp_No='$Emp_No', Name='$Name', Designation='$Designation', User_Type='$User_Type', Functional_Role='$Functional_Role', Employed='$Employed', Mobile='$Mobile', Email='$Email', DOJ='$DOJ',  Qualifications='$Qualifications', DOB='$DOB', Approver1_ID='$Approver1_ID', Approver1_Name='$Approver1_Name', Approver2_ID='$Approver2_ID', Approver2_Name='$Approver2_Name' , Approver='$Approver', Gender='$Gender', Blood='$Blood' WHERE id = '$ida'";
		$query = "INSERT INTO lastyeardata (Dealer_ID, Dealername, Category, Model, Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, December, Total, MS) VALUES ('$Dealer_ID','$Dealername', '$Category','$Model','$Current_Year','$Jan', '$Feb','$Mar', '$Apr', '$May', '$Jun','$Jul', '$Aug', '$Sep','$Oct', '$Nov', '$December','$Total','$MS')";
        $result = mysqli_query($conn, $query);
        if($result)
                    {
                            // echo "<script type='text/javascript'> document.location = 'Users.php'; </script>";
                            echo '<script type="text/javascript">Swal.fire({
                            text: "Last Year Data Details added Successfully",     
                            icon: "success",
                            showCancelButton: false,
                            confirmButtonColor: "#3085d6",
                            confirmButtonText: "OK"
                          }).then((result) => {
                            if (result.isConfirmed) {
                                window.location.href = "Last_Year_Data.php"
                            }
                          })</script>';
            
                    }
        else
            {
                    //echo '<script type="text/javascript"> alert("Details are Not Updated.")</script>';
                    echo '<script type="text/javascript">Swal.fire({
                        text: "Details are Not Updated. Please check the details entered.",
                        icon: "error",
                        showCancelButton: false,
                        confirmButtonColor: "#3085d6",
                        confirmButtonText: "OK"
                      }).then((result) => {
                        if (result.isConfirmed) {
                            window.history.back()
                        }
                      })</script>';
         }  
     } 
   }
    ?>

<form method="POST" action="">
					<div style="display: inline-block">
					
						 <div class="col-md-12"><label class="control-label">Dealer Name</label>
                                                        <select name="Dealer_Name" required class="form-control dropdown-toggle Dealer_Name"   >
                                                            <option value="">Select Dealer Name </option>
                                                            <?php
                                                                $qry    =   mysqli_query($conn,"SELECT * FROM users WHERE User_Type ='Dealer' AND status = '1';");
                                                                while($row = $qry->fetch_assoc()) {
                                                                echo '<option value="' . $row['Emp_No'] . '">' . $row['Name'] . '</option>';
                                                               
                                                                }
                                                            ?>
                                                        </select>
                          </div>            
					</div>
					
					<div id="wrap" class="datalist-wrapper" style="margin-top:-15px">
						<div class="loading-overlay">
							<div class="overlay-content">Loading...</div>
						</div>
						<div class="" id="dataContainer">

							<?php
                                // generate month labels
                                foreach(range(1,12) as $m)
                                {
	                                $mon[] = ucfirst(date('M',strtotime("2023-$m-01")));
                                }
                                ?>
                                <table style="margin-top:40px; height:15px" id="example" cellpadding="0" cellspacing="0" border="0" class="datatable table-sm table-hover table-striped table-bordered">
                                <thead>
                                <tr>
                                <?php
                                $Year = date("y");
                                $previousYear = $Year - 1;
                                ?>
                                <th style="text-align:center; font-weight:bold;width:2%">S.No</th>
                                <th style="text-align:center; font-weight:bold;width:8%">Category</th>
                                <th style="text-align:center; font-weight:bold;width:8%">Model</th>
                                <?php
                                foreach($mon as $m)
                                {?>
                                <th style="text-align:center; font-weight:bold;width:4.5%"><?=$m.$previousYear?></th>
                                <?php
                                }
                                ?>
                                <th style="text-align:center; font-weight:bold;width:5%">Total</th>
                                <th style="text-align:center; font-weight:bold;width:7%">MS %</th>
                                </tr>
                                </thead>
                                <?php
                                $qry=mysqli_query($conn,"SELECT * FROM model WHERE Brand_ID = '1' order by Category_Name ASC");
                                $n  =   1;
                                // remember last category, to detect when it changes
                                $last_cat = '';

                                while($row = $qry->fetch_assoc())
                                {
                                    $Category_Name = $row['Category_Name'];
                                    $Model_Name = $row['Model_Name'];
	                                // detect if the category changed
	                                if($last_cat !== $row['Category_Name'])
	                                {
		                                // test if not the first output
		                                if($last_cat !== '')
		                                {
			                                // close the previous section - output a total row
			                                ?>
			                                <tr>	
			                                <td colspan="3" style="text-align:center"> <?PHP echo $last_cat; ?> TOTAL</td>
			                                <?php
			                                foreach($mon as $m)
			                                {?>
			                                <td><input type="number" class="form-control col_Total<?=$m?>" name="<?=$m?>[]" style="width:100%" readonly></td>
			                                <?php } ?>
			                                <td><input type="number" name="cat_Total[]" class="form-control cat_Total" style="width:100%" readonly></td>
			                                <td>&nbsp;</td>
			                                </tr>
			                               
			                                <?php
		                                }
		                                // remember the new category
		                                $last_cat = $row['Category_Name'];
		                                // start a new section
		                                ?>
		                                <tbody>
		                                <?php
	                                }
                                ?>
                                <tr>
                                <td align='center'><?=$n++?></td>
                                <td style="text-align:center"><?=$row['Category_Name']?></td>
                                <td hidden>
                                        <input type="text" name="Category_Name[]" id="Category_Name" value=" <?php echo $Category_Name; ?> " style="width:100px" readonly />
                                </td>                                
                                    <td style="text-align:center"><?=$row['Model_Name']?></td>
                                    <td hidden>
                                        <input type="text" name="Model_Name[]" id="Model_Name" value=" <?php echo $Model_Name; ?> " style="width:100px" readonly />
                                    </td> 
                                <?php
                                foreach($mon as $m)
                                {?>
                                <td><input type="number" class="form-control calc  <?=$m?>" name="<?=$m?>[]" style="width:100%" ></td>
                                <?php } ?>
                                <td><input type="number" name="Total[]" class="form-control Total" style="width:100%" readonly></td>
                                <td><input type="number" name="MS[]" class="form-control MS" style="width:100%" readonly></td>
                                    <td hidden align='center'>
                                        <input type="checkbox" checked="checked" class="checkbox" name='lang[]' value="1" />
                                    </td>
                                </tr>
                                <?php
                                } // end of data loop

                                // if there was any output, close out the last section
                                if($last_cat !== '')
                                {
	                                // close the previous section - output a total row
	                                ?>
	                                <tr>
                                        <td hidden>
	                                <td colspan="3" style="text-align:center"><?PHP echo $last_cat; ?> TOTAL</td>
	                                <?php
	                                foreach($mon as $m)
	                                {?>
	                                <td><input type="number" class="form-control col_Total<?=$m?>" name="<?=$m?>[]" style="width:100%" readonly></td>
	                                <?php } ?>
	                                <td><input type="number" name="cat_Total[]" class="form-control cat_Total" style="width:100%" readonly></td>
	                                <td>&nbsp;</td>
	                                </tr>
	                                </tbody>
	                                <?php
                                }
                                ?>
                                </table>
							
						</div>
					</div>

                     <div style="text-align:center">
                        <input type="submit" name="addInvoice" class="addInvoice" id="addInvoice" value="Submit" style="width:100px;background: #0A2558;margin-top:10px; color:white;font-family:Cambria;border-radius:5px;height:30px;" />
                    </div>
				</form>

Can you please help me to insert the values on database along with column total.

mac_gyver · June 1, 2023

the point of DRY (Don't Repeat Yourself) code is to NOT write out , copy/paste/overtype, the same logic for every possible value. creating 12 variables and 12 copies of code is just taking up your time pushing keys on a keyboard.

while you probably do need to (initially) calculate all the monthly column totals, you only need to calculate the current monthly column total for each input event. i recommend that you write a javascript function to do this for a dynamically specified column in the current tbody section. if you then need to calculate all the monthly column totals, you can loop over an array of the monthly class names to call the single column function 12 times.

here's the version i came up with (this uses a different class naming for the total row) -

// calculate the column total for a given month class name (mon) and the current tbody element (el)
function calculate_col(mon,el) {
	var total = 0;
	var x;  
	el.find('.'+mon).each(function () {
			x = parseInt($(this).val());
			total += isNaN(x) ? 0 : x;
	});
	el.find('.'+mon+'_Total').val(total);
}

to get the current month class name inside the input event handling logic -

		// get the current class attributes (a string)
		var classList = $(this).attr("class");
		// split string on space characters
		var classArr = classList.split(/\s+/);
		// get the last element, the month class name
		var lastElement = classArr.pop();
		calculate_col(lastElement,$(this).closest('tbody'));

Senthilkumar · June 2, 2023

Dear mac_gyver,

Thanks. I have modified the code with reference of above code. Now each month's column total is working properly.

But while inserting the values to database the column total value is not inserting as seperate row . it is inserting on the next grade first row.

Can you please help me.

mac_gyver · June 2, 2023

the only data you should be storing are the user entered values. the rest of the values (category total, row total, MS) are derived from the user entered data, and should not be stored. another reason to only process the user entered values is because data submitted to your site can come from anywhere, not just your forms/links, can be set to anything, and cannot be trusted. you must validate all data submitted to your site before using it and you must use it securely in whatever context it is being used in. therefore, you want to operate only on the necessary values.

you should also NOT have a series of monthly columns in the table. you should have two tables. the first table gets a single row inserted with the unique (one-time) information about each set of data. the id (autoincrement primary index) from the first table would then be used in the second table to relate all the rows of monthly data back to the parent row in the first table. the second table would have columns for - id, parent id, either the month or the year-month, item id, and quantity. there would be one row in the second table for each non-zero quantity.

your form processing code needs to be simplified and secured - don't copy variables to other variables for nothing, just use the original variables that data is in, use DRY coding, by looping over the $mon array of month abbreviations and dynamically access the form data, trim and validate all input data before using it, and use a prepared query, prepared once before the start of any looping, instead of putting values directly into the sql query statement.

Barand · June 4, 2023

I would use these table (or similar)

TABLE: category        TABLE: model                         TABLE: sale
+----+------------+    +----+------------+-------------+    +----+------------+-------+--------+---------+
| id | cat_name   |    | id | model_name | category_id |    | id |  model_id  | year  | month  |  qty    |
+----+------------+    +----+------------+-------------+    +----+------------+-------+--------+---------+
| 1  | Category A |    | 1  | Model 1    | 1           |    CREATE TABLE `sale` (
| 2  | Category B |    | 2  | Model 2    | 1           |      `id` int(11) NOT NULL AUTO_INCREMENT,   
| 3  | Category C |    | 3  | Model 3    | 1           |      `model_id` int(11) DEFAULT NULL,   
+----+------------+    | 4  | Model 4    | 2           |      `year` year(4) DEFAULT NULL,   
                       | 5  | Model 5    | 2           |      `month` tinyint(4) DEFAULT NULL,   
                       | 6  | Model 6    | 2           |      `qty` int(11) DEFAULT NULL,   
                       | 7  | Model 7    | 3           |      PRIMARY KEY (`id`),   
                       | 8  | Model 8    | 3           |      UNIQUE KEY `idx_model_yr_mth` (`model_id`,`year`,`month`)   
                       +----+------------+-------------+    )

For the javascript, I prefer to use a combination of class and data attriibutes for element grouping EG

<?php
require 'db_inc.php';               // USE YOUR OWN
$pdo = pdoConnect('db2');           // CONNECTION CODE


################################################################################
# PROCESS POSTED DATA                                                          #
################################################################################
if ($_SERVER['REQUEST_METHOD']=='POST')  {
    #echo '<pre>' . print_r($_POST, 1) . '</pre>';
    
    $year = $_POST['year'];
    $pdata = [];
    foreach ($_POST['qty'] as $mod => $mdata) {

        foreach ($mdata as $mth => $qty) {
            if (intval($qty) && intval($mth) && intval($year) && intval($mod) ) {
                $pdata[] = sprintf("(%d, %d, %d, %d)", $mod, $year, $mth, $qty );
            }
        }
    }
    $pdo->exec("INSERT INTO sale (model_id, year, month, qty) VALUES " 
                . join(',', $pdata)
                . "ON DUPLICATE KEY UPDATE qty = VALUES(qty)
                ");
    header("Location: ?year=$year");
    exit;
}

################################################################################
# TABLE HEADINGS                                                               #
################################################################################

$year = $_GET['year'] ?? date('Y');

$dt1 = new DateTime("{$year}-01-01");
$dt2 = clone $dt1;
$dt2->modify("+12 months");
$range = new DatePeriod($dt1, new DateInterval('P1M'), $dt2);

$theadings = "<tr><th>S.No</th><th>Category</th><th>Model</th>";
foreach ($range as $d) {
    $theadings .= "<th>" . $d->format('My') . "</th>";
}
$theadings .= "<th>Total</th><th>MS &nbsp;%</th></tr>\n";

################################################################################
# CATEGORIES AND MODELS                                                        #
################################################################################

$res = $pdo->prepare("SELECT c.id as cid
                           , cat_name
                           , m.id as mid
                           , model_name
                           , month
                           , qty
                      FROM category c 
                           JOIN
                           model m ON c.id = m.category_id
                           LEFT JOIN sale s ON s.year = ?
                                     AND m.id = s.model_id
                      ORDER BY cid, mid, month
                      ");
$res->execute([ $year ]);
$empty = array_fill_keys(range(1,12), null);
$data = [];

################################################################################
# PUT  RESULTS INTO SUITABLY STRUCTURED ARRAY TO MATCH THE REQUIRED OUTPUT     #
################################################################################

foreach ($res as $r)  {
    if (!isset($data[$r['cid']]))  {
        $data[$r['cid']] = ['cat' => $r['cat_name'], 
                            'models' => []
                           ];
    }
    if (!isset($data[$r['cid']]['models'][$r['mid']])) {
        $data[$r['cid']]['models'][$r['mid']] = [ 'mod' => $r['model_name'],
                                                  'vals' => $empty
                                                ];
    }
    if ($r['qty']) {
        $data[$r['cid']]['models'][$r['mid']]['vals'][$r['month']] = $r['qty'];
    }
}


################################################################################
# TAKE THE ARRAY DATA AND OUTPUT TO THE TABLE                                  #
################################################################################

$tdata = '';
$n = 1;
$prev = '';
foreach ($data as $catid => $cdata)  {
    $catname = $cdata['cat'];
    $tdata .= "<tbody data-cat='$catid}'>\n";
    
    foreach ($cdata['models'] as $modid => $mdata) {
        $modname = $mdata['mod'];
        $tdata .= "<tr class='qty-row' data-model='$modid'><td>$n</td><td>$catname</td><td>$modname</td>";
        $catname = '';
        foreach ($mdata['vals'] as $m => $qty) {
            $tdata .= "<td><input class='w3-input w3-border qty-mth' 
                         data-cat='$catid' data-model='$modid' data-mth='$m' 
                         name='qty[$modid][$m]' value='$qty'>
                       </td>";
        }
        $tdata .= "<td><input class='w3-input w3-border qty-yr' data-cat='$catid' data-model='$modid' ></td>
                   <td><input class='w3-input w3-border ms-yr' data-cat='$catid' data-model='$modid' ></td>
                  ";
        ++$n;
    }
    $tdata .= "</tbody>\n
               <tr class='total-row'><td colspan='3'>TOTAL</td>";
    for ($m=1; $m<=12; $m++) {
        $tdata .= "<td><input class='w3-input w3-border tot-mth' data-cat='$catid' data-mth='$m' ></td>";
    }
    $tdata .= "<td><input class='w3-input w3-border tot-yr' data-cat='$catid' ></td>
               <td>&nbsp;</td>
              ";                  
}

################################################################################
# FUNCTIONS                                                                    #
################################################################################

function yrOpts($current)
{
    $yrs = range(date('Y'), date('Y')-4);
    $opts = "<option value=''>- select year -</option>\n";
    foreach ($yrs as $y) {
        $sel = $current==$y ? 'selected':'';
        $opts .= "<option $sel>$y</option>\n";
    }
    return $opts;
}
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8'>
<title>Example</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> 
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $(function() {
        $("#year").change( function() {
            let yr = $(this).val()
            location.href = "?year="+yr
        })
        
        $(".qty-mth").on('input', function() {
            calc_totals()    
        })
        
        calc_totals()
    })
    
    function calc_totals()
    {
        $(".qty-yr").each(function(k,v) {
            let mod = $(v).data("model")
            let toty = 0
            $(".tot-yr").val(0)
            $(".qty-mth[data-model="+mod+"]").each(function(k1,v1) {
                let q = parseInt($(v1).val())
                toty += isNaN(q) ? 0 : q
            })
            $(v).val(toty)
        })
        
        $(".tot-yr").each(function(k,v) {
            let cat = $(v).data("cat")
            calc_cat_totals(cat)
            calc_percents(cat) 
        })
    }
    
    function calc_cat_totals(cat)
    {
        let ytot = 0
        for (let m=1; m<=12; m++) {
            let mtot = 0
            
            $(".qty-mth[data-cat="+cat+"][data-mth="+m+"]").each(function(k,v){
                let q = parseInt($(v).val())
                mtot += isNaN(q) ? 0 : q
                ytot += isNaN(q) ? 0 : q
            })
            $(".tot-mth[data-cat="+cat+"][data-mth="+m+"]").val(mtot)
        }
        $(".tot-yr[data-cat="+cat+"]").val(ytot)
    }
    
    function calc_percents(cat)
    {
        let ytot = $(".tot-yr[data-cat="+cat+"]").val()
        if (ytot==0) return
        $(".qty-yr[data-cat="+cat+"]").each(function(k,v) {
            let mod = $(v).data("model")
            let q = $(v).val() * 100 / ytot
            q = q.toFixed(1)
            $(".ms-yr[data-model="+mod+"]").val(q)
        })
    }
    
</script>
<style type='text/css'>
    table {
        width: 100%;
        border-collapse: collapse;
        margin: 20px 0;
    }
    th, td {
        padding: 2px 4px;
        font-size: 9pt;
        text-align: center;
    }
    th {
        background-color: black;
        color: white;
    }
    .qty-mth, .tot-mth,
    .qty-yr, .ms-yr,
    .tot-yr  {
        width: 45px;
        margin: 0 auto;
    }
    .total-row {
        background-color: #D0D0D0;
    }
    .qty-row {
        background-color: #F8F8F8;
    }
    input {
        text-align: center;
    }
</style>
<script type='text/javascript'>
</script>
</head>
<body>
<div class='w3-container w3-padding'>
<form id='form1' method='POST'>
   <div class='w3-bar'>
        <div class='w3-bar-item'>Year </div>
        <select id='year' name='year' class='w3-bar-item w3-border'>
            <?= yrOpts($year) ?>
        </select>
    </div>
    <table border='1' class="table" id="myTable2">
    <thead>
        <?= $theadings ?>
    </thead>
        <?= $tdata ?>
    </table>
    <div class='w3-container w3-center'>
        <button class='w3-button w3-blue'>Save data</button>
    </div>
</form>
</div>
</body>
</html>

Senthilkumar · June 6, 2023

Dear mac_gyver,

Thanks for your suggession. I had changed the input name for the row total. So now it is updating properly as per my requirement.

Thaks for your support.

Senthilkumar · September 20, 2023

Dear Barand,

I want to update each category column total aslo to my databse. How to update the total. My code is

                <form method="POST" action="">
                    <div style="display: inline-block;width:50%">

                        <div class="form-group" style="width:100%;margin-top:10px">
                            <label class="control-label col-sm-6" for="Dealer_Name">Dealer Name:</label>
                            <div class="col-sm-8">
                                <input type="text" class="form-control Dealer_Name" id="Dealer_Name" placeholder="Search Dealer Name" name="Dealer_Name" Required style="font-size:14px !important"/>
                                <input type="hidden" class="form-control" id="Dealer_Name1" placeholder="Search Dealer Name" name="Dealer_Name1" />
                            </div>
                            <div style="text-align:center;margin-top:20px">
                                <button type="button" name="View" id="View" class="View" value="View" onclick="getLastYearData()" style="width:auto;margin-top:25px;background: #0A2558; color:white;font-family:Cambria;border-radius:5px;height:30px;padding:0 5px 0 5px;display:none">View Submitted Data </button>

                            </div>

                        </div>

                    </div>

                    <script>
						 $( function() {
								$( "#Dealer_Name" ).autocomplete({
									source: 'Auto_Complete_User.php',
									select: function (event, ui) {
									   $("#Dealer_Name1").val(ui.item.id);//Put Id in a hidden field
									}
								});
							});
                    </script>

                    <div id="wrap" class="datalist-wrapper" style="margin-top:-25px">
                        <div class="" id="dataContainer">

                            <?php
                                // generate month labels
                                foreach(range(1,12) as $m)
                                {
	                                $mon[] = ucfirst(date('M',strtotime("2023-$m-01")));
                                }
                            ?>
                            <table style="margin-top:40px; height:15px" id="example" cellpadding="0" cellspacing="0" border="0" class="datatable table-sm table-hover table-striped table-bordered">
                                <thead>
                                    <tr>
                                        <?php
                                $Year = date("y");
                                $previousYear = $Year - 1;
                                        ?>
                                        <th style="text-align:center; font-weight:bold;width:2%">S.No</th>
                                        <th style="text-align:center; font-weight:bold;width:8%">Category</th>
                                        <th style="text-align:center; font-weight:bold;width:8%">Model</th>
                                        <?php
                                foreach($mon as $m)
                                {?>
                                        <th style="text-align:center; font-weight:bold;width:4.5%">
                                            <?=$m.$previousYear?>
                                        </th>
                                        <?php
                                }
                                        ?>
                                        <th style="text-align:center; font-weight:bold;width:5%">Total</th>
                                        <th style="text-align:center; font-weight:bold;width:7%">MS %</th>
                                    </tr>
                                </thead>
                                <?php
                                $qry=mysqli_query($conn,"SELECT * FROM model WHERE Brand_ID = '1' order by Category_Name ASC");
                                $n  =   1;
                                // remember last category, to detect when it changes
                                $last_cat = '';

                                while($row = $qry->fetch_assoc())
                                {
                                    $Category_Name = $row['Category_Name'];
                                    $Model_Name = $row['Model_Name'];
	                                // detect if the category changed
	                                if($last_cat !== $row['Category_Name'])
	                                {
		                                // test if not the first output
		                                if($last_cat !== '')
		                                {
			                                // close the previous section - output a total row
                                ?>
                                <tr>
                                    <td colspan="3" style="text-align:center">
                                        <?PHP echo $last_cat; ?> TOTAL
                                    </td>
                                    <?php
			                                foreach($mon as $m)
			                                {?>
                                    <td>
                                        <input type="number" class="form-control col_Total<?=$m?>" name="col_Total_<?=$m?>[]" style="width:100%;font-size:14px;text-align:center" readonly />

                                    </td>
                                    <?php } ?>
                                    <td>
                                        <input type="number" name="cat_Total[]" class="form-control cat_Total" style="width:100%;font-size:14px;text-align:center" readonly />

                                    </td>
                                    <td>&nbsp;</td>
                                </tr>

                                <?php
		                                }
		                                // remember the new category
		                                $last_cat = $row['Category_Name'];
		                                // start a new section
                                ?>
                                <tbody>
                                    <?php
	                                }
                                    ?>
                                    <tr>
                                        <td align='center'>
                                            <?=$n++?>
                                        </td>
                                        <td style="text-align:center">
                                            <?=$row['Category_Name']?>
                                        </td>
                                        <td hidden>
                                            <input type="text" name="Category_Name[]" id="Category_Name" value="<?php echo $Category_Name; ?>" style="width:100px;font-size:14px;text-align:center" readonly />

                                        </td>
                                        <td style="text-align:center">
                                            <?=$row['Model_Name']?>
                                        </td>
                                        <td hidden>
                                            <input type="text" name="Model_Name[]" id="Model_Name" value="<?php echo $Model_Name; ?>" style="width:100px;font-size:14px;text-align:center" readonly />

                                        </td>
                                        <?php
                                foreach($mon as $m)
                                {?>
                                        <td>
                                            <input type="number" class="form-control calc  <?=$m?>" name="<?=$m?>[]" style="width:100%;font-size:14px;text-align:center" />
                                        </td>
                                        <?php } ?>
                                        <td>
                                            <input type="number" name="Total[]" class="form-control Total" style="width:100%;font-size:14px;text-align:center" readonly />

                                        </td>
                                        <td>
                                            <input type="number" name="MS[]" class="form-control MS" style="width:100%;font-size:14px;text-align:center" readonly />

                                        </td>
                                        <td hidden align='center'>
                                            <input type="checkbox" checked="checked" class="checkbox" name='lang[]' value="1" />
                                        </td>
                                    </tr>
                                    <?php
                                } // end of data loop

                                // if there was any output, close out the last section
                                if($last_cat !== '')
                                {
	                                // close the previous section - output a total row
                                    ?>
                                    <tr>
                                        <td hidden>
                                            <td colspan="3" style="text-align:center">
                                                <?PHP echo $last_cat; ?> TOTAL
                                            </td>
                                            <?php
	                                foreach($mon as $m)
	                                {?>
                                            <td>
                                                <input type="number" class="form-control col_Total<?=$m?>" name="col_Total_<?=$m?>[]" style="width:100%;font-size:14px;text-align:center" readonly />

                                            </td>
                                            <?php } ?>
                                            <td>
                                                <input type="number" name="cat_Total[]" class="form-control cat_Total" style="width:100%;font-size:14px;text-align:center" readonly />

                                            </td>
                                            <td>&nbsp;</td>
                                    </tr>
                                </tbody>
                                <?php
                                }
                                ?>
                            </table>

                        </div>
                    </div>

                    <div style="text-align:center">
                        <input type="submit" name="addInvoice" class="addInvoice" id="addInvoice" value="Submit" style="width:100px;background: #0A2558;margin-top:10px; color:white;font-family:Cambria;border-radius:5px;height:30px;" />
                    </div>
                </form>

    <?php

   if(isset($_POST['addInvoice'])){


		$Dealer_ID = $_POST['Dealer_Name1'];

		$dealname = mysqli_query($conn, "SELECT * from users WHERE Emp_No = '$Dealer_ID'");
		$dealerrow = $dealname->fetch_assoc();
		$Dealername = $dealerrow['Name'];
        $Status = 1;

		$Year = date("Y");
		$previousYear = $Year - 1;

    	foreach ($_POST['lang'] as $ID => $VAL) {

		        $Category = $_POST['Category_Name'][$ID];
		        $Model= $_POST['Model_Name'][$ID];
		        $Jan = $_POST['Jan'][$ID];
		        $Feb = $_POST['Feb'][$ID];
		        $Mar = $_POST['Mar'][$ID];
		        $Apr = $_POST['Apr'][$ID];
		        $May = $_POST['May'][$ID];
		        $Jun = $_POST['Jun'][$ID];
		        $Jul = $_POST['Jul'][$ID];
		        $Aug = $_POST['Aug'][$ID];
		        $Sep = $_POST['Sep'][$ID];
		        $Oct = $_POST['Oct'][$ID];
		        $Nov = $_POST['Nov'][$ID];
		        $December= $_POST['Dec'][$ID];
		        $Total = $_POST['Total'][$ID];
			    $MS = $_POST['MS'][$ID];


			//$query = "UPDATE users SET Branch ='$Branch', Posting_Location ='$Posted_Location', Department='$Department', Divison='$Division', Emp_No='$Emp_No', Name='$Name', Designation='$Designation', User_Type='$User_Type', Functional_Role='$Functional_Role', Employed='$Employed', Mobile='$Mobile', Email='$Email', DOJ='$DOJ',  Qualifications='$Qualifications', DOB='$DOB', Approver1_ID='$Approver1_ID', Approver1_Name='$Approver1_Name', Approver2_ID='$Approver2_ID', Approver2_Name='$Approver2_Name' , Approver='$Approver', Gender='$Gender', Blood='$Blood' WHERE id = '$ida'";
		$query = "INSERT INTO lastyeardata (Dealer_ID, Dealername, Category, Model, Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, December, Total, MS,Status) VALUES ('$Dealer_ID','$Dealername', '$Category','$Model','$previousYear','$Jan', '$Feb','$Mar', '$Apr', '$May', '$Jun','$Jul', '$Aug', '$Sep','$Oct', '$Nov', '$December','$Total','$MS','$Status')";
        $result = mysqli_query($conn, $query);
        if($result)
                    {
                            // echo "<script type='text/javascript'> document.location = 'Users.php'; </script>";
                            echo '<script type="text/javascript">Swal.fire({
                            text: "Last Year Data Details added Successfully",
                            icon: "success",
                            showCancelButton: false,
                            confirmButtonColor: "#3085d6",
                            confirmButtonText: "OK"
                          }).then((result) => {
                            if (result.isConfirmed) {
                                window.location.href = "Last_Year_Data.php"
                            }
                          })</script>';
                
                    }
        else
            {
                    //echo '<script type="text/javascript"> alert("Details are Not Updated.")</script>';
                    echo '<script type="text/javascript">Swal.fire({
                        text: "Details are Not Updated. Please check the details entered.",
                        icon: "error",
                        showCancelButton: false,
                        confirmButtonColor: "#3085d6",
                        confirmButtonText: "OK"
                      }).then((result) => {
                        if (result.isConfirmed) {
                            window.history.back()
                        }
                      })</script>';
         }
     }
   }
    ?>

The calculated column total row is not updating on database. only the actual rows are updating. Please guide me how to update the column total row to databse

Barand · September 20, 2023

You should not be storing totals.

Your data should be correctly designed (normalized). Come back when it is and I'll be glad to help.

Senthilkumar · September 20, 2023

can you please guide me how to do this

Barand · September 20, 2023

21 minutes ago, Senthilkumar said:

can you please guide me how to do this

Last time I tried to do that you ignored it...

Senthilkumar · September 20, 2023

I did not ignored it. I was diverted to another work. So i thought i will ask you later regarding this.

As you said the model table i stored the Brand, Category and Sub-Category ID.

I storing the ID along wiht name on all my table just for reference.

Senthilkumar · September 20, 2023

You please suggest me correct way for both the questions

Barand · September 20, 2023

Your model table should be four tables..

+------------+
| category   |
+------------+
| id         |----+
| cat_name   |    |
+------------+    |
                  |
                  |     +---------------+
                  |     | subcategory   |
                  |     +---------------+
                  |     | id            |------+
                  +----<| category_id   |      |
                        | subcat_name   |      |
                        +---------------+      |
                                               |
                                               |
                                               |       +------------+
                                               |       | model      |
                                               |       +------------+
                                               |       | id         |
                                               |       | model_name |
                                               +------<| subcat_id  |
                                          +-----------<| brand_id   |
                                          |            +------------+
                                          |
                        +------------+    |                              
                        | brand      |    |                              
                        +------------+    |                              
                        | id         |----+                              
                        | brand_name |                                   
                        +------------+

Senthilkumar · September 20, 2023

the brand table is on the same format which you sent. In addition to that names are available on that table

Barand · September 20, 2023

57 minutes ago, Senthilkumar said:

In addition to that names are available on that table

They shouldn't be. The brand name is an attribute of the brand entity. That is the only place it should be stored in your database - not duplicated across dozens of records in other tables. Only id values should appear in other tables as foreign keys.

Suppose Elon Musk takes over your company and decrees that, from now on, the name "EXCAVATOR" should appear on all company web pages and reports as "X-CAVATOR". Now ask yourself "in how many places in your spreadsheet-style db tables would you have change that spelling?" In my design that answer would be 1.

Senthilkumar · September 20, 2023

I changed the model table. Only kept the id . not names

image.png.f9e758fa6608a077a4af9f211283823d.png

Quote

Now ask yourself "in how many places in your spreadsheet-style db tables would you have change that spelling?" In my design that answer would be 1.

You are correct. Now i understand that, why you ask me to change the databse

Barand · September 20, 2023

If you know the subcategory then you know the category, so you don't need both in the model table- just the subcategory id.

Senthilkumar · September 20, 2023

we cannot link category and subcategory. both are different.

Barand · September 20, 2023

So your are saying that your subcategories do not belong to a parent category?

Senthilkumar · September 20, 2023

yes

Barand · September 20, 2023

1) why, then, are they called subcategories?

2) what is the relationship between your categories and subcategories?

Senthilkumar · September 20, 2023

The subcategory is not common for all the category. Some category is linked only one category & some subcategory is linked with many category

Sign In

Real time Java script calculation for update Row & column total

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information