database query fails randomly, cannot replicate on my machine

Question

edit #1:As you can see below I have multiple $db->query 's and I am wondering if this issue could be cause by too many calls to the database on slower connections? cause the page itself works the database calls just don't go through sometimes. IF this is the issue what would be a good solution for that?

I've been having a really hard time with this one. Some reason the database code only works 2/3rds of the time for customers. I have been unable to find a reason why it would not work for them either.

When some customers use my sight everything works fine up until the final page where the payment gateway sends me a transaction ID and a confirmation on whether or not the payment was successful(1 for yes 0 for no). But for some reason my database line that adds the transaction is not working

$db->query("UPDATE transactions 
            SET charge_id = '{$trans_id}' 
            WHERE cart_id = '{$cart_id}'");

$cart_id is from a cookie (issue does not lie with users having cookies blocked.) Now I had previously thought that the issue lied within safari and IE (since customers with issue had these browsers) but after some testing both browsers work on my computer and a friends (just in case). So now I really do not know what the issue could be. Without the transaction ID being set the transaction will not be marked as complete which means it doesn't get registered in an admin panel inventory systems and the quantity of the item does not get updated. The ordered and items are going through and processing So the issue has to be on this page.

Current possible ideas(not sure how to fix either):

Too many db query causing issues for people with slower interenet.

Auto cycle side bar causing an issue (2nd code block)

Code:

<?php require_once 'system/init.php'; include 'includes/head.php'; include 'includes/navigation.php'; include 'includes/headerpartial.php'; include 'includes/leftbar.php'; ?>

<div id="maincontent" class="col-md-8">

<?php

if ($_GET['response_code'] == 1) { $trans_id = $_GET['transaction_id'];

$db->query("UPDATE transactions SET charge_id = '{$trans_id}' WHERE cart_id = '{$cart_id}'");
$db->query("UPDATE cart SET paid = 1 WHERE id = '{$cart_id}'");
$tsql = $db->query("SELECT * FROM transactions WHERE charge_id = '$trans_id' ");
$tran = mysqli_fetch_assoc($tsql);
$domain = '.'.$_SERVER['HTTP_HOST'];
setcookie(CART_COOKIE,'',1,"/",$domain,false);

?> <h1 id="reciept">Thank you for your support!</h1><hr> <p id="reciept"> On behalf of LettuceHeadsFarm <?=$tran['full_name']?> we thank you for your purchase and hope you enjoy it! </p>

<p id="reciept"> You have selected <b>"<?=$tran['pickup-location']?>"</b> as your pickup point. </p>

<table id="nav-button" class="table table-bordered table-auto"> <tbody> <tr> <td>Transaction ID : <?=$tran['charge_id']?></td> </tr> <?php $a = 1; $it = 1; $string = $tran['items']; $itemar = explode(',', $string); $num = 1;

$istr = $tran['inventory'];
$stri = explode(',', $istr);

if ($tran['status'] != "Complete") {

    foreach (array_slice($stri, $num) as $inve ){
        $exploded = explode('.', $inve);
        $itname = $exploded['0'];
        $itquan = $exploded['1'];

        $db->query("UPDATE products 
                     SET `quantity` = `quantity` - '$itquan' 
                    WHERE title = '$itname'");
        $db->query("UPDATE products 
                      SET `Sold` = `Sold` + '$itquan' 
                    WHERE title = '$itname'");
        $it++;
   } 
   $compl = "Complete";
   $db->query("UPDATE transactions 
                 SET `status` = '$compl' 
               WHERE charge_id = '$trans_id'");
}

foreach (array_slice($itemar, $num) as $itemr ){

?> <tr> <td><?=$itemr?></td> </tr>

<?php $a++; } ?>

<tr> <td> Total: <?=money($tran['grand_total']);?> </td> </tr> </tbody>

</table> <?php }else { echo "Sorry, an error occurred: ".htmlentities($_GET['response_reason_text']); } ?> </div>

<?php include 'includes/rightbar.php'; include 'includes/footer.php'; ?>

Sidebar Code:

<!-- right side bar-->
<div id="sidebar" class="col-md-2" >
  <div class="col-md-12" style="font-size: 75%;">
    <ul id="tabs" class="nav nav-pills" role="toolbar">
          <li role="presentation">
              <a  href="#insta"></a>
          </li>
          <li role="presentation">
              <a  href="#WHoF"></a>
          </li>
          <li role="presentation">
              <a  href="#veggie"></a>
          </li>
          <li role="presentation">
              <a  href="#social"></a>
          </li>
      </ul>
  </div>
<br />
<br />
<div class="tabContent" id="insta">
        <div class="contentText" id="aboutContent">
          <!-- LightWidget WIDGET --> -info removed instagram widget-
        </div>

    </div>
    <div class="tabContent" id="WHoF">
        <div id="whoof">

            <?php
              $sql = "SELECT * from happening ORDER BY post_date desc limit 3 offset 0;";
              $result = $db->query($sql);
              ?>
              <?php while($post = mysqli_fetch_assoc($result)) :  ?>

              <p><b><?=$post['title'];?></b></p>
              <hr>
                <p ><?= $post['entry']; ?></p>
                <hr>

                  <?php  endwhile; ?>

        </div>
    </div>
    <div class="tabContent" id="veggie">
        <div>
            <p><a href="veggie.php">
<img border="0" alt="Veggie_crate" src="../images/header/veg.png" style="width: 100%; height: 100%;" >
</a></p>

        </div>
    </div>

    <div class="tabContent" id="social">
        <div>
           -info removed. facebook widget-
        </div>
    </div>




<script>
$(document).ready(function () {
         var timeInterval, tabCount = 0, currnetIndex = 1;
         tabCount = $('ul#tabs').find('li a').length;
         var tabContentObj = $('.tabContent');
         changeTabIndex();
         timeInterval = setInterval(function () { changeTabIndex(); }, 6 * 1000);

         function changeTabIndex() {
             if (currnetIndex > tabCount) {
                 currnetIndex = 1;
             }
             tabContentObj.hide();
            $('ul#tabs').find('li.selected').removeClass('active');
             $('ul#tabs').find('li.selected').removeClass('selected');
             var currentAncorObj = $('ul#tabs').find('li a').eq(currnetIndex - 1);
             currentAncorObj.parent().addClass('selected');
              currentAncorObj.parent().addClass('active');
             $(currentAncorObj.attr('href')).show();
             currnetIndex++;
         };

         $('#tabs li').mouseenter(function () {
             clearInterval(timeInterval);
         }).mouseleave(function () {
             timeInterval = setInterval(function () { changeTabIndex(); }, 4 * 1000);
         });

         $('#tabs li a').click(function () {
             tabContentObj.hide();
              $('ul#tabs').find('li.selected').removeClass('active');
             $('ul#tabs').find('li.selected').removeClass('selected');
             var currentAncorObj = $(this);
             currnetIndex = $('ul#tabs').find('li a').index($(this)) + 1;
             currentAncorObj.parent().addClass('active');
             currentAncorObj.parent().addClass('selected');
             $(currentAncorObj.attr('href')).show();
             currnetIndex++;

             //return false;
         });
     });
</script>







</div>

Answer 1

Try to generate a log of the user request and see what's going on.

$error_code = uniqid(mt_rand(), true);

file_put_contents(__DIR__ . '/cookie_' . $error_code  . '.log', print_r($_REQUEST, 1), FILE_APPEND);

This will let you see what information is being supplied by the user during cart submission in order to troubleshoot. $_REQUEST includes $_GET, $_POST, and $_COOKIE data.

If you haven't already, you should also test for the existence of the $cart_id instead of expecting it to have been submitted with the user's request.

if (!empty($cart_id) && $_GET['response_code'] == 1) { 
    $trans_id = (int) $_GET['transaction_id'];

   ...//
}else{
   echo "Sorry, an error occurred (Error Code: " . $error_code . "): ".htmlentities($_GET['response_reason_text']);
}