Tracking and storing invisible image data in PHP/MySQL

What script should we have in the checkout_success page?

[wildbest]

We use a third party payment gateway and are allowed to place a short script to track our affiliate sales. This is done by placing an invisible image in the "Thank You" (checkout_success) page that is displayed to the customer after the payment is processed. It should look something like this:

<img src="https://www.ourdomain.com/scripts/sale.php?ID=<AFFILATE_ID>&ORDERNUMBER=%ORDERNUMBER%&AMT=%ORDERTOTAL% height="1" width="1">

My question is what script should we have in the sale.php page, so that the invisible image is loaded to the checkout_success page and data is collected and stored in SQL for further processing. Is there any free php script that will do the job? Or should I better post this on the E-commerce WM forum?

[coopster]

You could have the script do anything you wanted or simply return an image -- the url is going to show up in your server logs too. So, it really depends what you want to track from this hit.

[wildbest]

Thank you, coopster. I want to pass the 1x1 invisible image to the "checkout-success" page, get the values of AFFILATE_ID, ORDERNUMBER, and ORDERTOTAL. All these values should be stored in a SQL database for further analysis. I think it is clear enough. What do other people use as a solution for such a task?

[coopster]

I suppose everybody does things a little differently. This would not be too hard to do though. The values you want are in the $_GET superglobal so you can check to see if they exist and if so, use them. If you want to do any more edit checking you could do so. Next, connect to your database and INSERT the new row. Lastly, send the image file back.

<?php 
// Get the data being sent to us: 
$AFFILATE_ID = (isset [php.net]($_GET [php.net]['AFFILATE_ID'])) ? $_GET['AFFILATE_ID'] : ''; 
$ORDERNUMBER = (isset($_GET['ORDERNUMBER'])) ? $_GET['ORDERNUMBER'] : ''; 
$ORDERTOTAL = (isset($_GET['ORDERTOTAL'])) ? $_GET['ORDERTOTAL'] : ''; 
// Connect to database: 
// Note, most people keep this section outside the public realm and use  
// a PHP include [php.net] to make the connection: 
$db_server = 'localhost'; 
$db_user  = 'db_user'; 
$db_pwd  = 'db_pass'; 
$db_name  = 'db_name'; 
if (mysql_connect [php.net]($db_server, $db_user, $db_pwd)) { 
 $AFFILATE_ID = mysql_real_escape_string [php.net]($AFFILATE_ID); 
 $ORDERNUMBER = mysql_real_escape_string [php.net]($ORDERNUMBER ); 
 $ORDERTOTAL = mysql_real_escape_string [php.net]($ORDERTOTAL); 
 $query = "INSERT INTO trackingTable (affID, ordNbr, ordTtl) VALUES ('$AFFILATE_ID','$ORDERNUMBER','$ORDERTOTAL')"; 
 mysql_query [php.net]($query); 
} 
// Send the image back: 
header [php.net]("Content-type: image/png"); 
readfile [php.net]('mySinglePixelImageInThisDirectory.png'); 
exit;  
?>

I just threw this down off the top of my head. It is a good start though. You could tweak it from here.

[wildbest]

This is very basic indeed. I have found a script named phphits that has the reporting functions I need, but will have to do some additional coding to include the invisible image parameter data added to the reports. Thanks anyway.