Leaderboard

"Incorrect string value" means you're trying to insert data that isn't valid for the column. In your case, the column is defined as utf8mb4 (aka UTF-8) but your string is not UTF-8. If the value is binary data then do not use VARCHARs in the first place. Those are for character data. Use VARBINARY instead. https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html Either that, or you aren't supposed to be inserting raw binary data but either hex digits or a base-64 encoded version of the data...

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>