Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jul 2005
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with INSERT INTO query

    Hello, I have a script that is going to be used for purchasing and dynamically creating gift certificates for printing.

    This script has two parts, one is to deal with purchasing dollar value gift certificates (ie: $50.00 and $100.00 certificates) and the second part is to allow the buyer to purchase a gift certificate for a specific course (this is for a spanish as a second language company) and the price is set for the cost of the course.

    The buyer has the option to purchase either types of gift certificate or both at the same time. When the buyer comes to the main page for purchasing our gift certs, they are given two options, they can choose dollar value certificate to start with or they can start with the course certs. In the order that I'm testing right now I start with the dollar value gift certs, and when the form is filled out and submitted, it is added to a temp shopping cart (mysql db entry), then the user is prompted to either purchase a course cert or checkout. I'm purchasing both for this example, so I picked the purchase a course cert option. On the course cert purchase form page, the user selects the quantity of course certs they want by inputing the number in the text field beside each course entry for each location (locations are organized by tab system... can register at multiple locations). Oh... I forgot to mention that the user can pay by check or by paypal for this example I've used the cheque option.

    Once the user submits the course cert registration form, the script generates the corresponding number of First Name and Last Name text fields on the next page so that we can print the persons name on the gift cert. When this step is completed and the submit button is pressed, it will add the course certs to a temp db entry just like the dollar value gift certs, and the it takes you to the checkout page automatically because I already have an order for dollar value gift certs in the db which is linked to the user by SID.

    On the checkout page, everything is still ok and working just fine, the user gets to review their order, and make any necessary changes.

    Now the weird part. When the buyer confirms the order on the checkout page by clicking on submit, the next page runs the following code (note: the $action var is taken from $_GET['action']):

    PHP Code:
    $link mysql_connect($db_host$db_login$db_password) or die("Could not connect to MySQL");

    mysql_select_db($db$link) or die("Could not select database: " $db);

    $query "SELECT * FROM giftcert_cart WHERE sid='$sid'";
    $query1 "SELECT * FROM course_cart WHERE sid='$sid'";

    $giftcert_num_rows mysql_num_rows(mysql_query($query));
    $course_num_rows mysql_num_rows(mysql_query($query1));

    if (
    $action == "submit_cart") {

      if (
    $giftcert_num_rows == 1){

        
    $row mysql_fetch_array(mysql_query($query));

        if (
    substr($row['payment'], 06) == "cheque") {

          
    $status "Pending Cheque";

        }
        else {

          
    $status "PayPal Notification";

        }

        
    $query2 "INSERT INTO giftcert_complete (orderid, date, firstname, lastname, email, phone, address1, address2, city, province, postalcode, fifty, onehundred, design, payment, status) VALUES ('" $row['orderid'] . "', '"$row['date'] . "', '" $row['firstname'] . "', '" $row['lastname'] . "', '" $row['email'] . "', '" $row['phone'] . "', '" $row['address1'] . "', '" $row['address2'] . "', '" $row['city'] . "', '" $row['province'] . "', '" $row['postalcode'] . "', '" $row['fifty'] . "', '" $row['onehundred'] . "', '" $row['design'] . "', '" $row['payment'] . "', '$status')";

        
    mysql_query($query2);

        if (
    mysql_affected_rows() > 0) {

          
    $query2 "DELETE FROM giftcert_cart WHERE sid='$sid'";
          
    mysql_query($query2);

        }
        else {
          
    $_SESSION['mysql_errors'][] = mysql_error($link);
          echo 
    mysql_errno($link) . ": " mysql_error($link). "\n";

          echo 
    " | INSERT INTO giftcert_complete failed! | ";

        }

      }

      if (
    $course_num_rows == 1) {

        
    $row mysql_fetch_array(mysql_query($query1));

        if (
    substr($row['payment'], 06) == "cheque") {

          
    $status "Pending Cheque";

        }
        else {

          
    $status "PayPal Notification";

        }

        
    $query2 "INSERT INTO course_complete (orderid, date, firstname, lastname, email, phone, address1, address2, city, province, postalcode, registrants, payment, status) VALUES ('" $row['orderid'] . "', '"$row['date'] . "', '" $row['firstname'] . "', '" $row['lastname'] . "', '" $row['email'] . "', '" $row['phone'] . "', '" $row['address1'] . "', '" $row['address2'] . "', '" $row['city'] . "', '" $row['province'] . "', '" $row['postalcode'] . "', '" $row['registrants'] . "', '" $row['payment'] . "', '$status')";

        
    mysql_query($query2);

        if (
    mysql_affected_rows() > 0) {

          
    $query2 "DELETE FROM course_cart WHERE sid='$sid'";
          
    mysql_query($query2);

        }
        else {
          
    $_SESSION['mysql_errors'][] = mysql_error($link);
          echo 
    mysql_errno($link) . ": " mysql_error($link). "\n";

          echo 
    " | INSERT INTO course_complete failed!";


        }

      }

      if (
    $row['payment'] == "paypal") {

        
    header("Location: http://www.spanishmania.ca/php/giftcert/paypal.php?order_id=" $row['orderid']);
        exit;

      }
      elseif (
    substr($row['payment'], 06) == "cheque") {

        
    header("Location: http://www.spanishmania.ca/php/giftcert/cheque.php?order_id=" $row['orderid']);
        exit;

      }


    Now with 99.9% of my trials the code executes just fine and it takes the temp db entry from giftcert_cart and course_cart and places them in giftcert_complete and course_complete respectively, and redirects the buyer to a page that simply re-displays their order totals and gives the user a paypal link to pay with or directions for mailing a cheque if that's the option that was chosen.

    Now in 0.1% of my trials, the entry for the dollar value certs does not get inserted into giftcert_complete but the condition mysql_affected_rows() > 0 is some how interpreted as true and the temp db entry from giftcert_cart is deleted even though the previous mysql query obviously didn't have any affected rows as it didn't insert the entry into into the db (if this happens, the temp entry should remain as a backup of the order for manual processing. this is why I check the mysql_affected_rows()).

    I don't understand why this code is executing if the temp entry is not inserted into giftcert_complete:

    PHP Code:
        $query2 "INSERT INTO giftcert_complete (orderid, date, firstname, lastname, email, phone, address1, address2, city, province, postalcode, fifty, onehundred, design, payment, status) VALUES ('" $row['orderid'] . "', '"$row['date'] . "', '" $row['firstname'] . "', '" $row['lastname'] . "', '" $row['email'] . "', '" $row['phone'] . "', '" $row['address1'] . "', '" $row['address2'] . "', '" $row['city'] . "', '" $row['province'] . "', '" $row['postalcode'] . "', '" $row['fifty'] . "', '" $row['onehundred'] . "', '" $row['design'] . "', '" $row['payment'] . "', '$status')";

        
    mysql_query($query2);

        if (
    mysql_affected_rows() > 0) {

          
    $query2 "DELETE FROM giftcert_cart WHERE sid='$sid'";
          
    mysql_query($query2);

        }
        else {
          
    $_SESSION['mysql_errors'][] = mysql_error($link);
          echo 
    mysql_errno($link) . ": " mysql_error($link). "\n";

          echo 
    " | INSERT INTO giftcert_complete failed! | ";

        } 
    just for everyone's knowledge, I checked my db along each step of my script and it definetly happens in the code above, but I'm not sure why, and I can't seem to replicate the error other than going through the purchase process about 30 to 50 times.

    I've tried print_r(get_defined_vars()) to double check my vars and make sure that nothing is getting screwed up anywhere, and everything is ok until this code above, but like I mentioned earlier, I have to submit the form 30 to 50 times before this happens. Also, I noticed this error when seeing the final purchase page with instructions on where to send your cheque where I have the totals displayed one last time for the buyer and that's where I noticed once that my dollar value gift cert total was 0.00, but like i said, I can't seem to replicate or reproduce the problem, 99.9% of the time both entries get added (course certs are always added... no problems with that one, just the dollar value gift certs.)

    just one last thing, i also tried getting the mysql_error() (and submitted 30 to 50 times again), but this returns nothing at all.

    If you want to see the script in action to better understand what I'm talking about, and hopefully you can re-created the problem, just visit:

    http://www.spanishmania.ca/php/giftcert/

    and register with any info, make sure you select the 'cheque' and 'receive them by mail' options.

    Thanks in advance for any help

  • #2
    New Coder
    Join Date
    Aug 2003
    Location
    Derby, UK
    Posts
    97
    Thanks
    0
    Thanked 14 Times in 14 Posts
    Sounds like one of two possibilities to me; either the problem is not where you think it is, or the record is being inserted but you are not finding it.

    Without seeing the rest of the code it would be impossible to speculate on the former as since you say you have tracked the code up to that point I will have to assume it is the latter. I would debug this by dumping the SQL of the insert query each time, as well as checking the error code/message. When you get the problem occuring check what insert it was trying to run and ideally try running that same query manually against the db to see what happens. Also I would comment out the delete from temp and have a debug statement there also while running these tests to make sure that the item is in the temp table and that the code to delete really si firing. Otherwise it is possible you are assuming the delete fired because the record is no longer in the tmep table, but actually you are mistaken?

    Also you don't state whether the 3-50 times you have to repeat are with identical data or different data each time? It is possible there is something about the data that is objecting to.

    If I had to guess I would say a row is being added to the complete table but whatever id you are usign to try and find it afterwards (orderid?) is not set to what you think.

    Fwiw, the code does match the functionality you are looking for as I see it, so the bug is either elsewhere or is triggered by specific data values coming into this code.

    HTH,

    Dai


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •