Jump to content

mac_gyver

Staff Alumni
  • Content Count

    4,311
  • Joined

  • Days Won

    113

Everything posted by mac_gyver

  1. a couple of implementation points - if you set the fetch mode to assoc when you make the database connection, you won't have to specify it in each fetch statement. you seem to be creating class properties regardless of if they are being used. this is just wasting your time typing things. only create properties, variables, ... if they are needed. for what you are doing, you only need a property for the database connection.
  2. in addition to not using a prepared query correctly, your code should be able to reuse an already prepared query, which it can simply by using implicate binding and calling the execute() method on the returned PDOStatment object with an array parameter consisting of the input data. next, you have two logic problems, where code isn't doing what you think. the first one - $result is normally a PDOStatment object. It will be a true value if the prepare() statement succeeded. It will be a false value if the prepare() statement failed due to an error. it doesn't have anything to do with data from a query. you should be using exceptions to handle database errors (connection, query, prepare, and execute) and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) to detect if there is data from a query, just fetch the data and test the fetched result. the second one - you are calling the buildTree(...) function with a row of data from a query, which is an array of the elements in a row. it is not an array of rows or an array of parent ids (which is what you should be doing), so looping over the elements in the supplied value doesn't make any sense. before writing code to do something, it would help if you first wrote a comment that defined what the input(s) are, what processing is going to be done, and what result is returned. next, you should ALMOST never run queries in loops. it is extremely inefficient, mainly due to the communications involved between php and the database server (for most simple queries, the time it takes to execute the query is several times less then the communication time, so you should perform a tasks using the fewest number of queries.) what you should do - execute the first query to get all the first level parent data. (i'm not sure why you have a separate table for the first level parent data, all the data should be in a single table.) get all the first level parent ids into an array. call a recursive function, with the array of parent id as its input, to get all the corresponding child data. if there is no further child data, return from the function. if there is child data, store it in a data structure using the current level's parent id as the array index at the current data level. get all the parent ids from the current level data into an array and call the recursive function again. note: you can use FIND_IN_SET(some_column,?) in a prepared query, then supply a comma delimited string of values via a prepared query place-holder, to let you (re)use a single prepared query inside the recursive function.
  3. that's usually because your code is fetching and discarding the first row. you would need to post your code to get specific help with what is wrong with it.
  4. doing this was a waste of time, since you never defined/set the variable that causes it to 'function', and the undefined variable error you got from it has nothing to do with with any sql query problem. your code needs to ALWAYS have error handling for statements that can fail. the easiest, simplest way of adding error handling for database statements is to use exceptions for errors and in most cases let php catch and handle the exception where it will use its error related settings (error_reporting, display_errors, log_errors) to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) when learning, developing, and debugging code/queries, you should display all errors, which will now include database errors. when on a live/public server, you should log all errors, which will now include database errors. to use exceptions for errors with the mysqli extension, add the following line of code before the point where you make the connection, and then remove any error handling logic you have in your code now - mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); the above will tell you if a query error is occurring, where in the code it occurred at, and what the error information is. if you are not getting any errors and you are not getting the expected result, you will need to troubleshoot your query/code to find out why.
  5. web servers are stateless. they don't know or care what happens outside of the current request. to do what you are trying, a quote/proposal/shopping cart, you will need to provide a means of propagating the selected item information between page requests. one method of doing this, that is straightforward to implement, is to store the cart information in a session based array variable. the work-flow would be - display products with a means of adding them to the cart. when the 'add to cart' form is submitted, add the item id(s) and quantity(ies) to the session based cart. if you use the item id as the cart's array index and the quantity as the array value, you will end up with the simplest code. to display the contents of the cart, retrieve the item ids from the session based cart (see the array_keys() function), query the database table holding the item information to get the name, description, price, ... of the items in the cart, loop over the result from the query to produce the output, getting the corresponding quantity from the session based cart to calculate the sub total for each item, add the item sub total to a running total variable, then finally display the total price. when the quote/proposal/shopping cart is finalized and converted into an order, you will need to store the contents of the session based cart in a database. you would need (at least) two tables to store the information. the 1st table - 'orders', would hold the unique/one-time order information. this will produce an order_id (auto-increment integer primary index). the 2nd table - 'order_items', would hold rows containing the item id and quantity information making up the order, related back to the order they belong to through the order_id value.
  6. the following is an sql query and the pdo based code needed to retrieve a user's membership status once it has been stored with the information needed to manage and track the data - $sql = "SELECT type, start_date, end_date FROM membership WHERE user_id = ? AND ? BETWEEN start_date AND end_date"; $stmt = $pdo->prepare($sql); $stmt->execute([$user_id,$date]); $user_membership = $stmt->fetch(); the membership table would have columns for - id (integer auto-increment primary index), user_id (integer user/bowler id) , type (integer membership type id that is mapped elsewhere to the membership status names), start_date (date), and end_date (date). the above query is SELECTing the start and end dates, in case you want to display them. remove them from the query if this information is not needed. example code allowing the selection of a made up user and selecting a date to demonstrate how the above query could be used - <?php // retrieve and display a selected user's membership status on any date (default is current date) // define mapping of membership id values to labels/names $membership_map = [3=>'Full Member', 2=>'Social Member', 1=>'Non-member']; require 'pdo_connection.php'; // condition inputs $date = $_GET['date'] ?? date('Y-m-d'); $user_id = $_GET['user_id'] ?? 0; // if a user has been selected, retrieve membership status if($user_id) { $sql = "SELECT type, start_date, end_date FROM membership WHERE user_id = ? AND ? BETWEEN start_date AND end_date"; $stmt = $pdo->prepare($sql); $stmt->execute([$user_id,$date]); $user_membership = $stmt->fetch(); } // make up sample users data - you would query your database table instead $users = []; $users[1] = ['first_name'=>'fn1', 'last_name'=>'ln1']; $users[2] = ['first_name'=>'fn2', 'last_name'=>'ln2']; $users[3] = ['first_name'=>'fn3', 'last_name'=>'ln3']; // display date selection input ?> <form> <input type='date' name='date' value='<?php echo $date;?>'><br> <?php // display user selection input ?> <select name='user_id'> <option value=''>Select a User</option> <?php foreach($users as $id=>$arr) { $sel = $user_id == $id ? ' selected' : ''; echo "<option value='$id'$sel>{$arr['first_name']} {$arr['last_name']}</option>"; } ?> </select><br> <input type='submit'></form> <?php // display the membership status if($user_id) { $user = $users[$user_id]; // 'retrieve' the specific user data from the made up sample data - you would query your database table instead echo "The user - {$user['first_name']} {$user['last_name']},"; if(empty($user_membership)) { //there was no matching row in the membership table echo " has no active membership matching the date - $date, and is therefore a $membership_map[1] on that date."; } else { // there was a matching row in the membership table echo " has an active membership, from: {$user_membership['start_date']}, to: {$user_membership['end_date']}, matching the date - $date, and has a membership type of - {$membership_map[$user_membership['type']]}"; } }
  7. i reviewed your code. i notice there's an event table. if you are going to allow members to participate in events, your design needs to be able to determine the membership status on any date, past/present/future, such as the date(s) of an event. by only storing the current status in a column and updating it to manage the membership expire and renewal, you will not be able to do anything like this. also, to 'update' the values, you will need to actually execute a query with great enough frequency to keep the values in all the rows up to date, so that someone visiting the site doesn't ever see wrong information. if you do what i posted in my reply above in this thread, you will have a simple solution that can determine the membership status on any date, doesn't require any update queries at all, and will always return current, correct and accurate membership status information.
  8. if you mean named place-holders, no, the order doesn't matter. they are matched via their names.
  9. that's a good introduction that can be boiled down to the following for select, insert, update, and delete queries - 1) when you make the connection, set the character set to match your database tables, set the error mode to exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc. 2) if there are no external/unknown values being put into the sql query statement, just use the PDO query() method. this returns a PDOStatement object for accessing the result from the query. 3) if there are external/unknown values being put into the sql query statement, use a ? place-holder for each value in the sql query syntax (without single-quotes around it/them) and add each value to an array. call the PDO prepare() method, which returns a PDOStatement object, same as for the above item, and then call the PDOStatement execute() method, with the array of input values as a parameter. Items #2 and #3 can be combined into a single user written method, so that you can have a common single-point call to use throughout your code.
  10. the code you are currently producing is where we were back when using the mysql_ extension. it took a lot of code to securely handle each different type of data being put into the sql query statement and a lot of code to provide error handling for all the statements that can fail. by using prepared queries, the simple and consistent PDO extension, and exceptions to handle errors, most of the implementation detail code disappears. you only have to validate that data meets the needs of the application, which in most cases is just to make sure it is not an empty value or that is has an expected format, form the sql query statement, with place-holders for any external/unknown data values and array of input data values, then either call the prepare/execute methods or the query() method (which you can combine by extending the PDO class with a general purpose query method that accepts an optional 2nd array parameter of input values) to run the query. letting php handle the exception/error will give you all the file name, line number, and sql error information, that will either get displayed or logged based on the php error settings, without requiring any conditional logic in your code or ever touching the code after it is written. this just takes one line of code to set the error mode to exceptions when you make the connection. (lol i just noticed that you are using the connection error statements in your insert query error handling.)
  11. nope. you should validate input only, not alter it. for any sanitation you can come up with, there are libraries of things hackers use that can get past it. the only fool-proof way of preventing anything in data from being treated as sql syntax is to use a true prepared query (PDO has emulated prepared queries that should be avoided.) prepared queries also eliminate all the single-quotes around values and all the concatenation, extra quotes for concentration, and any {} that people have used to get values into the sql query statement. don't waste your time writing and then changing code just because the context changes. write code once and leave it alone.
  12. since you should be using a prepared query with external/unknown data values, your current code is not general purpose and will need to be redone to make it secure. you will want to switch to the much simpler php PDO extension, since it requires fewer statements to accomplish any task and the result from prepared and non-prepared queries can be treated in the same way. also, you should be using exceptions to handle connection, query, prepare, and execute errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) by unconditionally outputting the raw error information on a live/public server, you will only be helping hackers who intentionally trigger errors, since connection errors contain the db username, and all the errors contain server path information. next, to dynamically produce sql statements, use arrays to hold the column/value terms and implode() the results. this will eliminate all the extra commas and substr() statements. lastly, you apparently didn't look very closely at the echoed sql query statement. you are putting back-ticks ` around the values. do you know what using back-ticks in an sql context means?
  13. in the most general purpose and simple implementation, the membership status value for a user is derived data and should not specifically be updated/reset, just queried for when needed. you would query to get the membership status value by querying to find if there is an 'active' (the current date or date in question is between the start and end dates) membership record for the user in question. if there is a matching record, retrieve the membership status value. if not, use the default Non Member value.
  14. the action='...' attribute in a form tag is the URL to which the form will submit. it is not the name of a php function, since the browser has absolutely no idea what the server side code is. since you should be submitting the form to the same page it is on, you can just leave the whole action='...' attribute out of the form tag. when the form is submitted, your form processing code needs to detect that a post method form was submitted, validate the submitted data, then safely supply that data to the INSERT query. to safely supply the data to a query, you will want to use a prepared query, with a place-holder in the sql query statement for each value, the supply the actual data when the query gets executed.
  15. no. $map is an array, with an element/line for each input/output mapping. starting what what i posted, to avoid making even more changes, add a line for each of your new role names and tax classes - $map['wholesale_silvia_silver'] = 'WholesalePSTGST'; $map['wholesale_silvia_gold'] = 'WholesalePSTGST'; ...
  16. yes. that is the ideal way of implementing this. but before you start making changes like this, make sure this is within your programming skill level. currently, the mapping of role (name) to tax class is hard-coded in the program logic you have shown in this thread, and it would appear that in an earlier version there were 5 different functions to do this, one for each possible input/output combination. the use of the $map array that i suggested, simplifies the current method, so that you are not writing out and fixing program logic for each possible combination. you just have an array that defines the input/output combinations. to change to having the tax class defined with the role, would require that you add a column to the database table (or perhaps just add a new key/value if the table is using the wordpress metadata style of storage), write a new ->getUserWholesaleClass() method to retrieve the value, then change the code you have posted in this thread so that it just gets and returns the tax class for the user.
  17. dreamweaver was never any good. the $map = []; line is valid for php 5.4 and higher. no. don't use the original logic. you should NOT find yourself adding program logic each time you add a new data value. this is a sign you are doing something wrong. in fact, wherever the roles are being defined, should have a column for the tax type, then just retrieve and use the tax type for the current user and all the code in this thread would go away.
  18. if the error is on the above line, it's missing a ) repeated in two places in the code.
  19. then, all the in_array() statement(s) can be removed. just get the role value (either using current() or the zero'th element of the array) and use it directly in comparisons. i suspect the code is using the returned values to dynamically/indirectly call user classes/methods, in which case the letter-case doesn't matter. if the error is on the $map = []; line, it is because you are using a very old and obsolete php version. the short array syntax [] was introduced in php 5.4.
  20. first, a couple of questions - 1. can a user have more than one role value at a time? i ask this because in the all_custom_tax_classes function, the last role found is what is returned, but in the in-line function, the first role found is what is returned. if there is only a single value in the user's role array, all this code can be greatly simplified. 2. the values being returned from the all_custom_tax_classes function have camel-case letter-case, but the values being returned from the in-line function are all lower-case. does the code at some point convert all values to lower-case? in any case, this code needs to be converted to be a general purpose data-driven design, where a data structure defines what simple code should do, since all it is doing is mapping found input values to output values. once this is done, all you need to do is edit the data structure to add new sets of values. the following (untested, could contain typos) is what the original/working code would look like - /* * APPLY DIFFERENT TAX RATE BASED ON CUSTOMER USER ROLE * (Code compacted in one unique hook instead of 5 functions with the same hook) */ // in the original version, the $tax_class input is conditionally modified and returned. $product is not used. function all_custom_tax_classes( $tax_class, $product ) { global $current_user; // used in isset() only // Getting the current user $curr_user = wp_get_current_user(); // not used in 'active' code $curr_user_data = get_userdata($current_user->ID); // not used in 'active' code // 1 customer_tax_exempt /* special tax rate: zero if role: Customer Tax Exempt */ /*if ( in_array( 'customer_tax_exempt', $curr_user_data->roles ) ) $tax_class = 'CustomerTaxExemptClass'; // 2 customer_pst_exempt // special tax rate: charge only GST if role: Customer PST Exempt if ( in_array( 'customer_pst_exempt', $curr_user_data->roles ) ) $tax_class = 'CustomerPSTExemptClass'; */ // 3, 4 & 5 WHOLESLE SUITE SPECIAL WHOLESALE TAX RATES if (isset($current_user) && class_exists('WWP_Wholesale_Roles')) { $wwp_wholesale_roles = WWP_Wholesale_Roles::getInstance(); $wwp_wholesale_role = $wwp_wholesale_roles->getUserWholesaleRole(); // get an array of the user roles - is there ever more than one element? // define an array that maps input values to output values - note: if the user role values were defined to be the same as the expected return values, this step wouldn't be needed $map = []; $map['wholesale_customer'] = 'WholesalePSTGST'; // charge both PST and GST $map['wholesale_pst_exempt'] = 'WholesalePSTExempt'; // charge only GST $map['wholesale_tax_exempt'] = 'WholesaleZeroTax'; // charge neither if (!empty($wwp_wholesale_role) { // the following assumes that the 1st role value found is what is returned. if a user can have more than one role, this differs from the original logic, in that the last role found is what is returned. // loop over the map array foreach($map as $key=>$value) { // test if the key is in the role array if(in_array($key,$wwp_wholesale_role)) { // if so, return the value corresponding to the key return $value; } } } } // if none of the role values was found in the user role(s), return the original $tax_class value to the calling code return $tax_class; } /* ADDITIONAL FILTERS TO ALTER THE SHIPPING TAX FOR DIFFERENT TAX CLASSES BASED ON CUSTOMER USER ROLE */ add_filter( 'woocommerce_product_get_tax_class', 'all_custom_tax_classes', 1, 2 ); // calls the above function add_filter( 'woocommerce_product_variation_get_tax_class', 'all_custom_tax_classes', 1, 2 ); // calls the above function // calls the in-line function. the $option_value input is returned if none of the logic returns first. add_filter( 'option_woocommerce_shipping_tax_class' , function( $option_value ) { global $wc_wholesale_prices; if ( $wc_wholesale_prices && is_a( $wc_wholesale_prices , 'WooCommerceWholeSalePrices' ) ) { $wwp_wholesale_role = $wc_wholesale_prices->wwp_wholesale_roles->getUserWholesaleRole(); // get an array of the user roles - is there ever more than one element? // define an array that maps input values to output values - note: if the user role values were defined to be the same as the expected return values, this step wouldn't be needed // use the same definition as above (you would actually do this through configuration data so as to not repeat it) // i'm assuming that the same letter-case values used above will work here. if not, alter this data as needed. $map = []; $map['wholesale_customer'] = 'WholesalePSTGST'; // charge both PST and GST $map['wholesale_pst_exempt'] = 'WholesalePSTExempt'; // charge only GST $map['wholesale_tax_exempt'] = 'WholesaleZeroTax'; // charge neither if (!empty($wwp_wholesale_role) { // the following assumes that the 1st role value found is what is returned. this is the same operation as the original code here. // loop over the map array foreach($map as $key=>$value) { // test if the key is in the role array if(in_array($key,$wwp_wholesale_role)) { // if so, return the value corresponding to the key return $value; } } } } return $option_value; } , 10 , 1 ); assuming no mistakes in the above code and it works correctly, all you would need to do is modify the $map array(s) to add your new definitions.
  21. assuming that you are dynamically producing the checkboxes (if not that would be your 1st step), you would test if the corresponding submitted post data isset() for the current checkbox you are outputting. to address that you are initially checking all the checkboxes, you would have a common program variable that you initially setup with the necessary data, then copy the submitted post data to the same variable once the form has been submitted. see the following example code - <?php // recursive trim call-back function function _trim($val) { if(is_array($val)) { return array_map('_trim',$val); } else { return trim($val); } } $post = []; // holds a trimmed, working copy, of the submitted post data or any initial data // post method form processing if($_SERVER['REQUEST_METHOD'] == 'POST') { // examine the submitted data echo '<pre>'; print_r($_POST); echo '</pre>'; $post = array_map('_trim',$_POST); // get a trimmed copy of the submitted post data. use elements in $post in the rest of the code on the page // the rest of the form processing code goes here... } // get method business logic - get/produce data needed to display the page // if you were editing existing stored data, you would initially (if the $post variable is empty) retrieve that here into the $post variable if(empty($post)) // note: this requires at least one non-checkbox/radio form field to be present in case all checkbox/radio fields are ever unchecked { // for a set of defined checkboxes that are initially set, the same as through you were editing existing data where all the checkboxes are initially checked, set that up here // loop over a 'defined' list of checkboxes. for demo purposes this is just a list of ids from 1-4 foreach(range(1,4) as $id) { $post['chk'][$id] = true; } } ?> <form method='post'> <input type='hidden' name='action' value='create'> <?php // loop over a 'defined' list of checkboxes. for demo purposes this is just a list of ids from 1-4 foreach(range(1,4) as $id) { // determine if checkbox is checked $chk = isset($post['chk'][$id]) ? ' checked' : ''; echo "<input type='checkbox' name='chk[$id]'$chk> Checkbox: $id<br>"; } ?> <input type='submit'></form>
  22. your INSERT query is open to sql injection. anyone can cause that query to insert data from any of your database tables, especially since you are using the 'root' user, and the rest of your code on this page will happily show the content that was just inserted from the other database/tables. i recommend that you take this code off of a live/public site until you actually secure it against sql injection, don't use the 'root' user in you applications, create a user that has just the database permissions you need, your delete operation should use a post method form, and in real life data isn't actually deleted, it is updated to indicate it is not in use, in case it ever needs to be recovered.
  23. define the column(s) in your table as unique indexes. insert the data and detect if a duplicate key error occurred. in the error handling logic, execute one select query to find which column(s) contain the same values you just tried to insert.
  24. actually, you have an unnecessary ( which then doesn't have a matching ), both on line #6. as to the posted logic. you have far too much code, mainly because your form and for processing code are not on the same page and you are using the mysqli database extension. if you put the form processing code on the same page as the form, stop copying variables to other variables, store validation errors in an array, switch to the much simpler php PDO database extension, and use exceptions to handle database errors, about half of the code you have now will go away. you also have a logic mistake later in the code. your 'usernotfound' condition is part of the password check logic (which can only be either true of false, there's no point in the final else statement), not as part of the fetch check logic. this type of mistake can be avoided by properly indenting your code and also by eliminating unnecessary code (see the above paragraph.)
  25. that's a statement of an alternate method to get php code to run on your server.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.