# Real time Java script calculation for update Row & column total

Go to solution Solved by Senthilkumar,

## Recommended Posts

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.

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">
<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>

<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>

\$(".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.

##### Share on other sites

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/>`

* 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();
});
});
});```
##### Share on other sites

Posted (edited)

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">
<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>
<?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>
\$(".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 by mac_gyver
##### Share on other sites

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>
\$(".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

\$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>
<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">
<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>
<?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">
</div>
</form>```

##### Share on other sites

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'));```

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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)
");
exit;
}

################################################################################
################################################################################

\$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);

foreach (\$range as \$d) {
\$theadings .= "<th>" . \$d->format('My') . "</th>";
}

################################################################################
# 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'>
<meta charset='utf-8'>
<title>Example</title>
<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 {
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>
<body>
<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">
<?= \$tdata ?>
</table>
<div class='w3-container w3-center'>
<button class='w3-button w3-blue'>Save data</button>
</div>
</form>
</div>
</body>
</html>```

##### Share on other sites

• Solution

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.

##### Share on other sites

• 3 months later...

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">
<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>
<?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">
</div>
</form>```
```    <?php

\$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

##### Share on other sites

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.

##### Share on other sites

can you please guide me how to do this

##### Share on other sites

21 minutes ago, Senthilkumar said:

can you please guide me how to do this

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

##### Share on other sites

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.

##### Share on other sites

You please suggest me correct way for both the questions

##### Share on other sites

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 |
+------------+        ```

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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

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

##### Share on other sites

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

##### Share on other sites

we cannot link category and subcategory. both are different.

##### Share on other sites

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

yes

##### Share on other sites

1) why, then, are they called subcategories?

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

##### Share on other sites

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

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.