endouken Posted March 28, 2011 Author Share Posted March 28, 2011 I'm working with chunks of the code from our different posts, do me a favor and post our fully updated new code to make it easier <?php session_start(); $localhost="00.000.000.00"; $username="###"; $password="###"; $database="mfirst"; mysql_connect($localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); // Clean up the code the user entered $uniquecode = mysql_real_escape_string($_POST['discountcode']); // OR whatever the name of the input is $discountcodecheck = mysql_query("SELECT uniquecode, discount FROM discounttable WHERE uniquecode = '".$uniquecode."'"); $row = mysql_fetch_array($discountcodecheck, MYSQL_BOTH); // MYSQL_BOTH incase it's alphanumeric, and establish the $row array with the codes $dcode = $row['uniquecode']; $remainingusescheck = mysql_query("SELECT uses FROM discounttable WHERE uniquecode = '".$uniquecode."'"); $row1 = mysql_fetch_array($remainingusescheck, MYSQL_NUM); // MYSQL_NUM since the uses should be a number value, establish $row1 array with the uses $uses = $row1['uses']; // Check the code vr the code the user entered, and let's make sure the user hasn't used it over 2 uses if (($dcode = $uniquecode) && ($uses <= 0)) { // Since they check out we can do our discount $discount = ($_SESSION['sessionprice'] - $row['discount']); $adduse = mysql_query("UPDATE discounttable SET uses=uses-1 WHERE uniquecode = '".$uniquecode."'"); $_SESSION['sessionprice'] = $discount; } else { // Let them know the code or the uses have been reached, you could seperate the if statement above to give an error for uses ?><script type="text/javascript"> alert("Discount Code Entered Is Either Not Valid Or Has Been Previously Used."); history.back(); </script><?php } // close our if for checking the code and the uses mysql_close(); ?> Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 28, 2011 Share Posted March 28, 2011 Okay let's have a look at what is going on with the uses. if (($dcode = $uniquecode) && ($uses <= 0)) { changes to: if (($dcode = $uniquecode) && ($uses != 0)) { Not quite working still - just rejects both the code with negative uses and the code with 90 uses (i changed <=2 to <=99) We will now check to make sure that they have 2 uses by saying they start with 2 uses for each code used by saying uses can't be 0. (You'll have to set the uses to 2 in the database before testing again, so clean up any negative numbers and numbers over 2.) It was working because it was rejecting a user from using a code. $adduse = mysql_query("UPDATE discounttable SET uses=uses-1 WHERE uniquecode = '".$uniquecode."'"); We're subtracting uses by 1 after the verification is passed so if they use the code once they will have 1 use left before reaching 0 where our code will not allow another use. $row1 = mysql_fetch_array($remainingusescheck, MYSQL_NUM); // MYSQL_NUM since the uses should be a number value, establish $row1 array with the uses $uses = $row1['uses']; We are getting the information from the database from our query earlier $remainingusescheck. We are then placing it into a variable $uses to compare to. This should work provided uses is a column in the discounttable table. (Again make sure the uses is set to 2 for each code available) Make that small adjustment and let me know what happens. (Make the updates to the database so they uses start at 2) EDIT: Will the problem lie here? as we're saying uses = $row1, however we're not specifying uses anywhere in $row1...i dunno? To answer your question no it shouldn't. We're just getting the information from the query for uses and placing the information into an array called $row1. So we can compare the number of uses associated with the code the user entered. So if the user has 1 use left $row['uses'] will be 1 and we're just placing it into a variable called $uses to make it easier to read in the IF statement, well for me anyway lol. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 28, 2011 Author Share Posted March 28, 2011 Made the changes to php and the database, and now it's rejecting both codes (one code has 2 uses, the other has 0). Where you say: if (($dcode = $uniquecode) && ($uses != 0)) { We will now check to make sure that they have 2 uses by saying they start with 2 uses for each code used by saying uses can't be 0. (You'll have to set the uses to 2 in the database before testing again, so clean up any negative numbers and numbers over 2.) It was working because it was rejecting a user from using a code. What does the exclamation mark mean in $uses != 0, and where did we say in php they have 2 uses - or did you simply mean via mysql we would specify this? Appreciate the perseverance and the tutorship! Tom. Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 28, 2011 Share Posted March 28, 2011 We started the uses as 2 in the database because the query we are using subtracts 1 from a correct code used. So we couldn't start with 0 because you'd go negative. $adduse = mysql_query("UPDATE discounttable SET uses=uses-1 WHERE uniquecode = '".$uniquecode."'"); You can change that around if you want and do SET uses=uses+1 if you want to start the uses at 0 in your database, but you will have to change the IF statment again. != means does not equal, so we use that because if it dosen't equal 0 they have uses remaining. Change: if (($dcode = $uniquecode) && ($uses != 0)) { to: if (('$dcode' == '$uniquecode') && ($uses != 0)) { That's just incase I made a syntax error, but give that a whirl and see what you get. At this point we know it's doing the check and that something isn't meeting the criteria. If these adjustments don't work I'll have a look at the code before the IF statment again. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 28, 2011 Author Share Posted March 28, 2011 Hmmm, it's still rejecting both codes (still with one code with 2 uses, the other has 0). It was working 50% before, subtracting -1 from the uses column - but it was also accepting a code with 0/zero uses remaining and then subtracting -1 so the codes remaining uses ran into negatives. Code we were using when this was happening was: if (($dcode = $uniquecode) && ($uses <= 2)) { Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 28, 2011 Share Posted March 28, 2011 if (($dcode = $uniquecode) && ($uses <= 2 && $uses >= 0)) { Give that a try.. Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 28, 2011 Share Posted March 28, 2011 I'll try to check in tomorrow to see how this goes. I'm going to visit my son in the NICU at the hospital but when he's sleeping I check from my phone to keep my mind busy! Quote Link to comment Share on other sites More sharing options...
endouken Posted March 28, 2011 Author Share Posted March 28, 2011 Heya, I don't know how to respond to that - other than i wish him and you well. Apologies i fell asleep last night - 4:30am over here when i sent my last message Still not working - just rejects both codes. I can re-assure you the mysql side of it is set up correctly, i've phpmyadmin as well as mysql workbench which both confirm the values for the two codes uses are 0 and 2 respectively. I look forward to hearing from you in due course, thank you for your help and patience. Tom. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 28, 2011 Author Share Posted March 28, 2011 Cant edit my above post, Might be worth mentioning that i've also noticed the following - which may help diagnose the problem: - When the input box is blank the java error message is displayed correctly on submit, however when an incorrect code is entered the error message is not displayed - it runs the scripts and the price doesn't change (so it works OK....but it doesn't show the 'if false' java error response other than on a blank submit) - i tried flipping the < and > signs to see what error it would throw up...however using the below code, both codes were accepted regardless of which < or > sign was used... if (($dcode = $uniquecode) && ($uses <= 0)) { Perhaps food for thought... Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 28, 2011 Share Posted March 28, 2011 That's because the code I gave you was generic and not all out super kick butt LOL. In reference to the Java error pop up. The if statement has to check uses so it knows they must be over 0 but under 2. That's why I did <= 2 (less than or equal to 2) and to prevent negative numbers I did != 0 (does not equal 0). Do you see my logic? I will be happy to do a kick butt super one when I get home in 10hrs so 11pm or later EST. Phone typing slow LOL Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 28, 2011 Share Posted March 28, 2011 if (($dcode = $uniquecode) && . . . Needs to be if (($dcode == $uniquecode) && . . . Quote Link to comment Share on other sites More sharing options...
endouken Posted March 28, 2011 Author Share Posted March 28, 2011 That's because the code I gave you was generic and not all out super kick butt LOL. In reference to the Java error pop up. The if statement has to check uses so it knows they must be over 0 but under 2. That's why I did <= 2 (less than or equal to 2) and to prevent negative numbers I did != 0 (does not equal 0). Do you see my logic? I will be happy to do a kick butt super one when I get home in 10hrs so 11pm or later EST. Phone typing slow LOL Heya. If you could help with a super kick butt one that would be awesome - i'm busy with the rest of the shopping cart process which i must admit is a hell of a lot easier than the discount engine you wrote! Chat laterz Tom. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 28, 2011 Author Share Posted March 28, 2011 if (($dcode = $uniquecode) && . . . Needs to be if (($dcode == $uniquecode) && . . . Thanks for replying Pikachu. Unfortunately that's not had any impact. It's as if the code is simply ignoring this element of the code, and processing the discount regardless of whether the 'uses' remaining is 0 or 2... Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 28, 2011 Share Posted March 28, 2011 Pika is correct for the syntax. I'll work on the full blown script later. Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 29, 2011 Share Posted March 29, 2011 I'm working on your script now and should be done soon! Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 29, 2011 Share Posted March 29, 2011 Okay I created a test database based on what you were using, and tested the code to make sure it worked and I had no issues. I've also setup this code to start each uniquecode with 2 uses. If you wished to start each uniquecode you'd have to modify the code to change the IF statment to the following: if ($uses >= 0 && $uses != 2) { And then you'd have to change the update to the database to add uses so you'd change it to this $subtractuse just change to $adduse so you can keep track of whats going on. $adduse = mysql_query("UPDATE discounttable SET uses=(uses+'1') WHERE uniquecode = '".$uniquecode."'"); I've also added error checking for the following: Make sure the user entered a code and didn't just click submit, if not notify the user. Make sure the user has entered a valid code, if not notify the user. Make sure the user hasn't used all of their uses, if they have notify the user. So here is your super duper kick butt discount engine: (I also attached it to my post so you can download it, selecting code has issues with keeping formatting.) ( I'll also take that drink now! LMAO ) <?php // Some error reporting error_reporting(E_ALL); // Start our session session_start(); // Database variables $localhost = "00.000.000.00"; $username = "###"; $password = "###"; $database = "mfirst"; // Establish a connection to the mfirst database $link = mysql_connect($localhost, $username, $password); if (!$link) { die('Cound not connect: ' . mysql_error()); } // Select the database we want to work with @mysql_select_db($database, $link) or die( "Unabled to select $database database"); // Echo our HTML form for user submission echo $form_block; // Let's start working with what happens when a user enters a code and clicks submit! if (isset($_POST['submit'])) { // Clean up the user inputted code to make it safe $uniquecode = mysql_real_escape_string($_POST['discountcode']); // Let's check to make sure unique code isn't empty! if (!empty($uniquecode)) { // Let's query the database to get the unique code, uses, and the discount amount. $codeinfo = "SELECT uniquecode, discount, uses FROM discounttable WHERE uniquecode = '".$uniquecode."'"; $result = mysql_query($codeinfo) or die( "Couldn't get the requested information" . mysql_error()); // Let's get the information returned from our $codeinfo query and place it into an array $row = mysql_fetch_array($result, MYSQL_ASSOC); // $row is now the array that will hold our data for the given code. // Make some variables to hold the information we want to keep things organized $dcode = $row['uniquecode']; $discountamt = $row['discount']; $uses = $row['uses']; // Let's check to make sure the code entered has been found (Rows returned will be 1) if (mysql_num_rows($result) == '1') { // A code was found so now let's find out if they have any uses remaining // Let's make sure uses isn't 0 and that they have 2 or fewer uses left if ($uses != 0 && $uses <= 2) { $discount = ($_SESSION['sessionprice'] - $discountamt); // Discount math to well subtract the discount from the end users cost $subtractuse = mysql_query("UPDATE discounttable SET uses=(uses-'1') WHERE uniquecode = '".$uniquecode."'"); $_SESSION['sessionprice'] = $discount; // Make sure the price for the session reflects the total discount applied // They have used all of their uses so let's tell the end user ?><script type="text/javascript"> alert("You used discount code <?php echo "$dcode"; ?> and saved <?php echo "$discountamt"; ?> . Your new total is <?php echo "$startprice"; ?> !"); history.back(); </script><?php } else { // They have used all of their uses so let's tell the end user ?><script type="text/javascript"> alert("You have used that discount code <?php echo "$dcode"; ?> twice and may no longer use it."); history.back(); </script><?php } // Ends the uses check } else { // The code the user entered wasn't found ?><script type="text/javascript"> alert("Sorry but you've entered an invalid code."); history.back(); </script><?php } // Ends the code check } else { // The user didn't enter a code ?><script type="text/javascript"> alert("You must enter a discount code in order to apply a discount."); history.back(); </script><?php } // Ends the empty post code } // Ends the submit routine // Close our database connection since we no longer need it. mysql_close($link); ?> [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
endouken Posted March 29, 2011 Author Share Posted March 29, 2011 Okay I created a test database based on what you were using, and tested the code to make sure it worked and I had no issues. I've also setup this code to start each uniquecode with 2 uses. If you wished to start each uniquecode you'd have to modify the code to change the IF statment to the following: if ($uses >= 0 && $uses != 2) { And then you'd have to change the update to the database to add uses so you'd change it to this $subtractuse just change to $adduse so you can keep track of whats going on. $adduse = mysql_query("UPDATE discounttable SET uses=(uses+'1') WHERE uniquecode = '".$uniquecode."'"); I've also added error checking for the following: Make sure the user entered a code and didn't just click submit, if not notify the user. Make sure the user has entered a valid code, if not notify the user. Make sure the user hasn't used all of their uses, if they have notify the user. So here is your super duper kick butt discount engine: (I also attached it to my post so you can download it, selecting code has issues with keeping formatting.) ( I'll also take that drink now! LMAO ) <?php // Some error reporting error_reporting(E_ALL); // Start our session session_start(); // Database variables $localhost = "00.000.000.00"; $username = "###"; $password = "###"; $database = "mfirst"; // Establish a connection to the mfirst database $link = mysql_connect($localhost, $username, $password); if (!$link) { die('Cound not connect: ' . mysql_error()); } // Select the database we want to work with @mysql_select_db($database, $link) or die( "Unabled to select $database database"); // Echo our HTML form for user submission echo $form_block; // Let's start working with what happens when a user enters a code and clicks submit! if (isset($_POST['submit'])) { // Clean up the user inputted code to make it safe $uniquecode = mysql_real_escape_string($_POST['discountcode']); // Let's check to make sure unique code isn't empty! if (!empty($uniquecode)) { // Let's query the database to get the unique code, uses, and the discount amount. $codeinfo = "SELECT uniquecode, discount, uses FROM discounttable WHERE uniquecode = '".$uniquecode."'"; $result = mysql_query($codeinfo) or die( "Couldn't get the requested information" . mysql_error()); // Let's get the information returned from our $codeinfo query and place it into an array $row = mysql_fetch_array($result, MYSQL_ASSOC); // $row is now the array that will hold our data for the given code. // Make some variables to hold the information we want to keep things organized $dcode = $row['uniquecode']; $discountamt = $row['discount']; $uses = $row['uses']; // Let's check to make sure the code entered has been found (Rows returned will be 1) if (mysql_num_rows($result) == '1') { // A code was found so now let's find out if they have any uses remaining // Let's make sure uses isn't 0 and that they have 2 or fewer uses left if ($uses != 0 && $uses <= 2) { $discount = ($_SESSION['sessionprice'] - $discountamt); // Discount math to well subtract the discount from the end users cost $subtractuse = mysql_query("UPDATE discounttable SET uses=(uses-'1') WHERE uniquecode = '".$uniquecode."'"); $_SESSION['sessionprice'] = $discount; // Make sure the price for the session reflects the total discount applied // They have used all of their uses so let's tell the end user ?><script type="text/javascript"> alert("You used discount code <?php echo "$dcode"; ?> and saved <?php echo "$discountamt"; ?> . Your new total is <?php echo "$startprice"; ?> !"); history.back(); </script><?php } else { // They have used all of their uses so let's tell the end user ?><script type="text/javascript"> alert("You have used that discount code <?php echo "$dcode"; ?> twice and may no longer use it."); history.back(); </script><?php } // Ends the uses check } else { // The code the user entered wasn't found ?><script type="text/javascript"> alert("Sorry but you've entered an invalid code."); history.back(); </script><?php } // Ends the code check } else { // The user didn't enter a code ?><script type="text/javascript"> alert("You must enter a discount code in order to apply a discount."); history.back(); </script><?php } // Ends the empty post code } // Ends the submit routine // Close our database connection since we no longer need it. mysql_close($link); ?> I will happily donate some money for a few drinks to a PayPal account - no jokes, you've been of unparalleled help - just let me know the email address (That's not against forum rules is it...?) This is working, but is throwing up a minor bug. The below error message is displayed... Notice: Undefined variable: form_block on line 25 ...triggered by: - Entering a valid code - the screen changes to this error message (at mysite/discoutncode.php) and stops. Hitting the back button returns to the previous page with the price changed successfully. So it works, but with an odd error message. - Enter an invalid code - the java error message displays and as soon as OK is clicked the website flashes to the above error page before going straight back to the previous page automatically with the price unchanged. So it works, but there's an odd flash of that error message. - Enter a code with 0 uses left - same as above: entering an invalid code. - Blank code - same as above: entering an invalid code. Worth noting a different/the correct java message is shown for 2, 3 and 4. Is it safe to just delete this line as it's undefined - or is there a bit missing from the code? Thanks! Tom. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 29, 2011 Author Share Posted March 29, 2011 Fixed! I commented out the line "echo $form_block;" and redirected back to the form using a header command at line 64 header ('Location: http://www.mysite'); And all seems to be working. Is what I've done OK? Thanks again for your help - let me know that email address Tom. PS - don't suppose you know the answer to "what mysql datatype is best for storing intervals" - i asked this in the mysql section of phpfreaks and didn't get any satisfactory responses. Currently I calculate the renewal date of the product in php, but if i want to add a different product with a different renewal date i would need to start messing around with php code - where ideally i want to reference todays date and add a duration to it which is assumed in the productdetails table...but it looks like mysql can only store dates, not a duration of time. And as there are different seconds in each month/year i can't use the integer datatype... Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 29, 2011 Share Posted March 29, 2011 You got the correct responses; even if they weren't what you wanted to hear. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 29, 2011 Author Share Posted March 29, 2011 You got the correct responses; even if they weren't what you wanted to hear. I got two responses - one from optikalefx who suggest what i've done - but this is not the correct/best way of calculating memberships based products for the reason I've stated above. The other response from The Little Guy suggested i use the MySQL date_sub() - i responded asking a follow up question as it seemed to me the end date was still being calculated using an assumption in php rather than an assumption in mysql. I received no reply back from The Little Guy so ergo i got no satisfactory response. There are two satisfactory responses: 1) confirmation that mysql cannot store a duration, or 2) confirmation that mysql can store a duration along with the method. "What i want to hear" bears absolutely no relevance to my perception of a satisfactory answer. If you look back over that thread, you will see neither 1) or 2) were garnered and instead i was (i believe) presented with two php based methods, which although leans towards conclusion #1 does not confirm it. Perhaps if you know the answer, you could let me know as i realize you have much more experience than I or even most on here? Also, i would like to point out i have been polite and courteous to all on here, despite the anonymity that internet forums provide. I realize you probably speak to many irritating and rude people on here, but I hope you do not judge me as one of these based on a my brief posting history. Maybe i need to post more to earn your respect, but that frankly seems like floored logic when deciding who you are and are not polite to. I did appreciate your earlier response however and my thanks to you was, and still is, genuine. Rgrds, Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 29, 2011 Share Posted March 29, 2011 I'm not trying to be rude, nor did I perceive you as rude, even if my response seemed rather curt. But you did get the correct response as to how to handle the problem in that thread; you don't need to store a duration, you need to store an end date. After that, your response went right back to wanting to store a duration in the database. Doing so forces a calculation to be made every time you need to check if something has expired, rather than performing a simple comparison. You said the expiration date would either be a month or a year, so what you really need to do is use MySQL's DATE_ADD() function, using a variable to store the interval, when inserting the record to calculate and store the expiration date. If there's the possibility that it may be a renewal of something that hasn't yet expired, you first query to see if the product is already purchased, and if so has it expired previously? If it has expired, update the expiration with the new expiration date based on CURDATE(), if it hasn't expired, update it by adding the interval to the expiration date in the record. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 29, 2011 Author Share Posted March 29, 2011 I'm not trying to be rude, nor did I perceive you as rude, even if my response seemed rather curt. But you did get the correct response as to how to handle the problem in that thread; you don't need to store a duration, you need to store an end date. After that, your response went right back to wanting to store a duration in the database. Doing so forces a calculation to be made every time you need to check if something has expired, rather than performing a simple comparison. You said the expiration date would either be a month or a year, so what you really need to do is use MySQL's DATE_ADD() function, using a variable to store the interval, when inserting the record to calculate and store the expiration date. If there's the possibility that it may be a renewal of something that hasn't yet expired, you first query to see if the product is already purchased, and if so has it expired previously? If it has expired, update the expiration with the new expiration date based on CURDATE(), if it hasn't expired, update it by adding the interval to the expiration date in the record. Perfect - thanks! I believe i miss-understood (down to lack of experience i admit - been doing php for 1-2 weeks now) The Little Guy's response - but your explanation goes further, far enough for me to grasp what you (and probably he originally) meant. In which case I am happy to admit you were partially correct - i did receive the response required, i just didn't realize it! So i've currently got a start and end date in my database, but i have "+1 year" in my php code. You mentioned using a variable to store the interval, could i just: - store a varchar of "+ 1 year" in a 'duration' column in my db, then - get this in my php code (pseudo: $duration = select duration from products where productid = $productid ) - make the end date calculation (pseudo: strtotime(date("F j, Y", strtotime($todayDate)) . "+$duration"); - and then insert it into the purchasehistory table. That way all the assumptions are in the database, or is this method unsuitable? Is there another (better) method of storing the +1 year variable? Have i got completely the wrong end of the stick?! Really appreciate you taking the time to reply - an honest thanks Tom. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 29, 2011 Share Posted March 29, 2011 Using MySQL's date and time functions is usually more efficient than using php's. Assuming that the subscription (and/or) renewal is done via a form, you'd use the duration value submitted in the form to build the query string, along the lines of: if( $_POST['duration'] == 'month' ) { $duration = '1 MONTH'; } elseif( $_POST['duration'] == 'year' ) { $duration = '1 YEAR'; } else { // value is not valid, so throw an error, etc. } // build the query string using the value from above $query = "INSERT INTO table ( start_date, end_date ) VALUES ( CURDATE(), DATE_ADD( CURDATE(), INTERVAL $interval )" If you need to extend/renew an existing subscription, you can look into using INSERT INTO . . . ON DUPLICATE KEY UPDATE . . . syntax, along with some logic to check if the current subscription has already expired or not. Quote Link to comment Share on other sites More sharing options...
Skewled Posted March 29, 2011 Share Posted March 29, 2011 Your perfectly fine! I didn't have your input form so I made a php html from and echo'd it so I could test the code.. but I was so tired that I didn't remove all my test features! // Echo our HTML form for user submission echo $form_block; You can remove that from the script without issues! Also check your PM's! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.