Solved

Looping through and inserting DB records

Posted on 2010-08-15
6
442 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

744 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

11 Experts available now in Live!

Get 1:1 Help Now