mac_gyver

you need to read the documentation for any php function you are trying to use. strcmp returns a zero if the strings are the same. don't even use strcmp for your passwords. you should be using php's password_hash() and password_verify() to handle your password hashing and comparison. there are examples in the php.net documentation showing how to use these. next, Don't Repeat Yourself - DRY. you are already running one sql query to find and then fetch the row matching the username. don't run that query again to get a single piece of data. you already have all the data from the row, from the first time you executed the query. don't loop to fetch data from a query that will at most match a single row. the while() loop code will go away anyway since you don't need to run the query the second time. you need to use a prepared query to supply any data to an sql query statement to prevent sql injection. you should not use or die() logic to handle sql query errors. use exceptions to handle sql errors and you should not output the raw php/mysql errors to the browser on a live site. you should validate each input separately and output a unique message for each validation error. you should NOT identify if the username or the password is the reason for not being able to log in. this will allow someone/bot script to repeatedly submit values to your code and first find valid usernames, then bruit force find a password. output the same generic - invalid username/password for each of these cases. you can log internally the actual reason for a login failure. don't use $_REQUEST variables. if you expect data to be in $_POST variables, use $_POST. you should also detect that a post method form was submitted at all, before trying to use any $_POST variables. this will prevent php errors. if the current visitor is already logged in, you should not run your login form/form processing code. lastly, when the visitor is correctly authenticated, you should store the user's id in a session variable to remember that they are logged in and who they are. all other code that tests if the visitor is logged in would test if the session variable holding the user's id is set and if they need to retrieve any current data about the visitor, they would use the user id to do so. this will insure that any user data is current and up to date.

where the logic test goes is -

because the toolbar class constructor, add_icon() method, and add_help() method are used throughout the application to define/add items to toolbars and set parameters for the added items (some of the constructor's icon_list properties are being set directly in the application code), you would change the existing relevant toolbar class code, not add new primary methods (and hope that none of the application code is defining it's own icon_list entries, since it's all publicly accessible data.) i would keep all the existing functionality. this will allow the application to be returned to it's current operation and if there are cases where the application code is defining it's own icon_list entries, the application will still work while you are finding and fixing these occurrences. just add a 'use buttons' feature to the existing code, i.e. if you have defined an entry to use a button, it will trigger the new code. i would just add a 'button' field to the icon_list definition. if the button field exists ( isset() ), the logic will run the new code for the button functionality. next, the code already has defined constants that are used for the current image alt='...' parameter. you should use these as your button text so that you are not repeating things in code that have already been defined. presumably, this has been set up to support multiple languages with a language selection menu/configuration. so, in the toolbar class constructor, add_icon(), and add_help() methods, add a 'button'=>true element to any icon_list entry that you want to switch to use a button. in the build_toolbar() method, add a logic test for this button array element. if it's not set, call the existing html_icon() function. if it is set, call a new function that's similar to the html_icon() function code but produces the html that you want for a button.

your code and queries are only as secure as you make them. if you use a prepared query incorrectly, it will won't be secure. by using a true prepared query (the PDO extension has emulated prepared queries turned on by default for all database types, despite what the documentation states, and you need to turn them off when you make the database connection) to supply data values to the sql query statement, your sql query will be safe against sql special characters in the data from breaking the sql syntax or breaking out of, i.e. escaping from, the sql syntax in the case of intentionally injected sql. after you choose the php database extension you are going to use, the posted code needs to do or not do a number of things - 1) you are mixing procedural and OOP style for the msyqli statements. you should be constant in your programming. using the PDO extension will fix this since it only has OOP statements. 2) using prepared queries will eliminate all the ...real_escape_string() calls. you are using one in the case of a value being output to the browser. the ...real_escape_string() statements are only used on string data being supplied to a non-prepared sql query statement, not on data being output to the browser. 3) you need to use exceptions to handle database statement errors. your code has error handling on only one of the query statements now. the PDO extension already uses exceptions for connection errors. you need to set the error mode to exceptions, for all the rest of the statements, when you make the connection. 4) all the post method form processing code should be grouped together and come near the top of your code. you should actually test if a post method form was submitted, then if you have multiple possible forms, detect a field name or field value that identifies if the Delete or Update form was submitted. 5) you should validate all the submitted data before using it, then only use it if it is valid. use an array to hold validation errors. you can then test if the array is empty or not to determine if there have been any validation errors. to display the errors, just output the contents of the array. 6) your delete checkbox logic only works for a single/last checkbox. in fact, all the form fields don't work when there is more than one set of data in the form. you need to use array names for all the form fields, with the $row['id'] value as the array index (this is needed to distinguish which row each form field value corresponds to) and you need to remove the hidden field with name='checkbox' (having this causes only the last checkbox in the form to work.) you would also need to add a loop in the Update form processing code to loop over all the submitted sets of data. 7) with prepared queries, when you are looping over data and executing the query inside the loop, you will prepare the query once before the start of the loop. the code inside the loop only gets the correct set of data and calls the ->execute() method. when you remove the hidden form field with name = 'checkbox' (which was done to prevent php errors when no checkboxes are checked, but because it is being output repeatedly, only allows the last checkbox to work), the logic in the delete form processing code will need to be changed. if there are no checked checkboxes, $_POST['checkbox'] won't be set. you need to add an if(isset($_POST['checkbox'])) test. 9) the code to retrieve the data needed to display the page should be removed from the actual html document/template. this will make it easier to write and test your code, and if you need to change the code to use a different database extension, such as the PDO extension, consolidates the database specific code in one place and makes the html document/template general purpose. just fetch the data from the query into a php variable and use that variable in the html document/template. 10) the html document you are creating is reusing DOM id='...' attribute values. id's must be unique. if the client-side code isn't actually referencing the id's, don't even put them into the markup. 11) you are outputting an empty <tr></tr> after each actual <tr>....</tr> of output. makes no sense and is repeating an id attribute value which is also not valid. only output markup that you need. 12) you are outputting the $row['id'] value in a form field. this is not an editable data value. the $row['id'] specifies which row the data belongs to. you can display the $row['id'] value if you wan't, but it should not be in a visible form field. see item #6 in this list for how the $row['id'] value should be used to associate the submitted values with the $row['id'] value they belong to. 13) when your SELECT query doesn't match any data, your code is correctly skipping over the code to produce the output from that data, but your code is also not doing anything to notify the user that there was no data to display. you should set up and display a message that the reason for the blank section on the page is that there is no data to display. 14) all data values being output to the browser should be passed through htmlentities(), with an appropriate 2nd parameter value, to prevent any html entities in the data from breaking the html on the page and to help prevent cross site scripting. 15) you are repeating the <tbody>...</tbody> tags for each set of data being output. while this is valid markup, unless you are going to style each table body section separately, there's no point in doing this and it is just adding clutter to the result and would prevent a large table body section from being able to be scrolled on the page.

to get the initial undefined index error, your index.php page probably has nested forms or some invalid markup at some point and isn't submitting a $_POST['id'] value. it's also possible that with all the redirecting you are doing, that you have redirected back around to the edit_author.php page from somewhere else. you should actually do all of this on a single page. it will simplify all the code and markup you are having to write and test. three things - 1) you should actually be using a link or a get method form on the index.php page, since you are determining what data will be gotten and displayed on the edit_author.php page. 2) you must ALWAYS validate the inputs you expect and set up and display error messages when the input isn't present, isn't a valid value or format, or doesn't match any expected data. only use the input value(s) after it has been validated. 3) you need to always have an exit; statement after a header() redirect to prevent the rest of the code on the page from running. this may be the cause of your undefined index error, if the code later on the page or on some other page is redirecting to the edit_author.php page. next, your UPDATE form processing code must first test if a post method form was submitted before trying to use the submitted data, then validate that data before using it. the UPDATE form processing code should come first in the logic flow, then you should retrieve any data for populating the form. you also need to detect if the update form has already been submitted to control if you should retrieve the data for populating the form (if there was a validation error and you re-display the form, you want to populate it with the just submitted data, not the values from the SELECT query.) the easiest way of determining if you should run the SELECT query or not is to use an internal array variable to hold the data being operated on. the UPDATE form processing code would copy the submitted $_POST data to the internal array variable. at the point of running the SELECT query, if the internal array variable is empty, run the SELECT query and retrieve the data into the internal array variable. use the internal array variable as the values you populate the form fields with. it will initially be the values from the SELECT query. after the form has been submitted, it will be the submitted form values.

see the following example, that separates all the database code out of the presentation logic, making it easier to write, test, and debug just the presenation logic - // retrieve and store the data into a structure that supports the output you are producing // this is made up data, your code would produce this same structure from your actual data $data = array(); // the main array index is the page name - gets tested against the value in $page for the active attributes $data['home'] = array('link'=>'not sure if your links include the / or if it is part of the shopConfig url','menu_display'=>'Home','children'=>array()); // parent only, no-empty children $data['riders'] = array('link'=>'riders','menu_display'=>'Riders','children'=>array( array('id'=>1,'identifier'=>'lee-smart','rider_name'=>'Lee Smart'), // add an array to the children array for each rider data row )); // married with children - go Al Bundy // produce the output from the data ?> <nav class="navbar navbar-default navbar-fixed-top"> <div class="container"> <div class="navbar-header"> <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar"> <span class="sr-only">Toggle navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> </div> <div id="navbar" class="navbar-collapse collapse"> <ul class="nav navbar-nav navbar-right"> <?php foreach($data as $page_name=>$arr) { if(empty($arr['children'])) { // parent only, no children $active = $page == $page_name ? " class='active'" : ''; echo "<li{$active}><a href='{$shopConfig['url']}{$arr['link']}'>{$arr['menu_display']}</a></li> "; } else { // w/children $active = $page == $page_name ? "active " : ''; echo "<li class='{$active}dropdown'> <a href='{$shopConfig['url']}{$arr['link']}' class='dropdown-toggle' data-hover='dropdown' data-toggle='dropdown'>{$arr['menu_display']}<b class='caret'></b></a> <ul class='dropdown-menu'> "; // loop over the children foreach($arr['children'] as $child) { // define different handling for different data structures switch($page_name) { case 'riders': echo "<li><a href='{$shopConfig['url']}{$arr['link']}/{$child['id']}/{$child['identifier']}'>{$child['rider_name']}</a></li> "; break; default: // child echo "<li><a href='{$shopConfig['url']}{$child['link']}'>{$child['menu_display']}</a></li> "; } } echo "</ul> </li> "; } } ?> </ul> </div><!-- /.navbar --> </div><!-- /.container --> </nav> for the example output you posted, this code produces the correct opening and closing elements and should work.

you have too many queries, code, and logic, so it's not possible to make any sense from the code you posted what result you actually want from that code. you should not run select queries inside of loops and you have closing html markup in an else{} statement, at about about line 90, that corresponds to opening html markup that's inside the corresponding if(){} statement. start by separating the concerns in your code. you should execute ONE sql query statement that gets all the related data at that you want, in the order that you want it, then fetch that data into a php array variable, then loop over the fetched data to produce the output. you can test each step to make sure it is doing what you want before going onto the next step. edit: you should also use exceptions to handle database statement errors so that you don't have to have error handling logic at each statement that can fail. with exceptions, your main code only has to deal with error free statements.

actually, yes. there are a number of programming practices and missing logic, that your code needs. 1) $showclosedgames - don't write variable names strung together like this. use underscores to separate the words that make up a variable name - $show_closed_games 2) if there are only two possible values for a variable, don't use an elseif() test, just use an else() or you can use a Ternary Operator. 3) don't use variables that end in 1, 2, ... For the posted code, you are writing out more variables than you need and then having more lines of code copying the extra variables to other variables. you should finish with the operations within one block of code before going on to the next block of code. you can then reuse common variable names like $query. 4) don't copy one variable to another. just use the original variable name in the code. 5) don't repeat yourself (DRY.) the parts of the sql query statement that are static should not be repeated. only the WHERE clause is dynamic/conditional and it is the only thing that should be part of the conditional logic. you should build the WHERE clause in a php variable, then use that variable in all the sql query statements. there will only be two sql query statements - 1) to get a count of the matching rows, and 2) to retrieve the actual data. 6) the sql query to get a count of the matching rows should use SELECT COUNT(*) so that it doesn't retrieve all the data from the table. you would fetch the count value into a php variable. 7) the purpose of getting the count of the matching rows is so that you can limit the maximum requested page number and you limit or determine if there is a next link in the pagination. the current code is not doing either of these things. if the requested page is 1, there is no previous link for the pagination. the current code is unconditionally setting up a value for a previous link. 9) the whole block of logic for the page value and sql query limiting needs help. you should get the requested page number (validating or casting it as an integer) or use page 1 as the default, then limit the page number between 1 and the maximum number of pages, then just use the final limited requested page number in the rest of the code. 10) the sql query to fetch the actual data has some error checking logic around it (which the end of wasn't included in the post, so we don't know what your code does do when there is a query error), but the first sql query to get a count of the matching rows doesn't have any error checking logic. consistency counts in programming. you should always have error checking/handling logic in your code. the easiest and most consistent way of handling sql statement errors is to use exceptions. 11) i have doubts about your $showopengames ($show_open_games), WHERE winner='' sql, and the $status open/closed logic. when $showopengames == 1, your queries are matching all the rows in the games table. when $showopengames == 0 (presumably to show only closed games), your queries are matching rows in the games table that have an empty winner value (which seems to mean games that are open.) you are then treating an empty winner value as being open and a non-empty winner value as being closed. this makes no sense. are there actually three possibilities for $showopengames - all, open, and closed, in which case the variable is not named very well, and program and query logic isn't doing what you expect? most of these points will actually simplify and reduce the amount of php and query logic, making it easier to write, test, and correct problems in your code and queries.

before you can write code to perform a task, you must define what you want the result to be. if you are displaying a list of questions and three possible answers for each question, wouldn't your code need to have html markup to do each of these things? for each question, display the question text, then display the three radio buttons, each with a value that indicates which answer they correspond to, and display the answer text for each radio button. for radio buttons to work, all the buttons in a group must have the same name. i recommend using an array name, with the question id as the array index. this will let you simply loop over the submitted data using a php foreach(){} loop to get the question id and the chosen answer value. to accomplish the stated output, you would need the question text, the question id (to produce the radio button name array index and to facilitate the efficient storage of the submitted answers), and the text for three possible answers (you should actually have answer ids as the radio button values, but for simplicity, you could use the answer text as the radio button values.) does your current design have these values? what is the q1 column? is that the question text or the question id? the name of your columns should indicate the purpose of the data in the column. if you have columns named q1, q2, q3, ... this is not a good design and should be fixed. see the next point - your database design should actually store the questions (with columns for the question id and question text) and possible answers (with columns for the answer id, answer text, and if the answers are unique to each question, a question id) in separate tables (the submitted responses are stored in a third table.) if you use exceptions to handle database statement errors, you can eliminate all the conditional logic you are adding for each statement (connection and query) that can fail. an exception will be thrown for any error, which you can let php catch, and your main code only has to deal with the error free execution of statements. i also recommend that you fetch and use data from any sql query using an associative array, instead of a numerical array. this will help prevent mistakes in your code and make your code easier to read.

it's not possible to write code or convert old code you have found on the web, without using the php.net documentation. next, the php mysqli extension is not the best choice to use. learn and use the php PDO extension instead. you should be using prepared queries to supply data to any sql query statement to protect against sql injection. don't use @ error suppressors, ever. all they do is hide problems and make it harder to write code that works. don't write and use functions like the fetchinfo() function. it is currently not secure and by having a function that selects a single column at a time, you will end up killing your database server with too many queries. don't write and use functions like the secureoutput() function. this function is repeating what it is doing, using a msyqli function, which has nothing to do with output, and is just nonsense code. to secure data being output to a web page, just use htmlentities() with the appropriate flag values.

doing this would also help in finding the problem -

to filter the status by ALL values would mean to either leave the entire status term out of the sql query statement OR cause the status term to always be a true value. the code you have posted isn't even producing the correct status terms when a value is selected. fix that problem first, and it should be clear how to make the ALL filter work. echo $sql to see what it actually is for each possibility. next, the php msyql_ extension is obsolete and has been removed from php for about one year. you need to use the php PDO extension, and use a prepared query to supply the data values to the sql query statement.

did verifymail.php ever have a sess_start(); call in it? all your files must be doing the same thing for the session to match up. it's likely that this was initially working because you already had an existing session, using session_start(), that matched up between the files. when you added sess_start() to just the index.php page, that created a new session with a second name, alongside the existing session, and so your verifymail.php had session data. once you finally closed your browser and started over, index,php was using the sess_start() settings, verifymail.php was using the session_start() settings, and there was no matching session data for verifymail.php to use. most of the code you have shown at the top of index.php is common logic that ALL your pages should use. why don't you have them in a file and require it into each page (Don't Repeat Yourself - DRY) or better yet, if you are at the point of wanting to set up custom session settings, why aren't you using a single file, index.php, to implement your entire site? having a single index.php file site would eliminate the need to even pass data in session variables and would eliminate all the repetitive code and html you are trying to write, test, and maintain.

presumably, before this assignment, you had assignments leading up to having a single form and the form processing code for it, hopefully on a single page, so that you weren't duplicating code and you could display any validation errors when you re-displayed the same form? this assignment, rather than just repeating that logic three times in separate files and stepping between the files, should be more about implementing this all on a single page, which would eliminate all the triplicated logic and html markup. when you move from one form to the next, you would just submit a hidden 'step' value with the form data. the code on a single page would use the step value to control what the page does. you can produce previous/next step form buttons (like pagination) to let the visitor move between steps. if not on the first step, you would display a previous button. if not on the last step, you would display a next button. if on the last step, the next button would become the final submit button. the form processing code would use previous/next input to modify the step value if the submitted form data is valid (you would stay on the same step number if the data isn't valid.) the form processing code would also use the submitted step value to control what it does. the submitted step value would tell the form processing code which set of inputs it should expect. the code would validate the input data and if the step is not the final form submission, store the validated data in a session variable (i would use the step number as a sub-array key for store the data from each step.) forget about trying to use an OOP class for any part of this, get the program logic to work first. if the submitted data is from the final form submission, after you validate the inputs, use all the submitted data for whatever purpose you need.

if you simplify the cart so that it uses the item_id as the array index and stores the quantity as the array value, all the code will be greatly simplified. when you add something to the cart, you would need to submit the item_id and the quantity. both these values should be submitted as post data. after you validate both submitted values, this is all the logic you need to either initially insert the item into the cart with the submitted quantity or to add an additional quantity to an existing item - $item_id = (int)$_POST['item_id']; // if the item is not in the cart, add it with quantity 0 (the next step will add to the quantity) if(!isset($_SESSION['cart'][$item_id])) { $_SESSION['cart'][$item_id] = 0; // add the item id to the cart with quantity 0 } // add the submitted quantity to whatever is in the cart for this item $_SESSION['cart'][$item_id] += (int)$_POST['quantity'];

the suggestion bsmither made has to do with a variable you are assigning a value to on line 3 in the posted code and a later line of code that's supposed to be using that same variable to supply a value to the sql query statement. they don't match and you would have probably gotten them correct if you had just written the code yourself rather than copy it from somewhere on the web. here's a laundry list of things your code should/shouldn't be doing - 1) use a get method form for a search function. 2) don't use $_REQUEST variables. Use the proper $_GET, $_POST, or $_COOKIE that you expect the data to be in. 3) when you validate input data, set up and output a message to the visitor when that data isn't valid. what happens now if a submitted name doesn't match your preg_match() statement? don't leave the visitor wondering why a web page doesn't display anything. 4) use a prepared query to supply data to the sql query statement. your current code, that's putting the (wrong) php variable directly into the sql query statement, is open to sql injection (someone could inject sql and get your current code to display the complete contents of any database table you have.) unfortunately, the php mysqli extension is not the best choice to use, ever, and more so with prepared queries. if you can switch to use the php PDO extension. 5) don't unconditionally output database errors to the visitor (your connection code now). unfortunately, your code isn't even checking for an error when it runs the sql query statement. the easiest way of checking for errors is to use exceptions. this will eliminate the need to add program logic at each database statement that can fail with an error (the connection and query statements.) if you let php catch the exception, it will use the php error_reporting/display_errors/log_errors settings to control what will happen with the actual error information. when learning, developing, and debugging php code, you would display all errors. on a live server, you would log all errors. 6) for a search, if the query doesn't match any data, set up and output a message to the visitor telling them so. again, don't leave the visitor wondering why a web page isn't displaying anything. 7) when updating old mysql_ based code, you have to update all the mysql_ statements. you missed one and there would be a run-time php error alerting you to the problem. 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 would help you by reporting and displaying all the errors it detects? you should separate the database specific code (that knows how to query and retrieve data) from the presentation code (that knows how to produce the output from the data.) doing this will make it easier to test your code and make your code more general purpose and easier to reuse. the way to do this is to store the fetched data into a php variable in the database specific code, then just use that php variable in the presentation code. this also makes it easy to implement item #6. you can just test if the php variable is empty or not to output the message to the visitor. 9) there's no good reason to copy values from one variable to another. when you switch to use a prepared query for the search, you would just use $_GET['name'] in the code. this would eliminate (one of) the current problems in the code. when you loop over the data from the query, don't copy values from the $row[...] variables into other variables. this is just wasting your time typing (and i'm pretty sure you are not doing this as part of a typing class.) just use the $row[...] variable where it is needed. 10) when outputting dynamic/variable values on a web page, because they can consist of any value that was allowed to be input/created, you need to apply htmlentities() to the output. 11) you can put php variable directly inside of a double-quoted string. this will simplify your code when producing output. you have a mess now and there's at least one extra </a> and some missing or misplaced <li></li> tags. 12) you should validate the html that your code outputs at validator.w3.org this would help find some of the problems mentioned in item #11. 13) if you store your database connection credentials in an external .php file and require it into your main code, you won't have to take the time to edit code when posting it on a help forum.

when you update old mysql_ based code, you must convert all the statements and when debugging php problems, get php to help you by setting error_reporting to E_ALL and display_errors to ON. you missed a mysql_real_escape_string (and a few mysql_error()) statements that would be throwing php errors to help pin down the problem. the reason this works on one system and not another is because php and the all in one system creators thought it would be funny to set up default database connection credentials and to have mysql_ statements try to make a connection when there isn't one. on the system where this doesn't work, there are probably no default connection credentials, so the mysql_real_escape_string function call can't make a connection and it returns a null/false value instead of the escaped search term.

if you are updating ALL the 'data' columns that exist in a row for any index value, you might as well just replace the row. i don't know how the performance of this multi-value method compares with the multi-value INSERT ... ON DUPLICATE KEY UPDATE ... method.

just because mysqlnd is installed, doesn't mean that the mysqli or PDO extension will use it. your installation of php would need to be compiled with switches set causing the mysqli and/or PDO extensions to use the mysqlnd driver. there would be mysqlnd entries shown in the client api sections of the msyqli/pdo_mysql phpinfo output. if the script cannot be easily switched to use the PDO extension, you may (untested) be able to conditionally detect the existence of the get_result() method and extend the mysqli stmt class with a user written method that returns an instance of a user written class that emulates the features of a mysqli result class that the script is using (afaik, it is not possible to create and return an actual populated instance of the built-in mysqli result class). this of course is just a kluge. the whole php mysqli prepared query implementation is bad, and this is just one more case highlighting that it should not be used.

the HEREDOC ending tags ( END_OF_TEXT; in your code) must start in the 1st column and be the only thing on the line. it's not clear if what you posted was the result of how you added the line numbers or if you actually have some white-space ahead of the Heredoc ending tags. you also have at least one weird single-quote, in front of the l_name array index, on about line 122, that needs to be a simple single-quoted - there's a missing single-quote on about line 140, ahead of the state array index name - there's another weird quote on about line 175, ahead of the add_type array index name - and there's more after that point, but i stopped looking. you can find these type of things by looking at the color highlighting, or lack of, in your programming editor. at each of these, the color highlighting stopped changing at that point. edit: here's some more suggestions - 1) use exceptions to handle database statement errors. this will eliminate all the logic from the code that's testing if the queries (and connection) worked. 2) use prepared queries. this will eliminate all the mysqli_real_escapes_string function calls from the code and all the extra variables being used to hold the escaped data. 3) if you are building a double-quoted php string, rather than escaping double-quotes within the string, just use single-quotes within the string. 4) you can put php variables directly inside a double-quoted php string. no need for a bunch of concatenation dots. these things will greatly simplify your code, so that you/we/i can see what it is trying to do.

if c1, c2, and pk in your example are the only columns, you can use a multi-value REPLACE query, otherwise use a multi-value INSERT ... ON DUPLICATE KEY UPDATE .. query (which, since the the data already exists, implements a multi-value UPDATE query). the max packet size refers to the data sent in each communication back and forth between php and the database server. for a true prepared query, the sql statement (which is a string) would be sent in one communication, and the data for each execute statement would be another communication. named place-holders are implemented solely in the php PDO driver. the actual sql statement that is sent to the db server has had them replaced with ? and the driver maps the names to the place-holder parameter number when it builds the communication command to send the data to the db server - here's an interesting read on what the binary transfer protocol is for the execute statement when using prepared queries - https://dev.mysql.com/doc/internals/en/com-stmt-execute.html#packet-COM_STMT_EXECUTE note: the binary transfer protocol is only available when using prepared queries and then it only really saves time if transferring binary data. for string data values, which is the default for PDO, unless you specifically bind the data using a non-string data type, the data is still sent as characters and there's no time savings in the communication. and here's the reason for the performance difference - for simple queries, the communication time (handshaking + actual transfer) for both the prepare() and execute() statements is much greater than the time to actually prepare (parse/plan) or to execute the query on the database server, so, from the php side, the only way to significantly reduce the amount of time taken is to reduce the number of separate communications. running a single row prepared query inside of a loop only saves about 5% (bench-marked using a mysqli prepared query a while ago) of the overall time, compared to running a non-prepared query inside of a loop, because all you have eliminated from the loop code is the communication of the sql statement string and the parse/plan step. you are still performing a separate communication for each pass through the loop and have to spend the time for the handshaking + actual transfer for each communication. the only signification time savings i have seen is when using a multi-value query, which Barand has also confirmed in his tests. to fully implement a bulk method, figure out the maximum number of data values you want to send at once. if the actual amount of data is less then the maximum you have chosen, dynamically produce the multi-value sql statement of your choice for that amount of data, prepare it, then supply the data as an array to the execute statement. if the actual amount of data is greater than the maximum you have chosen, break the data into equal size blocks that are less than the maximum. if there's an odd amount of data, you can include one dummy/repeat set of values to make all the blocks the same size. then, dynamically build the multi-value query statement with the correct number of sets of place-holders to match the block size, prepare the query, then loop over the actual data and break it into arrays of the correct size, fixing up the size of the last block if it is odd, and supply each array of data to the execute() statement.

another point about prepared queries, you prepare them once, then can execute them multiple times. the UPDATE query should be prepared once, before the start of the loop. the code inside the loop should only populate the data for the place-holders, then execute the query. @NotionCommotion, the OP's from and to values do make sense. from is an older date and needs to be the first parameter in the BETWEEN term for the statement to work. to is a newer date and needs to be the second parameter in the BETWEEN term.

do you have php's error_reporting set to E_ALL and display_errors set to ON in the php.ini (putting these settings into your code won't help show syntax errors in the same file where the settings are being set) on your development system so that php would help you by reporting and displaying all the errors it detects? you would be getting a php syntax error because you are mixing php and html markup inside of php code tags. you would also be getting a blank php page, which you didn't tell us you are getting as a symptom when you run your code. to output in-line html markup, you need to have a closing php tag to switch out of php 'mode'. with 300+ posts, you should be past having these basic problems.

example of the array/flag method, that has the added advantage of separating the database specific logic from the presentation logic, so that if you do end up with a correctly designed database table, you only have to change the database specific logic. the presentation logic would remain the same. if (isset($_GET['zoeknummer'])) { $pdo = new PDO("mysql:host=$dbhost; dbname=$database", $dbuser, $dbpass); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $sql = "SELECT lead_content FROM wp_wgbsupicrm_leads"; $res = $pdo->query($sql); // where $pdo is your db connection $matches = array(); // array/flag to hold the matching data while($lead = $res->fetchColumn()) { $data = json_decode($lead); if ($data->zoeknummer == $_GET['zoeknummer']) { $matches[] = $data; } } // presentation logic - produce the output from the data if($matches) { foreach($matches as $data) { echo "Uw plaats voor " ; echo $data->wachtlijstkomplex . ' : ' . $data->wachtlijstplaats . '<br>'; } } else { echo 'Geen resultaat gevonden met opgegeven nummer. <br> Kijk na of U het nummer correct heeft ingevuld.'; } }

if you are not tied to this database design, you should change it to properly store the data. by storing json encoded data, you cannot directly find matching data using the sql query. this will also not scale well as you must retrieve all the data in order to scan it for matches. this will result in a poorly performing web site when there is a moderate to large amount of data. if the data is stored properly, by storing the 'columns' in the json data in individual database table columns, you can find the row(s) that match the search term, directly in the sql query statement and all the code will be simplified. for your current design, the easiest way of using the FLAG method would be to use an array and store any data that matches the search term into the array. after you finish looping over the data, if the array is empty, there were no matches. if the array is not empty, loop over the entries in it and produce the output from the data.