Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

Looping through and inserting DB records

I am creating a script that would read values posted by IPN script from ejunkie . com shopping cart and insert them into my database. My dilemma is how to handle the multiple cart items. In the script attached I hardcoded the possibilities of having 3 cart items, but in reality there could be any number of items. Below is the format from the $_POST where:

 [option_selection1_X] represents the option values from cart #X
so CART 1 option 1 selection is  [option_selection1_1]

CART 2 then would be  [option_selection1_2]

[num_cart_items] => 2 represents that there are 2 items total in this order.

How can I modify my attached PHP code so that it can accept infinite number of cart items instead of just 3?


//CART 1 ITEM    
[item_name1] => site.com ENHANCED listing
    [item_number1] => enhanced
    [mc_gross_1] => 0
    [option_name1_1] => featured
    [option_selection1_1] => yes
    [option_name2_1] => months
    [option_selection2_1] => 12
    [num_cart_items] => 2
    [option_name3_1] => options
    [option_selection3_1] => images: (12) vtours: (3) files: (3)
//CART 2 ITEM
  [item_name2] => site.com STANDARD listing
    [item_number2] => STANDARD
    [mc_gross_1] => 0
    [option_name1_2] => featured
    [option_selection1_2] => no
    [option_name2_2] => months
    [option_selection2_2] => 10
    [num_cart_items] => 2
    [option_name3_2] => options
    [option_selection3_2] => images: (12) vtours: (3) files: (3)
//CART 1
IF (isset($_POST['option_selection1_1'])) {
$uploads1 = $_POST['option_selection1_1'];
$listingdb_id1 = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads1);
$images1 = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads1);
$vtours1 = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads1);
$files1 = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads1);
}
//CART 2
IF (isset($_POST['option_selection1_2'])) {
$uploads2 = $_POST['option_selection1_2'];
$listingdb_id2 = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads2);
$images2 = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads2);
$vtours2 = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads2);
$files2 = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads2);
}
//CART 3
IF (isset($_POST['option_selection1_3'])) {
$uploads3 = $_POST['option_selection1_3'];
$listingdb_id3 = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads3);
$images3 = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads3);
$vtours3 = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads3);
$files3 = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads3);
}

IF (isset($_POST['option_selection3_1']) $featured1 = "yes"; //CART 1
IF (isset($_POST['option_selection3_2']) $featured2 = "yes"; //CART 2
IF (isset($_POST['option_selection3_3']) $featured3 = "yes"; //CART 3

IF (isset($_POST['option_selection2_1']) $term1 = $_POST['option_selection2_1'];
IF (isset($_POST['option_selection2_2']) $term2 = $_POST['option_selection2_2'];
IF (isset($_POST['option_selection2_3']) $term3 = $_POST['option_selection2_3'];

//$term = $term1 + $term2 + $term3;
$pp_txn_id = $_POST['txn_id'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$plan = $_POST['item_name1']." + ".$_POST['item_name2']." + ".$_POST['item_name3'];
$discount_codes = $_POST['discount_codes'];
$num_cart_items = $_POST['num_cart_items'];

$message = $listingdb_id."\r\n".$term."\r\n".$featured."\r\n".$images."\r\n".$plan."\r\n";
mail ("test@goswap.org","post from e-junkie", $message);

IF (isset($_POST['item_name1'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id1','$pp_txn_id','$payment_status','$payment_amount','$featured1','$term1',now(),'$discount_codes','$num_cart_items','images1','vtours1','files1','$plan')";
			$recordSet = $conn->Execute($sql);
}
IF (isset($_POST['item_name2'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id2','$pp_txn_id','$payment_status','$payment_amount','$featured2','$term2',now(),'$discount_codes','$num_cart_items','images2','vtours2','files2','$plan')";
			$recordSet = $conn->Execute($sql);
}

IF (isset($_POST['item_name3'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id3','$pp_txn_id','$payment_status','$payment_amount','$featured3','$term3',now(),'$discount_codes','$num_cart_items','images3','vtours3','files3','$plan')";
			$recordSet = $conn->Execute($sql);
}

Open in new window

0
greenerpastures
Asked:
greenerpastures
  • 3
  • 2
1 Solution
 
SnarflesCommented:
Hi

What you will probably want to do is pass a variable to your script with the amount of carts and then use a for loop to go through it all.

Something along the lines of below. I havent tested it but the logic is there so it should be close.
$pp_txn_id = $_POST['txn_id'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];

$discount_codes = $_POST['discount_codes'];
$num_cart_items = $_POST['num_cart_items'];


if ($num_cart_items != null){

for ( $i = 1; $i <= $num_cart_items; $i++) {


IF (isset($_POST['option_selection1_'.$i])) {
$uploads = $_POST['option_selection1_'.$i];
$listingdb_id = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads);
$images = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads);
$vtours = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads);
$files = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads);
}

IF (isset($_POST['option_selection3_'.$i]) $featured = "yes"; 
IF (isset($_POST['option_selection2_'.$i]) $term = $_POST['option_selection2_'.$i];

IF (isset($_POST['item_name1'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id','$pp_txn_id','$payment_status','$payment_amount','$featured','$term',now(),'$discount_codes','$num_cart_items','images','vtours','files','$plan')";
			$recordSet = $conn->Execute($sql);
}
if ($plan != null){
$plan = $_POST['item_name'.$i];
}
else {
$plan .= " + ".$_POST['item_name'.$i];
}
}
$message = $listingdb_id."\r\n".$term."\r\n".$featured."\r\n".$images."\r\n".$plan."\r\n";
mail ("test@goswap.org","post from e-junkie", $message);


}
else {
	echo "No carts found. Epic Fail.";
}

Open in new window

0
 
Ray PaseurCommented:
Wouldn't this be a lot easier if PayPal could present the information in XML?  Are you listening PayPal?

Sorry for the rant.  Have a look a this code snippet.  Your part starts at line 66.  It is old code and not very pretty, but it demonstrates what you need to know about the arcane PayPal IPN data.

HTH, ~Ray
<?php // RAY_paypal_ipn.php - CUSTOM PayPal IPN PROCESSING

// LOCAL FUNCTIONS AND VARIABLES, DB STUFF, ETC. - NO SESSION HERE - FUNCTION warning_RAY() SENDS AN EMAIL MESSAGE
require_once('_config.php');

// READ THE POST FROM PayPal AND ADD 'cmd'
$req      = 'cmd=_notify-validate';
$postdata = '';
foreach ($_POST as $key => $value)
{
    $postdata .= "\n $key = $value ";              // SAVE THE COLLECTION
    $$key     = trim(stripslashes($value));        // ASSIGN LOCAL VARIABLES
    $value    = urlencode(stripslashes($value));   // ENCODE FOR BOUNCE-BACK
    $req      .= "&$key=$value";                   // APPEND TO VERIFICATION STRING
}

// SET THE HEADERS FOR THE CONFIRMATION POST BACK TO PayPal
$header .= "POST /cgi-bin/webscr HTTP/1.0\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= "Content-Length: " . strlen($req) . "\r\n\r\n";

// OPEN THE HTTP PIPE FOR THE POST BACK TO PayPal
$fp = fsockopen ('www.paypal.com', 80, $errno, $errstr, 30);

// TEST FOR SUCCESSFUL OPENNING OF THE HTTP PIPE
if (!$fp) // HTTP ERROR
{
    warning_RAY("IPN HTTP ERROR", "FSOCKOPEN FAILED \n\n ERRNO=$errno \n\n ERRSTR=$errstr \n\n");
    die();
}

// WITH HTTP OPEN - WRITE HEADER AND REQUEST
fputs ($fp, $header . $req);

// WITH HTTP OPEN - READ PayPal RESPONSE
$paypal_reply   = '';
$paypal_headers = '';
while (!feof($fp))
{
    $paypal_reply    = fgets ($fp, 1024);
    $paypal_headers .= $paypal_reply;
}
fclose ($fp);

// IF THIS IS TRULY A POST FROM PAYPAL, PROCESS ORDER NOTIFICATION
if ($paypal_reply == "VERIFIED")
{
    $errormsg = "";

    // IF PAYMENT IS NOT COMPLETED (MAY BE E-CHECK?)
    if ($payment_status != "Completed") { $errormsg .= "\nE: payment_status"; }

    // IF PAYMENT WAS NOT SENT TO ME?
    $receiver_email = strtolower($receiver_email);
    if ($receiver_email == "me@my.org") { } else { $errormsg .= "\nE: receiver_email"; } // ??? SET EMAIL ADDRESS

    // I AM NOT CHECKING SOME THINGS BECAUSE WE ARE USING ENCRYPTED OR STORED BUY-NOW BUTTONS
    if ($mc_currency != 'USD') { $errormsg .= "\nE: mc_currency"; }

    // CHECK FOR TXN_ID ALREADY PROCESSED - NORMAL FOR E-CHECK
    $sql = "SELECT txn_id FROM PAYPAL_ORDER_LOG WHERE txn_id = \"$txn_id\" ";
    if (!$result = mysql_query($sql, $db_connection)) { fatal_query_error($sql); }
    $num_rows = mysql_num_rows($result);
    if ($num_rows  > 0) { $errormsg .= "\nE: Transaction id $txn_id already processed $num_rows time(s)"; }

    // GENERATE ARRAYS OF PURCHASE DATA FROM THE WEIRD PAYPAL VARIABLE NAMES
    // ACHTUNG: NO POSITION ZERO IN THESE ARRAYS
    $w_item_number = array();
    $w_quantity    = array();
    $w_mc_gross    = array();
    $w_item_name   = array();

    // IF TRANSACTION TYPE IS CART, MAY BE MULTIPLE ITEMS IN DIFFERENT QUANTITIES - LOAD ARRAYS
    // NOTE NOTE NOTE NO POSITION ZERO IN THESE ARRAYS
    if ($txn_type == "cart") {
        while ($num_cart_items > 0) {
            $proxy    = "item_number" . "$num_cart_items";
            $w_item_number[$num_cart_items] = $$proxy;

            $proxy    = "quantity" . "$num_cart_items";
            $w_quantity[$num_cart_items] = $$proxy;

            $proxy    = "mc_gross_" . "$num_cart_items";
            $w_mc_gross[$num_cart_items] = $$proxy;

            $proxy    = "item_name" . "$num_cart_items";
            $w_item_name[$num_cart_items] = $$proxy;

            $num_cart_items--;
        }
    } else {
        // NOT A CART - SINGLETON ITEM ONLY - NORMALIZE INTO ARRAY FOR USE WITH ITERATOR
        // NOTE NOTE NOTE NO POSITION ZERO IN THESE ARRAYS
        $w_item_number[1] = $item_number;
        $w_quantity[1]    = $quantity;
        $w_mc_gross[1]    = $mc_gross;
        $w_item_name[1]   = $item_name;
    }

// *****************************************************
// ACTIVATE THIS CODE BLOCK TO SEE WHAT IS IN THE ORDER ARRAYS
//
//    ob_start();
//    echo "<pre>\n";
//    echo "\nW_ITEM_NUMBER"; var_dump($w_item_number);
//    echo "\nW_QUANTITY";    var_dump($w_quantity);
//    echo "\nW_MC_GROSS";    var_dump($w_mc_gross);
//    echo "\nW_ITEM_NAME";   var_dump($w_item_name);
//    $foo    = ob_get_contents();
//    ob_end_clean();
//    warning_RAY("IPN VARDUMPS", "$foo \n\n");
// *****************************************************


    // ITERATE OVER THE ARRAYS
    $kount = 0; // NO POSITION ZERO IN THESE ARRAYS
    while ($kount < count($w_item_number))
    {
        $kount++; // BUMP BEFORE WORKING
        $my_item_number = $w_item_number[$kount];
        $my_quantity    = $w_quantity[$kount];
        $my_mc_gross    = $w_mc_gross[$kount];
        $my_item_name   = $w_item_name[$kount];

        //
        // ??? PROCESS THE ORDERS USING YOUR BUSINESS LOGIC
        //

    } // END ITERATION OVER THE ORDERS

    // END OF NORMAL PAYPAL IPN PROCESSING
    die();
}

// NOT NORMAL PROCESSING
// LOG INVALID POSTS FOR MANUAL INVESTIGATION AND INTERVENTION
if ($paypal_reply == "INVALID")
{
    warning_RAY("IPN INVALID", "IPN REPLY $paypal_headers \n\n$errormsg \n\nPOST DATA FOLLOWS: $postdata \n\n");
    die();
}

// OTHERWISE, PayPal RETURNED BAD DATA (OR INTERNET HTTP ERRORS OR TIMEOUT)
warning_RAY("IPN REPLY UNKNOWN", "IPN REPLY $paypal_headers \n\n$errormsg \n\nPOST DATA FOLLOWS: $postdata \n\n");
die();

Open in new window

0
 
greenerpasturesAuthor Commented:
Snarfles,
1. Thanks that almost works, for some reason though it inserts the values 2 times in the table: ejunkie_orders, duplicating the entries, while 1 time in the ejunkie_uploads which is the way it should be.

2. I also has terrible time with this simple statement:
$sql = "SELECT * FROM 'ejunkie_uploads' WHERE listingdb_id = '$listingdb_id' ";
Why is it only accepting  it only these "   '  " quotes around the 'ejunkie_uploads' when copied from PhpMyadmin and not the normal single quotes "  '  " ? THe statement just would not work until I copied it over.
$pp_txn_id = $_POST['txn_id'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$discount_codes = $_POST['discount_codes'];
$num_cart_items = $_POST['num_cart_items'];

if ($num_cart_items != null){

for ( $i = 1; $i <= $num_cart_items; $i++) {

IF (isset($_POST['option_selection1_'.$i])) {
$uploads = $_POST['option_selection1_'.$i];
$listingdb_id = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads);
$images = preg_replace("/.*images: \((\d*)\).*/", "\\1", $uploads);
$vtours = preg_replace("/.*vtours: \((\d*)\).*/", "\\1", $uploads);
$files = preg_replace("/.*files: \((\d*)\).*/", "\\1", $uploads);
}

IF (isset($_POST['option_selection3_'.$i])) $featured = "yes"; //CART 1

IF (isset($_POST['option_selection2_'.$i])) $term = $_POST['option_selection2_'.$i];
$plan = $_POST['item_name'.$i];

$message = $listingdb_id."\r\n".$term."\r\n".$featured."\r\n".$images."\r\n".$vtours."\r\n".$files."\r\n".$plan."\r\n";
mail ("test@site.org","BEFORE processing", $message);

IF (isset($_POST['item_name'.$i])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,plan) VALUES ('$listingdb_id','$pp_txn_id','$payment_status','$payment_amount','$featured','$term',now(),'$discount_codes','$num_cart_items','$plan')";
			$recordSet = $conn->Execute($sql);
}

//UPLOADS TABLE
IF ($images != "" && $listingdb_id != "") {
$sql = "SELECT * FROM 'ejunkie_uploads' WHERE listingdb_id = '$listingdb_id' ";
$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$sql = "INSERT INTO `ejunkie_uploads` (listingdb_id,images,vtours,files) VALUES ('$listingdb_id','$images','$vtours','$files')";
$recordSet = $conn->Execute($sql);
} else {
mysql_query("UPDATE 'ejunkie_uploads' SET images = ".$recordSet->fields['images'] + $images." vtours = ".$recordSet->fields['vtours'] + $vtours.", files = ".$recordSet->fields['files'] + $files. "WHERE listingdb_id = ".$listingdb_id);
}


} // END OF UPLOADS

//$expiration = date("Y-m-d", mktime(0, 0, 0, date("m")+$term, date("d"),  date("Y")));

//IF(isnumeric($listingdb_id)) {

//mysql_query("UPDATE ".$config['table_prefix']."listingsdb SET listingsdb_expiration = '$expiration', listingsdb_featured = '$featured', listingsdb_active = 'yes' WHERE listingsdb_id = '$listingdb_id'");
//}


} //end of FOR 
} // END OF LOOP
else {
	echo "No carts found. Epic Fail.";
}

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
greenerpasturesAuthor Commented:
I also cannot get this statement to work and update the table when I pass in the existing listingdb_id that already is in this table:

} else {
mysql_query("UPDATE 'ejunkie_uploads' SET images = ".$recordSet->fields['images'] + $images.", vtours = ".$recordSet->fields['vtours'] + $vtours.", files = ".$recordSet->fields['files'] + $files. "WHERE listingdb_id = ".$listingdb_id);
}

I suspect something is wrong with this statement: if ($recordSet2 === false)
0
 
SnarflesCommented:
Hi

I dont think you need to have quotes around the table names in your queries.

I can't see why it would duplicate the entries in ejunkie_orders unless the $num_cart_items variable is being doubled somehow. Can you echo $num_cart_items for me and see that it only says 3?

I suspect something is wrong with this statement: if ($recordSet2 === false) <-- $recordSet2 isnt being set anywhere...

Hope that helps a little.

Cheers

Luke
0
 
greenerpasturesAuthor Commented:
Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now