mac_gyver

you would use a JOIN query between the posts and users table to get the username. by storing the username in with the posts - 1) it takes more storage. for both the data and any index on the column 2) the query will be slower 3) if a username is ever edited, you will have to insure that it gets changed every place it is stored. storing the user_id in with the posts, uses the least amount of storage, results in the fastest queries, and allows the username to be easily edited, since it will only be stored in the users table.

stop, or at least slow down. you should not store the username in with posts. the only place usernames should be stored is in the users table (whatever you have named it.) the users table should, via an auto-increment column, assign user ids. you would store the user id in the session variable and in any database tables holding information related to users. you would use the user id from the session variable to query for any data related to the current user. your posts table will then have an id/post_id auto-increment column, a user_id (integer) column and a body column (you probably should have a date or datetime column, so that you will know when the row was created.) the WHERE clause in the query would match the user_id column with the user_id from the session variable. next, do not put data directly into sql query statements. use prepared queries (you can research on the web or in the php.net documentation to find out what that means) and use the php PDO extension. the php PDO extension is much simpler and more constant over the mysqli extension you are currently using.

i have a question about the email you are sending. what does it have to do with updating the database data? if the mail is for daily task notification, it's not part of the update process and shouldn't even be part of this code. if you want to check for daily task information and send email(s) based on that information, you would trigger the email code via a scheduled task/cron job, query for the data you want, the produce and send the email(s). for your current code, you need to organize it so that you and anyone else that looks at it can see what it is even trying to do. this will help you to write logic that will do what you want. your search/filter form (start and end date) needs to use method='get', since it is controlling what data is gotten and displayed on the page. your data editing form is the only form that should use method='post'. (your last posted code in the other thread doesn't even have this form in it, so the code won't work anyways.) your code should be generally organized as follows - 1) common code needed for the whole page - start/restart session, 'require' or define in-line, code that your main code needs, initialize variables, ... 2) post method form processing code - 2.1) detect that a post method form has been submitted 2.2) if your code processes more than one form, such as for inserting, updating, and deleting, detect which form has been submitted, using a hidden 'action' field, with a different value for each of those processes 2.3) validate the input data 2.4) if there are no validation errors, use the submitted form data 3) get method code - code that retrieves/produces data needed to display the page 4) html document/template your code that's updating the data and sending an email related to that update would all be together as part of the code in item 2.4. you currently don't have the email code as part of that.

the OP is probably getting a 'commands out of sync' error at the prepare() call for the update query (or this isn't the actual code where the errors are at.) as to why he is not seeing the actual error - some code in one of the other included files could be modifying the mysqli error reporting mode or there is custom error handling being used and it is not actually handling the error type being generated. what is the content of the other three included files (less any sensitive data values in them)?

the second page should also produce the plot. there's no need for a third page.

of course. any value can be submitted to your code, and therefore cannot be trusted, because anyone or a bot script can create and send your web pages anything they want when they request your pages. people or bot scripts don't need your form(s) or your link(s) to request your pages. ^^^ this indicates a bad database design, where you are trying to use a database table like it is a spread sheet. using a series of numbered columns requires more code to perform any operation on the data and wastes storage. you should be storing each data item as a separate row in a table, not in numbered columns in a single row. this will simplify and speed up all your code.

if the session_start() on either page is not working (there would a php warning level error message) you would get the current symptom. do you have php's error reporting set to E_ALL, so that all errors will be reported? both the 'session already started' and the 'undefined index' errors are notice level error messages. how are you navigating between pages and other than the name of the page, is the rest of the url consistent between the pages? is the host-name/sub-domain part of the url changing between having and not-having a www. in them? is the path after the domain part of the url changing? if either of these things are changing between the pages and the session id cookie settings are not set up to match variations in the host-name or path, the initial session id cookie won't be sent from the browser to the server with the request for the second page and you would start a new session. does your code have any logic in it (anywhere) that is unsettling or setting the session variables to null values? if you have a header() redirect, that doesn't have an exit/die statement after it, to stop program execution, and it is followed by some code that's unsettling or setting the session variables to a null value, you would get the current symptom.

don't start trying a bunch of different things as an attempt to produce working code. that will just take you a long time, approaching infinity, to accomplish anything. stop and find what the actual problem is, then you can directly fix what's causing the problem. you have some php syntax errors in your 2nd file. one is due to not copying the code accurately and the other is a mistake in the code that was posted. if you set up your development system with php's error_reporting set to E_ALL and display_errors set to ON, in the php.ini, php will help you by reporting and displaying all the errors it detects. you may have to restart your web server to get these changes to take effect, after modifying the php.ini file. you cannot set these two settings in your code and have them report/display php syntax errors in the same file, since your code never runs when there is a php syntax error.

what sort of symptom or error did you get when you ran your code? you are telling us what you want to do, but didn't tell us what happened when you tried it. you might also what to check back in the other php help forum where you first asked about this on 07-26-2017, so that you would benefit from information in the reply already given and the phpfreaks and S.O. forum members won't be wasting their time telling you to make the same changes to your code.

^^^ actually, $stmt->anything won't work because $stmt is a Boolean false. depending what's in $mysqli (if not an already closed mysqli connection), echoing $mysqli->error may provide some helpful information.

only the last two pairs of errors seem to apply to the current code. after experimenting to reproduce those errors, you have created a mysqli connection in $con, but you have closed that connection prior to this code (in general, you should just let php close them automatically when the script ends.) you are also trying to mix PDO statements, which would use bindparm(), with mysqli statements, which would use bind_param().

i'm thinking you are getting a fatal run-time error due to the incorrect usage of prepared query statement objects in your code. 1) 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 help you by reporting and displaying all the errors it detects? if the http 500 error is due to a php error, this will cause the php errors to be displayed. 2) are you making use of the php.net documentation to learn what the statements you are using do and what value they return? your logic - if(mysqli_num_rows($result != 0)) { and if($stmt === "TRUE") { are basically nonsense that won't work or do anything useful. some other problems - 1) htmlentities() is an output function. it is only used on values you output on a web page. it is not used on values being put into sql query statements and you have repeated a block of code twice. 2) your SELECT query syntax is invalid. are you making use of the msyql database server documentation to learn how to write queries? 3) if your goal is to only insert a single ban appeal for any user, wouldn't your program logic need to do that? you have some (non-working) logic outputting a message if there is already an appeal for the submitted user, but you are unconditionally executing the INSERT query. this will INSERT a row every time the code gets executed and even if you fix the current program logic so that it does what you want, there's a race condition if there are multiple concurrent requests (think of someone hitting the submit button more than once) that would insert multiple rows of data. also, shouldn't you only accept and process a ban appeal if the user is banned? to fix the race condition problem and greatly simplify the logic you have, you need to set the mineuser column to be a unique index in your database table. then, all you need to do (one query) is try to insert the data and detect if the query throws a unique index error. if you get to this point, someone can post an example showing how to use exceptions and a try/catch block to do this in your logic. 4) your form processing code needs to validate the input data before using it. what happens if any of the values are empty? should you still run the rest of the code or setup and output validation error messages to the user?

your overall processing is - insert new data, delete old data from groups having a size greater than the queue size (30) from fastest to slowest, the following is a list of ways to insert data - 1) LOAD DATA INFILE query 2) LOAD DATA LOCAL INFILE query 3) a multi-value prepared INSERT query. you can dynamically prepare a single multi-value INSERT query with a block size to handle most of the data, then dynamically prepare a unique multi-value INSERT query to handle any final partial blocks of data. 4) a multi-value non-prepared INSERT query 5) a single value prepared INSERT query 6) a single value non-prepared INSERT query if you can, write the new data to a file and use option #1 or #2. this will be about 20 times faster than using single INSERT query inside a loop. reusing a prepared query will save about 5%, over a non-prepared query. using a new prepared query each time will actually take longer. if you had a problem with using prepared queries, it was likely due to this. if you want help with your prepared query code, you would need to post it. a multi-value INSERT query can easily insert 5K -10K rows in a single query (the limit is the max allowed packet size between the client/php and the db server, default is 1M byte) and will be between 5-10 times faster than using a single INSERT query inside a loop. ------------------- to do this, i would insert all the new data using the fastest method available, then as a second process, delete the old data item from each group that exceeds the queue size. for your current concatenated data, you can use a multi-value INSERT ... ON DUPLICATE KEY UPDATE ... query (rather than three queries) to concatenate new data items onto the end of the list, then after all the new data has been inserted/updated, you can use SUBSTRING_INDEX() in one single UPDATE query to remove any leading data item from the start of the lists.

php creates variables upon assignment of a value to them. outside of class properties, there is no specific variable declaration statement, and even there if you don't define them, php creates them upon assignment (may be version specific.) the lines of code are valid, and i'm pretty sure just examples of two different lines of code and not actual nested loops you intend to use. however, in general, you should try to program in general . program logic, that is performing the same steps each time you use it, such as assigning the sql query statement to a variable, executing the query, and fetching the data from the query, should be the same set of statements and (re)use the same variable names in the same place, except for the final variable you are assigning the data to. this will help reduce typo errors and will lead you to realize that you should create functions/class-methods with this common logic in them, to call, instead of actually repeating the same logic each time you need it. the numbered $resultX and $rowX are not needed and just make more work for you in keeping track of what variables are being used where. using the recommendation of separating the different concerns in your code, you would form the sql statement, execute it, and fetch the data from it before moving onto the next query your application needs. the php PDO extension is simpler and more constant than the php mysqli extension. for common activities, mysqli requires more statements than PDO. with PDO, no matter how you execute a query, you will always deal with a PDOStatement object. mysqli has a bunch of different types of query results, depending on the type of query and how you execute it, and one of them is dependent on php build and underlying driver availability. the default fetch mode even changes between the different types of mysqli results. the biggest differences in using mysqli and PDO are when dealing with prepared queries, which is what you should be using when supplying data values to the sql query statement. the mysqli prepared query programming interface is a joke to write code for and doesn't even come close to how easy PDO is to use.

when dealing with related data, between multiple or the same table(s), your goal should be to write a single sql query that gets the data you want in the order that you want it. you do this by writing a JOIN query. there are several types of joins and you can research on the web or in the mysql database documentation to find out more information about them. in order to separate the database specific code, that knows how to query for and retrieve the data, from the presentation code, that knows how to produce the output from the data, you should fetch the data from any query into a php variable named to indicate the meaning of the data. use a scalar variable for a single value, a one-dimensional array for a result set consisting of at most one row, and an array of rows for a result set consisting of one or more rows. by separating the different concerns in your code, it will be easier to define, write, test, and debug your code. $row is a variable and you can name them anything you want, to identify the meaning of the data in them. if you were querying for completely separate and unrelated meaning data, you would fetch the final data into differently named variables. with a JOIN query, the common data is repeated in each row in the result set. to get one instance of the common data (member details in your example), you can just reference the zero'th entry in the php array variable holding the data. to output the dynamic data (transactions in your example), just loop over the php array variable holding the data.

only form fields that have name='...' attributes will be submitted, using the name attribute's value as the $_POST['...] variable's name.

you should be learning, developing and debugging on a localhost development system. only after code is written and thoroughly tested should it be put onto a live server. the suggested settings cause all php errors to be reported and displayed, which is why the settings are named error_reporting and display_errors. if you cannot find how to set these in your code, ask, but about 80% of all the php threads in all the programming help forums on the web, and in the signature lines of a number of forum member's, show how you can set these in your code for debugging purposes.

you can set the error_reporting/display_errors settings in your code and the mysqli error handling must be set in your code.

until you do the two things suggested in reply #2, to get some feedback from php/mysqli as to where and what's occurring, we cannot help you. if the logic you have posted is all the code on the page, nothing is different with $conn between inserting a record and editing it.

what is the overall goal/purpose of what you are doing and who/what type of visitor will be doing it? if this is to let a general user select an item and have a random price generated, between the min/max values, you would NOT use the price from the form, since it can be manipulated by the visitor and be any value. you can display the range/min/max price for each choice, but you would generate the actual price value, on the server, once the form has been submitted. you can use ajax to submit the selected item id to the server, generate the random price on the server, save the chosen item's id and generated price on the server, and return and display the generated price in the browser, but this value returned to the browser is only for display purposes.

after reviewing the code, i'm going to dispute your statement that it basically does what it should do. of the four places you are calling the renderForm(....) function, only two have a parameter list that would produce expected results, and i'm not even sure the code is reaching the second of those two calls. forget about your renderForm() function. just put the html document at the end of the file and produce and store the input data that it needs in php variables. the inputs to the html document are the - id, form field values (if you use an array for these, you won't have to write out variables for each field and you can operate on all the data as a set), errors (if you use an array, you can easily validate all the data at once and have a discrete error message for each one), project/status select list data (fetch the data from the query into an array), and the craftgroup select list data (fetch the data from the query into an array.) by just moving the html document to the end and having the php code concentrate on producing the input data for it, a lot of the existing logic will be eliminated. a word about constancy, it counts when programming, especially if someone will have to read and figure out your code. the form field names and database column names should be the same and your project/status data should be named for what it actually is. a word about your database design. the tblMoveSheets table should store the project id and the craft id, not the project name and craft name and if the name/personname data are users for the system this code is part of, you would store the user id, not the name. the two select/option menus for the project and craft choices should use the data's id as the option value and the data's name as the display value, so that they submit the id values to be used by the insert/update query code. there doesn't appear to be any sort of user authenticate/permission system to control what the code does, so anyone can create or edit any data. if this is more than just a classroom assignment, you need to have a user authenticate/permission system. htmlentities() is an output function. it should only be used on the data as it is being output in the html document. you need to have error handling for all the database statements. the connection, query(), prepare(), and execute() statements can fail and provide important trouble-shooting information (bind_... statements throw php errors instead), which you should log on a live server and display when learning, developing, and debugging code. see point #2 in my post above in this thread for how to use exceptions to do this. this will eliminate the discrete error handling logic you have in the code now. as you have identified, the existence of an id is what controls if the code executes an insert or an update query. you should eliminate all the duplication in the two sections of code by only having the things that are different enclosed in conditional logic. only the sql query statement (and if you use the SET version of the INSERT query most the two query statements will be the same) and the binding of input parameters is different between the two queries. the validation of the inputs and the execution of the query are the same and you should organize the logic so that these common parts only exist once in the code. lastly, if you can, switch to use the php PDO extension. this will further simplify the code (there's no need to bind the results and you can skip binding the inputs, by supplying them as a parameter to the execute() call.) the INSERT query isn't even using a prepared query correctly now, needs more work, and is probably throwing errors.

apply some general trouble-shooting to the problem - 1) do you have php's error_reporting set to E_ALL, display_errors set to ON, and output_buffering set to OFF so that php will help you by reporting and displaying all the errors it detects and doesn't hide any errors or output from your code when the header() redirects are executed? 2) do you have error handling for the php mysqli statements? if you set the mysqli error mode to exceptions, combined with the above error_reporting/display_errors settings, php will catch and handle any mysqli errors and display the actual error information. to set the mysqli error mode to exceptions, add the following two lines of code before the point where you are making the database connection - // note: the $driver variable name used in the following two lines is unique to these two lines of code and don't have any relationship to any other variable used in your code $driver = new mysqli_driver(); $driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // MYSQLI_REPORT_ALL <- w/index checking; w/o index checking -> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

here is a quick/minimal example - <?php $errors = []; // an array to hold errors $data = []; // an array to hold a working copy of the submitted data $fields = []; // the defining structure of the form fields/validation // i'm guessing the following fields are/should-be radio button choices and some should have default values and wouldn't be not-set? $fields["contact_method"] = ['label'=>'','required'=>'','regex'=>'','regex_error'=>'']; $fields["phone_consent"] = ['label'=>'','required'=>'','regex'=>'','regex_error'=>'']; $fields["best_time"] = ['label'=>'','required'=>'','regex'=>'','regex_error'=>'']; $fields["referrer"] = ['label'=>'','required'=>'','regex'=>'','regex_error'=>'']; // text and textarea fields $fields["name"] = ['label'=>'Name','required'=>'y','regex'=>'/^[a-zA-Z ]*$/','regex_error'=>'may only contain letters and spaces']; // there are names that have more than letters and spaces $fields["email"] = ['label'=>'Email','required'=>'y','regex'=>'/([\w\-]+\@[\w\-]+\.[\w\-]+)/','regex_error'=>'is an invalid format']; $fields["phone_number"] = ['label'=>'Phone Number','required'=>'y','regex'=>'','regex_error'=>'']; $fields["message"] = ['label'=>'Message','required'=>'y','regex'=>'/^[a-zA-Z ]*$/','regex_error'=>'may only contain letters and spaces']; // needs to allow some punctuation // form processing if($_SERVER['REQUEST_METHOD'] == 'POST') // use a generic method to detect if the form was submitted { $data = array_map('trim',$_POST); // get a trimmed copy of all 1st level (non-array) form data. if any of your form fields are arrays, write a call-back function and use array_walk_recursive() instead. // validate data foreach($fields as $field=>$arr) { $value = $data[$field]; // if required if($arr['required'] == 'y' && $value == '') { $errors[$field] = "{$arr['label']} is required."; } // if regex pattern if($arr['regex'] && $value != '' && !preg_match($arr['regex'],$value)) { $errors[$field] = "The {$arr['label']} {$arr['regex_error']}."; } } // if no errors, use the form data if(empty($errors)) { $recipients = "[email protected]"; //E-Mail Recipient(s) $subject = "New Message Received"; //E-Mail Subject Line // apply htmlentities() to all data being used $email_data = array_map('htmlentities',$data); // note: this uses the default parameters for htmlentities. if you need to use different values, create a call-back function and use it in the array_map() call. $body = " EDITED THIS PART OUT TO SAVE SPACE IN THIS POST"; $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; $headers .= "From: [email protected]\r\n"; $headers .= "Return-Path: [email protected]\r\n"; if(mail($recipients,$subject,$body,$headers)) { header('Location: success.php'); //USER REDIRECT AFTER SUCCESSFUL FORM SUBMISSION ( CONFIRMATION PAGE ) die; // header redirect needs a die to stop execution so that the rest of the code isn't executed } else { $errors[] = 'The mail call failed.'; // your live server should be set up to log all php errors, but you can also capture the last php error, immediately after the mail() call, and use that here. } } } // code producing the web page // output any errors here... if(!empty($errors)) { echo implode('<br>',$errors); } // make a form for testing (all fields are type='text') - to apply the data driven design to this, you would add elements in the $fields array entries to tell this section of code what to do echo "<form method='post'>\n"; // apply htmlentities() to all data being used $form_data = array_map('htmlentities',$data); // note: this uses the default parameters for htmlentities. if you need to use different values, create a call-back function and use it in the array_map() call. foreach($fields as $field=>$arr) { echo "<label>{$arr['label']}: <input type='text' name='$field' value='". (isset($form_data[$field]) ? $form_data[$field]: '')."'></label><br>\n"; } echo "<input type='submit'></form>";

what's wrong, that's causing the current symptom (there are other problems too), is that you are not setting at least one of your variables from the submitted form data, so the big long if(...) statement is false and the code at the end is being skipped over. you need to constantly process each input (so that none of them get forgotten and you are not testing different versions of the same value), get rid of the test_input() function (it's from the web, improperly named, and improperly coded for what you are doing), and if you are up to it, use a data driven design that will simplify all the code, by creating an array that defines the expected form fields and what validation each field uses, that you would loop over to dynamically operate on the data, rather than to write out block after block of bespoke code for each possible input. to start with, use an array for the errors. to initialize it, just use $errors = []; add error messages to the array in the validation logic. when you are finished with the validation, if the errors array is empty, you can use the form data. this will eliminate the big long bespoke if(....) statement that you have now. there's also no good reason to create a bunch of variables, one for each possible field. just use an array variable to hold any modified version of the submitted $_POST data (it's generally a good idea to leave the original, un-modified submitted data in $_POST.) you should first trim all the input data, so that you can detect if all white-space characters have been submitted, then validate the trimmed data. when you are actually using the data, is when you would apply htmlentities() to the values. the defining of an array/data driven design, while it sounds like an advanced subject, only requires a little abstraction (one level) of coding, since the defining array will hold values that get used by the code to control what it does. the if() test for the mail() statement, needs an else() term to cause an error when the mail call fails. in general, when validating/operating on a user submission, you need to always do something when the code doesn't do what is expected. in short, get rid of the test_input() function, get rid of all those variables, trim() the data (all at once - if i have time, i'll show you a single statement that can do this), validate the data, then use the data if there are no validation errors. if i/someone has time, they will post an example.

the php error is because the ->prepare() failed AND you have no error handling for your php mysqli statements, so your code continued to run after the point of the error and tried to use $sql that contains a boolean false, rather than a prepared statement object. the easiest way of adding error handling for all your mysqli statements, is to use exceptions. there is information in the php.net documentation on how to enable exceptions for the mysqli extension. once you have some error handling, you will find that you have a error the sql syntax. the ON DUPLICATE KEY UPDATE ... part of the query has term(s) in it for the column(s) being updated, not for each value. the ending of your sql statement should be - ON DUPLICATE KEY UPDATE Sort=VALUES(Sort) the mysql VALUES() function is defined as VALUES(col_name) where the col_name is the INSERT column value to use.