Ajax onSubmit call to write an SQL flag in the database for a particular product

Hey guys. I've spent the better part of 3 weeks overhauling the Auction Product module for both cosmetic and functionality purposes, I plan on releasing it into the wild here when I'm finished.

One of the functional aspects I require is the auction actually ending itself when someone chooses the buy it now option:

here is the codes from the tpl file ..includes/templates/custom/templates/tpl_product_auction_info_display.php :

[/!-- bof winning bid thingy -->
<?php if (($difference == '0')[B] || ($purchased_buy_now == '1' )) [/B]{ ?> 

<!--beginning of Product Display code-->
<div class="AuctionEnded"><?php echo '<img src="../images/auction_now_closed.png">'; ?></div>
<?php
if ($purchased_buy_now == '1' && $auction_current_bid < $products_buynow_price && $products_buynow_price != '0') {
    echo '<div class="AuctionEndedBuyItNow">'.AUCTION_ENDED_BY_BUY_IT_NOW.'</div>'; }  ?>
 <?php 

The $purchased_buy_now variable is defined in the ../includes/modules/pages/product_auction_info/main_template_vars.php file like so:

   $sql = "select p.products_id, pd.products_name,
                  pd.products_description, p.products_model,
                  p.products_quantity, p.products_image, p.products_youtube, p.products_youtube2,
                  pd.products_url, p.products_price,
                  p.products_tax_class_id, p.products_date_added,
                  p.products_date_available, p.manufacturers_id, p.products_quantity,
                  p.products_weight, p.products_priced_by_attribute, p.product_is_free,
                  p.products_qty_box_status,
                  p.products_quantity_order_max,
                  p.products_discount_type, p.products_discount_type_from, p.products_sort_order, p.products_price_sorter, 
                  pae.bid_expire_date, pae.bid_reserve_price, pae.bid_expire_hour, pae.bid_start_price, 
                  pae.bid_minimum_increase, pae.bid_buynow_price, [B]pae.purchased_buy_now
[/B]           from   " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCT_AUCTION_EXTRA . " pae  
           where  p.products_status = '1'
           and    p.products_id = '" . (int)$_GET['products_id'] . "'
           and    pd.products_id = p.products_id and p.products_id = pae.products_id 
           and    pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";

    $product_info = $db->Execute($sql);

    $products_price_sorter = $product_info->fields['products_price_sorter'];


    $sql2 = "select bid_id, products_id, customers_bid, customers_id
             from  " . TABLE_PRODUCTS_AUCTION . "
             where products_id = '" . (int)$_GET['products_id'] . "'
             order by bid_id DESC
             limit 1";
    $auction_info = $db->Execute($sql2);
    
    $auction_current_bid = ($auction_info->fields['customers_bid'] >= 0.1) ? $auction_info->fields['customers_bid'] : $product_info->fields['bid_start_price'];
    $customer_high_bidder = $auction_info->fields['customers_id'];
    if ($customer_high_bidder =='') $customer_high_bidder = 'x';
    $products_buynow_price = $product_info->fields['bid_buynow_price'];
    $products_reserve_price = $product_info->fields['bid_reserve_price'];
    $bid_expire_date = $product_info->fields['bid_expire_date'];
    $bid_expire_hour = $product_info->fields['bid_expire_hour'];
[B]    $purchased_buy_now = $product_info->fields['purchased_buy_now'];
[/B]

So far so good. I know it works because I tried another way determining the $_Session[cart_quantity] > 0 in the header and it worked fine but it only flagged the variable if the person refreshed the page. Since Zen cart takes you right to the shopping cart after addinga product this method is no good. Tried another method checking for total quantity in stock as 0 and that worked fine but there's that overlap between when a person adds the product to the cart and actually checks out. Again, No good.

So.. I've come to the conclusion that I need an AJAX call to flag the purchased_buy_now field in the database when the person clicks the buy it now and adds the product to the cart.

Here's what I've come up with and it's not working. New to Ajax and I'm not even sure how to test it :(

in the tpl_product_auction_info_display file I threw a hidden field with an id of buy_now_flagger into the form passing a value of 1 and a call to function I wrote in the jsmain called ajax_buy_now_flager:

<?php echo zen_draw_form('cart_quantity', zen_href_link(zen_get_info_page($_GET['products_id']), zen_get_all_get_params(array('action')) . 'action=add_product&mod=auction'), 'post', 'onsubmit="return ajax_buy_now_flagger();"','enctype="multipart/form-data"').zen_draw_hidden_field('action', 'add_product').zen_draw_hidden_field('buy_now_flagger','1', 'id="buy_now_flagger"'); ?>
<!--eof Form start-->

Here's the javascript function:

function ajax_buy_now_flagger()  {  
var buy_now_flagger = document.getElementById("buy_now_flagger").value;  
var xhr;  
 if (window.XMLHttpRequest) { // Mozilla, Safari, ...  
    xhr = new XMLHttpRequest();  
} else if (window.ActiveXObject) { // IE 8 and older  
    xhr = new ActiveXObject("Microsoft.XMLHTTP");  
}  
var data = buy_now_flagger;  
     xhr.open("POST", "..includes/modules/pages/product_auction_info/buy_now_flag_php.php", true);   
     xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");                    
     xhr.send(data);  
     alert(buy_now_flagger);
} 

The Alert IS passing a value of 1. So far so good.

Here's my buy_now_flag_php.php that I included in the same directory as the jsmain and the header. This is where I'm getting lost because its not tripping the flag in the SQL database.

<?php
 $buy_now_update_flag= $_POST[buy_now_flagger];

echo "<script type='text/javascript'>alert('$buy_now_update_flag');</script>";
if ($buy_now_update_flag == '1') {

   $sql = "update " . TABLE_PRODUCT_AUCTION_EXTRA . " set purchased_buy_now = '" .  $buy_now_update_flag  . "' where products_id = '" . (int)$_GET['products_id'] . "'  LIMIT 1 ";
    $db->Execute($sql);
}
?>

Feels like I'm missing some info at the top of the PHP file to connect it somehow or I've botched the path in the javascript function. JavaScript alert is not working here. I don't know if this page is even being loaded. The actual sql write worked when I used it in my header.php before so I know that's not it.

Just need someone to give me a push in the right direction here.....

I solved this on my own but I'm not sure this is the correct or most secure way.

I added an "id=products_id" to my products_id field in the form, passing off the value of the product id # from the database (one reason my above php was doomed to failure before).

I changed my javascript function to this:

function ajax_buy_now_flagger()  {  
var buy_now_flagger_product_id = document.getElementById("products_id").value;  
var xhr;  
 if (window.XMLHttpRequest) { // Mozilla, Safari, ...  
    xhr = new XMLHttpRequest();  
} else if (window.ActiveXObject) { // IE 8 and older  
    xhr = new ActiveXObject("Microsoft.XMLHTTP");  
}  
var data = "products_id=" + buy_now_flagger_product_id;  
     xhr.open("POST", 'buy_now_flag_php.php', true);   
     xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");                    
     xhr.send(data);  
     alert(buy_now_flagger_product_id);
} 

and my buy_now_flag_php.php to this:

<?php
require('includes/application_top.php');

$buy_now_update_flag_product_id = $_POST['products_id'];

if (!empty($buy_now_update_flag_product_id)) {
   $sql = "update " . TABLE_PRODUCT_AUCTION_EXTRA . " set purchased_buy_now = '1' where products_id = '" .$buy_now_update_flag_product_id. "' LIMIT 1 ";
   
   $db->Execute($sql);
} else 
echo 'ILLEGAL ACCESS!!'

?>

By adding
require('includes/application_top.php');

to the top of my buy_now_flag_php.php and moving it the root I as able to init the $db

Is this safe/secure/optimal? How could I change this so that I can have buy_now_flag_php.php nested in the includes/modules/pages/product_auction_info folder but still init the $db? From what I understand here: http://stackoverflow.com/questions/10956918/how-to-make-queries-to-db-in-custom-script-using-zen-cart the require('includes/application_top.php'); needs to be called from the root because of relative paths.