mac_gyver

if you had properly stored your data as has been previously suggested in your threads, with date and amount columns and a row for each piece of data, this would be a simple task that could be accomplished in a straight-forward sql query. but because you have a year column and separate month name columns, your code must take the year and month from the 'from' and 'to' values and figure out which rows and which columns to use to calculate the result.

to normalize this data, you need an attribute table, with - id (autoincrement primary index) and name columns. as new attributes are defined, they would be inserted into this table. this table establishes attribute ids. the map table would map the csv column number to these attribute ids. you would JOIN with this table when displaying information to get the meaningful names for each attribute id. the stock table would not be laid out as a spreadsheet. you would instead insert only the unique one-time information for each item into the stock table. this would establish a stock id for each item for each user/feed. you would then have a stock attribute table with - id (autoincrement primary index), stock id, attribute id, and value columns. you would insert a separate row into the the stock attribute table for each csv column value for each stock id. when you are inserting the data, you would query the map table to get the set of rows for the user/feed and fetch these into an array. when you read each row of data from the csv file, you would then loop over this array of map data, use the csv column number to get the data value from the row of csv data, then use this data value and the attribute id for executing the stock attribute insert query.

the following is the syntax definition for a SELECT query - SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] the relevant parts that you need for this query are - SELECT select_expr , select_expr ... FROM table_references WHERE where_condition ORDER BY {col_name | expr | position} [ASC | DESC], ... you should list out the columns you are selecting, i.e. don't use * there would only be one FROM keyword in this particular query. the table_references section is where the JOIN part of the query goes. the where_condition would contain the columns, comparison operators, and prepared query place-holders for the brand and location matching. almost every query that can return a set of rows should have an ORDER BY ... term so that the result is in a desired order. the existing sql statement should be producing a query error and probably php errors. you should use exceptions for database statement errors (which is the default now in php8+) and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) you would set the PDO error mode to exceptions when you make the database connection. you should also set emulated prepared queries to false, so that you use true prepared queries. do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system, so that php will report and display all the errors it detects (which will now include database statement errors)? as to the rest of the code - the form processing and form should be on the same page. this will result in the least amount of code and provide the best user experience. the code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document. you need to validate the resulting web pages at validator.w3.org don't prepare and execute a non-prepared query. just use the ->query() method. if you set the default fetch mode when you make the database connection, you won't need to specify it in each fetch statement. don't use the ->rowCount() method. it is not guaranteed to work with SELECT queries. since you have fetched all the rows of data from the query into a php variable, you can just test that variable to determine if the the query matched any rows. for the required attribute to work for a select/option menu, the value attribute for the 1st option/prompt choice must be an empty string (the w3.org validator will point out this problem.) the form should be 'sticky' and re-select the option choices that match any existing search data, so that if the search doesn't match things the user is interested in, the user can just make a different selection and resubmit the form. in the location query, listing the column name twice does nothing. you only need to specify the column name once in the query. you can reference it as many times are you want in the result set. if you put the label tags around (before/after) the field they belong with, you can eliminate the for='...' and corresponding id='...' attributes, which will eliminate more of the markup errors (ids must be unique.) if you use implicit binding, by suppling an array of values to the ->execute([...]) call, you can eliminate the bindParam() statements. if you use simple positional prepared query place-holders ?, you can eliminate the repetitive typing of place-holder names in the query and php code.

the way to correct and simplify this is to - this will eliminate all the sumOpenAndAcceptedValueByClient() usage. the single query will give you the quote total (accepted and open) and open total (open only) values per client_id. you would then just use those values as you are looping to produce the output. i hope you are not doing this in every function that needs a database connection? this is creating multiple database connections per instance of your script and a database connection is one of the slowest operations you can perform. your main code should create one database connection, then supply it to any function that needs it as a call-time parameter. you should also use 'require' for things that your code must have for it to work. if you eliminate all the bindColumn() statements and just fetch each row into $row, you would reference each column value as $row['column_name'], which is probably how the code was originally written. this will also eliminate the multiple different names you are using for the same meaning data. the id/client_id will never be null from this query since it is from the primary table. this conditional test is unnecessary.

this is just indexing/pivoting the data by a column. PDO has a fetch mode that will 'automatically' do that for you, assuming that the sectionName is the first column in the SELECT ... list - $data = $stmt -> fetchAll(PDO::FETCH_GROUP); if for some reason you require the name values to be referenced as itemName in the fetched data, assign it that name as an alias in the sql query.

the place-holder mismatch will work using emulated prepared queries (which should be avoided whenever possible), but not for true prepared queries. the emulator is more of an approximator, because there are differences that you can detect between how it works vs a true prepared query.

the user table would hold the common one-time information. you would have a second table holding the unique information for the different types of users, with a separate row for each piece of information, related back to the user table through the user id.

the problem is you are using the $password variable for two different things. the code for any page should be laid out in this general order - initialization post method form processing get method business logic - get/produce data needed to display the page html document you should have one user database table with both client and psychologist registration/login data and a 'type' column holding a value that indicates which type of user they are. Don't Repeat Yourself (DRY.) it is not the responsibility of the sendOTPmail() function to create a database connection. you already have a database connection in the application, supply it to any function that needs it as a call-time parameter.

what's the difference between accepted and open and why doesn't the first query use accepted = 1 too? i'm betting that the $out = ""; variable in the first posted code is being used to build the output in the image, listing the results by the left-hand ranges (guessing per client), and the echo statements are just there for debugging? this is going to be a case of - don't show us your non-working attempt and expect us to figure out what's wrong, instead show us sample data and what result you expect from that data. also - don't waste your time with bindColumn() statements, this is wasted typing. just fetch the data and reference the data by its associative index (column) name. lastly, why are you preparing a non-prepared query that doesn't have any values being supplied to it? just directly use the ->query() method.

while there probably is a way of doing this in a query, with a sub-query/self-join, i would just perform the calculation when you produce the output.

you would add GROUP BY client.id to the query to get a SUM() per client. in fact, if your database was set to STRICT mode, you would be getting an error with the current query about using an aggerate function without a GROUP BY term. then, if you want both the SUM() per client and the grand total, you can add WITH ROLLUP to the end of the GROUP BY ... term.

client-side validation is a nicety for legitimate visitors. since you must validate the data on the server before using it, why go to all this trouble to duplicate the logic in the client that you (already) have in the server-side code? and if you want to pre-validate the data in the client, why not just send the relevant data values to the server using ajax, run it through the (existing) validation logic, and display the result returned by that validation logic?

what item? we cannot help you unless you post the complete code the reproduces the problem. in programming, there are just too many different ways of accomplishing a task. it takes knowing how you go to a point, in order to help you.

you would use the same method that the 'add more click' function is using, of starting with the current element (this) and using tree traversal methods with class selectors to navigate around in the DOM - https://api.jquery.com/category/traversing/tree-traversal/

this javascript was apparently part of a php echo statement at one time, since it is trying to concatenate $row['id'] inside a double-quoted string. you cannot just take something that was in a php context and use it by itself without reading what it is doing and modify it appropriately. before you can use ajax to make a http request, you must be able to design, write, test, and debug the html and php to accomplish the task. you will need most of the same code and that code must be secure, provide a good user experience, and contain error handling and validation logic so that it will either work or it will tell you why it doesn't, since adding ajax adds a layer on top of everything that makes debugging harder. next, the point of using a post method form/request when performing an action on the server, is so that a search engine indexing a site won't trigger those actions. the post method form processing code (regardless of using ajax or not) should - detect if a post method form was submitted before referencing any of the form data only use $_POST data enforce user 'delete' permission and data ownership/administrator-ship to control who can delete data trim, then validate all input data before using it use a prepared query so that any sql special characters in a value cannot break the sql query syntax, which is how sql injection is accomplished. to accomplish item #2 on this list, you would use a hidden field in the form for the $row['id'] value. this is the point of the ajax data being set to the form's data - data: $(this).serialize()

if you are not posting the actual code that you are having a problem with, the replies you get may not have anything to do with the problem. here's example code (tested with faked data from the SELECT query) showing most of the points that i made - <?php // initialization // recursive trim function function _trim($val) { if(is_array($val)) { return array_map('_trim',$val); } else { return trim($val); } } session_start(); // make database connection here... $table_name = 'your_table'; $error = []; // an array to hold user/validation errors $post = []; // an array to hold a trimmed working copy of the form data // get all subject data $sql = "SELECT id, subject_name FROM tbl_subjects_secondary"; $stmt=$pdo->query($sql); $subject_data = $stmt->fetchAll(); // post method form processing if($_SERVER['REQUEST_METHOD'] === 'POST') { // trim all the input data at once $post = _trim($_POST); // loop over the subject ids and validate the corresponding form data foreach(array_column($subject_data,'id') as $key) { if($post['test_score'][$key] === '') { $error['test_score'][$key] = "Test Score Field is Required"; } if($post['exam_score'][$key] === '') { $error['exam_score'][$key] = "Exam Score Field is Required"; } } // if no errors, use the submitted data if(empty($error)) { $sql = "INSERT INTO $table_name ( subject_id, test_score, exam_score ) VALUES ( ?, ?, ? )"; $stmt = $pdo->prepare($sql); foreach(array_column($subject_data,'id') as $key) { $stmt->execute([ $key, $post['test_score'][$key], $post['exam_score'][$key] ]); // note: error handling for this query is not included with this example code } } // if no errors, success if(empty($error)) { // 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 inserted the subject scores'; // redirect to the exact same url of the current page to cause a get request - PRG Post, Redirect, Get. die(header("Refresh:0")); } } // get method business logic - get/produce data needed to display the page // get all subject data - located above the post method form processing since it is also used by the validation logic // html document ?> <!DOCTYPE html> <html lang="en-US"> <head> <meta charset="utf-8"> <title>Multi-row Insert Example</title> </head> <body> <?php // display and clear any success message if(isset($_SESSION['success_message'])) { echo '<p>'.$_SESSION['success_message'].'</p>';; unset($_SESSION['success_message']); } ?> <?php // display the form ?> <form method="post"> <table class="table table-borderless"> <thead> <tr> <th>SN</th> <th>Subject</th> <th>Continuous Assessment Score</th> <th>Examination Score</th> </tr> </thead> <tbody> <?php $i = 1; foreach($subject_data as $row) { ?> <tr> <th><?= $i++ ?></th> <td><input type="text" class="form-control border-0" readonly value="<?=$row['subject_name']?>"></td> <td><input type="number" name="test_score[<?=$row['id']?>]" class="form-control" value="<?=htmlentities($post['test_score'][$row['id']]??'',ENT_QUOTES)?>"><span style="color: red; font-size: .8em;"><?= $error['test_score'][$row['id']]??'' ?></span></td> <td><input type="number" name="exam_score[<?=$row['id']?>]" class="form-control" value="<?=htmlentities($post['exam_score'][$row['id']]??'',ENT_QUOTES)?>"><span style="color: red; font-size: .8em;"><?= $error['exam_score'][$row['id']]??'' ?></span></td> </tr> <?php } ?> <tr> <td></td><td></td> <td colspan="2"><button type="submit" class="btn btn-primary w-50">Save</button></td> </tr> </tbody> </table> </form> </body> </html> this produces valid markup, validation per field, and repopulates the field values upon a validation error.

here's an additional point - if there are validation errors and you redisplay the form, you would populate the field values from the submitted form data so that the user doesn't need to keep reentering data over and over. they can just correct the errors and resubmit the form.

here's my typical laundry list of points for the posted code - you should not attempt to detect if a submit button is set. there are cases where it won't be. just detect if a post method form was submitted. you should trim all input data before validating it. you should validate all inputs separately, storing validation errors in an array using the field name as the main array index. since you have a set of inputs for each field name, you would also use the $key as a second index when storing validation errors. by validating the inputs separately, you can eventually get your logic attempting to display the errors adjacent to the corresponding field to work. after the end of the validation logic, if there are no errors (the $error array will be empty), use the submitted data. $table_name - if you are creating a bunch of different tables to hold this data, you should instead have one table with a column that identifies what is different about each set of data. the subject column in the table holding the submitted data should be the subject id, not the subject name. you should prepare the query once, before the start of the looping. it will be a poor user experience if you produce an alert for every row that gets inserted. you should instead store any result message in an array using the $key as the array index, then test/use the contents of this array, after the end of the looping. for this query, the data will be inserted unless there is a query error. most query errors are due to programming mistakes that won't occur once you have tested and debugging your application. you should use exceptions for database statement error handling. this is now the default setting in php8+. if this query can produce errors due to duplicate or out of range user submitted values, you would catch the database exception from it, test if the error number is for something that your code is handling, and setup error messages (add to the $error array) letting the user know what was wrong with the data that they submitted. for all other error numbers, just rethrow the exception and let php handle it. since this data is a set, this INSERT query should be part of a transaction, that will get rolled back if there are any errors for any of the inserts. you need to validate the resulting web pages at validator.w3.org, e.g. you can put a html table inside a form and you can put a form inside a single html table cell, but you cannot spread a form out inside a html table. an empty action='' attribute is not valid html5. just leave the action attribute out to get the form to submit to the same page it is on. the code producing the output is using 'subject_name', not 'subjects' and you would not test if it isset() to echo it, since this is the whole point of this code. it must be set or you have a programming mistake somewhere. there should be a subject id, and this should be used when storing the submitted data. you would either have a separate hidden array field for the ids or you would use the ids as the index for the existing array fields. if you set the default fetch mode to assoc when you make the database connection, you won't have to specify it in each fetch statement.

yes, but a prepared query's primary purpose is only to prevent sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished, provided you are either using a true/actual prepared query (requires a specific configuration option to be set when the database connection is made) or if using an emulated prepared query (which should be avoided whenever possible), you have set the character set when you make the database connection to match your database table's character set (this should be set in any case, so that a character conversion won't occur over the connection, but is rarely set at all.) if the code leading up to the query doesn't correctly enforce user permissions, detect if a form has been submitted, validate data, or halt php code execution when needed, the execution of the query will occur with whatever data was supplied to it by whoever or whatever submitted the data to the web page. as to the code/query you have posted above - if you build the sql query statement in a php variable, it makes debugging easier, helps prevent typo errors by separating the sql query syntax as much as possible from the php syntax, and allows you to see common code used for each query that can be consolidate into user functions/classes to eliminate repetitive code. the email column must be defined as a unique index, this query then needs exception error handling to detect if a duplicate index error (number) has occurred, and setup a message for the user that the email address is already in use. there should be a single DATE column for the date of birth, so that you can easily perform comparisons, sorting, formatting, or date arithmetic on the value. for the static/literal values, you can just put them directly into the sql query statement. exceptions should be used for database statement errors. this is the default setting now in php8+. when using exceptions for database statement errors, you would remove any existing conditional error handling logic (it will no longer get executed upon an error and any true conditional logic will always be true.) also, refer to item #8 in my first post in this thread and item #13 in my second post. if the query uses simple ? positional prepared query place-holders and implicit binding, by supplying an array to the executed call, all that verbose copy/pasting and typing can be eliminated. and by dynamically processing the data, using a data-driven design, you can eliminate all the bespoke logic building and executing the query entirely.

here's a point i saw while looking at the logic in this code that shows just how badly it is written - by first using empty() to test the input data, then trimming it later, values that consist of all white-space characters will pass the empty() test, but become an empty string after the trim() call, causing this code to use empty data values.

given that the redirects you have shown don't halt php code execution, by having an exit/die statement, if pages that should require a logged in user to access them can be accessed anyways, it is likely that the login check code on the 'secured' web pages doesn't halt php code execution if there is no logged in user. if there's no exit/die statement, all the rest of the code on the page still executes while the browser is performing the redirect. the first posted code, for the places edit processing, doesn't show any login check code at all (though it could be in the config.php file.) if this code 'assumes' that the only way to reach the protected pages is because this code redirected the user to that page, this is incorrect. anyone or anything can request any page. each 'secure' page must test for and prevent access by non-logged in users.

do you have any specific questions about any of the points that were made? if you are just starting out, i recommend that you start with one form field of one type, e.g. text since it is the most common type, and get your code fully working and tested with that single form field. then, pick one field of a different type, and repeat for each type of field. then you can worry about all the code needed for the rest of the fields.

addition points about the posted code - keeping the form data as a set in a php array variable and dynamically validating/processing the data will eliminate most of that code. i see you have an array to hold errors, but are not using it. by definition all post/get/cookie data are strings, regardless of the value they hold, and using is_string() on them will always be true. if you use simple ? positional prepared query place-holders and use implicit binding, by supplying an array to the ->execute([...]) call, all the database specific code will be simplified. 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. don't copy variables to other variables for nothing. just appropriately name and use the original variables that data is in. don't run multiple queries to get different pieces of data from the same table and row. list out the columns you are SELECTing in a query. you can use the PDO fetchAll() method instead of looping to build an array of the fetched data. when you loop to build the multi-row insert query VALUE terms, you can build the array of inputs that you will supply to the ->execute([...]) call in the same loop. also, if you build the terms in an array, then implode the array with the ',' character, you can eliminate the $i variable and the conditional logic. for the SELECT COUNT(*) ... query, you can just use the fetchColumn() method to get the count value. the INSERT INTO photos... query should be prepared once, before the start of the looping, then just call the execute([...]) method with each set of values as an array to the execute call inside the loop. you would only catch database exceptions for user recoverable errors, such as when inserting/updating duplicate or out of range submitted values. for all other database errors, just let php catch and handle the exceptions, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.)

the code for any page should be laid out in this general order - initialization post method form processing get method business logic - get/produce data needed to display the page html document you should keep the form data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code. this will eliminate the need to create discrete variables for every field and will let you do things like trim all the data at once, with one single line of code, dynamically validate the data, using a data driven design, and dynamically process the data, so that you don't need to write out repetitive code for every field. you should first trim, mainly so that you can detect if all white-space characters were entered, then validate all input data before using it, storing user/validation errors in an array using the field name as the main array index. to enforce user permissions, you would test at the earliest point in whatever operation you are performing if there is a logged in user and if that user has permission to perform the current operation and is the owner or an administrator of the data being being operated on. in your current code, the permission test for ownership of the place data is somewhere near the middle of the code. this should be right after the point where you have determined that there is form data and that the submit_token is valid. the post method form processing code should - detect if a post method form was submitted before referencing any of the form data. detect if the total size of the form data exceeded the post_max_size setting. if this occurs, both the $_POST and $_FILES data will be empty and there's no point in trying to use any of the form data because it won't exist. trim all the form data at once. verify the submit_token. determine ownership of the data being edited. validate all the input data. after the end of the validation logic, if there are no errors (the array holding the user/validation errors is empty), use (process) the form data. if the processing of the form data could produce duplicate errors for data that must be unique, you would detect this and add errors to the array holding the user/validation errors letting the user know what was wrong with the data that they submitted. after processing the form data, if there are no errors, redirect to the exact same url of the current page to cause a get request for that url. this will prevent the browser from trying to resubmit the form data if that page is browsed away from and back to or reloaded. every redirect needs an exit/die statement to stop php code execution. if you want to display a one-time success message, store it in a session variable, then test, display, and clear that session variable at the appropriate location in the html document. if there are errors at item #7 or #9 on this list, the code will continue on to display the html document, test for and display any user/validation errors, redisplay the form, populating field values with the submtited form data. any value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting. to allow you to initially query for the existing data, then use the submitted form data to populate the form fields, you would only query to get the existing data if the form has never been submitted, then fetch the data into the same php array variable that has already been mentioned for holding the form data.

here's a different approach - <?php echo '<pre>'; print_r($_POST); echo '</pre>'; ?> <img src="btn/colorpicker/darkred.png" data-color='darkred' onClick="pic_color(this)" class="pointer clrimg"> <img src="btn/colorpicker/yellow.png" data-color='yellow' onClick="pic_color(this)" class="pointer clrimg"> <img src="btn/colorpicker/purple.png" data-color='purple' onClick="pic_color(this)"class="pointer clrimg"> <script> function pic_color(el) { // set the form field to hidden document.getElementById('name_pref').type = 'hidden'; // set the form field value to the current element's data-color value document.getElementById('name_pref').value = el.dataset.color; // get the src attribute from the current element and set the pref_img src attribute document.getElementById('pref_img').setAttribute('src',el.getAttribute('src')); // display the pref_img element document.getElementById('pref_img').style.display = 'block'; } </script> <form method='post'> <input type="text" name="name_pref" id='name_pref' style="font-size: 24px;" value=""> <img style="display:none" id='pref_img' class="pointer clrimg"> <br> <input type='submit'> </form>