Solved

Looping through and inserting DB records

Posted on 2010-08-15
6
443 Views
Last Modified: 2013-12-13
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
Comment
Question by:greenerpastures
  • 3
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Snarfles earned 500 total points
ID: 33440801
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33440882
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
 

Author Comment

by:greenerpastures
ID: 33442355
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:greenerpastures
ID: 33442499
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
 
LVL 9

Expert Comment

by:Snarfles
ID: 33442989
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
 

Author Closing Comment

by:greenerpastures
ID: 33450212
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now