mac_gyver

don't bother with the mysqli extension. it is overly complicated, inconsistent, and in the case of procedural vs OOP statements, has different error responses for the same root problem. instead, use the much simpler, consistent, and more modern PDO extension. in php8+, both the mysqli and PDO extensions use exceptions for errors by default. the line of code that @Barand posted enables exceptions for errors for the msyqli extension. when you use exceptions for database statement errors, php will 'automatically' display or log the raw database errors, via an uncaught exception error. therefore, you can remove any existing error handling logic, since it won't ever get executed upon an error, simplifying the code. you should also name the connection variable as to the type of connection it contains, $mysqli/$pdo. this will let anyone looking at the code know what extension it is using, and also let you search for which code has been converted and which hasn't. you also need to use a prepared query when supplying external, unknown, dynamic values to a query when it gets executed, so that any sql special characters in a value cannot break the sql query syntax, which is how sql injection is accomplished. if you switch to the much simpler PDO extension, after you prepared the query, you can simply supply an array of the input values to the ->execute([...]) call.

the database server must find the row(s) being update. it then reads the data in those row(s), for two main reasons - if a column value doesn't change, it doesn't write the data back to the disk, skipping an operation. the database server performs this comparison for you, using the current 'locked' values from the row, in one ATOMIC operation, so that you are not potentially comparing new values with 'stale' data that could have been changed by some other concurrent operation on the data. so that you can perform things like SET count = count + 1 ... in a single query

ps, everyone on the same network will have the same ip address. if you are going to use a database based cart, you need to generate a unique value per visitor to use to identify each cart. the simplest way of doing this is to start a session, then use the session id as the identifier. pps, your cart should have a quantity column, so that someone could have more than one of each item.

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.

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

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>

the example you are using is actually from the link that @maxxd posted this is using the PDO extension. did you change your connection code to use the PDO extension? you would be getting a fatal runtime error if you didn't. this can actually be further simplified by using FIND_IN_SET() or NOT FIND_IN_SET(), for the query you are showing us, and using a single prepared query place-holder. the code you posted is fetching all the matching rows of data into $a_data. it is this variable that you would test/loop over to produce the output in the html document. if you set the default fetch mode to assoc when you make the PDO connection, you don't need to specify it in each fetch statement, simplifying the code. the only time you should handle database statement exceptions in your code are for user recoverable errors, such as when inserting/updating duplicate or out of range data. in all other cases, simply do nothing in your code and let php handle the exception, simplifying the code.

all of this code is unnecessary. you can use php's ... operator. see the Argument unpacking via ... example at this link - https://www.php.net/manual/en/migration56.new-features.php

after you have made a connection using the PDO extension, in a variable named $pdo (so that anyone looking at the code will get a hint at which extension it is using), the database specific code would be - // prepare the query. this returns a PDOStatment object, hence the variable named $stmt $stmt = $pdo->prepare($query); // execute the query, supplying an array of input values to the ->execute() call that correspond to the positional ? place-holders in the sql query statement $stmt->execute([$from, $to]); at the point of fetching the data from the query - while($row = $stmt->fetch()) { // use elements in $row, e.g. $row['employee_name'] } typical PDO connection code - $DB_HOST = ''; // database host name or ip address $DB_USER = ''; // database username $DB_PASS = ''; // database password $DB_NAME = ''; // database name $DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4 $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions (this is now the default in php8+) PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc, so that you don't need to specify it in each fetch statement ]; $pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

this might be a good time to switch to the much simpler and more modern PDO extension. you can simply and directly fetch and use data from a prepared query in the same way as for a non-prepared query.

my comments apply to all the code. since barand started with your code and added things to demonstrate how to make it work - input conditioning, default input values, a prepared query, a working get method, sticky, search form, and left the remainder as is, yes, these comments apply to the result of adding these programming practices to the starting code.

some points about the code/query - when you have a list of ORed terms in a query, use a single IN() comparison instead. don't put quotes around numbers. be consistent. one number in the query isn't quoted. use 'require' for things your php code must have for it to work. for a <label> tag to work, you must either put the correspond form field somewhere between the opening and closing label tags, with the label text, or you must put a for='...' attribute inside the opening label tag and a corresponding id='...' attribute inside the form field. you should validate your resulting web pages at validator.w3.org any dynamic value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting.

your column names imply that sales.sales_repid contains matching reps.rep_id values. if the query didn't produce any result, that indicates there isn't matching data, either in the join condition or in the where clause. you would need to post an example of the data that the query should match for anyone here to have a chance at helping with the problem.

the fun part in programming is learning new things and in succeeding in getting the computer to do what you want. copy/pasting things someone else has written isn't programming. i had never previously dynamically built a hyperlink using javascript. i began by researching (i.e. keep searching) until i found how to set the href attribute and how to set the innerText using javascript. since the method i used above uses jquery and is finding the class names within each row, i starting by giving an empty hyperlink markup a class name of 'file' - <a class='file' target="_blank"></a> then the code to dynamically build the hyperlink, using the javascript Directory and Document variables, became - $('#Aid_'+Aid).find('.file').attr("href", '/'+Directory+'/'+Document); $('#Aid_'+Aid).find('.file').text(Document); you would need to research what these would need to be when not using jquery (my previous post above names the two attributes) and using the composite ids being used in the code you copied.

since your version didn't even put values into the json data, neither of the respondents attempted to fix that part of the code. i recommend that you supply the directory and document values separately in the json data. in the ajax success code, you would create javascript variables for both of those values. since this isn't a form field, it doesn't have a .value attribute (what your code was trying to do.) to set the href value, you would set the .href attribute with a relative url made up from the directory and document values. to set the display text, you would set the .innerText attribute with the document value.

your database design is not normalized. this results in repeating the same data values over and over. you should have a column for, and store the Ass_ID value in the mykra table, instead of repeating the 'Point_Description' and 'Point_Marks' values in every row in that table. doing this will also simplify and help secure the form code, since you will no longer be passing the description[] and rating[] fields through the form, and it will simplify the ajax related code. some points about the code - you need to stop copying variables to other variables for nothing. this is just a waste of your time typing. just use the original variables that data is in. you need to go through the code and eliminate all the unused code and unused variables. an empty action='' attribute is not valid html5. to cause a form to submit to the same page it is on, leave out the entire action attribute. you cannot set the value attribute of a type='file' field, so you might as well remove that from the ajax code (you aren't setting a 'file' value in the json data, so that code wasn't doing anything anyways.) you would want to build/output the href link using directory/document data. if there is a validation/user error in the post method form processing code, the form fields need to be 'sticky' and repopulate their values with any existing form data, so that the user doesn't need to keep reentering/editing values over and over. you havn't posted it yet, but the insert/update query (there's a single query to do this) could result in duplicate data. you need to handle this in the code and setup an error message for the user letting them know what was wrong with the data that they submitted, so that they can modify the offending value and resubmit the data again. you need to validate the resulting web pages at validator.w3.org as to getting this to work, the Ass_ID value is the 'key' to finding the correct <tr> row of form inputs to operate on. it is also the 'key' to inserting/updating the correct rows of data in the database table when the form is submitted. in the html table/form code, you need a hidden array field with the Ass_ID values in it. this will let you associate the submitted Ass_ID values with the corresponding data from the other form array fields. you also need a way of referencing the <tr> for each row. to do this add an id='...' attribute, such as - <tr id='<?='Aid_'.$row['Ass_ID']?>'> in the Mykra_view.php code, you need to build the json data with the Ass_ID value, such as - $return_arr[] = array( 'Aid' => $row['Ass_ID'], // add the Ass_ID to the json data "target" => $row['Marks_Target'], "actual" => $row['Marks_Actual'], "date" => $row['DOS'], "remarks" => $row['Marks_Description'] ); then in the ajax success code, you would get the Aid value and use it to select/find the correct form field value to set with the data - <script> function viewData() { var month = document.getElementById("Month").value; var employeeid = document.getElementById("employeeno").value; $.ajax({ type: 'get', dataType: 'JSON', url: 'Mykra_view.php', data: 'Month=' + month + '&employeeno=' + employeeid, success: function (response) { var len = response.length; for (var i = 0; i < len; i++) { var Aid = response[i].Aid; var target = response[i].target; var actual = response[i].actual; var date = response[i].date; var remarks = response[i].remarks; $('#Aid_'+Aid).find('.target').val(target); $('#Aid_'+Aid).find('.actual').val(actual); $('#Aid_'+Aid).find('.date').val(date); $('#Aid_'+Aid).find('.remarks').val(remarks); } } }); } </script> to make the above work, like in your previous thread, you would change the id='...' attributes to class='...' attributes (you would actually add the class names to the existing class='...' attributes.)

the only redirect you should have in your post method form processing code should be upon successfully logging in and it should be to the exact same URL of the current page to cause a get request for that page. if you don't do this, anyone can use the browser's developer tools network tab to see what the submitted username and password are. if you want the user to be able to go to a different page, provide navigation links, or ever better, integrate the login system with any page that needs it. by putting the form and form processing on different pages and doing this - header("location:login.php?msg=error"), you are opening your site to a phishing attack, where someone can trick your users to enter their username/password on the phishing site, then redirect to your site and make it look like they mistyped their information.

the column in question in the bandstage table is apparently mybandid

so, how about the var_dump output? if you actually use all the points made, including switching to the much simpler and more modern PDO database extension, you will get code that looks like this (untested) - <?php // initialization session_start(); $errors = []; // array to hold user/validation errors // post method form processing if($_SERVER['REQUEST_METHOD'] === 'POST') { // validate input data if(!isset($_SESSION['id'])) { $errors['logged_in'] = 'You must be logged in to delete your bandstage data.'; } // if no errors, use the input data if(empty($errors)) { require 'pdo_connection.php'; $sql = "DELETE FROM bandstage WHERE id = ?"; $stmt = $pdo->prepare($sql); $stmt->execute([ $_SESSION['id'] ]); } // if no errors, success if(empty($errors)) { // note: if you want to display a one-time success message, store it in a session variable, then test, display, and clear that variable in the html document $_SESSION['success_message'] = 'You have successfully deleted your bandstage data.'; // redirect to the exact same url of the current page to cause a get request for the page - PRG Post, Redirect, Get. die(header("Refresh:0")); } } // get method business logic - get/produce data needed to display the page // html document // note: this in incomplete and only shows the parts corresponding to this example code ?> <?php // display and clear any success message if(isset($_SESSION['success_message'])) { echo $_SESSION['success_message']; unset($_SESSION['success_message']); } ?> <?php // display any errors if(!empty($errors)) { echo implode('<br>',$errors); } ?>

using var_dump() on the session variable (see my reply above) will help identify if it contains any non-printing/white-space characters. if by that you mean the header() statement. no, he told you to put that line of code - at the header() statement, it is too late to put in that line of code. does that mean you have multiple databases or does that mean you actually have only one database, but it has multiple tables in it? if you do have multiple databases, are you selecting the correct one and is that the same one you are looking at to see if the records are being deleted?

what result or output are you getting on this page? what does adding the following, after the session_start(), show - var_dump($_SESSION['id']); a bunch of points about the posted code - the php error related settings should be in the php.ini on your system. if you put the database connection code in a separate .php file, you can just require it when needed. this will save you from having to redact the connection credentials when posting the code. the line that barand posted, which enables exceptions for errors, should always be part of your code. this is also the default setting in php8+ don't output raw database connection onto a web page or tell the visitor that a database error occurred. this only helps hackers. when using exceptions for database statement errors, the error handling logic you have now won't ever be executed upon and error and should be removed, simplifying the code. you should set the character set when you make the database connection to match your database tables, so that no character conversion occurs over the connection. you should be using a post method form when performing an action on the server, such as deleting data. if this a get method button/link, when a search engine indexes your site, you could end up with a bunch of your data getting deleted. $_SESSION['id'] is an 'input' to your code. you must validate it before using it. technically, you should trim() it before validating it if it could have accidentally gotten modified when putting the value into the session variables. don't copy variables to other variables for nothing. just use the original variables. you should also be using a prepared query when supplying external, unknown, dynamic value to a query when it gets executed. php automatically destroys all resources on a page when your script ends. there's no need to close database connections in your code. upon successful completion of post method form processing code, you should perform a redirect to the exact same url of the page to cause a get request for that page. this will prevent the browser from trying to resubmit the form data should the user reload or navigate away from and back to that page.

exactly what settings and what values did you set them to? you should set error_reporting = E_ALL (this value is case-sensitive) and set display_errors = On (this value is not case-sensitive) and these lines should not be commented out. did you stop and start your web server to insure that any changes to the php.ini took affect? what does the phpinfo() output show for these settings after you have attempted to set them and restarted the web server?

i don't think anyone understands, based on the description, what the overall work flow is. could you describe or show what the user should see at each step. you state the html table should be empty while the page is loading, but you show php code looping over the result of some not-shown query producing that html table output. if the purpose of this php code is to produce a 'template', which the ajax success code is supposed to populate with values, you need to tell us? you would also not use a loop. you would only output one instance of the markup. the only thing i can tell you based on the posted attempt is what has been written in your threads before, you cannot repeat ids in the markup. all the code like - id="Ass_Description" will not work when there is more than one instance on a page. you need to use the technique from the last thread, where class names are used, then you use the .find() method to reference each element.

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

the boolean value returned by the exaction of a SELECT query only indicates that the query executed without error (a true value) or failed due to a query error (a false value.) it doesn't indicate that the query match any data. a query that doesn't match any data is a successful query, but returns an empty result set. there's a single query that does what you want. an INSERT ... ON DUPLICATE KEY UPDATE ... query. you would define the uid column as a unique index if it is not already defined that way. there's no need to try to select data in order to decide if you should insert new data or update existing data and in fact, there's a race condition in the existing code where multiple concurrent instances of your script can all find that data doesn't exist and will all attempt to insert the same data, resulting in duplicates or in errors. also, use a prepared query when supplying external, unknown, dynamic values to a query when it gets executed. there's two reasons for doing this - 1) addslashes() doesn't protect against every possible character that can break the sql query syntax, since it doesn't take into account the characters set of your database tables, which is why php remove its ridiculous magic_quotes a long time ago, and 2) it will provide a performance increase when executing the same query more than once within one instance of your script.